psql View Users: The Three Commands That Show Every Account in the Database

Q: How do I list all users in PostgreSQL?

`\du` in psql for the quick list, or `SELECT rolname FROM pg_roles;` for the SQL form. Both show every role in the database, including users (login-enabled roles) and groups (login-disabled roles).

Q: What is the difference between pg_user and pg_roles?

pg_user is a legacy view that shows only roles that can log in, with the old use* column naming. pg_roles is the source of truth and shows every role, every attribute, every configuration setting.

Q: How do I see only the superusers?

`SELECT rolname FROM pg_roles WHERE rolsuper;`. The list should be very short — ideally one or two roles. If it is longer, the database is over-permissioned.

Q: How do I see roles with a password expiry?

`SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolvaliduntil IS NOT NULL;`. Roles with rolvaliduntil set to a past date cannot log in with a password.

Q: Can I see which roles are members of a group?

`SELECT rolname FROM pg_roles WHERE 'group_name' = ANY (rolmember);`. The rolmember column is the array of role OIDs the role is a member of.

Three psql commands show every user (or role) in a PostgreSQL database: \du for the quick list, SELECT * FROM pg_user; for the system catalog with login info, and SELECT * FROM pg_roles; for the full role catalog with privileges and configuration. Each one answers a different question. The reason “psql view users” is still a top search is that the meta-commands are powerful but inconsistent — \du shows roles, pg_user shows users, and pg_roles shows everything but is not quite the same thing as a user.

The list of “how to see users” commands is short. The interesting decisions are which one to use when, and what the difference is between a “user” and a “role” in PostgreSQL.

The direct answer: three commands, three questions
\du: the quick list
pg_user: the catalog with login info
pg_roles: the full role catalog
The user-vs-role distinction
Filtering by attribute: superusers, replication, can-login
The deployment-time case: which users can connect to the production database
FAQ

The direct answer: three commands, three questions

-- 1. The quick list (psql meta-command, not SQL)
\du

-- 2. The system catalog with login info
SELECT usename, usesuper, usecreatedb, userepl
  FROM pg_user;

-- 3. The full role catalog
SELECT rolname, rolsuper, rolcanlogin, rolcreatedb, rolreplication
  FROM pg_roles;

The first is a psql shortcut, not SQL. The second is the legacy user catalog. The third is the modern source of truth, and it is what psql itself uses to render \du.

`\du`: the quick list

The fastest way to see the list. Inside psql:

\du

The output is a table with three columns: Role name, List of roles with membership, and the implicit description column. A typical output:

                                     List of roles
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
 admin       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 app_user    |                                                            | {}
 readonly    |                                                            | {readonly_group}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 reporting   |                                                            | {}

The Attributes column shows the flags the role has: Superuser, Create role, Create DB, Replication, Bypass RLS, and a few others. The Member of column shows role inheritance: readonly is a member of readonly_group, which means it inherits the group’s privileges.

The \du command is just a wrapper around pg_roles. To see the same output as SQL:

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb,
       rolcanlogin, rolreplication, rolbypassrls
  FROM pg_roles
 ORDER BY rolname;

This is the form that works in any client, not just psql.

`pg_user`: the catalog with login info

pg_user is a view that exists for backward compatibility. It is a subset of pg_roles that filters to roles that have login (rolcanlogin = true) and renames the columns for the older “user” naming convention.

SELECT * FROM pg_user;

The output includes usename (the role name), usesuper (is superuser), usecreatedb (can create databases), userepl (can initiate replication), usebypassrls (can bypass row-level security), passwd (always ******** — the password is not exposed), valuntil (password expiry), and useconfig (per-role runtime configuration).

The columns are renamed from the pg_roles convention (rol* instead of use*). The legacy naming is what makes pg_user a less popular choice in modern code; new code should use pg_roles.

The interesting column: passwd is always ******** in the catalog, regardless of the actual password. PostgreSQL stores the password hash internally and never exposes it through SQL. The hash is in pg_authid (which is not user-readable), and the only way to see it is with superuser access to the system catalog directly.

`pg_roles`: the full role catalog

The source of truth. Every user, every group, every role, with the full attribute set:

SELECT rolname, rolsuper, rolcanlogin, rolcreatedb, rolreplication,
       rolbypassrls, rolconnlimit, rolvaliduntil
  FROM pg_roles
 ORDER BY rolname;

The interesting columns:

rolcanlogin — can the role be used to log in? Roles with this set are “users” in the legacy sense. Roles without it are “groups.”
rolsuper — is the role a superuser? Superusers bypass every permission check.
rolcreatedb — can the role create databases?
rolreplication — can the role initiate replication? Required for logical replication.
rolbypassrls — can the role bypass row-level security? Useful for backup users and admin accounts.
rolconnlimit — connection limit for the role. -1 means no limit.
rolvaliduntil — password expiry. NULL means no expiry.

For an audit, the right query is:

SELECT rolname, rolsuper, rolcanlogin, rolcreatedb, rolreplication,
       rolbypassrls, rolconnlimit,
       COALESCE(rolvaliduntil::text, '(no expiry)') AS password_expiry
  FROM pg_roles
 ORDER BY rolsuper DESC, rolcanlogin DESC, rolname;

