How to List Users in PostgreSQL

Key Takeaways

1. Use \du or \du+ in psql to list PostgreSQL users and roles.
2. SQL queries on pg_user provide customizable user detail reports.
3. Viewing current users and superusers helps monitor access and permissions.

Managing user accounts is a fundamental aspect of PostgreSQL administration. Whether you're auditing access, assigning roles, or ensuring security compliance, knowing how to list users and their attributes is essential. This guide outlines two primary methods to retrieve user information in PostgreSQL: using the psql command-line interface and executing SQL queries.

---

1. Listing Users Using the psql Command-Line Interface

The psql tool provides built-in meta-commands to interact with the PostgreSQL server.

Step 1: Connect to the PostgreSQL Server

Open your terminal and connect to your PostgreSQL server using the psql command:

psql -U postgres

Replace postgres with your username if different. You'll be prompted to enter your password.

Step 2: List All Users

Once connected, use the \du command to display all user accounts (roles) in the current PostgreSQL database cluster:

\du

This command presents a table with the following columns:

• Role name: The name of the user or role.
• Attributes: Role attributes such as Superuser, Create role, Create DB, etc.
• Member of: Lists any roles that the user is a member of.

For more detailed information, including descriptions, use the \du+ command:

\du+

---

2. Listing Users Using SQL Queries

Alternatively, you can retrieve user information by querying system catalogs.

Querying the pg_user View

The pg_user view provides information about database users. Execute the following SQL statement:

SELECT usename AS role_name,
  CASE
    WHEN usesuper AND usecreatedb THEN
      CAST('superuser, create database' AS pg_catalog.text)
    WHEN usesuper THEN
      CAST('superuser' AS pg_catalog.text)
    WHEN usecreatedb THEN
      CAST('create database' AS pg_catalog.text)
    ELSE
      CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name DESC;

This query returns:

• role_name: The name of the user.
• role_attributes: A textual representation of the user's privileges.

---

Additional Tips

• Viewing the Current User: To identify the current connected user, execute:

  SELECT current_user;

  or

  SELECT session_user;

• Listing Superusers: To list all superusers, run:

  SELECT usename FROM pg_catalog.pg_user WHERE usesuper = true;

• Checking Active Connections: To view currently active users and their sessions:

  SELECT * FROM pg_catalog.pg_stat_activity WHERE state = 'active';

---

Summary

• Use \du or \du+ in psql to list all users and their attributes.
• Execute SQL queries on pg_catalog.pg_user for detailed user information.
• Utilize additional queries to monitor current users and active connections.

By employing these methods, database administrators can effectively manage user accounts and maintain PostgreSQL security and integrity.

FAQs

How do I list all users in PostgreSQL?

Use the \du or \du+ command in the psql terminal.

Can I check user privileges using SQL?

Yes, query pg_catalog.pg_user to see usernames and their role attributes.

How can I find the current connected user?

Run SELECT current_user; or SELECT session_user; in SQL.

We are Leapcell, your top choice for hosting backend projects.

Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:

Multi-Language Support

• Develop with Node.js, Python, Go, or Rust.

Deploy unlimited projects for free

• pay only for usage — no requests, no charges.

Unbeatable Cost Efficiency

• Pay-as-you-go with no idle charges.
• Example: $25 supports 6.94M requests at a 60ms average response time.

Streamlined Developer Experience

• Intuitive UI for effortless setup.
• Fully automated CI/CD pipelines and GitOps integration.
• Real-time metrics and logging for actionable insights.

Effortless Scalability and High Performance

• Auto-scaling to handle high concurrency with ease.
• Zero operational overhead — just focus on building.

Explore more in the Documentation!

Follow us on X: @LeapcellHQ