Up to 80% of data breaches in the U.S. start with unauthorized access. Without insights into who’s accessing your database, their login privileges, and the duration of each session, it can be difficult to prevent potential security threats.
Fortunately, PostgreSQL is a powerful open-source relational database management system you can use to manage access control. Known for its advanced support, complex queries, and rich features, PostgreSQL can list users in your database for security audits, compliance checks, and database access management.
But how exactly do you list users in PostgreSQL? This guide walks you through the steps of listing PostgreSQL users using command-line queries like psql and graphical user interface (GUI)-based tools.
Prerequisites
Before we start, ensure you have:
- Installed PostgreSQL on your system
- Access to a PostgreSQL instance with privileges to list users.
- Basic familiarity with the psql command-line tool or GUI-based clients.
How To List Users in PostgreSQL Using psql Command Line
The most direct way to list PostgreSQL users is to use the psql command-line interface. This method lets you quickly retrieve user information from the database catalog using SQL queries.
Step 1: Connect to PostgreSQL
Open a terminal on your Unix system or command prompt on Windows and enter the following command:
psql -U postgres
If your PostgreSQL runs on a non-default port, this command will fail to connect. You may want to specify the port using:
psql -U postgres -p 5433
The “-U postgres” specifies the superuser name. If you’re unsure of the correct user name, confirm with the following command. Then replace “postgres” accordingly:
SELECT rolname FROM pg_roles WHERE rolsuper = true;
After running the command with the correct superuser name, you’ll see a prompt asking you for a password, as shown below:
Password for user postgres:
Type the password and press Enter. If the connection is successful, you’ll see the following prompt:
postgres=#
You’re now connected to PostgreSQL and can run commands.
Step 2: List all roles
Enter the following command to list all roles:
\du
This command displays a table of roles with and without login privileges.
Step 3: Detailed list including descriptions
If you want a description of roles, run this command:
\du+
In the output:
- “Role Name” is the PostgreSQL user/role.
- “Attribute” is role permission.
- “Member of” shows if the role is part of other groups.
Querying PostgreSQL system catalog using the psql command-line tool
If you need more detailed information about user roles, privileges, and database access, run the following queries:
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication FROM pg_roles;
To filter only roles with login privileges, run:
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication
FROM pg_roles
WHERE rolcanlogin = true;
In the pg_roles output:
- rolname is a user’s role name.
- rolsuper indicates whether the user is a superuser.
- rolcreaterole specifies if a user can create new roles.
- rolcreatedb shows if a user can create databases.
- rolcanlogin indicates whether the role has login privileges.
- rolreplication shows if the role is used for replication.
Listing users and their privileges
You can also check actions that users can perform to help you with security audits, maintain compliance, and monitor privilege changes to prevent accidental or malicious modifications.
Show users with their permissions
SELECT grantee, privilege_type, table_schema, table_name FROM information_schema.role_table_grants;
Check user-specific grants on tables
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';
To check schema-level privileges, use:
SELECT*
FROM information_schema.role_schema_grants
WHERE grantee = 'username';
For database-level privileges:
SELECT datname, datacl
FROM pg_database;
Listing users and their membership roles
You can also check role membership — users assigned to groups — to understand inherited permissions and audit group access.
SELECT
r.rolname AS role_name,
m.rolname AS member_name
FROM
pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid
JOIN
pg_roles m ON a.member = m.oid;
Check to see which users can access external data sources:
SELECT
um.srvname AS foreign_server,
r.rolname AS user_role
FROM pg_user_mappings um
JOIN pg_roles r ON um.umuser = r.oid;
How To List Users in PostgreSQL Using GUI Tools
Instead of running SQL commands manually, you can use GUI tools, such as pgAdmin, DBeaver, or DataGrip, to list and manage your database users easily:
- pgAdmin: Connect to your PostgreSQL server > Expand your server and select your database >Go to Login/Group Roles.
- DBeaver: Connect to your PostgreSQL database > Database Navigator > Security > Users & Roles
- DataGrip: Connect to your PostgreSQL database > Database Explorer > PostgreSQL instance > Schemas >Roles
Managing PostgreSQL Users: Create, Grant, Revoke, Delete
Beyond listing users, PostgreSQL lets you create new users, adjust permissions, or remove outdated accounts.
Create a new user with login privileges, a secure password, and database access:
CREATE ROLE new_user WITH LOGIN PASSWORD 'securepassword';
GRANT CONNECT ON DATABASE your_db TO new_user;
Grant privileges:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO new_user;
Revoke privileges:
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM new_user;
Delete a user:
DROP ROLE new_user;
Before deleting a user, ensure they don’t own a database object. If the user owns a database object, this command will fail.
Common Issues and Troubleshooting
You may encounter some common issues and errors when listing users in PostgreSQL:
User not listed in \du output
If a user doesn’t appear in the \du list, check if they exist:
SELECT 1 FROM pg_roles WHERE rolname = 'username';
If the user exists but isn’t listed, grant them login access with:
ALTER ROLE username WITH LOGIN;
If the user doesn’t exist, create them with login privileges:
CREATE ROLE username WITH LOGIN PASSWORD 'password';
Permission denied errors
This issue occurs if you lack sufficient privileges to query the database catalog. A superuser can rectify it by checking your current privileges and role assignment:
SELECT * FROM pg_roles WHERE rolname = 'username';
If you’re missing the required permissions, the superuser can grant you them based on what you want to do.
Automating User Auditing in PostgreSQL
Managing user access is just the first step. You want to continuously monitor your database to prevent unauthorized access or misuse of privileges. The best practice is to automate user auditing to track logins, query execution, and role changes to ensure security and compliance without manual oversight.
Step 1: Enable PostgreSQL audit logging
Modify PostgreSQL settings to log key activities.
ALTER SYSTEM SET log_statement = 'ddl'; -- Logs only data definition changes (e.g., role modifications)
ALTER SYSTEM SET log_connections = on; -- Logs user logins
ALTER SYSTEM SET log_disconnections = on; -- Logs user logouts
ALTER SYSTEM SET log_duration = on; -- Logs query durations
ALTER SYSTEM SET log_min_duration_statement = 1000; -- Logs queries that take longer than 1 second
SELECT pg_reload_conf(); -- Applies changes without restarting
These settings track role modifications, user logins/logouts, and slow queries while keeping logs manageable.
Step 2: Use the pgAudit extension for advanced logging
For more detailed tracking of users' actions for compliance, enable the pgAudit extension:
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'all';
SELECT pg_reload_conf();
This command will help you record what users do to make audits easier.
Step 3: Periodic role review
Regularly auditing user roles and permissions prevents unauthorized access. Use the following query to check key role attributes:
SELECT rolname, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles;
Automating this check can simplify security checks.
How StrongDM Simplifies PostgreSQL User Management
While PostgreSQL provides built-in role management, manually handling database users can be tedious. StrongDM simplifies PostgreSQL access with:
- Centralized Access Control – Manage database users without manual role assignment
- Integration with Existing IAM Systems – Connect with Okta, Azure AD, and other identity providers to automate user provisioning and de-provisioning.
- Audit Logging and Monitoring –Track all database actions in real time for easier compliance audits and reduced risks of unauthorized access.
- Role-Based Access Control (RBAC) Made Simple – Easily manage users without dealing with PostgreSQL commands.
- Automated Least-Privilege Control – Enforce just-in-time access to reduce security risks.
Try StrongDM today to enhance your PostgreSQL access security and simplify multiple-platform database access management.
Additional Resources
