How to Check Whether a Role Exists in PostgreSQL

Check PostgreSQL’s pg_roles catalog with an EXISTS query to get a clear true or false answer for an exact role name. If the role exists but an application still cannot connect, also check whether it has the LOGIN attribute and whether you are querying the same PostgreSQL server the application uses.

How PostgreSQL Roles Work

PostgreSQL uses the term role for both login accounts and group-style permission roles. A role can own objects, receive privileges, and be a member of other roles. It does not automatically have permission to connect.

Roles belong to the PostgreSQL server cluster, not to one individual database. If app_user exists on a server, it is visible from every database on that server, although it may not have permission to use every database or schema.

Role names are case-sensitive only when created with double quotes:

CREATE ROLE AppUser;

The statement above creates the lowercase role appuser, because PostgreSQL folds unquoted identifiers to lowercase. This creates a distinct mixed-case role:

CREATE ROLE "AppUser";

When checking a role, search for the exact stored name.

How to Check Whether a Role Exists

Use this table to choose the quickest check for your situation.

Need Best method Result
A reliable true or false result for a script Query pg_roles with EXISTS One Boolean value
A quick interactive lookup in psql Use \du Role list and attributes
Diagnose a connection failure Query rolcanlogin and rolconnlimit Login and connection-limit details

Step 1: Connect to the Correct PostgreSQL Server

Connect with an administrator role or another role that can already log in to the PostgreSQL instance. Use the same host and port configured in the failing application whenever possible.

psql -h <host> -p <port> -U <admin_role> -d <database_name>

Once connected, verify the database and role for the current session:

SELECT current_database(), current_user;

If you received FATAL: role "name" does not exist while running psql, do not try to connect as that missing role. Connect as a known administrator role instead. Running psql without -U commonly makes PostgreSQL try your operating-system username as the database role name.

Step 2: Run an Exact Role Lookup

Run this query, replacing app_user with the role name you need to check:

SELECT EXISTS (
  SELECT 1
  FROM pg_roles
  WHERE rolname = 'app_user'
) AS role_exists;

The result is unambiguous:

 role_exists
-------------
 t

A result of t means the role exists on this PostgreSQL server. A result of f means it does not.

For a quick interactive check in psql, use:

\du app_user

psql displays matching roles and their attributes. Use the SQL query when you need an exact Boolean result in a migration, deployment script, health check, or application tool.

For application code, bind the role name as a query parameter rather than building SQL by concatenating user input.

Step 3: Check Whether the Existing Role Can Log In

If the role exists, inspect the attributes that matter for a connection:

SELECT
  rolname,
  rolcanlogin,
  rolsuper,
  rolcreaterole,
  rolcreatedb,
  rolconnlimit,
  rolvaliduntil
FROM pg_roles
WHERE rolname = 'app_user';

Pay particular attention to these fields:

Field Meaning for Troubleshooting
rolcanlogin Must be true for an initial psql or application connection
rolconnlimit -1 is the default and means no concurrent-connection limit
rolvaliduntil A password-based login can fail after this timestamp
rolcreaterole Allows a non-superuser to create and manage permitted roles
rolsuper Indicates superuser status; do not grant this merely to fix a login problem

A successful EXISTS result proves the name is present, not that it can be used to open a connection.

Only roles with LOGIN can be used as the initial role for a database connection, as described in PostgreSQL’s role attributes documentation. A non-login role is often intentional: teams commonly use such roles as permission groups and grant them to login roles.

Fix a Missing or Non-Login Role

Make changes only after confirming that the role name, server, and intended permissions are correct. Creating a role with the wrong name or on the wrong PostgreSQL instance will not fix an application connection error.

Create a Missing Login Role

To create a login role without a database password:

CREATE ROLE app_user LOGIN;

PostgreSQL also supports this equivalent shortcut:

CREATE USER app_user;

CREATE USER is effectively CREATE ROLE with LOGIN included by default.

If the role must authenticate with a PostgreSQL password, include a password clause:

CREATE ROLE app_user LOGIN PASSWORD 'replace-with-a-secret';

Use a real secret-management or deployment mechanism for production passwords. Do not paste a production password into shared terminal history, source code, tickets, or logs. A PostgreSQL password is separate from the operating-system password, and it matters only when the configured client-authentication method requires one.

Creating a role only creates the identity. It does not automatically grant access to a database, schema, table, or application data.

Enable Login for an Existing Role

If the role exists and rolcanlogin is false, enable login for a role you are authorized to administer:

ALTER ROLE app_user LOGIN;

