How to List All Users and Roles in PostgreSQL?

List All Users and Roles

Write a PostgreSQL query to list all users and roles in the database.

Solution:

-- List all roles and users
SELECT rolname, rolcanlogin FROM pg_roles;

Explanation:

• Purpose of the Query:
• Retrieves all roles and checks if they can log in.
• Key Components:
• pg_roles: A system catalog table containing role information.
• Real-World Application:
• Helps administrators audit user access.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to display all roles with their login capabilities and connection limits.
• Write a PostgreSQL query to list every user along with their assigned roles and the creation dates of those roles.
• Write a PostgreSQL query to list users and roles, filtering out system accounts from the results.
• Write a PostgreSQL query to list all roles and include extra details like the role's inheritance properties.

Go to:

• PostgreSQL User and Role Management Queries Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Assign a User to a Role.
NEXT : Remove a User.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.