psql show users is not one question — it is three questions wearing the same clothes, and the answer you get depends on which one you actually meant. The fast one-liner is the du meta-command inside psql; the audit-grade query is SELECT rolname, rolcanlogin, rolsuper FROM pg_roles; and the “who can touch this specific database” version needs a join against pg_database and information_schema.role_table_grants. If you have ever typed du and then said “but where is the one 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 this one is long.
The phrase “show users” is also the phrase that catches teams during security audits, role reviews, and the moment a developer says “I cannot connect, can you tell me what users exist on this cluster?” The naive answer is du and a screenshot. The working answer is the query that matches the question, and the question is usually one of three: who has any role on this cluster, who can actually log in, and who can touch this specific database.
Table of contents
- The one-line answer (and why it is almost never the right one)
- Question 1: “Who exists on this cluster?”
- Question 2: “Who can actually log in?”
- Question 3: “Who can touch this specific database?”
- The audit-grade version: roles, members, grants, and password status
- Why
duanddu+do not match the SQL system catalogs - What to do when the user you are looking for is not in the list
- How to bake this into a runbook
- FAQ
The one-line answer (and why it is almost never the right one)
Open psql against any database on the cluster and run:
duThat is the answer 90% of blog posts will give you. It works. It is also a list of every role on the cluster, not a list of users, and the difference between a “role” and a “user” is the part that confuses the question.
A role is a Postgres concept. A user is a role with the LOGIN attribute. The du meta-command shows both. The output looks like this:
List of roles
Role name | Attributes | Member of
-----------+-------------------------------------------------------+-----------
app_user | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readonly | | {}The middle column is the difference. app_user is a user because it has login. readonly is a role that has not been granted login. The third column, “Member of,” is the role inheritance — a role that belongs to another role and inherits its permissions.
If the user you are looking for is not in that list, the user does not exist on the cluster. The user is not “hidden” by the du command, the user is not on this database, or the user exists on a different cluster. None of those are problems with du; they are problems with the question.
Question 1: “Who exists on this cluster?”
The full list of roles on the cluster lives in pg_roles. The du command is a friendly wrapper around that catalog. The SQL form is:
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolconnlimit, rolvaliduntil
FROM pg_roles
ORDER BY rolname;This is the same answer du+ gives you, except the columns are explicit and you can filter them. The output is one row per role. The rolvaliduntil column is the one most blog posts skip, and it is the column that answers “why does this user keep getting locked out.” A non-null rolvaliduntil means the role has a password expiration set, and the role stops being able to log in at that timestamp.
If you want the same shape as du but with the rows you actually care about:
SELECT rolname,
rolsuper,
rolcreaterole,
rolcreatedb,
rolcanlogin,
rolreplication,
CASE WHEN rolvaliduntil IS NULL THEN 'never'
ELSE rolvaliduntil::text
END AS password_valid_until
FROM pg_roles
ORDER BY rolcanlogin DESC, rolname;The ORDER BY rolcanlogin DESC puts the actual users at the top and the group/service roles at the bottom, which is what you usually want.
Question 2: “Who can actually log in?”
If you mean “users” as “people or services that can authenticate,” the filter is rolcanlogin = true. A role can exist, have permissions, and still not be able to log in.
SELECT rolname, rolsuper, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolcanlogin = true
ORDER BY rolname;For a production cluster, this is the list you want on a dashboard. Every role in this list can authenticate, every role outside this list cannot, and the gap between “exists” and “can log in” is the gap that catches teams during audits.
A common audit question is “how many service accounts do we have.” The answer is:
SELECT COUNT(*) AS service_account_count
FROM pg_roles
WHERE rolcanlogin = true
AND rolname NOT IN ('postgres');Subtract postgres if you want to exclude the superuser. Subtract any role that is named after a human if you want only service accounts. The query is the same shape, the filter is what changes.
Question 3: “Who can touch this specific database?”
This is the question most blog posts skip and the one most security audits actually ask. Roles are cluster-wide. Database access is granted per database. The two are not the same.
The list of users with explicit grants on a specific database is:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
GROUP BY grantee, privilege_type
ORDER BY grantee;That gives you table-level grants. The database-level access is the CONNECT privilege, and the query for that is:
SELECT datname, datacl
FROM pg_database
WHERE datname = current_database();The datacl column is a Postgres ACL string. The empty or NULL value means “public has CONNECT, which means everyone can connect.” A populated value means someone took the time to restrict it. A team that has ever been through a SOC 2 audit knows that the second form is the form the auditor wants.
For a per-user view of database access:
SELECT r.rolname,
has_database_privilege(r.rolname, current_database(), 'CONNECT') AS can_connect,
has_database_privilege(r.rolname, current_database(), 'CREATE') AS can_create,
has_database_privilege(r.rolname, current_database(), 'TEMPORARY') AS can_temp
FROM pg_roles r
WHERE r.rolcanlogin = true
ORDER BY r.rolname;The has_database_privilege function is the part most blog posts skip and the part the audit needs. It returns true/false per role per privilege per database, and it is the query that tells you “this user can connect to this database but cannot create tables.” The has_*_privilege family is the right tool whenever the question is “can this role do this specific thing.”
The audit-grade version: roles, members, grants, and password status
If the question is “give me the full picture for a SOC 2 / ISO 27001 / internal review,” one query is not enough. The picture is four queries stitched together, and the four queries are the ones security teams will ask for in order:
Query 1: every role on the cluster, with what each role can do.
SELECT rolname,
rolsuper,
rolcreaterole,
rolcreatedb,
rolcanlogin,
rolreplication,
rolbypassrls,
rolconnlimit,
CASE WHEN rolvaliduntil IS NULL THEN 'never'
ELSE rolvaliduntil::text
END AS password_valid_until
FROM pg_roles
ORDER BY rolsuper DESC, rolcanlogin DESC, rolname;Query 2: role membership — which role is a member of which other role.
SELECT r.rolname AS group_role,
m.rolname AS member_role,
am.admin_option
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
ORDER BY r.rolname, m.rolname;The admin_option column tells you whether the member can grant the group role to other members. A true value is a privilege escalation path; an auditor will flag it.
Query 3: schema-level and table-level grants for the public schema.
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantee, table_schema, table_name;The WHERE filter excludes the system schemas, which would otherwise dominate the output.
Query 4: who owns objects in the public schema — the “stale owner” problem.
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_owner <> current_user;A table whose owner is a user that no longer exists is a “stale owner.” DROP and ALTER on that table will fail until the owner is reassigned. This is the query that catches the “I deleted the dev user and now I cannot drop the test table” problem.
The four queries together are the audit. Run them in order, dump them to a file, and the file is the evidence.
Why du and du+ do not match the SQL system catalogs
The du command reads from pg_roles the same way the SQL query does, but the rendering is lossy. The du+ output collapses boolean attributes into a comma-separated string and uses a Member of column for group membership. The SQL form keeps each attribute as a separate column. The two are equivalent for “show me the users” but not equivalent for “show me everything about a specific user.”
If a row is missing from du but visible in pg_roles, the most common cause is that you connected as a role that does not have the pg_roles read permission. The default postgres superuser can see every row. A non-superuser can see only the rows the role has been granted access to. The fix is either to connect as a superuser, to grant the pg_read_all_settings role, or to query the pg_authid view directly (which is what pg_roles is built on top of, minus the password hashes).
The pg_shadow view is the older equivalent of pg_roles and is still in the documentation. It exists for backward compatibility, and the only column it has that pg_roles does not is passwd, which is the password hash. The pg_shadow view is superuser-only. If you are writing a query that needs to check “does this user have a password set at all,” pg_shadow is the answer. For everything else, pg_roles is the answer.
What to do when the user you are looking for is not in the list
Four causes, in order of how often they bite.
The user is on a different cluster. The most common cause. psql connects to one cluster at a time, and the role you are looking for lives on a different cluster. The fix is to check the connection string. If you are on a managed Postgres from RunxBuild, the cluster URL is the one in the dashboard, not the local development cluster.
The user exists but has no login. The du command shows roles, not users. A role without LOGIN is a group role, not a user. The fix is to grant login:
ALTER ROLE my_service WITH LOGIN;The user is masked by a pg_hba.conf rule. Postgres uses the pg_hba.conf file to control which users can connect from which hosts. A user that exists and has login can still be blocked from connecting from a specific IP, a specific database, or a specific authentication method. The fix is to check pg_hba.conf (usually /etc/postgresql/<version>/main/pg_hba.conf on Linux) and confirm the rule for the user.
The role is in a different database’s ACL list. If the user is missing from pg_database.datacl, the user has the implicit CONNECT privilege that comes from being PUBLIC. The user is not missing — the user is “everyone.” The fix is to revoke the public default if you want to lock the database down.
The diagnostic order is: check the cluster, check the role’s LOGIN attribute, check pg_hba.conf, check the database ACL.
How to bake this into a runbook
A real production team does not run these queries by hand every Monday morning. The team saves the four audit queries as a SQL file, runs the file on a schedule, and stores the output in version control. The cron job looks like:
psql "$DATABASE_URL" -f audit-queries.sql > audit-$(date +%Y%m%d).txtThe audit-queries.sql file contains the four queries above. The output is plain text, diffable, and reviewable. The team diffs the file week over week to spot new roles, expired passwords, and stale owners. The diff is the part the auditor actually wants.
For a managed Postgres without shell access, the same approach works through psql over a connection string. The DATABASE_URL is the only thing that changes. The RunxBuild managed Postgres is the same shape: provision the database, get the connection string, run the queries, store the output. The platform does not change the runbook; it just removes the part where you have to keep Postgres running yourself.
A small note on storing query output: the pg_shadow view contains password hashes, and the password hash should never be in the audit output. The four queries above do not select from pg_shadow, so the output is safe. The day you add a fifth query that selects passwd, the day the audit file stops being shareable.
FAQ
What is the difference between du and du+ in psql?
du shows the role name and 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. For most purposes, the SQL form (SELECT rolname, rolsuper, ... FROM pg_roles) gives you more control over the output.
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, because the role lives in the cluster-wide pg_roles catalog, not in any one database.
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 (it is a group role, not a user; 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. A common pattern is to use this filter in audit dashboards so the count reflects the actual number of authenticated identities, not the total number of role definitions.
Can I see who can connect to a specific database?
Yes. The pg_database.datacl column is the ACL string for the database. For a per-user view, the has_database_privilege(rolname, datname, 'CONNECT') function returns true or false. The combination of pg_database and has_database_privilege is the answer to “who can touch this specific database” and is the form security audits usually want.
How do I check if a user has a password set?
The pg_shadow view is the only one that exposes the password hash, and it is superuser-only. The rolvaliduntil column in pg_roles is the password expiration date; a NULL value means the password never expires. A non-null value means the role stops being able to log in at that timestamp, which is the answer to “why does this user keep getting locked out.”
How do I export the user list for an audit?
The cleanest approach is to save the four audit queries above as a .sql file and run psql "$DATABASE_URL" -f audit.sql > audit-$(date +%Y%m%d).txt. The output is plain text, diffable, and reviewable. Run the script on a schedule (weekly or monthly), store the output in version control, and the diff between this week and last week is the part the auditor actually wants.