PostgreSQL List Users

Quick Answer

To list all users in PostgreSQL:

SELECT usename FROM pg_user;

Or for more detail:

SELECT * FROM pg_user;

But keep reading—PostgreSQL's user management has nuances worth understanding.

Users vs Roles

In modern PostgreSQL, users and roles are the same thing. A "user" is just a role with login permission.

-- These are equivalent
CREATE USER alice WITH PASSWORD 'secret';
CREATE ROLE alice WITH LOGIN PASSWORD 'secret';

The distinction matters when listing users: some commands show only roles with login permission, while others show all roles.

Methods to List Users

1. Using pg_user (Login Roles Only)

SELECT usename, usecreatedb, usesuper
FROM pg_user;

Returns only roles that can log in:

| usename | usecreatedb | usesuper | |---------|-------------|----------| | postgres | t | t | | alice | f | f | | bob | f | f |

2. Using pg_roles (All Roles)

SELECT rolname, rolcanlogin, rolsuper, rolcreatedb
FROM pg_roles;

Returns all roles, including groups and roles without login:

| rolname | rolcanlogin | rolsuper | rolcreatedb | |---------|-------------|----------|-------------| | postgres | t | t | t | | alice | t | f | f | | readonly | f | f | f | | admin_group | f | f | f |

3. Using psql Command (\du)

In the psql terminal:

\du

Output:

                             List of roles
 Role name |                   Attributes
-----------+-----------------------------------------------
 alice     |
 bob       | Create DB
 postgres  | Superuser, Create role, Create DB, Replication
 readonly  | Cannot login

For more detail:

\du+

4. Using information_schema

SELECT DISTINCT grantee
FROM information_schema.role_table_grants
WHERE grantee NOT LIKE 'pg_%';

This shows roles that have been granted permissions on tables.

Detailed User Information

See Everything About Users

SELECT
  r.rolname AS username,
  r.rolsuper AS is_superuser,
  r.rolcreatedb AS can_create_db,
  r.rolcreaterole AS can_create_roles,
  r.rolcanlogin AS can_login,
  r.rolconnlimit AS connection_limit,
  r.rolvaliduntil AS password_expires
FROM pg_roles r
WHERE r.rolcanlogin = true
ORDER BY r.rolname;

Check User's Group Memberships

SELECT
  r.rolname AS username,
  ARRAY_AGG(g.rolname) AS member_of
FROM pg_roles r
LEFT JOIN pg_auth_members m ON r.oid = m.member
LEFT JOIN pg_roles g ON m.roleid = g.oid
WHERE r.rolcanlogin = true
GROUP BY r.rolname;

Find Users with Superuser Access

SELECT rolname
FROM pg_roles
WHERE rolsuper = true;

Database-Specific Permissions

Who Can Access a Specific Database?

SELECT
  datname AS database,
  pg_catalog.pg_get_userbyid(datdba) AS owner,
  datacl AS access_privileges
FROM pg_database
WHERE datname = 'myapp';

List Users with Access to Current Database

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_catalog = current_database()
GROUP BY grantee, privilege_type;

Table-Level Permissions

Who Can Access a Specific Table?

SELECT
  grantee,
  privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'users';

All Permissions for a User

SELECT
  table_schema,
  table_name,
  privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'alice';

Active Connections

See who's currently connected:

SELECT
  usename AS username,
  datname AS database,
  client_addr AS ip_address,
  state,
  query_start,
  query
FROM pg_stat_activity
WHERE usename IS NOT NULL;

Count connections per user:

SELECT
  usename,
  COUNT(*) AS connection_count
FROM pg_stat_activity
WHERE usename IS NOT NULL
GROUP BY usename
ORDER BY connection_count DESC;

Common Administrative Tasks

Create a New User

CREATE USER alice WITH PASSWORD 'secure_password';

Grant Database Access

GRANT CONNECT ON DATABASE myapp TO alice;
GRANT USAGE ON SCHEMA public TO alice;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO alice;

Make a User Read-Only

-- Create a readonly role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Assign users to the role
GRANT readonly TO alice;

Remove a User

-- First revoke permissions
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM alice;

-- Then drop the user
DROP USER alice;

Quick Reference

| Command | Description | |---------|-------------| | SELECT * FROM pg_user | List login-capable users | | SELECT * FROM pg_roles | List all roles (including groups) | | \du | psql shortcut to list roles | | \du+ | psql shortcut with more detail | | SELECT * FROM pg_stat_activity | Show active connections |

Summary

• In PostgreSQL, users are just roles with LOGIN permission
• Use pg_user for login-capable users, pg_roles for all roles
• The \du command in psql is the quickest way to see users
• Check pg_stat_activity to see who's currently connected
• Use information_schema.role_table_grants to audit permissions