Skip to content
AccessVault Docs

Privilege Analysis

The Privileges tab compares what each user is granted with what they’ve actually used, and flags over-provisioned roles — granted privileges with no recorded usage. It’s the fastest way to act on least-privilege before an audit.

Requirements

The analysis reads usage counts from the pg_stat_statements extension. If it isn’t installed, the tab says so and offers an Enable Extension button, which runs CREATE EXTENSION IF NOT EXISTS pg_stat_statements; (your database user needs rights to create extensions, and most servers also need pg_stat_statements in shared_preload_libraries, which requires a restart — manual instructions are shown in the tab).

Running an analysis

Click Run Analysis (later, Re-run). Summary pills show total users, over-provisioned users, users with no usage data, and total unused privileges. Results group into:

  • Over-Provisioned — users holding privileges with zero recorded calls.
  • No Usage Data — users with no captured statements; usage can’t be determined, so nothing is flagged.
  • Privileges Match Usage — nothing to do.
  • Superusers — skipped: superusers bypass privilege checks, so grant analysis doesn’t apply.

Each user row shows their privilege chips marked ✓ (granted and used) or ✗ (granted, no recorded calls), plus call counts per statement type and total execution time.

How to read the results — honest caveats

  • Usage is cumulative since pg_stat_statements was last reset. On a freshly enabled extension or a recently reset server, “unused” mostly means “not used yet” — let it collect over a representative period before revoking.
  • Classification is per user, by statement type (SELECT/INSERT/UPDATE/DELETE), not per schema or per table. A user flagged as using SELECT used it somewhere, not necessarily everywhere it’s granted.
  • The DROP privilege (schema CREATE — see Managing Users) is never flagged unused, because DDL isn’t captured by this analysis.

Revoking unused privileges

Revoke Unused on an over-provisioned row immediately executes real REVOKE statements for each flagged privilege, across every non-system schema — there is no preview step and no confirmation dialog, so treat the button as live. Each (schema, privilege) revoke is written to the audit log individually; if one fails, the run stops there and the revokes already executed stay applied.

As a guardrail, the button refuses to strip every privilege a user holds — for that, edit the user directly in the Users tab, which also gives you per-schema control.

Exporting

Export CSV saves the analysis (one row per user: status, granted / used / unused privileges, and per-statement-type call counts) as privilege-analysis-YYYY-MM-DD.csv — handy as evidence of a least-privilege review.