How to List Users in PostgreSQL

Galaxy Glossary

How can I find out who uses my PostgreSQL database?

Shows every database role—helpful for auditing who can log in and what they can do.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why would I need to list users?

Audit security, verify role creation, and check login permissions. Knowing every role prevents privilege creep and flags unused accounts.

Which catalog views expose users?

pg_roles, pg_user, and pg_authid (super-users only).pg_roles is the safest read-only source.

What is the basic SQL to list all roles?

Select from pg_roles: SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

How do I include membership information?

Join pg_roles to pg_auth_members to see which users belong to which group roles.

How can I filter by login-capable users?

Add WHERE rolcanlogin to exclude pure group roles.

Can psql show users without SQL?

Yes.Run \du or \dg in psql for a formatted list.

Example: who can query customer data?

Join pg_roles with information_schema.role_table_grants to find roles granted SELECT on Customers.

Step 1 – list roles

SELECT r.rolname FROM pg_roles r;

Step 2 – add table grants

SELECT DISTINCT grantee FROM information_schema.role_table_grants WHERE table_name = 'customers';

Best practices for user management

Create group roles (e.g., app_readonly), grant privileges to the group, and add individual users to it.Revoke unused accounts promptly.

How do I script periodic audits?

Schedule the syntax below in cron or pgAgent, then write results to a log table for compliance.

What permissions are required to query pg_roles?

CONNECT to the database and a role with pg_read_all_stats membership, or be a superuser.

Is there an easier GUI option?

Tools like Galaxy surface user lists in one click and let you endorse audit queries for team reuse.

.

Why How to List Users in PostgreSQL is important

How to List Users in PostgreSQL Example Usage


-- Find users who can modify Orders totals
SELECT grantee
FROM   information_schema.role_table_grants
WHERE  table_name = 'orders'
  AND  privilege_type IN ('INSERT','UPDATE','DELETE');

How to List Users in PostgreSQL Syntax


-- Basic user list
SELECT rolname                     AS user_name,
       rolsuper                    AS is_superuser,
       rolcanlogin                 AS can_login,
       rolvaliduntil               AS password_expiry
FROM   pg_roles
ORDER  BY rolname;

-- Include role membership
SELECT member.rolname   AS member_name,
       role.rolname     AS parent_role
FROM   pg_auth_members m
JOIN   pg_roles member ON member.oid = m.member
JOIN   pg_roles role   ON role.oid   = m.roleid
ORDER  BY role.rolname, member.rolname;

-- Ecommerce example: users with SELECT on Customers
SELECT grantee
FROM   information_schema.role_table_grants
WHERE  table_name = 'customers' AND privilege_type = 'SELECT';

Common Mistakes

Running SELECT * FROM pg_user and assuming it shows all roles. It hides group roles and non-login accounts. Use pg_roles for completeness.
Granting privileges to individual users instead of group roles. This causes management overhead. Fix by creating a group role and adding users to it.

Frequently Asked Questions (FAQs)

Does pg_roles include system roles?

Yes, roles starting with pg_ are system generated. Filter them out with WHERE rolname NOT LIKE 'pg_%'.

Can I see password hashes?

Only superusers can query pg_authid. Regular users should rely on rolvaliduntil to check password expiry without exposing hashes.

How do I drop an unused user?

Reassign or drop owned objects, then execute DROP ROLE username;. Ensure the role is not a member of required group roles.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie