How to List All Users in PostgreSQL

Last updated on September 24th, 2025 at 07:32 am

Database administrators need to regularly keep track of all the users in PostgreSQL database system. This will help identify unknown users and block unauthorized database access. Also, as and when people leave your team, it is essential to remove them from your system and revoke their access. This will help you control access to your databases. In this article, we will learn how to list all users in PostgreSQL.

How to List All Users in PostgreSQL

User management is commonly used in many places such as PostgreSQL database reporting. There are two main ways to get a list of all users in PostgreSQL – using psql tool and using plain SQL query.

1. Using PSQL

PSQL tool provides readymade commands to perform certain tasks. Here are a couple of commands to get list of users.

List Users

You can easily get list of users using \du command.

#- \du

The above command will display a table of user roles along with their user privileges. It is a useful shortcut for system administrators to easily view who can access their system.

List Users with Details

If you want more details, you can use the \du+ command. It will display a table of ‘role name’ that is PostgreSQL user role, ‘attribute’ that is role permission, and ‘member of’ that is if it is part of other groups.

#- \du+

This command is useful if you want to thoroughly evaluate each user accessing your data.

2. Using SQL

The above commands always return a list of all PostgreSQL users. Sometimes you may need to customize the output or view only a subset of this result. For this purpose, you can use SQL queries. Now let us look at some of the SQL queries that you can use to get user details.

PostgreSQL stores details of all user roles in pg_roles system database table. It is automatically created at the time of PostgreSQL installation and maintained by PostgreSQL server. Here is the query to get a list of users with details.

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication FROM pg_roles;

In the above query, the column names mean the following:

• rolname – user role name
• rolsuper – flag to indicate if user is a superuser
• rolcreaterole – indicates if user can create new roles
• rolcreatedb – indicates if user can create databases
• rolcanlogin – indicates if user role has login privileges
• rolreplication – indicates if role is used for replication

Filter only roles with login privileges

Using the above information, we can get specific users as per our requirement. Here is the query to get all those users who have login privileges.

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication  
FROM pg_roles  
WHERE rolcanlogin = true;

In the above query, we select rows where rolcanlogin column has true value, indicating they have login privileges. Similarly, you can filter rows based on other columns’ values.

Show users with their permissions

If you want to see all users along with their permissions, then you need to query table information_schema.role_table_grants.

SELECT grantee, privilege_type, table_schema, table_name FROM information_schema.role_table_grants;

This query is useful to periodically evaluate user permissions and privileges so you can modify them as per your requirements.

Check user-specific grants on tables

Sometimes, you may want to see the user privileges for tables of only a specific user. In such case, you can filter the result of above query using grantee column name as shown.

SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';

This query will help you understand which all tables a given user is able to access.

To check schema-level privileges

If you want to check a user’s schema level privileges, you need to query information_schema.role_schema_grants table.

SELECT *  
FROM information_schema.role_schema_grants  
WHERE grantee = 'username';

For database-level privileges

If you want to view user privileges pertaining to a database, then you can query pg_database.

SELECT datname, datacl  
FROM pg_database;

Check role membership

Sometimes, you may need to check which users have been assigned to groups. Here is the SQL query for this purpose. For this purpose, we use pg_roles and pg_auth_members tables.

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;

User not listed in \du output

Often you may need to view details about users that are not listed in \du output. You can do this with the help of the following query, which selects rows from pg_roles system table by filtering on rolename column value.

SELECT 1 FROM pg_roles WHERE rolname = 'username';

Let us look at some of the other commands commonly used in conjunction with listing users.

3. List Databases

PSQL utility also provides many useful commands to list databases, schemas and tables. You can easily get a list of all databases using \l or \l+ command.

#- \l
OR
#- \l+

Alternatively, you can also use the following SELECT statement to get a list of databases from pg_catalog table.

SELECT oid, datname
FROM pg_database;

4. List Schemas

Similarly, you can also use \dn or \dn+ commands to get a list of schemas in PostgreSQL.

#- \dn
OR
#- \dn+

Alternatively, you can also use the following SQL query to fetch this information from information_schema database.

SELECT schema_name, schema_owner
FROM information_schema.schemata;

5. List Tables

If you want to list details of all tables in your PostgreSQL installation, then you can run the \dt or \dt+ command.

#- \dt
OR
#- \dt+

You can also view details of all tables with the following SQL query.

SELECT tablename 
FROM pg_catalog.pg_tables;

Conclusion

In this article, we have seen several different ways to list all PostgreSQL users. If you want details of all users then you can use \dt or \dt+ command. If you want to view only specific column information about users or view information about only certain users then you can use SELECT query on pg_roles table. You can use any of the above commands as per your requirement.

Also read:
How to Alter Column to NOT NULL in PostgreSQL
How to Prevent SQL Injection Attack
How to Check if PostgreSQL Array Contains Value