How To List Users in PostgreSQL (psql command & GUI tools)

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 Delinea Simplifies PostgreSQL User Management

While PostgreSQL provides built-in role management, manually handling database users can be tedious. Delinea 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 Demo today to enhance your PostgreSQL access security and simplify multiple-platform database access management.