List PostgreSQL Roles and Their Privileges

calendar Apr 17, 2026 / · 4 min read · postgresql administration security pg_roles  ·
Share on: linkedin copy

How to List PostgreSQL Roles and Privileges

PostgreSQL uses roles for both users and groups. A role with the LOGIN attribute is a user account. A role without LOGIN is typically a group role used to collect privileges that are then granted to login roles. Understanding which roles exist, what privileges they have, and which roles belong to which groups is a fundamental security audit task.

This article shows SQL queries to list all roles, their key attributes, and role membership.

Sample Code

List all roles with key attributes:

 1SELECT
 2    rolname                            AS role_name,
 3    rolsuper                           AS superuser,
 4    rolcreaterole                      AS can_create_roles,
 5    rolcreatedb                        AS can_create_db,
 6    rolcanlogin                        AS can_login,
 7    rolreplication                     AS replication_role,
 8    rolbypassrls                       AS bypass_rls,
 9    rolconnlimit                       AS connection_limit,
10    rolvaliduntil                      AS password_expiry
11FROM
12    pg_catalog.pg_roles
13ORDER BY
14    rolsuper DESC,
15    rolcanlogin DESC,
16    rolname;

List only login roles (users):

 1SELECT
 2    rolname         AS username,
 3    rolsuper        AS superuser,
 4    rolcreatedb     AS can_create_db,
 5    rolconnlimit    AS connection_limit,
 6    rolvaliduntil   AS password_expiry
 7FROM
 8    pg_catalog.pg_roles
 9WHERE
10    rolcanlogin = true
11ORDER BY
12    rolname;

List role membership (who belongs to which group):

 1SELECT
 2    r.rolname                          AS role,
 3    m.rolname                          AS member_of,
 4    am.admin_option                    AS is_admin
 5FROM
 6    pg_catalog.pg_auth_members am
 7    JOIN pg_catalog.pg_roles r  ON r.oid  = am.member
 8    JOIN pg_catalog.pg_roles m  ON m.oid  = am.roleid
 9ORDER BY
10    m.rolname, r.rolname;

Find all superuser accounts:

1SELECT rolname
2FROM pg_catalog.pg_roles
3WHERE rolsuper = true
4ORDER BY rolname;

Notes: Works on all supported PostgreSQL versions. pg_roles is a view over pg_authid that hides the password hash column, making it safe to query as any role.

Code Breakdown

  • pg_catalog.pg_roles — A view over pg_authid that exposes role attributes without the password hash. Safe to query as any user.
  • rolname — The role name as used in SQL (CREATE ROLE, GRANT, SET ROLE).
  • rolsuper — If true, this role is a superuser and bypasses all permission checks. Keep the number of superusers minimal.
  • rolcreaterole — If true, this role can create, alter, and drop other roles.
  • rolcreatedb — If true, this role can create new databases.
  • rolcanlogin — If true, this role can authenticate and open a database session. False means it is a group role.
  • rolreplication — If true, this role can initiate streaming replication and run pg_backup_start()/pg_backup_stop().
  • rolbypassrls — If true, this role bypasses all Row Level Security policies.
  • rolconnlimit — Maximum number of concurrent connections. -1 means unlimited.
  • rolvaliduntil — Password expiry timestamp. Null means no expiry.
  • pg_catalog.pg_auth_members — Stores role membership. member is the OID of the role that belongs to the group, roleid is the OID of the group.
  • admin_option — If true, the member can grant this group membership to other roles.

Key Points

  • Every PostgreSQL database cluster has a postgres superuser role created at initdb time.
  • Predefined system roles starting with pg_ (e.g., pg_monitor, pg_read_all_stats, pg_signal_backend) are built-in since PostgreSQL 10. They are not superusers but grant specific elevated privileges.
  • A role that is a member of a group role inherits its privileges by default. This can be controlled with NOINHERIT.
  • Passwords are not visible through pg_roles. The underlying pg_authid table stores the hash, but only superusers can query it.

Insights and Explanations

Superuser sprawl is a security risk:

Every superuser can do anything in the cluster — bypass RLS, read all tables, terminate any connection, run COPY to/from files on the server. Audit the superuser list regularly and revoke superuser from accounts that do not need it.

Demote a superuser to a regular role:

1ALTER ROLE myuser NOSUPERUSER;

Using group roles to manage permissions:

Rather than granting privileges directly to every login role, grant them to a group role, then add users to the group:

1CREATE ROLE readonly NOLOGIN;
2GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
3GRANT readonly TO alice;
4GRANT readonly TO bob;

When a new table is added, only the group role needs updating.

Checking effective privileges for a role:

 1-- See database-level privileges
 2SELECT
 3    datname,
 4    datacl
 5FROM pg_database
 6WHERE datname = 'mydb';
 7
 8-- See table-level privileges
 9SELECT
10    grantee,
11    table_name,
12    privilege_type
13FROM information_schema.role_table_grants
14WHERE grantee = 'alice';

Checking which roles have the replication attribute:

1SELECT rolname
2FROM pg_catalog.pg_roles
3WHERE rolreplication = true;

Only replication roles should be used in pg_hba.conf replication entries.

Additional Considerations

  • Permissions: Any user can query pg_roles. Only superusers can query pg_authid (which includes the password hash).
  • Row Level Security: rolbypassrls should be assigned with caution. Even non-superuser roles with BYPASSRLS can read rows that RLS policies are meant to hide.
  • Password expiry: Setting rolvaliduntil enforces password rotation but only for password-based authentication. It does not affect certificate or peer authentication.
  • \du command: In psql, \du runs a formatted version of a similar query. The SQL approach used here is more portable and scriptable.

References

Posts in this series