PostgreSQL Column-Level Permissions Audit Query

calendar Jun 6, 2026 / · 6 min read · column_privileges information_schema permissions security audit administration  ·
Share on: linkedin copy

PostgreSQL Column-Level Permissions Audit Query

Which roles can read which columns? Table-level grants are easy to list, but they hide a finer layer of access. A role denied SELECT on a table can still hold SELECT on two of its columns, and that is exactly where personal data quietly stays reachable after a wider grant is revoked. The information_schema.column_privileges view answers the column-level question directly: every grantee, every column, every privilege, in one query.

Purpose and Overview

PostgreSQL lets you grant privileges at the column level, not just the table level. A statement like GRANT SELECT (email, phone) ON customers TO analyst gives the role access to two columns and nothing else on the table. These grants are powerful for least-privilege design, but they are also easy to lose track of, because the usual table-level audit does not show them.

information_schema.column_privileges is the SQL-standard view that exposes column-level grants. Each row is one privilege held by one grantee on one column. The view reports four facts per row: who holds the privilege (grantee), what it is (privilege_type), where it applies (schema, table, column), and whether the holder may pass it on (is_grantable).

One important property of the view: it only shows privileges that the querying role is a party to. A non-superuser sees the grants it made or received, not the full picture. To audit the whole database, run the query as a superuser.

Sample Code

 1SELECT
 2    table_schema,
 3    table_name,
 4    column_name,
 5    grantee,
 6    privilege_type,
 7    is_grantable
 8FROM information_schema.column_privileges
 9WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
10ORDER BY table_schema, table_name, column_name, grantee;

Notes: Works on every supported PostgreSQL version — information_schema is part of the SQL standard. Run as a superuser to see all grants; a regular role sees only privileges it is party to. Filter by grantee or table_name to narrow a large result.

Code Breakdown

The query is a straight read of one standard view, with a filter that removes system noise.

The Returned Columns

grantee is the role that holds the privilege. privilege_type is one of SELECT, INSERT, UPDATE, or REFERENCES — the four privileges that can be granted per column. is_grantable is YES or NO and tells you whether the grantee can grant the same privilege onward with WITH GRANT OPTION. Together they describe a single, specific grant.

The Schema Filter

WHERE table_schema NOT IN ('pg_catalog', 'information_schema') drops the system schemas. Those schemas carry many default privileges that have nothing to do with your application data, and including them buries the rows you care about. Excluding them keeps the result focused on user tables.

Why information_schema and Not pg_attribute

The raw access control lists live in pg_attribute.attacl as PostgreSQL ACL arrays — compact but hard to read. information_schema.column_privileges does the work of expanding those arrays into one readable row per grant. For an audit you want the readable form; reach for pg_attribute.attacl or aclexplode() only when you need the raw representation.

The Ordering

Sorting by schema, table, column, and grantee groups the output the way a reviewer reads it: all grants on one column sit together, and all columns of one table sit together.

Key Column Privilege Types

SELECT

The right to read the column. Column-level SELECT is the most common column grant and the one most relevant to data-exposure audits, because it controls who can see a value.

INSERT

The right to supply a value for the column in an INSERT. A role with column-level INSERT on some columns can insert rows that set those columns and leave the rest to defaults.

UPDATE

The right to change the column's value in an UPDATE. Column-level UPDATE lets you allow a role to modify a status field, say, without granting write access to the whole row.

REFERENCES

The right to create a foreign key that references the column. This is easy to overlook in audits but still a real grant, because it lets a role build dependencies on the column.

Note that DELETE does not appear here. Deletion operates on whole rows, so it is a table-level privilege only — there is no column-level DELETE.

Practical Applications

A column-privilege audit is a routine step in any access review.

Personal-Data Exposure Audit

Filter the result to the columns that hold personal or sensitive data and check the grantee list. Any role that should not see an email, a phone number, or a national identifier but appears against that column is a finding to remediate.

Least-Privilege Verification

When a design intends a role to touch only specific columns, this query confirms the grants match the design. Extra columns in the result mean the role has more access than intended.

Pre-Compliance Access Review

Compliance frameworks ask who can access what. Running this query as a superuser produces an evidence artifact: a complete list of column-level grants across the application schema, ready to attach to a review.

Diffing Grants Across Environments

Run the query in staging and production and compare the output. Differences reveal grants that drifted — a column opened up in one environment but not the other. Saving the sorted result to a file in each environment turns the comparison into a simple diff, which makes drift obvious and easy to attach to a change record.

Tracking Grants After a Role Reassignment

When a role is dropped and its objects are reassigned, or when responsibilities move between teams, column grants can linger on the old grantee. Re-running this query after the change confirms that access followed the intended role and that no stale column privilege was left behind.

Version Compatibility

information_schema.column_privileges is defined by the SQL standard and has been present and stable across every modern PostgreSQL major version, from 12 through 17 and well before. The column set and semantics do not change between versions, which makes the query safe to reuse across upgrades.

The one behavior to keep in mind is visibility. The view filters to privileges the querying role is party to, so a non-superuser audit is incomplete by design. Run it as a superuser for full coverage. When you need the raw, unfiltered access lists, query pg_attribute.attacl directly or expand it with aclexplode(), which returns one row per ACL entry regardless of the current role — at the cost of working with internal ACL representation rather than the friendly standard view.

Best Practices

  • Run as a superuser for complete coverage — the view hides grants the querying role is not party to, so a non-superuser result understates the real access.
  • Read an empty result correctly — no rows for a table means no explicit column grants, not no access. Table-level privileges may still apply; check table_privileges too.
  • Combine with table_privileges for the full picture — column grants and table grants together define what a role can reach; auditing one without the other misses half the story.
  • Re-audit after role changes — grants accumulate as roles are created, altered, and reassigned; schedule the audit rather than running it once.
  • Prefer column grants over ad-hoc views for simple cases — column-level grants express least privilege directly, but know the maintenance cost as the schema grows and weigh a security-barrier view when logic gets complex.

References

Posts in this series