How to List Users in PostgreSQL (the Two Meanings Nobody Explains)

Most “psql list users” tutorials start with \du and stop there. That is half the answer, and it is the half that rarely matches what a developer actually needs. “List users” in PostgreSQL can mean two completely different things: the role accounts inside the database, and the operating system accounts that own the database process. The two overlap at the postgres superuser and diverge everywhere else. Mixing them up is how you end up creating a role that the app cannot authenticate as, or trying to grant a permission to an account that does not exist on the server.

The short version: use \du and SELECT rolname FROM pg_roles for database roles (the ones your app connects as). Use \du+ when you also need membership details. Never create database users by editing /etc/passwd on a managed host — your host will not let you, and even if it did, the database would not see them.

The direct answer

To list PostgreSQL database users (called “roles”), connect with psql and run the meta-command \du. The output shows every role in the cluster, its attributes (superuser, createdb, login, etc.), and the role it belongs to. For a SQL version, run SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin, rolreplication FROM pg_roles;. Both give you the same list, with \du formatted for humans and the pg_roles query useful when you want to filter or join against other tables.

If you actually meant operating system users, that is a different question and a different command, covered in the section below.

The two meanings, side by side

If you are asking about…	Use this command	What you get
Database roles (login accounts inside Postgres)	\du or SELECT rolname FROM pg_roles;	All roles defined in the cluster
Roles with full attribute details	\du+ or query pg_roles columns directly	Roles plus superuser, createdb, login flags
Members of a role (group memberships)	\du <rolename> or join pg_auth_members	Roles granted to that role
Currently connected users	SELECT usename, application_name, client_addr FROM pg_stat_activity;	Active sessions, not role definitions
Operating system users	cat /etc/passwd or getent passwd	OS accounts on the host

A quick way to keep them straight: Postgres roles are stored in pg_authid and exposed read-only through pg_roles. OS users are stored in /etc/passwd and have nothing to do with the database until you map them via peer authentication in pg_hba.conf.

Listing roles the developer way

The most useful queries for an app backend are not the textbook ones. They are the ones that answer the actual question: “who can log in to this database, and what can they do once they are in.”

Roles that can log in (the only ones your app can authenticate as):

SELECT rolname, rolcanlogin, rolsuper, rolcreatedb
FROM pg_roles
WHERE rolcanlogin = true
ORDER BY rolname;

Roles that are superusers (handle with care — they can do anything):

SELECT rolname
FROM pg_roles
WHERE rolsuper = true;

Roles created in the last 30 days (handy when auditing a handoff or a migration):

SELECT rolname, rolcreated
FROM pg_roles
WHERE rolcreated > now() - interval '30 days'
ORDER BY rolcreated DESC;

The rolcreated column is populated when a role is created with a VALID UNTIL clause or when the role was originally created by an older Postgres version. If it is null, the column is not set, which is normal and not a problem.

Listing role members (the part that surprises people)

Postgres has roles that own objects, and roles that can log in, and roles that are just groups. The relation between them lives in pg_auth_members. If you have a readonly role that several real users inherit from, \du shows the inheritance but it is hard to read. The SQL version is clearer.

SELECT
  r.rolname AS role,
  m.rolname AS member,
  am.admin_option
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
ORDER BY r.rolname, m.rolname;

That query answers “who is in what group,” which is the question that actually matters when you are trying to figure out why a user can or cannot read a table.

Listing currently connected sessions

“List users” sometimes means “who is connected right now.” That is pg_stat_activity, not pg_roles.

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  query_start,
  LEFT(query, 80) AS query_preview
FROM pg_stat_activity
ORDER BY query_start DESC NULLS LAST;

This is the view to check when a deployment is stuck on a long migration and you want to know which connection is holding a lock. It is also the one to look at when a connection pool is exhausted and you need to see who is not releasing their sessions.

Listing operating system users (the other meaning)

If the question was about OS users — usually because you ssh’d into a host and want to know who else has shell access — that is unrelated to Postgres. The relevant commands are standard Linux:

cat /etc/passwd        # all OS users
getent passwd         # all users, including ones from LDAP/SSSD
who                   # users currently logged in
last                  # recent login history

On a managed database, you will not have access to the host filesystem, and that is by design. Postgres itself does not need an OS user per role; the postgres OS account owns the database process, and every logical user is a Postgres role.

The mistake that breaks managed databases

The single most common cause of “the new user cannot connect” tickets on a managed Postgres is creating a role in the wrong place. Three patterns to avoid:

