Listing all users and roles in PostgreSQL

PostgreSQL: Show Users in a Database

In PostgreSQL, users (also called roles) have specific privileges and permissions. Displaying a list of all users is often necessary for database administration, to review access control, and manage permissions across the system. This guide covers how to list all users in PostgreSQL, detailing the syntax and useful examples.

Syntax:

To show all users in a PostgreSQL database, you can use the following queries:

1. Using \du Command in psql

The \du command is a quick way to display all users and their roles directly from the psql command-line interface.

\du

2. Querying the pg_roles System Catalog

Another method is to query the pg_roles system catalog, which stores information about all roles (users and groups) in PostgreSQL.

SELECT rolname FROM pg_roles;

Example 1: Displaying Users with the \du Command

Code:

-- Lists all users and roles in the PostgreSQL database with associated attributes
\du

Explanation:

• This command, when executed in the psql interface, shows all users along with attributes like whether they have superuser status, login privileges, and more. The \du output will also indicate if a user is a superuser, inherits privileges, can create databases, and other permissions.

Example 2: Displaying Users with a Query on pg_roles

Code:

-- Retrieves a list of all usernames (roles) from the pg_roles system catalog
SELECT rolname FROM pg_roles;

Explanation:

• The pg_roles system catalog contains role-related information. By querying the rolname column, you get a simple list of all user names in the database.

Example 3: Showing Additional Details for Users

Code:

-- Retrieves user details, including superuser and login privileges
SELECT 
    rolname,          -- Username
    rolsuper,         -- Superuser status (true/false)
    rolcreaterole,    -- Role creation privileges (true/false)
    rolcreatedb,      -- Database creation privileges (true/false)
    rolcanlogin       -- Login capability (true/false)
FROM pg_roles;

Explanation:

• This query provides a detailed view of each user's permissions, such as whether they are a superuser, can create roles, create databases, or log in. This detailed output is useful for auditing and managing user permissions in PostgreSQL.

Important Notes:

1. Difference Between Roles and Users:

• In PostgreSQL, a user is essentially a role with login privileges. All roles are stored in pg_roles, whether they have login capabilities or not. To see only roles that can log in, you can add a WHERE rolcanlogin = true filter.

2. Granting and Modifying User Permissions:

• To modify user permissions, you can use the ALTER ROLE command. For example:

Code:

ALTER ROLE username WITH CREATEDB;

This command grants the user username permission to create databases.

3. Superuser Privileges:

• Users with superuser privileges have full control over the PostgreSQL instance, so it's crucial to monitor and limit superuser access as much as possible.

All PostgreSQL Questions, Answers, and Code Snippets Collection.