Do not enable login on a shared group role unless that is the intended design. A safer pattern is often to create a separate login role, then grant it membership in the group role that holds permissions.

Set a Connection Limit When Needed

A role’s connection limit controls how many concurrent connections it may open. The default is -1, which means no limit.

Use this creation syntax when your connection policy requires a cap; replace <integer> with the intended limit before running it:

CREATE ROLE app_user LOGIN CONNECTION LIMIT <integer>;

A connection limit is not a substitute for a connection pool. If an application opens many short-lived connections, fix the application or pool configuration as well.

Resolve Permission Errors While Creating Roles

A non-superuser generally needs the CREATEROLE attribute to create roles. An administrator can create a role intended to hold that capability:

CREATE ROLE provisioning_admin CREATEROLE;

CREATEROLE does not allow a role to create superusers or take control of existing superuser roles. If CREATE ROLE or ALTER ROLE returns a permission error, use an approved administrator account rather than escalating the application account to superuser.

Diagnose “Role Does Not Exist” Errors

A missing-role error can be caused by a genuinely absent role, but it can also mean the client is connecting somewhere other than you expect.

Verify the Host, Port, and Cluster

Because roles are cluster-wide, checking the wrong PostgreSQL server is a common source of confusion. A role may exist in local development but not in a container, test environment, managed instance, or different Docker volume.

Compare the failing application’s connection settings with the psql connection you used:

  • Host name or IP address
  • Port
  • Database name
  • User or role name
  • SSL and connection-routing settings, if applicable

Verify that the application’s configured user or role name is the PostgreSQL role you checked. A role created in SQL Server does not create a corresponding PostgreSQL role; the two database systems maintain separate accounts.

Check Letter Case and Quoting

If an administrator created "AppUser" with double quotes, PostgreSQL requires that exact capitalization. A connection attempt as appuser is a request for a different role.

Avoid quoted mixed-case role names unless you have a strong compatibility reason. Lowercase names such as app_user are simpler to use in SQL, environment variables, connection strings, and shell commands.

Separate Authentication Problems From Role Problems

If the role exists and has login permission, the next failure may be authentication rather than role lookup. Typical causes include an incorrect password, an expired password validity setting, an authentication rule that rejects the client, or a client certificate mapping issue.

Do not assume that an existing operating-system account proves that a PostgreSQL role exists. PostgreSQL can use the operating-system username as a default connection name, but it maintains its own role catalog.

Confirm the Fix From the Application Path

After creating or changing a role, test the same route your application uses: same server, port, database, and role name.

psql -h <host> -p <port> -U <role_name> -d <database_name> -c "SELECT current_user;"

A successful command returns the authenticated PostgreSQL role. If it fails, preserve the full error text and verify the target server before making further permission changes.

Use this final checklist:

  • The pg_roles query returns true for the exact role name.
  • The application and your test connect to the same PostgreSQL server.
  • The intended connection role has rolcanlogin = true.
  • Password and client-authentication settings match the connection method.
  • The role has only the database, schema, and object privileges the application needs.
  • The application can complete its actual database operation after connecting.

Next, connect to the same PostgreSQL server used by the failing client and run the pg_roles EXISTS query with the exact role name from the error message.

Frequently Asked Questions

How do I check whether a PostgreSQL user exists?

Check pg_roles, because PostgreSQL users are roles with the LOGIN attribute. Run an EXISTS query against pg_roles using the exact role name, or use \du role_name in psql for an interactive lookup.

Can a PostgreSQL role exist without being able to log in?

Yes. A role can exist solely as a group for shared permissions. Only a role with LOGIN can be the initial identity for a psql or application connection; CREATE USER name creates a login role by default.

Why does psql say my role does not exist?

psql may be trying your operating-system username when you do not specify -U. Connect explicitly with psql -U <role_name>, and verify that the host and port point to the PostgreSQL server where you checked pg_roles.

What does a connection limit of -1 mean in PostgreSQL?

A role connection limit of -1 means no concurrent-connection limit is set for that role. For non-superuser roles, a nonnegative connection-limit integer sets the maximum number of simultaneous connections allowed for that role.

Is there a built-in read-only role in PostgreSQL?

No. PostgreSQL does not provide one universal built-in role named read_only. A role with that name may be a team-created role, so check it in pg_roles; it may intentionally lack LOGIN and exist only to receive read privileges.

Why does CREATE ROLE fail in PostgreSQL?

CREATE ROLE can fail when the account running it does not have authority to create roles. A superuser bypasses that permission check; otherwise, the executing role needs CREATEROLE. That attribute still does not allow it to create superuser roles.