PostgreSQL list users

I’ve found that understanding how to list and manage users is fundamental to maintaining secure and efficient database environments. In this PostgreSQL tutorial, we will learn about listing users in PostgreSQL.

PostgreSQL list users

In this section, we will explore the use of the PostgreSQL list user command to display all users in a PostgreSQL database server.

There are two methods by which we can list all user accounts (or roles) in the current PostgreSQL database server. One is \du command and the other is \du+. Let’s implement it using the psql tool.

Approach-1: Using \du and \du+ command

First, open your terminal and connect to PostgreSQL with the psql client:

psql -U postgres

You’ll be prompted for the password for the postgres user. After successful authentication, you’ll see the PostgreSQL command prompt.

Now, list all users is with the \du meta-command:

\du

After executing the above query, I got the expected output as shown in the screenshot below.

Now, we will check it with the help of the \du+ command. The \du+ command adds an extra column known as description. This command is generally used when we want to display more information.

\du+

Let’s see its implementation.

Approach 2: Using SQL Quries

We can also list users using a SQL statement.

Using a basic SQL Query

We can execute the below basic SQL query to get the user lists.

SELECT usename AS role_name,
       usesysid AS role_id,
       usecreatedb AS can_create_db,
       usesuper AS is_superuser,
       usebypassrls AS can_bypass_rls
FROM pg_catalog.pg_user
ORDER BY role_name;

After executing the above query, I got the expected output as per the below screenshot.

Querying data from the pg_catalog.pg_user catalog with CAST function.

The statement shown below returns all users in the current database server by querying data from the pg_catalog.pg_user catalog.

Select usename as role_name,
  CASE 
     WHEN usesuper AND usecreatedb THEN 
	   CAST('superuser, create database' as pg_catalog.text)
     WHEN usesuper THEN 
	    CAST('superuser' as pg_catalog.text)
     WHEN usecreatedb THEN 
	    CAST('create database' as pg_catalog.text)
     ELSE 
	    CAST('' as pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Read: How to backup PostgreSQL database

Using Information Schema Views

We can execute the below query for this purpose.

SELECT usename AS username,
       valuntil AS password_expiry,
       useconfig AS user_config
FROM pg_catalog.pg_user
LEFT JOIN pg_catalog.pg_auth_members ON pg_user.usesysid = pg_auth_members.member
LEFT JOIN pg_catalog.pg_roles ON pg_roles.oid = pg_auth_members.roleid;

After executing the above query, I got the expected output as per the below screenshot.

Query for Users with Their Roles and Attributes

We can execute the below query for this purpose.

SELECT r.rolname,
       r.rolsuper,
       r.rolinherit,
       r.rolcreaterole,
       r.rolcreatedb,
       r.rolcanlogin,
       r.rolconnlimit,
       r.rolvaliduntil,
       ARRAY(SELECT b.rolname
             FROM pg_catalog.pg_auth_members m
             JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
             WHERE m.member = r.oid) AS memberof
FROM pg_catalog.pg_roles r
WHERE r.rolcanlogin
ORDER BY 1;

After executing the above query, I got the expected output as per the below screenshot.

Aditional Examples

Example 1: List users and permissions

Several scripts in PostgreSQL databases help us manage our users. The simplest way to list users is to run the command we have already discussed, i.e., \du, which may not provide detailed information.

However, with the help of the following script, we can determine which databases users have permission to connect to. Let’s check it by implementing the statement.

select pgu.usename as user_name,
       (select string_agg(pgd.datname, ',' order by pgd.datname) 
        from pg_database pgd 
        where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as database_name
from pg_user pgu
order by pgu.usename;

Let’s review the output for this.

With the help of the below script, we can also list on a database basis.

select pgd.datname as database_name,
       (select string_agg(pgu.usename, ',' order by pgu.usename) 
        from pg_user pgu 
        where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as user_name
from pg_database pgd
order by pgd.datname;

Let’s check the output for the same.

In the above script, we can also query other permissions by typing one of the following permission types instead of connecting. i.e., SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, TEMPORARY or TEMP, EXECUTE, and USAGE.

We can verify the user permissions details in the database using the script below.

SELECT grantee
      ,table_catalog
      ,table_schema
      ,table_name
      ,string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE grantee != 'postgres'
GROUP BY grantee, table_catalog, table_schema, table_name;

Let’s review the implementation for this.

And if we want, we can revoke all the privileges from a user with the command given below.

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM user_name;

Let’s check the output for the same.

Read: PostgreSQL Export Table to CSV

Example 2: List users connected to our PostgreSQL database

Let’s check how to check who is connected to our PostgreSQL database. The code for connecting users is given below.

select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_addr as client_address, 
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;

Let’s check the output for the above query.

Columns

• process_id: This is the process ID of this backend
• username: This is the name of the user logged into this backend
• database_name: This is the name of the database this backend is connected to
• client_address: This is the IP address of the client connected to this backend
• application_name: This is the name of the application that is connected to this backend
• backend_start: This is the time when this process was started. For client backends, it’s the time the client is connected to the server.
• State: Current overall state of this backend. Possible values are: active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, disabled.
• state_change: It is the time when the state was last changed.

Rows

• One row: Represents one active connection
• Scope of rows: All active connections

Read: PostgreSQL drop all tables

Example 3: List users’ privileges

This will instruct us on how to create a query to the Postgres data dictionary to determine all the privileges a particular user has. Let’s see the code for this and then its implementation. The code below is to check table permissions.

select * from information_schema.role_table_grants 
where grantee='postgres';

Let’s check the output for the same as shown in the below screenshot.

To verify ownership, we will use the code below.

select * from pg_tables 
where tableowner = 'postgres';

Let’s check the output for the same.

For checking schema permissions, we will use the following code.

select  
  r.usename as grantor, e.usename as grantee, nspname, privilege_type, is_grantable
from pg_namespace
join lateral (
  SELECT
    *
  from
    aclexplode(nspacl) as x
) a on true
join pg_user e on a.grantee = e.usesysid
join pg_user r on a.grantor = r.usesysid 
 where e.usename = 'postgres'
;

Let’s check the output for the same as per the below screenshot.

Read: PostgreSQL DATE_PART() Function

Example 4: List users in group

To check the list of users in the group we will implement with the help of the query given below. This will give us usernames as well as the respective groups.

SELECT usename, groname 
FROM pg_user, pg_group
WHERE pg_user.usesysid = ANY(pg_group.grolist)
AND pg_group.groname in (SELECT DISTINCT pg_group.groname from pg_group);

Let’s check the output for the same.

Best Practices for PostgreSQL User Management

Based on my experience managing PostgreSQL databases for companies across the USA, here are some best practices:

1. Follow the principle of least privilege: Only grant users the permissions they need to perform their duties
2. Use role hierarchies: Create group roles with appropriate permissions and add users to these roles
3. Regular audits: Periodically review user lists and their permissions
4. Password policies: Implement strong password requirements and rotation policies

Read: PostgreSQL INSERT Multiple Rows

Conclusion

Listing and managing PostgreSQL users is a fundamental skill for database administrators. Whether you prefer the simplicity of psql meta-commands like \du, the flexibility of SQL queries, or the convenience of GUI tools, understanding how to view and interpret user information using the above information provided is essential for maintaining secure and efficient database environments.

Also, read some of our latest PostgreSQL articles.

• PostgreSQL function returns a table
• PostgreSQL Update
• PostgreSQL Concat + Examples
• PostgreSQL Date Difference 
• Create a stored procedure in PostgreSQL
• PostgreSQL list databases