“List users in psql” is at least three different questions, and the answer you get depends on which one you actually meant: the du meta-command shows every role in the cluster, the SELECT rolname FROM pg_roles WHERE rolcanlogin = true query shows every role that can log in, and the join against pg_database and has_database_privilege shows every role with CONNECT on a specific database. If you have ever typed \du and said “but where is the user I just created,” you have hit the first of those three questions. Most blog posts answer it as if there is only one question, which is why the search results are unsatisfying.
This post walks through the three patterns, when to use each, and the managed Postgres twist that is the only one most teams will end up using.
Table of contents
- The three questions behind “list users in psql”
- The fast answer: the du meta-command
- The SQL answer: pg_roles with the login filter
- The database-scoped answer: the join against pg_database
- The managed Postgres twist
- The audit pattern: export to a file, version-control it
- How this fits the rest of the stack
- FAQ
The three questions behind “list users in psql”
The three questions, in order of how often teams ask them:
- “What roles exist on this cluster?” This is the catalog-level question. The answer is every row in
pg_roles, regardless of login status or database grants. The right command is\du(or\du+for the verbose form). - “What login-enabled users can connect to the database?” This is the application-level question. The answer is every role with
rolcanlogin = true, because group roles cannot log in. The right command is the SQL filter. - “Which users can connect to this specific database?” This is the audit-level question. The answer is every role with
CONNECTprivilege on the target database, which is a join againstpg_database.datacland thehas_database_privilegefunction.
The reason most blog posts miss the second and third is that they answer the first and stop. The first is the most general, but the second and third are the ones most teams actually need for security audits and access reviews.
The fast answer: the du meta-command
The \du meta-command is the canonical answer. The form is:
\du
-- or
\du+The output is a table with three columns: role name, list of attributes, and member of. The + form adds a description column and a longer member list.
The attributes column shows the role’s privileges: Superuser, Create role, Create DB, Replication, Bypass RLS. The member of column shows the group roles this role is a member of. Both are derived from the pg_roles catalog.
The gotcha: \du shows group roles and login roles together. A team that just created a user with CREATE ROLE (no WITH LOGIN) will see the role in \du but will not be able to log in as it. The team’s mental model for “users” usually means “login-enabled roles,” and the meta-command does not separate the two.
The SQL answer: pg_roles with the login filter
The SQL answer is the same data as \du but filterable. The form is:
SELECT rolname, rolsuper, rolcanlogin, rolcreatedb
FROM pg_roles
WHERE rolcanlogin = true
ORDER BY rolname;The rolcanlogin = true filter separates login-enabled users from group roles. The result is the actual list of authentication identities the database will accept.
The gotcha: pg_roles is a view, not a table. The underlying table is pg_authid, which is superuser-only. A team that needs to inspect password hashes or expiration dates has to read pg_authid (or its view pg_shadow), which requires superuser access. The standard pattern is to grant pg_read_all_settings or the role-level SELECT on pg_authid for the audit user, not to give every user superuser.
The second gotcha: rolcanlogin is the attribute, not the grant. A role with rolcanlogin = false cannot log in regardless of the grants. A role with rolcanlogin = true and no CONNECT on the target database still cannot connect to that database.
The database-scoped answer: the join against pg_database
The database-scoped answer is the one security audits want. The form is:
SELECT r.rolname, d.datname,
has_database_privilege(r.rolname, d.datname, 'CONNECT') AS can_connect
FROM pg_roles r
CROSS JOIN pg_database d
WHERE r.rolcanlogin = true
AND d.datistemplate = false
AND has_database_privilege(r.rolname, d.datname, 'CONNECT')
ORDER BY d.datname, r.rolname;The query joins every login-enabled role against every non-template database, filters by has_database_privilege, and returns the answer. The datistemplate = false filter excludes template0 and template1, which are not user-facing.
The gotcha: the has_database_privilege function checks the effective grant, which includes role membership. A user that is a member of a group role with CONNECT is reported as having CONNECT, even if the user does not have a direct grant. The audit needs to factor in role membership, not just direct grants.
The second gotcha: a superuser is reported as having CONNECT on every database. The audit needs to filter superusers out, or to flag them separately, because the audit is moot if a superuser is in the system.
The managed Postgres twist
The managed Postgres pattern is the one most teams will end up using. The pattern is: the platform provisions a Postgres instance, the platform creates a default user, the team’s app uses the default user, and the team creates additional users through the platform’s dashboard or through SQL.
The gotcha: the platform’s default user is usually a superuser. The team should treat the default user as the bootstrap user, not the application user. The team’s app should use a per-application user with the minimum privileges the app needs. The default user is for admin work (schema migrations, backups, role management), not for the app’s runtime traffic.
The second gotcha: the platform’s role management is a separate feature from the database. Some platforms (Render, Fly, Supabase) expose role management through the dashboard. Others (RDS, Cloud SQL) require SQL. The team should know which mode the platform is in before relying on it for audits.
The audit pattern: export to a file, version-control it
The audit pattern is the same regardless of which query the team uses. The team runs the query, exports the result to a file, and stores the file in version control. The diff between this week’s file and last week’s file is the part the auditor actually wants.
The standard form is:
psql "$DATABASE_URL" -c "COPY (
SELECT r.rolname, d.datname,
has_database_privilege(r.rolname, d.datname, 'CONNECT') AS can_connect
FROM pg_roles r
CROSS JOIN pg_database d
WHERE r.rolcanlogin = true AND d.datistemplate = false
ORDER BY d.datname, r.rolname
) TO STDOUT WITH CSV HEADER" > audit-$(date +%Y%m%d).csvThe export is plain CSV, diffable, and reviewable. The team stores it in a directory that the audit script owns, and the diff is the change log.
The gotcha: the COPY ... TO STDOUT requires the pg_read_server_files or pg_write_server_files grant, depending on the direction. The simpler form is psql -c "SELECT ..." --csv, which uses the client-side CSV writer. The result is the same.
How this fits the rest of the stack
The user-management pattern is also a cost pattern. Every user is a connection the database has to handle, every role is a permission the audit has to verify, and every superuser still in use is a risk the team is paying for in incident response. The team’s mental model for the database cost is the instance, the storage, the bandwidth, and the connection count. The RunxBuild hosting calculator is the right place to model that — pick the Postgres tier, the storage, the connection count, and the expected query volume, and the calculator shows what the database costs at the team’s actual usage.
Useful related references:
FAQ
How do I list users in psql?
Three ways: the \du meta-command for the catalog-level list, the SELECT rolname FROM pg_roles WHERE rolcanlogin = true query for the login-enabled filter, and the join against pg_database with has_database_privilege for the database-scoped list. The right one depends on which question you actually mean.
What is the difference between du and du+ in psql?
\du shows the role name, the list of attributes (superuser, create role, create DB, replication, bypass RLS), and the role membership. \du+ adds the description column and a longer member list. Both are wrappers around pg_roles.
Does du show users from other databases on the cluster?
Yes. Roles are cluster-wide, not database-scoped. A user created in one database is visible to \du when you connect to any other database on the cluster.
Why is the user I just created not showing up in du?
Three common causes. The role is on a different cluster (check the connection string). The role does not have the LOGIN attribute (run ALTER ROLE name WITH LOGIN). Or the role exists but the pg_hba.conf rule blocks this connection. The diagnostic order is cluster, login attribute, pg_hba.conf.
How do I show only users that can log in, not group roles?
The filter is WHERE rolcanlogin = true against pg_roles. The \du output shows both by design; the SQL filter is how you separate users from groups.
Can I see who can connect to a specific database?
Yes. The pg_database.datacl column is the ACL string. For a per-user view, the has_database_privilege(rolname, datname, 'CONNECT') function returns true or false. The combination is the answer to “who can touch this specific database.”
How do I check if a user has a password set?
The pg_shadow view exposes the password hash and is superuser-only. The rolvaliduntil column in pg_roles is the password expiration date; a NULL value means the password never expires.
How do I export the user list for an audit?
The cleanest approach is to save the audit query as a .sql file and run psql "$DATABASE_URL" -f audit.sql --csv > audit-$(date +%Y%m%d).csv. Run the script on a schedule, store the output in version control, and the diff between weeks is the auditor’s input.