postgres show users: The Three Commands That Work When `\du` Doesn't

\du is what every blog post suggests. \du is what most Stack Overflow answers paste. \du works fine on a fresh local box. The first time you run \du against a managed cluster, a read replica, or a role with limited privileges, you hit a wall — and the wall is not always visible. The three commands that actually work in every case are \du, SELECT * FROM pg_user, and SELECT * FROM pg_roles WHERE rolcanlogin = true;. Each one covers a different failure mode. Together, they handle every “postgres show users” request.

Table of contents

• Table of contents
• The direct answer
• Why \du fails on a managed cluster (and why that is fine)
• Command 1: \du for the headline
• Command 2: pg_user for the columns psql hides
• Command 3: pg_roles WHERE rolcanlogin for managed clusters
• The seven columns you actually want to see
• When to use \du+ vs the SQL views
• FAQ
• FAQ

The short version: if you have superuser access and want the headline, run \du+. If you want the columns psql hides (expiry, createdb, superuser flag), run SELECT usename, usesuper, usecreatedb, valuntil FROM pg_user. If you want every login account on a managed cluster, run SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolcanlogin = true. The three together cover 99% of real cases.

Table of contents

• The direct answer
• Why \du fails on a managed cluster (and why that is fine)
• Command 1: \du for the headline
• Command 2: pg_user for the columns psql hides
• Command 3: pg_roles WHERE rolcanlogin for managed clusters
• The seven columns you actually want to see
• When to use \du+ vs the SQL views
• FAQ

The direct answer

Three commands, in order of how often they solve the problem:

psql> \du

That is the meta-command. Fast, but limited.

SELECT usename, usesuper, usecreatedb, valuntil
FROM pg_user
ORDER BY usename;

That is the SQL view. Has the columns psql hides. Works without superuser.

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

That is the catalog view. Works on every cluster, managed or not, with the right filter for login users.

If you only remember one, remember the third one. It runs on managed services, on read replicas with replica-side permission, and on every cluster regardless of which view \du happens to be aliased to.

Why \du fails on a managed cluster (and why that is fine)

On AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL, Supabase, Neon, and most managed providers, \du works, but it shows you a subset of what you expect. Three reasons:

1. The postgres role is hidden. The provider reserves superuser access for itself. The application role you connect as sees a smaller world.
2. Group roles are excluded. \du only shows roles with LOGIN. Group roles (analytics_team, readonly_users) are filtered out.
3. Replication roles are excluded. The replication role that the provider uses for read replicas is hidden from your session.

None of this is a bug. It is a deliberate isolation layer. The fix is to query the catalog directly, not the meta-command.

Command 1: \du for the headline

\du is a meta-command, not a SQL statement. It is implemented in psql itself and queries pg_roles with WHERE rolcanlogin = true. The output:

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 app_user  |                                                            | {}
 readonly  |                                                            | {}

Three columns: name, attributes, member of. The attributes column truncates. The member-of column shows inherited groups. There is no \du --json and no \du --with-expiry.

Variations:

• \du <pattern> — filter by name pattern (\du 'app%').
• \du+ — add description column.
• \du * — every role matching the wildcard (same as no filter).

\du is fine when you want to know “is there a role called X” or “what roles exist on this box.” It is insufficient when you need the audit details.

Command 2: pg_user for the columns psql hides

pg_user is a built-in view over pg_shadow that hides the password column. The columns:

Column	Meaning
usename	Role name
usesysid	Internal OID-like identifier
usecreatedb	Boolean: can the role create databases
usesuper	Boolean: is the role a superuser
usecatupd	Boolean: can update system catalogs directly
userepl	Boolean: can initiate streaming replication
passwd	Hidden in pg_user, visible in pg_shadow
valuntil	Password expiry timestamp (NULL = never)
useconfig	Session defaults for run-time variables

The query that actually audits a cluster:

SELECT usename,
       usesuper,
       usecreatedb,
       userepl,
       usecatupd,
       valuntil AS password_expires
FROM pg_user
ORDER BY usesuper DESC, usename;

The ORDER BY usesuper DESC puts superusers at the top, which is where you want to start when you are auditing who has too much access.

If you also need to see which group each user belongs to, join to pg_auth_members:

SELECT u.usename,
       (SELECT array_agg(r.rolname)
        FROM pg_auth_members m
        JOIN pg_roles r ON r.oid = m.roleid
        WHERE m.member = u.usesysid) AS groups
FROM pg_user u
ORDER BY u.usename;

That gives you the human-readable audit view most teams actually want.

Command 3: pg_roles WHERE rolcanlogin for managed clusters

pg_roles is the canonical view that exposes every role in the cluster, not just login users. It is the same data \du reads from, but without the meta-command filtering. The query for managed clusters:

SELECT rolname,
       rolsuper,
       rolcreatedb,
       rolcreaterole,
       rolreplication,
       rolbypassrls,
       rolcanlogin,
       rolvaliduntil
FROM pg_roles
WHERE rolcanlogin = true
  AND rolname NOT LIKE 'pg_%'
ORDER BY rolname;

The rolname NOT LIKE 'pg_%' filter hides the reserved system roles (pg_signal_backend, pg_read_server_files, etc.) that PostgreSQL creates automatically. They are real roles, but they are not part of your user audit.

