Sign Up
Back to Blog

Postgres List Users: The Safer Way to Audit Roles

Sean

Platform Writer

Jun 07, 2026
11 min read

To list users in Postgres, open psql and run \du or query SELECT * FROM pg_catalog.pg_user;. For a production database, though, that is only the first move. The useful audit is not “show me names.” It is “show me who can log in, who can create objects, who owns data, and which role is too powerful for an app runtime.”

That distinction matters. A startup can ship happily for months with one database user, one migration script, and one admin password living in too many places. Then the product grows: background workers arrive, a customer export job appears, read-only analytics need access, and suddenly “just use the Postgres user” becomes the database version of leaving the office key under the mat.

This guide gives you the commands that actually help: quick role listings, privilege checks, owner checks, and a practical cleanup workflow for deployed apps.

Table of contents

Postgres list users role audit guide for deployed applications

The quick command to list users in Postgres

Inside psql, the shortest answer is:

\du

That shows roles, role attributes, and role membership. In many setups, \du and \dg produce similar output because PostgreSQL treats users and groups as roles with different attributes.

A typical result looks like this:

 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 app_user   |                                                            | {}
 readonly   | Cannot login                                               | {}
 deployer   | Create DB                                                  | {}
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

For a local dev database, that may be enough. For production, keep going. The roles that matter most are usually not the ones with the loudest names; they are the ones with LOGIN, CREATEDB, CREATEROLE, BYPASSRLS, or accidental membership in a powerful parent role.

If you are connecting through a deployed app, keep credentials in environment variables and never paste production passwords into a README. RunxBuild’s database user management docs and database networking docs are good reference points when you want the app, worker, and admin access paths to stay separate.

Users vs roles: the naming trap

PostgreSQL does not separate users and groups the way many people expect. It has roles. A role can log in, own objects, inherit privileges, belong to another role, or act like a group.

A “user” is basically a role with the LOGIN attribute. That is why some commands say users and others say roles.

For example:

CREATE ROLE reporting_reader;
CREATE USER app_user WITH PASSWORD '...';

CREATE USER is shorthand for creating a role that can log in. CREATE ROLE is more general.

This matters because a clean production setup often has both:

  • login roles for apps and people
  • group roles for shared permissions
  • owner roles that own schemas and tables but do not log in
  • migration roles that can change structure but are not used by the runtime app

If every role can log in and every app role owns tables, your future self will eventually pay for that convenience. Usually during a deploy. Usually while someone is asking why the dashboard is down.

List users with SQL when psql shortcuts are not enough

\du is convenient, but SQL is easier to save, automate, and run from admin dashboards.

To list login roles:

SELECT rolname
FROM pg_roles
WHERE rolcanlogin = true
ORDER BY rolname;

To include useful attributes:

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

To use the older compatibility view:

SELECT usename, usesuper, usecreatedb
FROM pg_catalog.pg_user
ORDER BY usename;

pg_user is simple, but pg_roles gives a better view of modern role attributes. The official PostgreSQL pg_roles documentation is worth bookmarking if you manage production databases.

For psql-specific shortcuts, the official psql documentation covers meta-commands such as \du, \dg, \l, and \dn.

Check who can log in and who is overpowered

When you audit Postgres users, start with the risky attributes.

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

A production app role usually does not need to be a superuser. It usually does not need to create databases. It usually should not create roles. It should have the minimum privileges needed to read and write the tables it owns or uses.

A reasonable split looks like this:

app_runtime        login, read/write app tables
app_migrator       login, alter schema during deploys
readonly_reporter  login, select only
app_owner          no login, owns app schema
postgres_admin     human/admin path only

That structure is less convenient on day one, but it makes incidents smaller. If the app runtime leaks, it cannot create new roles or rewrite the whole cluster. If a reporting tool misbehaves, it cannot mutate production data. If migrations need more power, that power lives in the deploy path instead of the request path.

On RunxBuild, pair that with managed database credentials, environment variables, and private access rules. If you are estimating whether to split a database, worker, and app into separate resources, the RunxBuild hosting calculator helps model the deployment before you turn a permission problem into a billing surprise.

See database and schema privileges

Listing users tells you who exists. Privilege checks tell you what they can touch.

To see database-level privileges:

SELECT
  datname,
  pg_catalog.pg_get_userbyid(datdba) AS owner,
  datacl
FROM pg_database
WHERE datistemplate = false
ORDER BY datname;

To see schema privileges:

SELECT
  schema_name,
  grantee,
  privilege_type
FROM information_schema.schema_privileges
ORDER BY schema_name, grantee, privilege_type;

To inspect table privileges for a schema:

SELECT
  grantee,
  table_schema,
  table_name,
  privilege_type
FROM information_schema.table_privileges
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantee, table_schema, table_name, privilege_type;

This is where you find the real mess: a supposedly read-only user with INSERT, an old migration role that can still log in, or a dashboard user that owns tables it should only query.

A production audit checklist for app databases

Use this when a database has moved beyond a side project.

  1. List every role with \du or pg_roles.
  2. Mark which roles can log in.
  3. Identify all superusers and roles with CREATEDB, CREATEROLE, or BYPASSRLS.
  4. Separate runtime users from migration users.
  5. Check which role owns each schema and table.
  6. Confirm the app user has only the privileges it needs.
  7. Confirm read-only tools are actually read-only.
  8. Rotate passwords that were shared during early development.
  9. Move credentials into environment variables or managed secrets.
  10. Test the deployed app after permission changes, not just local psql.

The last step is the one teams skip. A permission change can pass in your terminal and fail inside a deployed worker because that worker uses a different connection string. A prototype without logs is just a mystery with a URL, so keep deploy logs close while you tighten access.

How this changes in deployed apps

In local development, one postgres superuser is tempting because it removes friction. In deployment, it creates unclear blast radius.

A better pattern is to give each runtime a narrow role:

  • the web API gets the app runtime user
  • the background worker gets the same or a worker-specific user
  • migrations get a deploy-time user
  • analytics gets a read-only user
  • humans use admin access only when necessary

RunxBuild supports this style because apps, services, and databases can live on the same deployment path while still using separate environment variables and network boundaries. If your project is growing from a static page into an API with state, the managed database docs, Node service docs, and Python service docs are useful next steps.

The goal is not to make Postgres feel complicated. The goal is to keep the easy command, \du, from becoming the only audit you ever run.

FAQ

How do I list all users in Postgres?

Run \du inside psql to list roles and attributes. For a SQL version, query SELECT rolname FROM pg_roles WHERE rolcanlogin = true ORDER BY rolname; to show roles that can log in.

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

PostgreSQL uses roles for both users and groups. A user is commonly a role with the LOGIN attribute, while a group-style role often has no login and exists to hold shared permissions.

How do I see permissions for a Postgres user?

Start with \du for role attributes, then check information_schema.table_privileges and schema privileges for actual access. Role attributes show cluster-level power; grants show what the user can do to databases, schemas, and tables.

How do I list superusers in Postgres?

Run SELECT rolname FROM pg_roles WHERE rolsuper = true ORDER BY rolname;. Superuser access should be rare in production and should not be used as the normal application connection.

Should my app connect as the postgres user?

No, not in production. Create a dedicated app role with only the privileges the runtime needs, store its credentials in environment variables, and reserve admin users for maintenance.