How to list users in PostgreSQL

When working with PostgreSQL, you may find it necessary to list or view all the users (also known as roles) available in your system. This guide provides a comprehensive overview of how to list users in PostgreSQL, along with related administrative tasks.

1. Connecting to PostgreSQL

Before we start, make sure you can connect to your PostgreSQL instance. This can be done with a command-line tool like psql or a cloud-based tool like Basedash. We’ll use psql for this example:

psql -U your_username -d your_database_name

Replace your_username with your PostgreSQL username and your_database_name with the name of the database you want to connect to. If you're the PostgreSQL server administrator, you might use "postgres" as the username.

2. Listing All Users

To list all the users in PostgreSQL, use the \du command inside the psql interface:

\du

This will display a list of all roles, along with their attributes, such as superuser status, role creation capabilities, etc.

3. Displaying User Attributes

The output from \du shows the role name, attributes, and member of columns. Here's what each column means:

• Role name: The name of the user or role.
• Attributes: A set of flags indicating the user's permissions. Common flags include:
  • SUPERUSER
  • CREATEDB: Can create new databases.
  • CREATEROLE: Can create new roles.
  • REPLICATION: Can initiate streaming replication and backups.
• Member of: Shows any groups (other roles) the role is a part of.

To display detailed information about a specific user, you can use the following SQL query:

SELECT * FROM pg_roles WHERE rolname='your_username';

Replace your_username with the name of the user you're interested in.

4. Additional Queries Related to Users

Checking User's Privileges on Tables

To check the privileges a user has on tables, you can use:

\dp

Or, for a specific table:

\dp your_table_name

Finding Which Users Are Connected

If you're interested in finding out which users are currently connected to your PostgreSQL server, use:

SELECT usename, datname, client_addr FROM pg_stat_activity;

Getting User's Assigned Databases

To see the databases to which a user has access, you can run:

SELECT datname FROM pg_database, pg_user WHERE usename = 'your_username' AND has_database_privilege(usename, datname, 'CONNECT');

Replace your_username with the name of the user you're interested in.

Conclusion

Listing users and understanding their privileges is crucial for managing access and security in your PostgreSQL system. This guide provides you with the essential commands to do so. Always ensure that you manage user access carefully, especially in production environments. Regularly reviewing user roles and permissions helps in maintaining a secure and efficient system.