1. Creating a user in the wrong database. A CREATE USER in database A does not exist in database B. You need to either create the role at the cluster level (CREATE ROLE without specifying a database) or create it in every database the app uses.
2. Forgetting the LOGIN attribute. CREATE USER and CREATE ROLE differ in their default attributes. CREATE USER is shorthand for CREATE ROLE ... LOGIN. CREATE ROLE without LOGIN makes a group role that cannot be used for connections. If you see “role does not exist” on a perfectly valid name, this is often why.
3. Trying to manage users on the host. On a managed Postgres, the host filesystem is not your concern. The database is the source of truth, and the dashboard or psql is the only way to manage users. Editing /etc/passwd on the host is a non-starter; the platform will not let you, and even if you could, the database would not see the change.

The clean version of creating a new app user on a managed Postgres is:

CREATE ROLE app_readwrite LOGIN PASSWORD 'strong-password-here';
GRANT CONNECT ON DATABASE app_prod TO app_readwrite;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

The same idea with one line of plumbing per future table, set up once:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

That last ALTER DEFAULT PRIVILEGES is the line most tutorials skip and that solves the “my new app user cannot see the tables I just created” support ticket.

A short audit script for a fresh database

When you inherit a Postgres instance, run this in order to see what you are dealing with:

-- 1. How many roles, and how many can log in?
SELECT COUNT(*) AS total_roles,
       COUNT(*) FILTER (WHERE rolcanlogin) AS login_roles,
       COUNT(*) FILTER (WHERE rolsuper) AS superusers
FROM pg_roles;

-- 2. Who are the login roles?
SELECT rolname FROM pg_roles WHERE rolcanlogin ORDER BY rolname;

-- 3. Are there any roles that should not be there?
SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolvaliduntil IS NOT NULL AND rolvaliduntil < now();

-- 4. Who is connected right now?
SELECT usename, count(*)
FROM pg_stat_activity
GROUP BY usename
ORDER BY count(*) DESC;

That four-query sweep is enough to spot a leftover superuser, an expired account, an unexpected active session, or a connection pool that is being eaten by a misbehaving service. It is also the answer to “what is the security posture of this database” in five minutes.

A note on managed Postgres

On a managed database, the SQL above works exactly the same. The interface around it is just different: instead of editing pg_hba.conf or restarting the cluster, you set role passwords through a dashboard, and the platform handles connection pooling, backups, and high availability behind the scenes. The database itself does not know that it is managed. The roles, the memberships, and the permissions all behave the same.

If you are sizing a new database for a project, the hosting calculator gives a quick read on what managed Postgres costs for the connection and storage shape you are about to need. The managed Postgres overview has the cluster-side options worth knowing about (read replicas, point-in-time recovery, connection pooling) before you commit to a tier.

FAQ

What is the difference between a user and a role in PostgreSQL?

A “user” in PostgreSQL is just a role with the LOGIN attribute. CREATE USER is a shortcut for CREATE ROLE ... LOGIN. Roles without LOGIN are groups, and roles with LOGIN are accounts that can authenticate.

How do I list users with passwords?

You cannot read passwords from psql. They are stored as hashes in pg_authid and are only accessible to superusers, and even then through a low-level catalog that the documentation discourages you from touching. The right move is to use \du to see role names and manage passwords through CREATE ROLE ... WITH PASSWORD or ALTER ROLE ... WITH PASSWORD.

Can I see who created a role?

Not directly. PostgreSQL does not record the creator of a role in pg_roles. If you need an audit trail, enable logical replication, statement logging, or platform-level audit logs that capture the CREATE ROLE statement.

Why does \du not show the password expiry?

Run \du+ (with the plus) to see extended attributes including Valid until. The same data is available in the rolvaliduntil column of pg_roles.

How do I find which user a connection is using?

Run SELECT current_user, session_user, usename FROM pg_stat_activity WHERE pid = pg_backend_pid(); for the current session, or join on pid for any other session. current_user is the role that permissions are checked against, session_user is the role that started the session, and they can differ after SET ROLE.

How do I delete a user?

DROP ROLE rolname; if the role owns no objects, or DROP ROLE rolname; followed by REASSIGN OWNED BY rolname TO other_role; and DROP OWNED BY rolname; if it does. Skipping the cleanup leaves the database in a state where the role cannot be dropped, which is the most common follow-up ticket.

Are roles per-database or per-cluster?

Roles are per-cluster. A role created in one database can log in to any database in the same cluster (subject to GRANT and CONNECT permissions). If you need per-database isolation, you need separate clusters, not separate roles.

Closing thought

“List users” is one of those queries that looks trivial and turns out to be a small map of how Postgres thinks about identity, authentication, and permissions. The \du meta-command is the front door. The interesting work — group memberships, login attributes, valid-until dates, active sessions, ownership chains — is one or two queries past it. Knowing which list you actually wanted, and which set of permissions each role on that list has, is the difference between managing a database and being managed by it.