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.

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

Previous PostgreSQL Exercise: Assign a User to a Role.