psql Show All Users: Why \du Shows Roles, Not Users, and the Three Queries That Actually Work

Q: What is the difference between \du and \du+ in psql?

\du lists roles with the standard attributes. \du+ lists roles with the extended attributes (description, member-of, etc.).

Q: How do I list only login-capable roles?

SELECT rolname FROM pg_roles WHERE rolcanlogin = true ORDER BY rolname;. The rolcanlogin column is true for users (vs. groups).

Q: How do I list roles with non-expiring passwords?

SELECT rolname FROM pg_roles WHERE rolvaliduntil IS NULL AND rolcanlogin = true. Roles with rolvaliduntil IS NULL have passwords that never expire. The team should set an expiration for every role that has a password.

Q: Can a role have LOGIN but no password?

Yes. A role with LOGIN but no password can authenticate via peer authentication, certificate authentication, or other methods, but cannot authenticate via password.

Q: How do I see the privileges a role has?

SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'app_user'.

psql’s \du meta-command shows roles, not users. The distinction is the source of most confused Stack Overflow answers about “listing users in Postgres.” The right answer depends on what the searcher means: list every login-capable role, list every role with a password, or list every role the current user can see. The three answers are different queries, and the right query depends on the question.

This post is the unblocker. The first half is what \du actually does and why the role-vs-user confusion matters. The second half is the three queries that work on every Postgres version, plus the role-attribute trap that catches teams that interpret the output incorrectly.

The interesting thing about “psql show all users” is that the question is almost always a follow-up to a permission or audit question. The team is debugging a connection issue, an audit log, or a privilege escalation. The team that picks the right query is the team that gets the answer. The team that picks \du and assumes the output is “all users” is the team that misses the system roles, the missing-logins, and the case-sensitivity quirks.

$psql Show All Users: Why \\du Shows Roles, Not Users, and the Three Queries That Actually Work$

The direct answer
What \du actually does
The three queries that work
Query 1: every login-capable role
Query 2: every role with a password
Query 3: every role the current user can see
The role-attribute trap
The case-sensitivity and naming trap
The opinion this post is built on
FAQ

The direct answer

The most useful query, which works on every Postgres version and answers most “show all users” questions:

SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole
FROM pg_roles
ORDER BY rolname;

For the psql meta-command version:

\du

The output is a list of roles, not a list of users. The distinction matters, and the rest of the post is the distinction.

What \du actually does

\du is a psql meta-command. When the team runs \du, psql sends a query to the server and formats the result. The query is roughly:

SELECT r.rolname, r.rolsuper, r.rolinherit,
       r.rolcreaterole, r.rolcreatedb,
       r.rolcanlogin, r.rolreplication, r.rolbypassrls,
       ARRAY(
         SELECT b.rolname
         FROM pg_catalog.pg_auth_members m
         JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
         WHERE m.member = r.oid
       ) AS member_of
FROM pg_catalog.pg_roles r
WHERE r.rolname <> 'pg_signal_backend'
ORDER BY 1;

The query selects from pg_roles, the system catalog of all roles. The output includes the role name, the superuser flag, the inherit flag, the create-role flag, the create-database flag, the login flag, and the list of roles the role is a member of. The output does not include a distinction between “user” and “role” — because there is no distinction.

The reason the answer is confusing: Postgres inherited the CREATE USER and CREATE GROUP syntax from earlier versions. In Postgres 8.1 and later, both CREATE USER and CREATE GROUP create roles, with LOGIN and NOLOGIN as the only difference. The legacy syntax is preserved for compatibility, but the underlying object is the same: a role.

The trap: a team that runs \du, sees the output, and assumes the list is “all users” is the team that misses the system roles (pg_signal_backend, pg_read_server_files, etc.), the inherited roles, and the case where a role has LOGIN but no password (and therefore cannot actually log in).

The three queries that work

The three queries that answer most “show all users” questions:

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolvaliduntil
FROM pg_roles
WHERE rolcanlogin = true
ORDER BY rolname;

This is the closest equivalent to “show all users.” It returns every role that can log in, with the role’s superuser, create-database, create-role flags, and the password expiration date.

The output looks like:

 rolname  | rolsuper | rolcreatedb | rolcreaterole | rolvaliduntil
----------+----------+-------------+---------------+---------------
 app_user | f        | f           | f             | 2027-06-17
 postgres | t        | t           | t             |

The postgres role is the superuser, created at cluster init. The app_user is the application’s user, with a password that expires in 365 days (or whatever the team set).

This is the right query for most “list users” requests. The query is portable, the output is clear, and the result is what the team expects.

Query 2: every role with a password

SELECT rolname, rolpassword IS NOT NULL AS has_password, rolvaliduntil
FROM pg_roles
WHERE rolcanlogin = true
ORDER BY rolname;

The query above (1) shows every login-capable role, but it does not show whether the role has a password. A role can have LOGIN but no password — in which case the role cannot authenticate using a password, only via peer authentication or other methods.

The query above (2) adds the has_password column. The output is the same as (1) plus a boolean: does this role have a password set? A role with no password can still log in via peer authentication (same OS user) or via certificate authentication, but cannot log in via password.

