How do you list users and roles in PostgreSQL?

Run \du in psql for a formatted list, or query pg_roles when you need something scriptable. PostgreSQL has no separate users: a user is simply a role that carries the LOGIN attribute. pg_user and pg_shadow are compatibility views over pg_authid, and only a superuser can read the password column in pg_authid.

Key facts

| Fact | Value | |---|---| | Users vs roles | The same object since PostgreSQL 8.1; CREATE USER is CREATE ROLE ... LOGIN (CREATE ROLE) | | Readable by everyone | pg_roles — same columns as pg_authid with rolpassword blanked to ******** | | Superuser-only | pg_authid — holds the actual rolpassword hash, so SELECT is restricted | | Membership edges | pg_auth_members — one row per grant, with admin_option, inherit_option, set_option | | Predefined roles | pg_monitor, pg_read_all_stats, pg_read_all_settings, pg_signal_backend; pg_read_all_data / pg_write_all_data added in PostgreSQL 14 (Predefined Roles) | | PostgreSQL 16 change | CREATEROLE can no longer hand out SUPERUSER, REPLICATION or BYPASSRLS, and may only administer roles it owns (Role Attributes) | | Our fleet, superuser check | Across 50 monitored instances, 8.0% have more superuser login roles than the check tolerates |

Why this happens

PostgreSQL keeps roles in a single cluster-wide catalog rather than per database, which is why \du shows the same list no matter which database you are connected to. Roles are not users in the operating-system sense — they are one object type that can own things, be granted privileges, be granted to other roles, and optionally log in.

The confusion usually starts with the three views. pg_authid is the real catalog table, and because it stores the password hash in rolpassword, it is readable only by a superuser. pg_roles is a public view over the same rows with the password replaced by ********, so it is what you should reach for in any script that is not running as a superuser. pg_user and pg_shadow are backward-compatibility views that predate roles: pg_user only shows roles with LOGIN, and it renames the columns (usename, usesuper, valuntil). pg_group is the mirror image, listing roles with members. If a role appears in \du but not in your pg_user query, it simply has no LOGIN attribute.

Attributes are stored as booleans on the role row and control what the role may do cluster-wide, independently of any object-level GRANT (Role Attributes):

• rolsuper — bypasses every permission check except the right to log in.
• rolcreatedb, rolcreaterole — may create databases, or create and alter other roles.
• rolcanlogin — may be used as a connection role name.
• rolreplication — may start replication connections and create/drop replication slots.
• rolbypassrls — is exempt from every row-level security policy.
• rolconnlimit — concurrent connection cap for that role; -1 means unlimited.
• rolvaliduntil — the password expiry timestamp. NULL means the password never expires, and it constrains the password only, never the ability to connect by another authentication method.

Membership is separate again. GRANT role_a TO role_b writes a row into pg_auth_members, and since PostgreSQL 16 that row carries three independent options: ADMIN OPTION (may grant the role onwards), INHERIT (privileges apply automatically) and SET (may SET ROLE to it). Before 16, inheritance came from the member's own rolinherit attribute; now it can be set per grant (GRANT). PostgreSQL 16 also tightened CREATEROLE, which previously amounted to a quiet path to superuser: a CREATEROLE role can now only modify roles it owns, and cannot grant SUPERUSER, REPLICATION or BYPASSRLS at all.

How to detect it

Start in psql. \du lists roles with LOGIN and their attributes, \dg is an exact alias for it, and \du+ adds the role's comment (psql). Add a pattern to filter: \du app*.

For anything you want to script, alert on or diff between environments, query the catalog directly:

SELECT r.rolname,
       r.rolsuper, r.rolcreatedb, r.rolcreaterole,
       r.rolcanlogin, r.rolreplication, r.rolbypassrls,
       r.rolinherit,
       r.rolconnlimit,
       r.rolvaliduntil,
       ARRAY(SELECT b.rolname
               FROM pg_auth_members m
               JOIN pg_roles b ON b.oid = m.roleid
              WHERE m.member = r.oid
              ORDER BY 1) AS member_of
  FROM pg_roles r
 WHERE r.rolname NOT LIKE 'pg\_%'
 ORDER BY r.rolsuper DESC, r.rolname;

Expect one row per role in the cluster. The pg_\_% filter drops the predefined roles, which are present on every instance and are not yours to review. Read the first rows first: anything with rolsuper = t and rolcanlogin = t is a role that can do anything, over the network.

Membership options, which \du does not show at all:

SELECT g.rolname   AS granted_role,
       m2.rolname  AS member,
       am.admin_option,
       am.inherit_option,
       am.set_option
  FROM pg_auth_members am
  JOIN pg_roles g  ON g.oid  = am.roleid
  JOIN pg_roles m2 ON m2.oid = am.member
 ORDER BY 1, 2;

inherit_option and set_option exist from PostgreSQL 16 onwards; on 15 and earlier the same query works without those two columns.

Listing a role's attributes tells you nothing about what it can actually read. For that, use the access-privilege inquiry functions and the information schema (Access Privilege Inquiry Functions):

-- Can this role even connect to this database?
SELECT rolname, has_database_privilege(rolname, current_database(), 'CONNECT') AS can_connect
  FROM pg_roles WHERE rolcanlogin;

-- Effective table grants, membership included
SELECT grantee, table_schema, table_name, privilege_type
  FROM information_schema.role_table_grants
 WHERE grantee NOT IN ('postgres', 'PUBLIC')
 ORDER BY 1, 2, 3;

information_schema.role_table_grants only shows grants where the current user is the grantor, the grantee, or a member of the grantee role (role_table_grants) — so run it as a superuser if you want the whole picture, and cross-check with \dp (or \z), which prints the raw ACL on each table.

