PostgreSQL - Users and Roles Queries - \du+ in DBever

 

PostgreSQL Users and Roles Queries

Introduction

In PostgreSQL, there is no strict distinction between users and roles. Everything is a role; a user is simply a role that has the LOGIN privilege (rolcanlogin = true). This document provides practical SQL queries to audit roles, role memberships, and privileges in a PostgreSQL database environment. All names are kept generic for reuse.

Key Concept

Users: Roles with LOGIN privilege (rolcanlogin = true)

Groups/Roles: Roles without LOGIN privilege (rolcanlogin = false)

A role can be a member of other roles, inheriting permissions.

1.      List All Roles with Attributes and Membership

 

SELECT r.rolname,

       r.rolsuper,

       r.rolinherit,

       r.rolcreaterole,

       r.rolcreatedb,

       r.rolcanlogin,

       r.rolconnlimit,

       r.rolvaliduntil,

       ARRAY(

           SELECT b.rolname

           FROM pg_catalog.pg_auth_members m

           JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)

           WHERE m.member = r.oid

       ) AS memberof,

       r.rolreplication,

       r.rolbypassrls

FROM pg_catalog.pg_roles r

WHERE r.rolname !~ '^pg_'

ORDER BY 1;

Explanation

This query lists all non-system roles and shows:

• Superuser privileges

• Database creation privileges

• Login capability

• Connection limits

• Membership in other roles

• Replication privileges

• Row-level security bypass capability

2. List Users and Their Granted Roles

SELECT

    u.rolname AS username,

    r.rolname AS granted_role

FROM pg_auth_members m

JOIN pg_roles u ON m.member = u.oid

JOIN pg_roles r ON m.roleid = r.oid

WHERE u.rolcanlogin = true

ORDER BY u.rolname, r.rolname;

Explanation

This query shows which roles are granted to login users only, making it useful for access audits.

3. Get Roles Assigned to a Specific User

SELECT roleid::regrole,

       member::regrole

FROM pg_auth_members

WHERE member = 'username'::regrole;

Explanation

Replace 'username' with the target user. This query returns all roles granted to that specific user.

4. Alternative Query — User Membership View

SELECT

    r.oid AS role_id,

    r.rolname,

    r.rolcanlogin,

    COALESCE(b.rolname, '') AS memberof

FROM pg_catalog.pg_roles r

LEFT JOIN pg_catalog.pg_auth_members m ON r.oid = m.member

LEFT JOIN pg_catalog.pg_roles b ON m.roleid = b.oid

WHERE r.rolcanlogin = true

  AND r.rolname !~ '^pg_';

Explanation

This query focuses only on login roles (users) and shows which group roles they belong to.

Common Audit Use Cases

• Security audits

• Access reviews

• Compliance checks

• Migration planning

• Privilege troubleshooting

Best Practices

• Avoid granting superuser unless absolutely required

• Use group roles for permission management

• Periodically audit role memberships

• Monitor replication and bypass RLS privileges

• Remove unused roles

Conclusion

Understanding PostgreSQL roles and memberships is essential for secure database administration. The queries provided in this guide help administrators audit and manage access efficiently.