Migrate to RunxBuild and earn up to $50 in hosting credit on your first deposit.

Calculate your savings
unxBuild
Log in Get Started
Back to Blog Databases

postgresql psql list users: The Query That Actually Works on a Real Cluster

Sean

Platform Writer

Jun 18, 2026
7 min read

Most “psql list users” tutorials hand you one command and stop. The command works on a fresh local box. The command falls apart the first time someone inherits a managed database with fifty roles, half of which are groups, three of which are role-only, and one that someone named do_not_delete_alex_2024. The version that actually works on a real cluster is the one that knows the difference between a role and a login user, can read pg_authid without tripping over permissions, and treats the meta-command \du as a shortcut, not a source of truth.

postgresql psql list users: The Query That Actually Works on a Real Cluster

Table of contents

The honest answer for “how do I list users in psql on PostgreSQL”: start with \du to get the headline, then run SELECT usename, usecreatedb, usesuper, valuntil FROM pg_user ORDER BY 1; for the columns psql hides by default, then read pg_roles for the role-level view that includes groups. If you only ever run one query, run the pg_user one — it is the closest to “show me the humans who can log in,” and it has been stable since PostgreSQL 9.x.

postgresql psql list users: the query that actually works on a real cluster

Table of contents

The direct answer

The fastest path on a vanilla PostgreSQL:

psql> \du

That meta-command lists roles with login permission. It is fine for a one-line audit. It is not the whole picture, because it does not show you: roles without LOGIN, group roles, password expiry, replication privileges, or role attributes (CREATEDB, CREATEROLE, SUPERUSER). For those you need the underlying catalogs.

If you want one query that captures most of what people actually mean by “list users”:

SELECT usename,
       usesuper,
       usecreatedb,
       usecatupd,
       valuntil AS password_expires,
       passwd IS NOT NULL AS has_password
FROM pg_shadow
ORDER BY 1;

pg_shadow is readable only by superusers on modern PostgreSQL. If you are not a superuser, you almost always have access to pg_user instead, which is the same view but hides the password column:

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

That is the query that works on a real cluster, with the right columns, without leaking sensitive data you do not need.

What psql \du actually shows you

\du calls pg_catalog.pg_roles under the hood, filters to roles where rolcanlogin = true, and renders three columns: role name, attribute list, and member-of list. The output looks like:

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 app_user  |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 readonly  |                                                            | {}

The three columns hide information most real databases need. The attribute list is truncated when it gets long. The “Member of” column shows inherited group memberships. The role name is the only column you can sort on by eye.

\du+ adds Description. \du <pattern> filters by name with a LIKE pattern. That is it. There is no \du --json, no \du --with-passwords, no \du --with-grants. For those you need SQL.

The three views that matter: pg_user, pg_shadow, pg_roles

PostgreSQL exposes user data through three overlapping catalog views. They look similar. They are not the same.

ViewPurposePassword columnSuperuser-readableWhat you use it for
pg_userFriendly “current user” viewNo (hidden)YesListing human login accounts without exposing password hashes
pg_shadowSame as pg_user, plus raw passwd columnYesNo — superuser onlyAuditing password hashes, expiry, and authentication methods
pg_rolesEvery role, including groups and NOLOGIN rolesNoYesListing every role in the cluster regardless of login permission

The reason all three exist is permission, not duplication. pg_shadow is the raw catalog. pg_user and pg_roles are public views over pg_shadow and pg_authid that hide what a non-superuser should not see.

A practical taxonomy:

  • Human login users: visible in pg_user and pg_shadow. Use pg_user for display.
  • Service accounts: visible in pg_user and pg_shadow. Often have NOLOGIN parent roles in pg_roles.
  • Group roles: visible only in pg_roles (no rolcanlogin).
  • Replication roles: visible in pg_roles. Look for rolreplication = true.
  • Reserved system roles: pg_signal_backend, pg_read_server_files, etc. Visible in pg_roles. Usually excluded with a WHERE rolname NOT LIKE 'pg_%' filter.

How to filter for login users only (the version most teams want)

Most “list users” requests actually mean “show me the login accounts on this database so I can audit them.” The query that does that without showing every group role:

SELECT r.rolname,
       r.rolsuper,
       r.rolcreatedb,
       r.rolcreaterole,
       r.rolreplication,
       r.rolbypassrls,
       r.rolcanlogin,
       r.rolvaliduntil AS password_expires,
       ARRAY(SELECT b.rolname
             FROM pg_auth_members m
             JOIN pg_roles b ON m.roleid = b.oid
             WHERE m.member = r.oid) AS member_of
FROM pg_roles r
WHERE r.rolcanlogin = true
ORDER BY r.rolname;

That gives you every login role with its attributes, its expiry, and its group memberships in a single row. Run it once and you have the audit most teams actually want.

If you are on a managed service like AWS RDS, Neon, or Supabase, the same query works. The connection string is what changes, not the catalog.

Why your \du output is empty on a managed cluster

A common failure mode: someone connects to a managed PostgreSQL and runs \du, gets back only the application role, and concludes “there are no other users.” That is wrong. Managed services usually:

  1. Hide the postgres superuser behind a different connection string.
  2. Strip roles marked rolreplication = true from \du output.
  3. Show only roles the connecting user has permission to see.

The fix is to query pg_roles directly and filter on rolcanlogin. If the result is still suspiciously short, query pg_authid via pg_shadow (if you have superuser) and look for system roles you might be missing.

A second failure mode: connecting as a user that is not a superuser and getting permission denied for table pg_shadow. Use pg_user or pg_roles instead. Both are accessible to non-superusers by default.

The query that lists users with their database grants

A “list users” request often turns into “list users and tell me which databases they own.” That requires joining pg_roles to pg_database:

