PostgreSQL List Users

View all users, roles, and permissions in PostgreSQL.

Quick Answer

To list all users in PostgreSQL:

SQL

SELECT usename FROM pg_user;

Or for more detail:

SQL

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.

SQL

-- 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)

SQL

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)

SQL

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

SQL

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

SQL

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

SQL

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

SQL

SELECT rolname
FROM pg_roles
WHERE rolsuper = true;

Database-Specific Permissions

Who Can Access a Specific Database?

SQL

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

SQL

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?

SQL

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

All Permissions for a User

SQL

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

Active Connections

See who's currently connected:

SQL

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:

SQL

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

SQL

CREATE USER alice WITH PASSWORD 'secure_password';

Grant Database Access

SQL

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

SQL

-- 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

SQL

-- 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