How to View Users in PostgreSQL
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.
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.
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.
\duThis 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.
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:
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 | tThis 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:
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
User | Superuser | Can create roles | Can create databases | Can log in
------|----------|-----------------|----------------------|------------
user1 | f | f | f | tConclusion
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.
Jorge García
Fullstack developer