SELECT r.rolname,
       ARRAY(SELECT datname FROM pg_database d WHERE d.datdba = r.oid) AS databases_owned
FROM pg_roles r
WHERE r.rolcanlogin = true
ORDER BY r.rolname;

If you also want schema-level ownership, add pg_namespace:

SELECT r.rolname,
       (SELECT array_agg(nspname) FROM pg_namespace n WHERE n.nspowner = r.oid) AS schemas_owned,
       (SELECT array_agg(datname) FROM pg_database d WHERE d.datdba = r.oid) AS databases_owned
FROM pg_roles r
WHERE r.rolcanlogin = true
ORDER BY r.rolname;

That is the audit query a CTO runs once a quarter to figure out which roles still own production schemas after a year of team turnover.

The five places to look for hidden users

A real cluster has users living in five different catalogs. Miss one and your audit is incomplete.

  1. pg_shadow — login users with passwords. The “real” list.
  2. pg_roles — every role including groups. Add a filter for rolcanlogin if you only want login users.
  3. pg_auth_members — role memberships. The “alice is in the analytics group” facts. Use a join from pg_roles to read it.
  4. pg_database.datdba — the role that owns each database. A user can own a database without rolcanlogin on the role they own things as.
  5. information_schema.enabled_roles — the roles enabled for the current session. Useful for debugging “why does my connection not have permission to X.”

If you are doing an actual audit, walk all five. The interesting finding is usually in #4 — a database owned by a role that was supposed to be deleted two years ago.

If your audit reveals a dozen orphan roles, an over-permissioned app_user, and a readonly account nobody can trace, the right next step is to run that cleanup against a managed PostgreSQL instance where the catalog changes are reversible. The same pg_roles queries run unchanged on RunxBuild’s managed Postgres — and the audit logs let you see who ran what against the cluster, which is the part you usually wish you had after the cleanup is done. For a sanity check on what the host itself costs to keep running while you clean up, the RunxBuild hosting calculator will show you the bill before you commit.

FAQ

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

\du lists roles with login permission and three columns (name, attributes, member of). \du+ adds a description column. There is no flag to add the password column or the expiry — for those you need SQL against pg_user or pg_shadow.

Why does \du not show the postgres role?

It usually does. On managed services like AWS RDS, the postgres role is hidden behind a separate admin connection string and not visible to the regular application user. That is by design — the managed service reserves superuser access for its own maintenance tasks.

Can I list users without being a superuser?

Yes. pg_user and pg_roles are readable by any role with CONNECT permission to the database. The only view that requires superuser is pg_shadow, which exposes the password hash column.

How do I see when a user’s password expires?

The valuntil column in pg_user and pg_shadow stores the password expiry timestamp. A NULL value means the password never expires. Run SELECT usename, valuntil FROM pg_user ORDER BY valuntil NULLS LAST; to find accounts that need rotation.

How do I find users that have not logged in recently?

PostgreSQL does not track last-login time per user by default. You need log_statement = 'all' plus log parsing, or a third-party audit extension like pgaudit. The catalog itself does not record login history.

What is the difference between a role and a user?

There is no difference. In PostgreSQL, “user” and “role” are the same concept; historically CREATE USER was an alias for CREATE ROLE ... LOGIN. \du shows roles with LOGIN; \dg shows group roles without it. The underlying catalog is pg_authid (raw) and pg_roles (public view).

How do I list service accounts separately from human users?

There is no native “this is a service account” flag. The convention is to put service accounts in their own group role (e.g. service_accounts) or to name them with a prefix (svc_, app_, bot_). Filter with WHERE rolname LIKE 'svc_%' or join pg_auth_members to a known group.

Can I export the list to CSV?

Yes. From psql, run \copy (SELECT ... FROM pg_user) TO '/tmp/users.csv' CSV HEADER;. From a client, wrap the query in COPY (...) TO STDOUT CSV HEADER and pipe to a file. Most BI tools accept the same CSV directly.

FAQ

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

\du lists roles with login permission and three columns (name, attributes, member of). \du+ adds a description column. There is no flag to add the password column or the expiry — for those you need SQL against pg_user or pg_shadow.

Why does \du not show the postgres role?

It usually does. On managed services like AWS RDS, the postgres role is hidden behind a separate admin connection string and not visible to the regular application user. That is by design — the managed service reserves superuser access for its own maintenance tasks.

Can I list users without being a superuser?

Yes. pg_user and pg_roles are readable by any role with CONNECT permission to the database. The only view that requires superuser is pg_shadow, which exposes the password hash column.

How do I see when a user’s password expires?

The valuntil column in pg_user and pg_shadow stores the password expiry timestamp. A NULL value means the password never expires. Run SELECT usename, valuntil FROM pg_user ORDER BY valuntil NULLS LAST; to find accounts that need rotation.

How do I find users that have not logged in recently?

PostgreSQL does not track last-login time per user by default. You need log_statement = 'all' plus log parsing, or a third-party audit extension like pgaudit. The catalog itself does not record login history.

What is the difference between a role and a user?

There is no difference. In PostgreSQL, CREATE USER is an alias for CREATE ROLE ... LOGIN. \du shows roles with LOGIN; \dg shows group roles without it. The underlying catalog is pg_authid (raw) and pg_roles (public view).

How do I list service accounts separately from human users?

There is no native is_service flag. The convention is to put service accounts in their own group role (service_accounts) or to name them with a prefix (svc_, app_). Filter with WHERE rolname LIKE 'svc_%'.

Can I export the list to CSV?

Yes. From psql: \copy (SELECT ... FROM pg_user) TO '/tmp/users.csv' CSV HEADER;. From a client, wrap the query in COPY (...) TO STDOUT CSV HEADER.

#PostgreSQL#psql#User Management#Database Admin#Postgres Roles