This is the right query for an audit. The team is checking which roles have passwords, which roles’ passwords expire, and which roles have no password (and therefore rely on a different authentication method).

Query 3: every role the current user can see

SELECT rolname, rolsuper, rolcanlogin
FROM pg_roles
ORDER BY rolname;

A superuser sees every role. A non-superuser sees only the roles they are a member of, plus the public role. The query above (3) shows what the current user can see.

For a non-superuser, the output is limited. The team that runs the query as a non-superuser sees a smaller list than the team that runs it as a superuser. The difference is the visibility the current user has.

The right answer for an audit is to run the query as a superuser. The right answer for “what can I see” is to run the query as the current user and accept the limited view.

The role-attribute trap

The role attributes that catch teams:

rolsuper. A role with rolsuper = true is a superuser. The role can do anything in the cluster. The team that creates a “user” and grants SUPERUSER has created a security hole. The team that audits the role list should look for rolsuper = true and challenge every entry.
rolcreatedb. A role with rolcreatedb = true can create new databases. The team’s application does not need this. The team’s migration user might. The audit should check the privilege is justified.
rolcreaterole. A role with rolcreaterole = true can create new roles. The team’s application does not need this. The team’s DBA might. The audit should check the privilege is justified.
rolvaliduntil. A role with rolvaliduntil set has a password that expires. The team that sets the expiration is the team that forces password rotation. The team that does not set the expiration has passwords that never expire, which is a security hole.

The audit query:

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolvaliduntil
FROM pg_roles
WHERE rolsuper = true
   OR rolcreatedb = true
   OR rolcreaterole = true
   OR rolvaliduntil IS NULL
ORDER BY rolname;

The output is the list of roles that have elevated privileges or non-expiring passwords. The team audits each one.

The case-sensitivity and naming trap

Postgres role names are case-sensitive when quoted, case-insensitive when unquoted:

CREATE USER App_User ... and CREATE USER app_user ... create different roles.
CREATE USER "App_User" ... and CREATE USER "app_user" ... create different roles.
\du shows the names as stored. The team that runs \du and assumes case-insensitive matching is the team that is surprised when the connection string does not match.

The convention: always use lowercase role names, never quote them. The team that follows the convention does not have case-sensitivity bugs. The team that mixes cases has bugs that surface at the worst possible time.

The connection string:

postgresql://app_user:***@db.internal:5432/app_db

The role name in the URI is app_user. Postgres parses the URI, extracts the role name, and uses it to authenticate. The role name in the URI must match the role name in the database, exactly, including case.

The trap: the team creates App_User in the database. The team uses app_user in the URI. The connection fails with “role does not exist.” The team is confused because the role “exists” (they just created it). The role exists; it is just a different role.

The fix: always use lowercase. The convention prevents the bug.

The opinion this post is built on

\du shows roles, not users. The distinction is the source of most confused answers to “list users in Postgres.” The right answer depends on what the team is actually asking: list every login-capable role, list every role with a password, or list every role the current user can see. The three queries cover the cases. The audit query covers the security review.

The deeper discipline: the role list is the access control list. The team that audits the role list quarterly is the team that catches the role with SUPERUSER that should not have it, the role with rolvaliduntil IS NULL that should have an expiration, the role with rolcreatedb = true that should not. The query is one line. The audit is the discipline.

The platform is the multiplier. A platform with a managed Postgres that surfaces the role list, the role attributes, and the password expiration is a platform where the audit is a dashboard, not a query. A platform that does not is a platform where the team runs the query by hand and forgets to run it next quarter.

The RunxBuild platform handles the managed Postgres, the role provisioning, and the audit trail. The team focuses on the application.

For a sanity check on the cost of the managed Postgres, the hosting cost calculator gives a real number to compare against.

FAQ

What is the difference between `\du` and `\du+` in psql?

\du lists roles with the standard attributes. \du+ lists roles with the extended attributes (description, member-of, etc.). The extended version is more verbose; the standard version is enough for most queries.

Why does `\du` show roles I did not create?

Because Postgres creates system roles at cluster init time (postgres, plus the read-only roles in newer versions). These are the superuser and the read-only monitoring roles. The team that filters the output can exclude them with a WHERE rolname NOT LIKE 'pg_%' clause.

SELECT rolname FROM pg_roles WHERE rolcanlogin = true ORDER BY rolname;

The rolcanlogin column is true for users (vs. groups). The query returns every login-capable role.

How do I list roles with non-expiring passwords?

SELECT rolname FROM pg_roles WHERE rolvaliduntil IS NULL AND rolcanlogin = true ORDER BY rolname;

Roles with rolvaliduntil IS NULL have passwords that never expire. The team should set an expiration for every role that has a password.

Can a role have `LOGIN` but no password?

Yes. A role with LOGIN but no password can authenticate via peer authentication (same OS user), certificate authentication, or other methods, but cannot authenticate via password. The team that creates a role with LOGIN and no password should make sure the alternative authentication method is configured.

How do I see the privileges a role has?

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'app_user';

The query shows every table-level privilege the role has. The team audits the output for unexpected privileges.