pg_permission_audit — Who Has Access to What in PostgreSQL? — Blog

What Does This Tool Do?

pg_permission_audit creates a complete map of all roles, grants, and access rights in the database. It shows not only directly assigned privileges but also traces the inheritance chain through role memberships. The result is a clear picture: who can access which tables with which operations — and why.

How Is It Called?

Claude CLI (Natural Language)

● ● ● Claude CLI — Permission Audit

claude> Who has access to which tables?

▶ Calling tools...
→ pg_connect { "profile": "production-db" }
✓ Connected to production-db (PostgreSQL 16.x)

→ pg_permission_audit {}

Permission audit: 2 roles, 1 schema analyzed

👤 PUBLIC
public.pg_stat_statements — SELECT (is_grantable: NO)

👤 mcp_test
public.partitioned_events — SELECT, INSERT, UPDATE, DELETE (is_grantable: YES)

MCP Protocol (Programmatic)

● ● ● MCP Protocol — Connection

// Programmatic invocation via MCP SDK
const result = await client.callTool("pg_connect", { "profile": "production-db" });

// Response:
{ "status": "connected", "version": "PostgreSQL 16.x" }

● ● ● MCP Protocol — Permission Audit

// Programmatic invocation via MCP SDK
const result = await client.callTool("pg_permission_audit", {});

// Response:
[
{
    "grantee": "PUBLIC",
    "table_schema": "public",
    "table_name": "pg_stat_statements",
    "privilege_type": "SELECT",
    "is_grantable": "NO"
},
{
    "grantee": "mcp_test",
    "table_schema": "public",
    "table_name": "partitioned_events",
    "privilege_type": "DELETE",
    "is_grantable": "YES"
},
{
    "grantee": "mcp_test",
    "table_schema": "public",
    "table_name": "partitioned_events",
    "privilege_type": "INSERT",
    "is_grantable": "YES"
},
{
    "grantee": "mcp_test",
    "table_schema": "public",
    "table_name": "partitioned_events",
    "privilege_type": "SELECT",
    "is_grantable": "YES"
},
{
    "grantee": "mcp_test",
    "table_schema": "public",
    "table_name": "partitioned_events",
    "privilege_type": "UPDATE",
    "is_grantable": "YES"
}
]

What Happens Under the Hood?

The tool executes multiple queries against the PostgreSQL system catalog:

Roles and Attributes

-- All user-defined roles with their attributes
SELECT rolname, rolsuper, rolcanlogin, rolcreatedb, rolcreaterole, rolconnlimit, rolvaliduntil FROM pg_roles WHERE rolname NOT LIKE 'pg_%' ORDER BY rolname;

Role Memberships

-- Inheritance chain: which role inherits from which?
SELECT r.rolname AS role, m.rolname AS member_of FROM pg_auth_members am JOIN pg_roles r ON am.member = r.oid JOIN pg_roles m ON am.roleid = m.oid ORDER BY r.rolname;

Table Privileges

-- Table-level grants from information_schema
SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY grantee, table_schema, table_name;

Output in Detail

grantee — Role name or PUBLIC
table_schema — Schema of the table
table_name — Name of the table
privilege_type — Privilege type: SELECT, INSERT, UPDATE, DELETE
is_grantable — Whether the role can grant this privilege to others (YES/NO)

Pro Tip: Every superuser has implicit access to everything — that's why the tool shows no explicit privileges for superuser roles. The superuser_count metric helps detect excessive superuser usage.

What Does the Test Suite Validate?

The test suite validates pg_permission_audit against a PostgreSQL instance with 3 test roles (mcp_test_readonly, mcp_test_readwrite, mcp_test_admin) with different grant levels:

● ● ● vitest run — Testsuite

✓ pg_permission_audit > lists all roles with attributes
✓ pg_permission_audit > includes privilege details per role
✓ pg_permission_audit > detects superuser status correctly

Test Files 1 passed (1)
Tests 3 passed (3)
Duration 215ms

Test Data Scenario

The seed database contains:

mcp_test_readonly — SELECT on all tables in app and perf
mcp_test_readwrite — SELECT, INSERT, UPDATE, DELETE on app; SELECT, INSERT on perf
mcp_test_admin — SUPERUSER with full access

Tests validate that:

All roles are listed with their attributes (superuser, canlogin)
Privilege details per role are correctly returned
Superuser status is correctly detected

Summary

pg_roles + pg_auth_members

Data Sources

Output Fields

Tests

read-only

Type

pg_permission_audit answers the most important security question for any database: who has access to what — and why? It replaces manual querying of pg_roles, pg_auth_members, and information_schema.role_table_grants with a single, structured audit report.

Combined with pg_gdpr_audit (→ where is PII?), it forms the core of the /pg-comply workflow skill.

pg_permission_audit — Who Has Access to What in PostgreSQL?