List of Users in Postgres: The Safe Query That Works on Every Version, and the Role-Attribute Trap Behind It

To list users in Postgres, query pg_roles (the modern source) or pg_user (the legacy view). The pg_user view shows only login-capable roles; pg_roles shows every role. The right answer for most “list users” questions is SELECT rolname FROM pg_roles WHERE rolcanlogin = true ORDER BY rolname; — works on every Postgres version since 8.1, returns the login-capable roles, and the result is what the team expects. The trap is the pg_shadow view, which is restricted to superusers and was removed in Postgres 10.

This post is the safe query, the role-attribute trap, and the audit checklist. The first half is the SQL. The second half is the role-attribute check, the case-sensitivity gotcha, and the platform-side answer for managed Postgres.

The interesting thing about “list of users in postgres” is that the question usually surfaces in an audit or a debugging context. The team is checking who has access, who can log in, who has a password that does not expire. The team that picks the right query is the team that gets the right answer. The team that picks the wrong query is the team that misses the system roles, the missing-logins, or the case-sensitivity quirks.

Table of contents

• The direct answer
• The three sources: pg_user, pg_shadow, pg_roles
• The safe query
• The role-attribute trap
• The case-sensitivity gotcha
• The audit checklist
• The platform-side answer
• The opinion this post is built on
• FAQ

The direct answer

The most useful query:

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

Returns every login-capable role, with the role’s superuser, create-database, create-role flags, and the password expiration date. Works on every Postgres version since 8.1. The result is what the team expects.

The three sources: pg_user, pg_shadow, pg_roles

Postgres has three sources of role information. Each has a different use case, and the legacy two have different visibility rules.

pg_user is a view over pg_shadow that exposes only the columns safe for non-superusers. The view shows the role name, whether the role is a superuser, and a few other safe attributes. The view does not show the password hash. The view has existed since early Postgres versions and is the most-cited source in Stack Overflow answers.

pg_shadow is the underlying table that backs pg_user. The table includes the password hash. The table is restricted to superusers (pg_authid in newer versions, the same idea but with a different name). A non-superuser who queries pg_shadow directly gets an error. The table was removed in Postgres 10; the modern equivalent is pg_authid, which is also superuser-only.

pg_roles is the modern, public-friendly view over pg_authid. The view exposes most role attributes (name, login flag, superuser flag, create-database flag, create-role flag, password expiration, etc.) but does not expose the password hash. The view is the right answer for “list roles” in modern Postgres.

The summary:

• For “list login-capable users” in any modern Postgres: pg_roles filtered by rolcanlogin = true.
• For “list users with the password hash” (audit context, superuser only): pg_authid (Postgres 10+) or pg_shadow (older).
• For the legacy Stack Overflow answer: pg_user (works, but limited columns).

The team that uses pg_roles is the team that gets the most information with the least surprise. The team that uses pg_shadow is the team that is on a Postgres version that should be upgraded.

The safe query

The safe query, expanded:

SELECT 
    rolname AS username,
    rolcanlogin AS can_login,
    rolsuper AS is_superuser,
    rolcreatedb AS can_create_db,
    rolcreaterole AS can_create_role,
    rolreplication AS can_stream_replication,
    rolbypassrls AS can_bypass_rls,
    rolvaliduntil AS password_expires_at
FROM pg_roles
ORDER BY rolname;

The query returns every role (not just login-capable) with the relevant attributes. The team can filter with a WHERE clause:

-- Every login-capable role
SELECT rolname FROM pg_roles WHERE rolcanlogin = true ORDER BY rolname;

-- Every superuser
SELECT rolname FROM pg_roles WHERE rolsuper = true ORDER BY rolname;

-- Every role with non-expiring passwords
SELECT rolname FROM pg_roles WHERE rolvaliduntil IS NULL AND rolcanlogin = true ORDER BY rolname;

-- Every role created in the last 30 days
SELECT rolname FROM pg_roles WHERE rolcreated > now() - interval '30 days' ORDER BY rolcreated DESC;

The queries are portable, the output is clear, and the result is what the team expects. The queries are the boring answer that works on every Postgres version.

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, including reading other users’ data, dropping databases, and bypassing row-level security. The team that creates a “user” and grants SUPERUSER has created a security hole. The audit 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.
• rolreplication. A role with rolreplication = true can be used for streaming replication. The team that does not use streaming replication should not have any role with this flag. The team that does use it should have a single, named replication role.
• rolbypassrls. A role with rolbypassrls = true can bypass row-level security. The team that uses row-level security should not have any role with this flag except a single, named admin role.

