How to View and Manage PostgreSQL Users Efficiently

Knowing how to view and manage users in PostgreSQL is crucial for maintaining database integrity and access control. This is fundamental not only for security but also for efficient database administration, reinforcing the necessity of mastering user management tasks. Given the importance of user management, let’s delve deeper into the various functionalities available in PostgreSQL for viewing and managing users, empowering you to effectively control database access and maintain a secure environment.

How to view all users in PostgreSQL?

To list all users, or roles, in your PostgreSQL database, utilize the pg_roles system catalog. This catalog provides information about database roles. Execute the following SQL query to display all roles along with their attributes:

SELECT rolname, rolsuper, rolinherit,
       rolcreaterole, rolcreatedb, rolcanlogin,
       rolreplication, rolconnlimit, rolvaliduntil
FROM pg_roles;

Here, rolname represents the role’s name, rolsuper shows if the role possesses superuser rights, rolinherit indicates whether the role inherits privileges, rolcreaterole determines if the role can create new roles, rolcreatedb assesses if the role can create databases, rolcanlogin identifies if the role has login capabilities, rolreplication permits the role to start streaming replication and backups, rolconnlimit sets the maximum number of concurrent connections for the role, and rolvaliduntil shows the role’s expiration time, if applicable.

Examining specific user attributes

To gather details about a particular user, adapt the query to focus on the rolname. For instance, to acquire information about a user named ‘example_user’, execute this SQL command:

SELECT rolname, rolsuper, rolinherit,
       rolcreaterole, rolcreatedb, rolcanlogin,
       rolreplication, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolname = 'example_user';

This tailored query fetches the same attributes for just ‘example_user’.

For access management workflows, Basedash complements SQL controls with governed reporting, shared visibility, and AI-assisted analysis so teams can monitor permission changes without losing auditability.