PostgreSQL user listing exposes the governance gap in database access

At a glance

What this is: This is a how-to guide for listing PostgreSQL users and roles, with the key finding that access visibility must be paired with auditing and privilege review to matter.

Why it matters: It matters because database access governance depends on knowing who can log in, what they can do, and whether those entitlements are still justified across NHI, autonomous, and human identity programmes.

By the numbers:

• Up to 80% of data breaches in the U.S. start with unauthorized access.

👉 Read StrongDM's guide to listing PostgreSQL users and roles

Context

PostgreSQL role listing is a governance control as much as a technical task. If teams cannot reliably see roles, login privileges, and membership chains, access reviews become incomplete and revocation decisions lag behind reality.

For database environments, this gap shows up in both NHI and human admin workflows. The same review failure that leaves an over-privileged service account in place can also leave a human DBA with broader access than their current job requires.

Key questions

Q: How should teams review PostgreSQL users for access governance?

A: Review PostgreSQL users by combining direct role listing with membership, privilege, and login checks. A complete review should confirm who can log in, what inherited privileges they receive, and whether the account still has an approved business need. Access review is only useful when it drives revoke or re-certification decisions, not when it stops at enumeration.

Q: Why do PostgreSQL role memberships create hidden access risk?

A: Role memberships can hide effective privilege because a user may inherit permissions from one or more group roles. That means the account name alone does not describe the real access surface. Security teams should treat inherited permissions as part of the entitlement review, especially where database admins share roles across projects or environments.

Q: What signals show PostgreSQL access is drifting beyond its intended scope?

A: Look for dormant roles that still have login rights, accounts with broad inherited privileges, and users whose membership no longer matches their job or service function. If access logs show little or no legitimate use, but the role remains active, the entitlement is likely stale and should be reviewed for revocation.

Q: What should teams do when PostgreSQL access reviews uncover outdated roles?

A: Revoke the unnecessary grants first, then remove unused login rights and decommission accounts that no longer have a clear owner. For any role that must remain, document the approval basis and the next review date. Strong database governance depends on closing the loop between listing, audit, and revocation.

Technical breakdown

Listing PostgreSQL roles with psql and catalog queries

PostgreSQL stores role and privilege data in system catalogs, which is why commands such as \du, \du+, and SELECT queries against pg_roles return different levels of detail. The basic list shows which roles exist and whether they can log in. The catalog queries expose attributes such as superuser status, role creation rights, database creation rights, and replication privileges. That distinction matters because many access reviews fail when teams stop at simple role enumeration and never inspect effective privilege scope.

Practical implication: use catalog queries, not just role lists, when reviewing database access.

Role membership and inherited privileges in PostgreSQL

PostgreSQL role membership is more than a naming convenience. A user can inherit permissions from group roles, which means the visible account name may not reflect the actual effective access. Queries that join pg_roles with pg_auth_members reveal these chains and are essential for understanding who can act with delegated authority. In practice, this is where access creep hides, especially in shared admin groups and long-lived database access patterns.

Practical implication: review inherited permissions separately from direct grants before certifying access.

Database auditing, login logging, and pgAudit

Listing users is only the first step in a control loop. PostgreSQL audit settings such as log_connections, log_disconnections, and log_statement create a record of login activity and schema changes, while pgAudit extends that visibility to a broader set of actions. These logs support compliance, but they also make privilege misuse observable. Without them, access reviews describe who should have access, not what they actually did with it.

Practical implication: enable login and role-change logging before relying on periodic access reviews.

Breaches seen in the wild

• MongoBleed breach — MongoBleed exposed secrets across 87K MongoDB servers.
• DeepSeek breach — DeepSeek breach exposed 1M+ log lines and sensitive secret keys.

Read our 52 NHI Breaches Analysis report for a comprehensive view of breaches impacting Non-Human Identities including AI Agents.

NHI Mgmt Group analysis

Database user listing is a visibility control, not a governance outcome. Listing roles in PostgreSQL only tells you what exists at a point in time. It does not tell you whether the account is still needed, whether the effective privileges are inherited, or whether the access path is being used as intended. Teams that treat enumeration as the control end up with audit comfort and weak entitlement hygiene. The practitioner conclusion is simple: visibility without review is inventory, not governance.