The audit query:

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolreplication, rolbypassrls, rolvaliduntil
FROM pg_roles
WHERE rolsuper = true
   OR rolcreatedb = true
   OR rolcreaterole = true
   OR rolreplication = true
   OR rolbypassrls = true
   OR (rolvaliduntil IS NULL AND rolcanlogin = true)
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 gotcha

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

• CREATE USER app_user ... and CREATE USER "app_user" ... create the same role.
• CREATE USER app_user ... and CREATE USER App_User ... create different roles.
• CREATE USER "app_user" ... and CREATE USER "App_User" ... create different roles.

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 role names, never quote them. The convention prevents the bug. The audit query above returns the names as stored, which makes the case-sensitivity obvious in the output.

The audit checklist

The checklist for auditing the user list:

1. List every login-capable role. SELECT rolname FROM pg_roles WHERE rolcanlogin = true ORDER BY rolname;
2. Look for unexpected superusers. SELECT rolname FROM pg_roles WHERE rolsuper = true; Every entry should be justified.
3. Look for roles with CREATEDB or CREATEROLE privileges. Both should be justified.
4. Look for roles with non-expiring passwords. SELECT rolname FROM pg_roles WHERE rolvaliduntil IS NULL AND rolcanlogin = true; Every entry should be a deliberate choice.
5. Look for roles with BYPASSRLS privileges. Should be a single, named admin role, not a default.
6. Look for case-sensitivity issues. The role names should all be lowercase. The audit query returns the names as stored.
7. Look for the postgres superuser’s password. The role should have a strong password and an expiration. The team’s hosting platform should not allow the postgres role to be used by the application.
8. Compare to the application’s expected users. The audit output should match the set of users the application expects. Any unexpected user is a security incident.

The checklist runs in a few minutes. The checklist catches the most common security mistakes. The checklist is the boring answer that saves the team the incident.

For a deeper look at the user-creation side of the same discipline, the create user in postgres guide covers the minimum privilege pattern that should be in place before the audit runs.

The platform-side answer

The platform-side answer for managed Postgres:

• AWS RDS. The audit query runs against the pg_roles view. The master user is a superuser. The application uses a separate user with minimum privileges. The password is in AWS Secrets Manager. The rotation is automated.
• Google Cloud SQL. Same pattern. The audit query runs against the pg_roles view. The application user has minimum privileges. The password is in GCP Secret Manager.
• The RunxBuild platform. Same pattern, integrated. The platform manages the master user, surfaces the audit query in the dashboard, and handles the password rotation. The team focuses on the application.

The pattern is the same: the master user is for the platform, the application user is for the application, the audit is for the team, the rotation is for the secret store. The right platform handles all four.

For a sanity check on the cost of the managed Postgres, the hosting cost calculator gives a real number to compare against. The cheapest platform is rarely the one that surfaces the audit query.

The opinion this post is built on

Listing users in Postgres is one query, but the query’s correctness depends on what the team is asking. The team that asks “show me every login-capable role” gets the right answer from pg_roles WHERE rolcanlogin = true. The team that asks “show me every user with a password” gets a different answer. The team that asks “show me the role hierarchy” gets a third answer. The right answer is the answer that matches the question.

The deeper discipline: the user list is the access control list. The team that audits the user list quarterly is the team that catches the unexpected superuser, the non-expiring password, the case-sensitivity bug, the missing user, the wrong privilege. The query is one line. The audit is the discipline. The team that has both is the team that does not have a security incident caused by a misconfigured role.

The platform is the multiplier. A platform that surfaces the role list, the role attributes, the password expiration, and the audit checklist is a platform where the access control 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 is built around surfacing the things the team needs to audit, and the role list is one of them.

The right answer is the boring answer. pg_roles for the role list. WHERE rolcanlogin = true for the login filter. The audit query for the security check. The platform for the automation. The boring answer is the one that survives the audit.

FAQ

What is the difference between pg_user and pg_roles in Postgres?

pg_user is a legacy view over pg_shadow that exposes only the columns safe for non-superusers. pg_roles is the modern view over pg_authid (the post-10 successor to pg_shadow) that exposes most role attributes safely. For modern Postgres, pg_roles is the right answer.

Can I see the password hash for a role?

Only as a superuser, and only via pg_authid (Postgres 10+) or pg_shadow (older). The pg_user and pg_roles views do not expose the hash. If the team needs to see the hash, the team needs to be a superuser and use the right view.

How do I list only login-capable users?

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 find 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.

How do I find superusers?

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

Every entry should be justified. The team’s application should not be connecting as a superuser.

How often should I audit the user list?

Quarterly is a reasonable cadence for most teams. The audit is the query above, plus a check that every entry in the role list is justified. The team that audits more often catches the issues faster. The team that audits less often has the issues for longer.