List PostgreSQL Roles and Their Privileges

How to List PostgreSQL Roles and Privileges

Which accounts can log in, which can create databases or other roles, and which are quietly superusers? PostgreSQL answers all three from pg_roles, a view over the role catalog that every user can read. Joined to pg_auth_members it also shows who belongs to which group, and the privilege-inquiry functions turn that into a list of who can touch a given table.

Purpose and Overview

PostgreSQL uses one object, the role, for both users and groups. A role with the LOGIN attribute is what other systems call a user account. A role without it is usually a group that collects privileges, which are then passed to login roles by granting membership. Roles belong to the whole cluster, not to one database, so a single audit covers every database on the server.

A role audit has two halves. The first is attributes: superuser, create-database, create-role, replication, bypass-row-security, connection limit and password expiry. The second is membership: which roles hold which other roles, and with what options. The queries below return both, then go one step further to the privileges a role ends up with on actual objects.

Sample Code

All roles with their attributes:

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

Role membership on PostgreSQL 16 and later:

 1SELECT
 2    r.rolname          AS member,
 3    g.rolname          AS member_of,
 4    b.rolname          AS granted_by,
 5    am.admin_option,
 6    am.inherit_option,
 7    am.set_option
 8FROM pg_catalog.pg_auth_members am
 9JOIN pg_catalog.pg_roles r ON r.oid = am.member
10JOIN pg_catalog.pg_roles g ON g.oid = am.roleid
11JOIN pg_catalog.pg_roles b ON b.oid = am.grantor
12ORDER BY g.rolname, r.rolname;

On PostgreSQL 15 and earlier, inherit_option and set_option do not exist; drop those two columns and the rest runs unchanged.

Login roles that can read one table:

1SELECT rolname
2FROM pg_catalog.pg_roles
3WHERE rolcanlogin
4  AND has_table_privilege(rolname, 'public.orders', 'SELECT')
5ORDER BY rolname;

Superusers and replication roles:

1SELECT rolname, rolsuper, rolreplication
2FROM pg_catalog.pg_roles
3WHERE rolsuper OR rolreplication
4ORDER BY rolname;

Notes: pg_roles works on all supported versions. Replace public.orders with a table in the database you are connected to. The first query hides the predefined pg_ roles; remove the WHERE clause to see them.

Code Breakdown

The queries read three catalog objects and one function family.

pg_roles

pg_roles is a publicly readable view of pg_authid with the password field blanked out. It is safe to query as any role, and it carries an oid column for joins to pg_auth_members. The pg_authid catalog itself holds the password hash and is not publicly readable.

Attribute columns

• rolsuper — true for a superuser, who bypasses all permission checks except the right to log in.
• rolcreaterole — the role can create more roles. In PostgreSQL 16 and later it can alter and drop only roles it holds with ADMIN OPTION, which it receives automatically for roles it creates.
• rolcreatedb — the role can create databases.
• rolcanlogin — the role can be the initial session identity; false marks a group role.
• rolreplication — the role can initiate replication connections and create and drop replication slots.
• rolbypassrls — the role bypasses every row-level security policy.
• rolconnlimit — maximum concurrent connections for this role; -1 means no limit.
• rolvaliduntil — password expiry time, used only for password authentication; null means no expiry.

pg_auth_members