Role membership is where PostgreSQL access drift becomes hard to see. Inherited permissions can make a user appear narrow while the underlying group grants broader operational power. That is the failure mode hidden by simple role checks, and it is exactly where database governance gets out of sync with real access. The practitioner conclusion is to treat membership chains as first-class evidence in access certification.

Database logging closes the gap between declared and actual access. Without login and role-change telemetry, teams cannot tell whether a role list reflects current reality or stale entitlement. This is why audit logging belongs in the same control conversation as role review. The practitioner conclusion is to pair enumeration with evidence of use, not assume one substitutes for the other.

PostgreSQL user governance is a lifecycle problem as much as a permissions problem. Create, grant, revoke, and delete actions only work as controls when they are tied to joiner-mover-leaver discipline and periodic recertification. Static administration habits are where dormant access accumulates. The practitioner conclusion is to align database role governance with lifecycle review, not ad hoc DBA practice.

From our research:

• The average organisation believes more than 1 in 5 of their non-human identities are insufficiently secured, according to The 2024 ESG Report: Managing Non-Human Identities.
• Two-thirds of enterprises have endured a successful cyberattack resulting from compromised non-human identities, with a quarter encountering multiple attacks, according to the same report.
• For broader governance context, see Ultimate Guide to NHIs for lifecycle, visibility, and least-privilege controls across machine identities.

What this signals

Role inventory will keep expanding faster than manual governance can absorb it. PostgreSQL environments tend to accumulate roles, inherited privileges, and service-oriented accounts over time, which means the governance problem shifts from discovery to continual entitlement hygiene. Teams should expect database access review to become more operational, with tighter links to lifecycle events and revocation triggers.

Access visibility has to connect to business ownership. A complete list of PostgreSQL users is useful only when each account has an owner, purpose, and review cadence. That is where database governance aligns with broader identity programmes, including NHI lifecycle management and human admin certification.

For teams building out the control model, the relevant question is no longer whether PostgreSQL can list users. It is whether those listings can feed a reliable review process, and whether the organisation can act on stale access before it becomes persistent exposure.

For practitioners

• Inventory direct and inherited PostgreSQL privileges Run role and membership queries together so you can see both explicit grants and permissions inherited through group roles. Include superuser, role creation, database creation, and login flags in the review.
• Turn role listings into recertification evidence Use pg_roles output as the starting point for access review, then validate whether each account still has a business purpose, an owner, and a current approval trail.
• Enable login and role-change logging Capture user logins, logouts, schema changes, and role modifications before depending on manual checks. Without telemetry, you cannot tell whether a permission is merely assigned or actively used.
• Automate revoke and offboarding checks Tie database role removal to joiner-mover-leaver workflows so stale users and unused privileges do not survive personnel changes or project completion.

Key takeaways

• Listing PostgreSQL users is a visibility step, but it does not by itself prove access is governed.
• Inherited role membership and missing audit logs are the two biggest reasons database access reviews miss effective privilege.
• The practical fix is to tie enumeration, logging, and revocation into one lifecycle workflow, not treat them as separate tasks.

Key terms

• PostgreSQL Role: A PostgreSQL role is the identity object used to represent a user, group, or permission holder inside the database. Roles can log in, own objects, inherit privileges, or act as membership containers, so they are the core unit for database access governance and review.
• Inherited Privilege: Inherited privilege is access a role receives through membership in another role rather than through a direct grant. It matters because the effective permission set can be broader than a simple user list suggests, which is why database governance must inspect membership chains, not just named accounts.
• Database Access Recertification: Database access recertification is the periodic review of who still needs access, what they can do, and whether the entitlement remains justified. In PostgreSQL, this must include direct grants, role membership, login rights, and audit evidence so the review reflects real operational privilege.

Deepen your knowledge

PostgreSQL user enumeration, role membership review, and access auditing are core topics in our NHI Foundation Level course, the industry's only accredited NHI security programme. If you are building a database governance process from the same starting point, it is worth exploring.

This post draws on content published by StrongDM: How To List Users in PostgreSQL (psql command & GUI tools). Read the original.