Superusers first, then login-enabled roles, then everything else. The output is a clean audit trail for who can do what in the database.

The user-vs-role distinction

PostgreSQL has roles, not users. The legacy “user” concept (with CREATE USER) is a role with rolcanlogin = true. The “group” concept (with CREATE GROUP) is a role with rolcanlogin = false. Both are stored in pg_roles.

This means:

CREATE USER app_user WITH PASSWORD '...'; is equivalent to CREATE ROLE app_user WITH LOGIN PASSWORD '...';
CREATE GROUP readonly; is equivalent to CREATE ROLE readonly NOLOGIN;
CREATE ROLE admin WITH LOGIN SUPERUSER; creates a superuser role that can log in

The \du meta-command hides this distinction by showing all roles. The \dg meta-command (same output, different name) is the legacy way to show groups specifically. In modern code, just use pg_roles and filter with rolcanlogin if you want users only.

For an application, the right pattern is:

One role per application service, with rolcanlogin = true and rolcreatedb = false and rolsuper = false.
One role per read-only reporting user, with the right GRANT privileges.
One superuser role for admin work, with a strong password stored in a secret manager.

The common filtered queries:

-- Every superuser
SELECT rolname FROM pg_roles WHERE rolsuper;

-- Every role that can log in
SELECT rolname FROM pg_roles WHERE rolcanlogin;

-- Every role that can initiate replication
SELECT rolname FROM pg_roles WHERE rolreplication;

-- Every role that can create databases
SELECT rolname FROM pg_roles WHERE rolcreatedb;

-- Every role that bypasses RLS (audit this — usually a small set)
SELECT rolname FROM pg_roles WHERE rolbypassrls;

For an audit, run the superuser query first. The list should be very short (ideally one or two roles). If it is longer, the database is over-permissioned. The bypass-rls query is the second one to check — every role in that list is a potential audit-log blind spot.

The replication list is also worth knowing. Every role in that list can read the WAL stream, which means it can read every committed change. The list should be the minimum number of roles needed to support the replication topology.

The deployment-time case: which users can connect to the production database

For a production database, the audit is the same query with two extra filters:

SELECT rolname, rolconnlimit, COALESCE(rolvaliduntil::text, '(no expiry)') AS password_expiry
  FROM pg_roles
 WHERE rolcanlogin
 ORDER BY rolname;

The audit checklist:

Every login role has a non-default password. (Check the password rotation policy, not the catalog — the catalog never shows the password.)
Every login role has a rolvaliduntil date. A role with no expiry is a long-lived credential waiting to leak.
Every login role has a rolconnlimit appropriate for its purpose. The application role should have a limit; the admin role should not need one but should have one anyway as a safety check.
The postgres superuser has its password rotated and stored in a secret manager, not in the application code.

For a managed database (Render Postgres, Fly Postgres, AWS RDS), the platform provides a default user, and the right pattern is to create a per-application user with the minimum privileges the app needs, and to use the platform-managed user only for admin work. The connection string the app uses is the per-application user’s; the connection string the platform’s admin UI uses is the managed one. For a hosted equivalent where the role management is the platform’s job, the MCP server pattern extends to a database tool that gives AI agents scoped, auditable access without handing them the superuser credentials.

How this fits the rest of the stack

Listing users is also a cost audit moment — every user is a connection the database has to handle, every role is a permission the audit has to verify, and every superuser still in use is a risk the team is paying for in incident response. The team’s mental model for the database cost is the instance, the storage, the bandwidth, and the connection count. The RunxBuild hosting calculator is the right place to model that — pick the Postgres tier, the storage, the connection count, and the expected query volume, and the calculator shows what the database costs at the team’s actual usage.

Useful related references:

FAQ

How do I list all users in PostgreSQL?

\du in psql for the quick list, or SELECT rolname FROM pg_roles; for the SQL form. Both show every role in the database, including users (login-enabled roles) and groups (login-disabled roles).

What is the difference between `pg_user` and `pg_roles`?

pg_user is a legacy view that shows only roles that can log in, with the old use* column naming. pg_roles is the source of truth and shows every role, every attribute, every configuration setting. New code should use pg_roles.

How do I see only the superusers?

SELECT rolname FROM pg_roles WHERE rolsuper;. The list should be very short — ideally one or two roles. If it is longer, the database is over-permissioned.

How do I see the password for a user?

You cannot, through SQL. PostgreSQL stores the password hash internally and the pg_user.passwd column is always ********. The hash is in pg_authid, which is only readable by superusers directly through the file system, not through SQL.

How do I see roles with a password expiry?

SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolvaliduntil IS NOT NULL;. Roles with rolvaliduntil set to a past date cannot log in with a password (the connection is rejected with “password authentication failed for user”).

Can I see which roles are members of a group?

SELECT rolname FROM pg_roles WHERE 'group_name' = ANY (rolmember);. The rolmember column is the array of role OIDs the role is a member of, which is the modern way to query role inheritance.