For the full audit including groups, drop the rolcanlogin filter:

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

That shows you login users, group roles, replication roles, and reserved system roles in one query. The rolcanlogin column makes the category obvious.

The seven columns you actually want to see

When “show users” is shorthand for “show me the audit columns,” the seven columns that matter are:

1. rolname / usename — the role name itself.
2. rolsuper / usesuper — is it a superuser.
3. rolcreatedb / usecreatedb — can create databases.
4. rolreplication / userepl — can stream replication.
5. rolbypassrls — can bypass row-level security.
6. rolvaliduntil / valuntil — password expiry.
7. member_of — group memberships (requires a join to pg_auth_members).

A single query that returns all seven:

SELECT r.rolname,
       r.rolsuper,
       r.rolcreatedb,
       r.rolreplication,
       r.rolbypassrls,
       r.rolvaliduntil,
       COALESCE(
         (SELECT array_agg(g.rolname)
          FROM pg_auth_members m
          JOIN pg_roles g ON g.oid = m.roleid
          WHERE m.member = r.oid),
         '{}'
       ) AS member_of
FROM pg_roles r
WHERE r.rolcanlogin = true
ORDER BY r.rolname;

That is the query you run when someone from security asks “who has access to this cluster.”

When to use \du+ vs the SQL views

Use \du+ when:

• You are interactive, not scripted.
• You only need the headline.
• You are on a dev box.

Use pg_user when:

• You need the expiry column.
• You are a non-superuser.
• You want a script-friendly output.

Use pg_roles WHERE rolcanlogin when:

• You are on a managed cluster.
• You need the full attribute list.
• You are auditing for compliance.

The honest answer is that “postgres show users” has three correct answers and the one you want depends on what you are actually doing. None of them are wrong. None of them are complete.

If the audit reveals that you are running a managed Postgres cluster and the bill is starting to look unreasonable for the workload, the next move is to compare what your current provider charges against what an equivalent setup runs on a platform like RunxBuild’s hosting calculator — most teams find that managed Postgres is 2x to 3x the raw compute cost once you account for replicas, connection pooling, and the per-connection fees. For the actual deployment side (the app that talks to this database), the same managed-Postgres instance sits naturally next to RunxBuild’s backend services so the database, the API, and the worker queue live on one bill, not three.

FAQ

What is the difference between \du and \du+?

\du shows name, attributes, and member-of. \du+ adds a description column. Neither shows the password or expiry — for those, query pg_user or pg_shadow directly.

Does \du show password hashes?

No. \du reads from pg_roles, which hides the password column. To see password hashes, you need superuser access and a query against pg_shadow.

How do I show users on a managed cluster like AWS RDS or Neon?

The same \du works, but the output is filtered by the provider. Use SELECT rolname FROM pg_roles WHERE rolcanlogin = true; for the complete list of login users your application role can see.

What is the difference between a user and a group role?

A user is a role with LOGIN. A group role is a role without LOGIN that other roles inherit from. \du shows users; \dg shows groups. The underlying catalog is the same.

Why are there pg_% roles in the output?

PostgreSQL automatically creates reserved roles for internal features (logical replication, file access, monitoring). Filter them out with WHERE rolname NOT LIKE 'pg_%' if they are noise in your audit.

How do I find users with no password expiry?

Run SELECT usename, valuntil FROM pg_user WHERE valuntil IS NULL ORDER BY usename;. Passwords with NULL expiry never expire — usually a compliance finding.

Can I see when each user was created?

The pg_authid.rolcreatedb and pg_authid catalog do not track creation time. You need log_statement = 'ddl' plus log parsing, or pgaudit, to reconstruct creation history. The catalog itself is silent on it.

What is pg_shadow?

pg_shadow is the raw catalog behind pg_user. It is readable only by superusers because it exposes the passwd column. Use pg_user instead unless you need the password hash for a security audit.

FAQ

What is the difference between \du and \du+?

\du shows name, attributes, and member-of. \du+ adds a description column. Neither shows the password or expiry — for those, query pg_user or pg_shadow directly.

Does \du show password hashes?

No. \du reads from pg_roles, which hides the password column. To see password hashes, you need superuser access and a query against pg_shadow.

How do I show users on a managed cluster like AWS RDS or Neon?

The same \du works, but the output is filtered by the provider. Use SELECT rolname FROM pg_roles WHERE rolcanlogin = true; for the complete list of login users your application role can see.

What is the difference between a user and a group role?

A user is a role with LOGIN. A group role is a role without LOGIN that other roles inherit from. \du shows users; \dg shows groups.

Why are there pg_% roles in the output?

These are reserved roles PostgreSQL creates automatically for internal features. Filter them out with WHERE rolname NOT LIKE 'pg_%' if they are noise.

How do I find users with no password expiry?

Run SELECT usename, valuntil FROM pg_user WHERE valuntil IS NULL ORDER BY usename;. Passwords with NULL expiry never expire — usually a compliance finding.

Can I see when each user was created?

The catalog does not track creation time. You need log_statement = 'ddl' plus log parsing, or pgaudit, to reconstruct creation history.

What is pg_shadow?

pg_shadow is the raw catalog behind pg_user. It is readable only by superusers because it exposes the passwd column. Use pg_user unless you need the password hash.