Listing All Users in PostgreSQL

PostgreSQL is a powerful, open-source relational database management system widely used in production environments. One of the most common administrative tasks is listing all users in PostgreSQL. Whether you are a beginner learning database basics or an intermediate professional managing production systems, understanding how PostgreSQL users work is essential.

This guide provides a detailed, beginner-friendly explanation of how to list PostgreSQL users, including SQL queries, psql commands, real-world use cases, and best practices.

What Are Users in PostgreSQL?

In PostgreSQL, users are known as roles. A role can act as:

• A database user
• A group of users
• A role with specific privileges

PostgreSQL treats users and roles the same internally, which provides flexibility and fine-grained access control.

Why Listing All Users in PostgreSQL Is Important

Knowing how to list users in PostgreSQL is critical for database administration and security management.

Common Use Cases

• Auditing database access
• Troubleshooting permission issues
• Managing application users
• Ensuring security compliance
• Cleaning up unused accounts

Understanding PostgreSQL System Catalogs

PostgreSQL stores metadata about users and roles in system catalogs. The most important catalog for listing users is:

• pg_roles

This system view contains all roles, including login users and group roles.

Listing All Users in PostgreSQL Using psql

If you are using the PostgreSQL interactive terminal (psql), the easiest way to list users is with a built-in command.

Using the \\du Command

📄

\du

This command displays:

• Role name
• Role attributes
• Group memberships

Example Output

📄

 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 app_user  |                                                            | {}
 readonly  | Cannot login                                               | {}

This output clearly shows which users can log in and what privileges they have.

Listing All Users in PostgreSQL Using SQL Queries

For scripting, automation, or application-level access, SQL queries are preferred.

Basic Query to List All Users

📄

SELECT rolname FROM pg_roles;

This query returns all roles, including users and groups.

Listing Only Login Users

To list only users who can log in:

📄

SELECT rolname 
FROM pg_roles 
WHERE rolcanlogin = true;

Detailed PostgreSQL Users Information

You can retrieve detailed user attributes using additional columns.

Query with Full User Details

📄

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

Explanation of Columns

Real-World Example: Auditing Database Users

Imagine you are managing a production PostgreSQL server. You want to verify which users have login access.

📄

SELECT rolname 
FROM pg_roles 
WHERE rolcanlogin = true;

This helps you identify:

• Unused accounts
• Security risks
• Application users that no longer exist

Listing Users with Database Access

To see which users can connect to a specific database:

📄

SELECT datname, usename 
FROM pg_database d
JOIN pg_user u ON has_database_privilege(u.usename, d.datname, 'CONNECT');

This query is useful in multi-database environments.

Common Mistakes When Listing Users in PostgreSQL

• Confusing roles with users
• Ignoring non-login roles
• Granting excessive privileges
• Not auditing users regularly

Listing all users in PostgreSQL is a fundamental skill for database administrators and developers. Whether you use psql commands or SQL queries, understanding PostgreSQL roles helps improve security, performance, and maintainability.

By mastering PostgreSQL user listing techniques, you gain better control over access management and system reliability.

Frequently Asked Questions (FAQs)

1. What is the difference between users and roles in PostgreSQL?

PostgreSQL treats users and roles as the same object. A user is simply a role with login privileges.

2. How do I list only active users in PostgreSQL?

You can filter login-enabled users using the rolcanlogin column from pg_roles.

3. Can I list users without using psql?

Yes, you can list PostgreSQL users using SQL queries in any database client or application.

4. Is pg_user different from pg_roles?

pg_user is a legacy view that shows only login users, while pg_roles shows all roles.

5. Why should I avoid using the postgres superuser?

Using the postgres superuser for applications increases security risks and violates the principle of least privilege.