How to list users in PostgreSQL

Quick Answer

In psql, run \du:

\du

From SQL, query pg_roles and skip the built-in pg_* roles:

SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole
FROM pg_roles
WHERE rolname !~ '^pg_'
ORDER BY rolname;

Users and roles are the same thing

PostgreSQL has no separate "user" object. Everything is a role. A user is a role with the LOGIN attribute, and a group is a role without it that other roles are members of. CREATE USER is the same as CREATE ROLE ... LOGIN.

That's why the commands below list all roles, and why you'll see entries marked Cannot login: those are groups, not people or apps.

List users in psql

postgres=# \du
                              List of roles
 Role name  |                         Attributes
------------+------------------------------------------------------------
 app        | 5 connections                                             +
            | Password valid until 2027-01-01 00:00:00+00
 readers    | Cannot login
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS

An empty Attributes column means an ordinary role that can log in and has no special powers.

postgres=# GRANT readers TO auditor WITH INHERIT FALSE, SET TRUE;
postgres=# \drg
 Role name | Member of |   Options    | Grantor
-----------+-----------+--------------+----------
 app       | readers   | INHERIT, SET | postgres
 auditor   | readers   | SET          | postgres

List users with a SQL query

For apps, GUIs or scripts, query pg_roles. It's readable by every role, and it masks passwords as ********.

SELECT rolname        AS role,
       rolcanlogin    AS can_login,
       rolsuper       AS superuser,
       rolcreatedb    AS create_db,
       rolcreaterole  AS create_role,
       rolconnlimit   AS conn_limit,     -- -1 means no limit
       rolvaliduntil  AS password_expires
FROM pg_roles
WHERE rolname !~ '^pg_'                  -- hide built-in roles like pg_read_all_data
ORDER BY rolname;

To list only roles that can log in, the "users", add AND rolcanlogin, or use the older pg_user view, which contains login roles only:

SELECT usename, usesuper, usecreatedb, valuntil FROM pg_user ORDER BY usename;

List group membership

To see which users belong to which group role, the same information \drg shows:

SELECT r.rolname AS group_role,
       m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
WHERE m.rolname !~ '^pg_'
ORDER BY 1, 2;

Role membership doesn't tell you which tables a role can read. That comes from grants, which \dp tablename shows for a single table.

See who is connected right now

\du lists the roles that exist, not who is logged in. For live sessions, use pg_stat_activity:

SELECT usename, datname, application_name, client_addr, state, backend_start
FROM pg_stat_activity
WHERE usename IS NOT NULL
ORDER BY backend_start;

And to see who you are in the current session:

SELECT current_user, session_user;

current_user changes after SET ROLE. session_user is always the role you logged in as.

Frequently Asked Questions

What is the difference between \du and \dg?

None. Both list every role. They are kept as two names because users and groups are both roles in PostgreSQL.

How do I list only users that can log in?

SELECT rolname FROM pg_roles WHERE rolcanlogin, or SELECT usename FROM pg_user, which contains login roles only.

Where did the "Member of" column in \du go?

PostgreSQL 16 removed it from \du. Use the new \drg command, or query pg_auth_members.

Can I see a user's password?

No. PostgreSQL stores only a SCRAM-SHA-256 hash, readable by superusers in pg_authid. Set a new password with ALTER ROLE name PASSWORD instead.

What are the pg_ roles in the list?

Predefined roles such as pg_read_all_data and pg_monitor, 14 of them on PostgreSQL 16. Grant them to users for common permission sets. \du hides them; \duS shows them.