Listing Users in PostgreSQL

Method 1: Using the System View pg_roles

The system view pg_roles in PostgreSQL contains information about all roles (users and user groups) defined in the database. You can query this view to get a list of all users.

Copy Code

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

• rolname: Name of the role (user).
• rolsuper: Indicates if the role has superuser privileges.
• rolcreaterole: Indicates if the role can create other roles.
• rolcreatedb: Indicates if the role can create databases.
• rolcanlogin: Indicates if the role can log in (i.e., it is a user).

Method 2: Using the System View pg_user

The system view pg_user is a legacy view that displays information about users who can log in to PostgreSQL. It is a subset of pg_roles, specifically focused on users.

Copy Code

SELECT usename, usesysid, usecreatedb, usesuper, userepl
FROM pg_user;

• usename: Name of the user.
• usesysid: User's system ID.
• usecreatedb: Indicates if the user can create databases.
• usesuper: Indicates if the user has superuser privileges.
• userepl: Indicates if the user has replication permissions.

Method 3: Using the \du Command in psql

If you are using the psql command-line tool, you can use the \du command to list all users and their roles in a more user-friendly format.

Copy Code

\du

This command displays a list of all users and their role attributes.

Practical Example

Suppose we want to see a list of all users in PostgreSQL along with their privileges and roles. We will use the pg_roles view for this purpose.

Copy Code

SELECT rolname AS "User",
       rolsuper AS "Superuser",
       rolcreaterole AS "Can create roles",
       rolcreatedb AS "Can create databases",
       rolcanlogin AS "Can log in"
FROM pg_roles;

Expected Output

The query output should look something like this:

Copy Code

User      | Superuser | Can create roles | Can create databases | Can log in
----------+----------+-----------------+----------------------+------------
 postgres | t        | t               | t                    | t
 user1    | f        | f               | f                    | t
 user2    | f        | t               | f                    | t

This table shows all users in the system along with their privileges and roles.

Viewing Details of a Specific User

To view the details of a specific user, you can filter the query using the username. For example, to see details for the user user1:

Copy Code

SELECT rolname AS "User",
       rolsuper AS "Superuser",
       rolcreaterole AS "Can create roles",
       rolcreatedb AS "Can create databases",
       rolcanlogin AS "Can log in"
FROM pg_roles
WHERE rolname = 'user1';

Expected Output

Copy Code

User  | Superuser | Can create roles | Can create databases | Can log in
------|----------|-----------------|----------------------|------------
user1 | f        | f               | f                    | t

Conclusion

Knowing how to view users in PostgreSQL is essential for database administration and security. In this article, we covered several methods to list users and retrieve relevant information about their roles and permissions. These methods include using system views such as pg_roles and pg_user, as well as the \du command in the psql command line.

Practicing these commands and queries will help you better manage users and roles in PostgreSQL, ensuring that only authorized users have access and appropriate privileges.