Listing Users in PostgreSQL: A Comprehensive Guide

PostgreSQL manages access control and permissions based on database user accounts referred to as roles. All database activity is performed by roles with associated privileges to access data, execute commands or modify database objects.

As an administrator, listing and inspecting configured users is critical for:

• Understanding who can access databases and what operations they can perform
• Troubleshooting login or permission issues for database applications and users
• Assigning appropriate data access and security policies based on least privilege

This in-depth guide explores the various techniques to list PostgreSQL roles. We‘ll compare capabilities of different user listing tools, examine metadata in system catalogs, troubleshoot access issues and summarize key takeaways.

Overview of PostgreSQL User Roles

PostgreSQL manages database access permissions using the concept of roles. Roles allow login and group roles to control what users can do. Some key characteristics:

Login Roles

• Allow login to PostgreSQL database with a username and password
• Often created for individual database users including admins and app users

Group Roles

• Used to manage permissions collectively for groups of users
• Group role privileges propagate to member login roles

Superusers

• Special login roles with unlimited access to all database objects and commands
• Defaults like the postgres admin account and others when SUPERUSER privilege granted

Let‘s explore the tools to list these user roles before digging deeper into their permissions.

List All User Roles with \du

The \du meta-command provides a quick overview of user roles from within psql:

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 appuser   |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

We can see key attributes like:

• Role name – The name of the user account
• Attributes – Privileges granted like superuser status
• Member of – Other roles the role is a member of

To view details for a specific user, append the role name:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 appuser   |                                                            | {}

This prints an empty result if the role does not exist yet.

While convenient, \du exposes limited role information from pg_authid. For additional metadata, we need to query other system catalogs as shown ahead.

Query User Role System Catalogs

PostgreSQL maintains detailed user role metadata across several system catalogs:

• pg_authid – Basic role properties like name, sysid, superuser status
• pg_user – Additional attributes like passwords and validity
• pg_roles – Privileges granted to roles

We can join data across these to list all role information. For example:

SELECT
  rolname,
  rolsuper,
  rolinherit,
  rolcreaterole,
  rolcreatedb  
FROM
  pg_catalog.pg_roles
JOIN
  pg_catalog.pg_authid
ON 
  pg_authid.oid = pg_roles.oid
ORDER BY
  rolname;  

Which returns:

 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb
---------+----------+------------+--------------+------------
 appuser | f        | t          | f            | f
 postgres | t       | t          | t            | t

Alternatively, we can query them individually. For example, pg_user exposes additional properties:

SELECT
  usename,
  usesysid,
  valuntil,
  useconfig
FROM
  pg_catalog.pg_user;

Returns:

 usename | usesysid | valuntil | useconfig 
---------+----------|----------|----------
 appuser |    16384 |          |
 postgres |       10 |          |

These system catalogs provide authoritative user role metadata directly from PostgreSQL. Querying them can expose details beyond high level commands.

Compare User Roles Between Catalogs

We just saw how pg_user contains additional properties beyond pg_roles. Some user attributes reside across multiple catalogs:

Privileges

• pg_authid – rolsuper (superuser)
• pg_roles – Over 40+ fine-grained privileges like CREATEROLE, CREATEDB

Authentication & Security

• pg_shadow – Passwords and validity
• pg_authid – rolcatupdate (catalog update)
• pg_user – useconfig (per-user configs)

Role Membership

• pg_auth_members – Role membership associations

Comments

• pg_description – User comments
• pg_shdescription – Shadow comments

Comparing metadata between catalogs explains exactly how PostgreSQL manages user roles under the hood.

Now let‘s shift gears to accessing this information through user interfaces…

List Roles via psql \l

The psql \l meta-command provides a database-oriented view of user privileges:

                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+--------------+-------------+-----------------------
 appdb     | appuser | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres 
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

This lists the users that can access each database based on GRANTs rather than showing user details directly. \l reveals a user‘s database-level privileges.

For managing access control, \l delivers an instant view of who can access which databases. We will cover advanced privilege management later on.

Graphical User Listing in pgAdmin

The pgAdmin GUI provides interactive user management capabilities.

To list roles, navigate to the Login/Group Roles tree node:

We can view and modify user properties by right-clicking:

The interface centralizes user listing and editing for convenience. However, power users may prefer SQL or psql for more fine-grained control.

Troubleshooting User Access Issues

Let‘s shift our focus to troubleshooting authorization issues that may arise during application usage:

Common Error Messages

• Permission denied for schema MYSCHEMA
• permission denied for table MYTABLE
• permission denied for sequence MYSEQ
• User cannot create database objects
• Password authentication failed for user

Checking User Privileges

We can inspect role privileges to identify missing grants needed to resolve access errors:

List privileges from pg_roles:

SELECT * 
FROM pg_roles
WHERE rolname=‘appuser‘;

Check privileges on specific database objects:

SELECT 
  has_schema_privilege(‘appuser‘, ‘myschema‘, ‘USAGE‘) AS schema_access,
  has_table_privilege(‘appuser‘, ‘mytable‘, ‘SELECT‘) AS table_select; 

Querying role privileges determines appropriate grants to resolve issues.

Granting Missing Privileges

Once we identify a missing privilege, grant it with:

GRANT SELECT ON mytable TO appuser;

We can also grant privileges broadly at the database or schema level. Repeatedly listing roles after grants helps narrow down problems.

Best Practices for Managing Roles

Managing users securely and efficiently takes some care around granting privileges:

Avoid Overprivileging

Only grant actual privileges needed rather than defaulting to superuser access where not needed. Give applications and users minimum database access based on exact needs.

Leverage Group Roles

Use groups to manage collective privileges rather than handling each user individually. Add users to groups to inherit permissions.

Restrict Sensitive Access

Be careful when granting privileges like COPY TO, file URI access or disabling row security policies. Monitor sensitive columns closely as well.

Use Views to Restrict Columns

Hide direct table access behind views that limit exposure to sensitive columns. This minimizes access control risks.

Listing user privileges during development and testing catches overgrants before production. Follow the principle of least privilege by granting only required data access.

Summary Tables

For quick reference, here are some summary tables covering key user role concepts:

psql Meta Commands

Command	Description
\du	List user roles with attributes and memberships
\du myuser	Checks privileges for myuser
\l	List user access privileges by database

System Catalogs

Catalog	Description
pg_authid	Basic user role properties
pg_user	Additional attributes like password
pg_roles	Role privileges and attributes

Troubleshooting Access Issues

Task	SQL Example
Check privileges	SELECT has_table_privilege(...);
Grant privilege	GRANT SELECT ON ...;
Revoke privilege	REVOKE ALL PRIVILEGES ON ...;
See grants on object	\z mytable

Conclusion

Understanding PostgreSQL user roles is crucial for administering database security and access policies.

In this extensive guide, we explored various interfaces to list users, examined role metadata in system catalogs, and covered techniques to inspect and troubleshoot privileges.

Following best practices around auditing user rights while restricting permissions can prevent issues down the road. Use this as a reference for managing your critical PostgreSQL access control layer.