Each row says that member belongs to roleid. The grantor column names the role that made the grant. From PostgreSQL 16 the row also records inherit_option (the member automatically uses the group's privileges) and set_option (the member can run SET ROLE to the group). admin_option means the member can grant the membership to others.

has_table_privilege

has_table_privilege(role, table, privilege) answers the question the documentation phrases as "Does user have privilege for table?" The privilege can be SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER or, on recent versions, MAINTAIN. The function page does not say how it treats membership, so for a role that gets access through a group, confirm the result by comparing it with pg_has_role and the inherit_option of the membership.

Key Role Concepts

Attributes are not inherited

Role attributes such as CREATEDB are not passed on by membership. A member of a role that has CREATEDB cannot create databases, even when the membership has the inherit option. The member can do so only after SET ROLE to that role, when the membership allows it. Privileges on objects behave differently: they are inherited.

Inheritance changed in 16

Before PostgreSQL 16, inheritance was an attribute of the member role: NOINHERIT applied to all of its memberships. From 16 it is decided per grant, with GRANT ... WITH INHERIT TRUE or FALSE, and the role attribute only sets the default for new grants. The inherit_option column reflects the per-grant value, so audits on 16 should read it rather than rolinherit.

Predefined roles

Roles named pg_... are built in and carry specific powers. pg_signal_backend arrived in PostgreSQL 9.6. pg_monitor, pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables came in PostgreSQL 10. pg_read_all_data and pg_write_all_data came in 14, and pg_use_reserved_connections in 16. The file-access roles pg_read_server_files, pg_write_server_files and pg_execute_server_program can be used to reach superuser-level access, so treat membership in them like superuser.

The bootstrap superuser

Every cluster has one superuser created by initdb. It takes the name of the operating system user that ran initdb unless another name is given, and is often called postgres.

Practical Applications

Superuser audit

Run the superuser query on a schedule and compare it with the approved list. Any new name is a finding. To demote an account that no longer needs the power:

1ALTER ROLE report_user NOSUPERUSER;

Building group roles

Grant privileges to a group role and add users to it, so a new hire is one statement:

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

GRANT ... ON ALL TABLES names the tables in the schema, and it also covers views and foreign tables. For tables created later, set a default:

1ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
2    GRANT SELECT ON TABLES TO readonly;

Default privileges apply to objects created by the named role (or the current role if FOR ROLE is omitted), so name the role that creates the tables. They do not change privileges on objects that already exist. On PostgreSQL 14 and later, granting pg_read_all_data is a shorter route when read access to everything is acceptable.

Who can read this table

The table-privilege query above is the check to run before a data-protection review. Superusers pass every check, so they appear in the result whatever the grants say. Adding AND NOT rolsuper removes them, but the remaining rows are not only roles with an explicit grant: they also include roles that reach the table through group membership or through a predefined role such as pg_read_all_data.

Database-level rights

1SELECT datname, datacl, datconnlimit
2FROM pg_database
3ORDER BY datname;

datacl holds the access privileges on each database and datconnlimit its connection limit (-1 means no limit). An empty privileges entry means the object has default privileges, which always include all privileges for the owner.

Version Compatibility

pg_roles and the rol... columns listed are stable across supported versions. PostgreSQL 16 is the line that matters for audits: membership gained the inherit_option and set_option columns, CREATEROLE was restricted to roles the creator administers through ADMIN OPTION, and the inheritance default moved from the role to the grant.

On 16 and later a CREATEROLE role can alter and drop only roles it holds ADMIN OPTION on, which by default means the roles it created. It cannot create REPLICATION users or grant or revoke the REPLICATION or BYPASSRLS privilege, and it has no power over superusers. On 15 and earlier, CREATEROLE could change many aspects of any non-superuser role. The predefined roles listed above set the minimum version for each grant.

Best Practices

• Keep superusers few — list them on a schedule and demote any that are not required.
• Own and use are separate — the role that owns tables should not be the role applications log in with, because an owner can grant itself everything on its own objects.
• Use group roles — grant to groups and add members, and set default privileges for future tables.
• Prefer predefined roles — pg_monitor and pg_read_all_data are narrower than superuser for monitoring and read access.
• Test absence of access — loop has_table_privilege over login roles to check that a role cannot do what it should not.
• Set expiry deliberately — rolvaliduntil ends password logins only; it does not affect certificate or other non-password methods.

References

• PostgreSQL: Database Roles — Overview of roles, attributes, membership and predefined roles.
• PostgreSQL: pg_roles — Columns of the role view and its relation to pg_authid.
• PostgreSQL: pg_auth_members — Membership catalog with admin_option, inherit_option and set_option.
• PostgreSQL: Role Attributes — What each attribute grants, including the limits of CREATEROLE.
• PostgreSQL: Predefined Roles — Built-in pg_ roles and the access each carries.
• PostgreSQL 16 Release Notes — Role inheritance, CREATEROLE and reserved-connection changes.
• PostgreSQL: Privileges — Privilege types, default privileges and how access privilege entries are read.
• PostgreSQL: pg_database — Database catalog with the datacl and datconnlimit columns.
• EDB: Auditing Users and Roles in PostgreSQL — Checking which roles hold a privilege on a table with has_table_privilege, and combining privileges across inherited roles with pg_has_role.