Three audit queries worth keeping. Superusers that can log in:

SELECT rolname FROM pg_roles WHERE rolsuper AND rolcanlogin;

Login roles whose password never expires — NULL rolvaliduntil is the default, so this is usually most of them:

SELECT rolname, rolvaliduntil
  FROM pg_roles
 WHERE rolcanlogin AND rolname NOT LIKE 'pg\_%'
   AND (rolvaliduntil IS NULL OR rolvaliduntil > now())
 ORDER BY rolvaliduntil NULLS FIRST;

Orphaned login roles — those that own nothing, are a member of nothing, and have never been seen connecting:

SELECT r.rolname
  FROM pg_roles r
 WHERE r.rolcanlogin
   AND r.rolname NOT LIKE 'pg\_%'
   AND NOT EXISTS (SELECT 1 FROM pg_auth_members m WHERE m.member = r.oid)
   AND NOT EXISTS (SELECT 1 FROM pg_class c   WHERE c.relowner = r.oid)
   AND NOT EXISTS (SELECT 1 FROM pg_database d WHERE d.datdba  = r.oid)
   AND NOT EXISTS (SELECT 1 FROM pg_stat_activity a WHERE a.usename = r.rolname);

pg_stat_activity is a point-in-time snapshot, so treat that last condition as a hint rather than proof. A role that connects nightly will look orphaned at 10am.

How MyDBA shows this

MyDBA collects the role catalog on a slow (roughly daily) cadence and scores four security checks against it: H1 counts superuser login roles and warns above 2, critical above 5; H4 counts elevated-privilege roles (CREATEROLE, CREATEDB, REPLICATION, BYPASSRLS) on the same thresholds when check depth is standard or full; H3 reads password_encryption and fails anything that is not scram-sha-256; H2 reports observed SSL usage as informational only, because sampled connections are evidence, not enforcement. Across 50 monitored instances, 8.0% currently fail H1 and, of the 33 where password_encryption could be read, 15.2% fail H3. Failing checks become rows on the Action Items page at /recommendations, ranked alongside vacuum, index and configuration findings rather than in a separate security list, so you see role hygiene against everything else competing for the same maintenance window. If you want to see what these checks say about your own roles, start with the free PostgreSQL health check.

How to fix it

1. Cut the superuser list to the roles that genuinely need it. For each one, decide whether a predefined role covers the actual need: pg_monitor for a monitoring agent, pg_read_all_data for a reporting role on PostgreSQL 14 or later (Predefined Roles). Then ALTER ROLE app_reporting NOSUPERUSER; GRANT pg_read_all_data TO app_reporting; 2. Strip the attributes nobody uses. ALTER ROLE r NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS; BYPASSRLS in particular silently defeats every policy you wrote — see the row-level security guide. 3. Give human login roles an expiry. ALTER ROLE alice VALID UNTIL '2027-01-01'; This bounds the password, not the session, so pair it with a calendar reminder rather than assuming it revokes access. 4. Cap runaway roles. ALTER ROLE batch_etl CONNECTION LIMIT 5; keeps one client from consuming the whole max_connections budget. 5. Drop orphaned roles, carefully. DROP ROLE fails while the role owns objects; run REASSIGN OWNED BY old_role TO new_owner; then DROP OWNED BY old_role; in each database before dropping (DROP ROLE). 6. Re-check membership after any change. Re-run the pg_auth_members query above; a grant with admin_option lets its holder re-create the access you just removed.

How to prevent it

Keep role creation in version control rather than in somebody's psql history, so \du and the repository agree. Grant through group roles — one app_read role that humans and services are members of — instead of attaching privileges to individual login roles, so revoking access is one REVOKE rather than an audit. Set password_encryption = scram-sha-256 in postgresql.conf and rotate any password created while md5 was in effect, since the stored hash does not change until the password is set again (Password Authentication).

On PostgreSQL 16 and later, prefer a CREATEROLE administrator over a second superuser: it is now genuinely constrained rather than superuser by another name. And diff the role list between staging and production periodically — a role that exists in only one of them is usually either a leftover or a gap. The broader set of controls is in the PostgreSQL security checklist, and if a role exists but still cannot connect, the problem is normally authentication rather than the catalog: see no pg_hba.conf entry for host.

FAQ

What is the difference between \du and \dg?

Nothing. In modern psql they are the same command — \dg is kept from the era when groups and users were distinct objects, and both now list roles from pg_roles. \du+ adds the role description column, and either accepts a pattern such as \du app* to filter the output.

Why can I not query pg_authid?

Because it contains rolpassword, the stored password hash or SCRAM verifier. PostgreSQL restricts SELECT on that catalog to superusers for exactly that reason. Use pg_roles instead: identical rows and columns, with the password replaced by ********. Only queries that genuinely need the hash — a password-reuse audit, for instance — need superuser access.

How do I list only login users, not group roles?

Filter on the LOGIN attribute: SELECT rolname FROM pg_roles WHERE rolcanlogin;. The legacy pg_user view applies that filter for you but renames the columns, so the explicit filter is easier to read alongside the rest of your audit queries.

Does VALID UNTIL expire the role or the password?

Only the password. Once rolvaliduntil has passed, password authentication for that role fails, but any authentication method that does not use the stored password — trust, peer, certificate, GSSAPI — keeps working, and existing sessions are not disconnected. Treat it as one control among several, not as a way to revoke a role.

How do I see what a role can actually read?

Attributes are not privileges. Use has_table_privilege() and has_database_privilege() for specific questions, information_schema.role_table_grants for a full list (run as superuser to see grants you did not issue yourself), and \dp in psql to print the raw ACL string on each table.

Part of the The PostgreSQL health check checklist guide. Last verified against PostgreSQL 18, 2026-09-17.