Audit Row-Level Security Policies with pg_policies

Audit Row-Level Security Policies with pg_policies

pg_policies turns a table's row-level security setup — however many CREATE POLICY statements are scattered across migration files — into one queryable inventory: one row per policy, with the roles it binds to, the command it governs, and the boolean expressions doing the actual filtering. A \d on the table shows that a policy exists; it does not show whether that policy is permissive or restrictive, which roles it actually applies to, or what its USING clause evaluates to at runtime. That gap between "a policy is attached" and "here is exactly what the policy does" is what an audit needs closed before trusting row-level security in production.

Purpose and Overview

By default, PostgreSQL tables carry no row security policies at all. If a role has table-level access through the standard SQL GRANT system, every row is equally visible and modifiable — row-level security is not implied by anything else. That changes only when a table owner runs ALTER TABLE ... ENABLE ROW LEVEL SECURITY, documented in the Row Security Policies chapter. Once enabled, normal access to the table must be allowed by a policy. If row security is enabled but no policy has been created yet, PostgreSQL assumes a default-deny stance: no rows are visible, and none can be modified, by anyone other than the table owner or a role that bypasses row security.

Policies themselves come from CREATE POLICY, which defines a name, an optional command scope (ALL, SELECT, INSERT, UPDATE, or DELETE — ALL is the default), an optional role list (PUBLIC by default), and up to two boolean expressions: USING for which rows are visible, and WITH CHECK for which new or modified rows are allowed to be written. A table can carry multiple policies, and PostgreSQL combines them: permissive policies (the default) are combined with OR, so any one of them can grant access; restrictive policies are combined with AND, so every one of them must pass.

pg_policies is where all of that configuration becomes readable in one place instead of reconstructed from DDL history. It resolves the schema and table a policy belongs to, whether it is permissive or restrictive, the role array it applies to, the command it's scoped to, and the actual text of the USING and WITH CHECK expressions. What it does not show is who bypasses row security entirely — superusers, roles with the BYPASSRLS attribute, and (unless a table has been altered with FORCE ROW LEVEL SECURITY) the table's own owner. A complete audit reads pg_policies alongside those role attributes, not instead of them.

Sample Code

 1SELECT
 2    schemaname,
 3    tablename,
 4    policyname,
 5    permissive,
 6    roles,
 7    cmd,
 8    qual,
 9    with_check
10FROM
11    pg_policies
12ORDER BY
13    schemaname,
14    tablename,
15    policyname;

Filtered variant, for restrictive policies only:

 1SELECT
 2    schemaname,
 3    tablename,
 4    policyname,
 5    roles,
 6    cmd,
 7    qual
 8FROM
 9    pg_policies
10WHERE
11    permissive = 'RESTRICTIVE'
12ORDER BY
13    schemaname,
14    tablename;

Notes: permissive holds the literal text PERMISSIVE or RESTRICTIVE, matching the AS clause in CREATE POLICY. cmd holds one of the command keywords a policy can be scoped to — ALL, SELECT, INSERT, UPDATE, or DELETE. roles is an array; an empty or PUBLIC-only array means the policy applies to every role connecting to the table, not just an explicit list.

Code Breakdown

The base query is a full inventory scan; the filtered variant isolates the policy type most likely to unexpectedly deny access.

Reading permissive and roles

1permissive, roles

permissive tells you which Boolean operator PostgreSQL uses when more than one policy applies to the same query — OR for every permissive policy in the set, AND for every restrictive one. roles is a name[] array resolved from the policy's TO clause. If no role was specified when the policy was created, or PUBLIC was used explicitly, the policy applies to every user on the system — reading this column is the fastest way to confirm a policy that was meant to be scoped to one application role was not accidentally left open to PUBLIC.

The cmd Column and Command-Scoped Enforcement

1cmd

A policy created without a FOR clause defaults to ALL, meaning it is evaluated on the selection side and the modification side of every command. A policy scoped to a specific command behaves differently per command type: an UPDATE policy needs both a role's SELECT-level access to see which rows qualify for update and its own USING/WITH CHECK pair to control the result, while a DELETE policy only controls which visible rows can actually be removed — a row can be visible under a SELECT policy and still be un-deletable if it fails the DELETE policy's USING expression. Reading cmd alongside qual is what tells you which of those enforcement paths a given row is actually going through.

qual vs. with_check — Visibility vs. Modification

1qual, with_check

qual holds the USING expression: the condition that decides which existing rows a role can see or act on. with_check holds the WITH CHECK expression: the condition new or updated row values must satisfy before PostgreSQL allows the write, throwing an error if they don't. For an ALL or UPDATE policy created with only a USING clause and no explicit WITH CHECK, PostgreSQL reuses the USING expression for both purposes — so a NULL in with_check does not mean the policy has no write-side check; it means the check is identical to qual.

Key Row-Level Security Policy Concepts

Permissive vs. Restrictive Policies

Permissive policies are the default and are combined with a Boolean OR — administrators use them to add to the set of accessible rows. Restrictive policies are combined with AND and are meant to narrow access that a permissive policy already granted. The order matters operationally: at least one permissive policy has to exist to grant a baseline before a restrictive policy can usefully reduce it. If only restrictive policies exist on a table, the AND combination has nothing to start from and no rows are accessible to anyone — a documented failure mode worth checking for directly in pg_policies before assuming a new restrictive rule is layering on top of existing access rather than replacing it.

Default-Deny When Row Security Is Enabled but Unpoliced

Enabling row security with ALTER TABLE ... ENABLE ROW LEVEL SECURITY does not itself grant or restrict anything — it switches the table into a mode where a policy is required for access. A table with row security enabled and zero rows in pg_policies is not "wide open with no rules"; it is locked to everyone except the owner and roles that bypass row security. Catching this state matters because it is easy to enable row security as a first migration step and forget the follow-up CREATE POLICY, which surfaces later as application code that can no longer see any rows at all.

Owner, Superuser, and BYPASSRLS Bypass

Superusers and any role carrying the BYPASSRLS attribute always bypass row security on every table, and a table's owner normally bypasses it too. None of that is visible in pg_policies — the view only describes the policies themselves, not who is exempt from them. An audit that stops at pg_policies can miss the fact that a service account with BYPASSRLS set sees every row regardless of how carefully the policies underneath are written.

FORCE ROW LEVEL SECURITY

A table owner who wants their own connections subject to the same policies as everyone else can run ALTER TABLE ... FORCE ROW LEVEL SECURITY. Without it, the owner's normal bypass stands even with row security enabled and policies defined — worth checking directly against the table definition alongside the pg_policies output, since the view gives no signal either way about whether force mode is set.

Referential Integrity Always Bypasses Row Security

Unique constraints, primary keys, and foreign key checks bypass row security entirely, by design — otherwise a foreign key lookup could fail unpredictably depending on which rows a policy currently hides. The practical implication for an audit is that row-level security controls what an application query can see and change; it does not control what the constraint system checks underneath that query, which is a documented channel through which information about hidden rows can leak if policies and constraints are designed without that interaction in mind.

Practical Applications

Reading pg_policies on a schedule turns row-level security review from a per-migration memory exercise into a repeatable inventory check.

Multi-Tenant Isolation Verification

On a schema where every tenant-scoped table is expected to carry a policy filtering on a tenant identifier, querying pg_policies and diffing the result against the expected table list catches two different failure modes in one pass: a table where row security was never enabled at all (full cross-tenant visibility for any role with a GRANT), and a table where it was enabled but the policy's qual expression doesn't actually reference the tenant column the way the rest of the schema assumes it does.

Pre-Deployment Security Review

Before a schema change ships, comparing the roles column against the list of application roles that are actually expected to touch a table catches a policy that was written scoped to PUBLIC when it was meant to be scoped to a single service role — a difference that is easy to miss reading a migration file but immediate reading the resolved pg_policies row.

Diagnosing Unexpectedly Empty Result Sets

When a query that used to return rows suddenly returns none after row security was enabled, checking pg_policies for the affected table distinguishes two very different causes: a qual expression that is filtering out rows because of a role mismatch or a stale session variable, versus a default-deny state with no applicable policy for that role and command at all.

Reviewing a New Restrictive Policy Before It Ships

Before adding a restrictive policy meant to further lock down access — for example, one requiring a local connection the way PostgreSQL's own row-security documentation illustrates for an administrator role — querying pg_policies for existing permissive coverage on the same table confirms the restrictive policy will narrow real access rather than combine with nothing and leave the table unreadable to everyone.

Building a Compliance-Ready Access Record

Exporting the full pg_policies result set gives a security reviewer a plain record of exactly which tables have row-level access controls, which roles they bind to, and which command types they cover — sourced from the server's own resolved state rather than hand-transcribed from CREATE POLICY statements spread across a migration history.

Version Compatibility

As of the current PostgreSQL documentation, the supported major versions run 14 through 18, PostgreSQL 19 is in beta, and 9.6 and earlier releases are already past their support window. Row-level security behavior described here — the ENABLE/FORCE ROW LEVEL SECURITY toggle, the PERMISSIVE/RESTRICTIVE combination rules, and the pg_policies column set — is documented as current behavior across that supported range.

One area worth checking against your specific release before relying on it: CREATE POLICY's documented interactions with MERGE — requiring SELECT permission on both source and target relations, and applying UPDATE- or DELETE-scoped policies to the corresponding MERGE actions — assume the engine you're running actually supports MERGE syntax. Confirm that against the release notes for your specific supported major version rather than assuming every table already covered by INSERT/UPDATE/DELETE policies in pg_policies is automatically covered the same way once a MERGE-based load path is added.

Disabling row security with ALTER TABLE ... NO FORCE ROW LEVEL SECURITY or a plain DISABLE ROW LEVEL SECURITY does not remove any policies already defined — they are simply ignored while row security is off, and reappear in force the moment it is re-enabled. That behavior, along with the row_security configuration parameter that can be set to off to surface an error (rather than silently filtered results) when a query would otherwise be affected by a policy, is documented as current and version-independent within the supported range — useful to know when validating a backup or export pipeline against row-level security rather than assuming it needs re-verifying on every upgrade.

Best Practices

• Treat pg_policies as the audit source of truth, not the migration files — DDL history can drift from what's actually enabled on the live table, especially after a hotfix applied directly in production.
• Test as the table owner and as a policy-scoped role separately — owners bypass row security by default, so testing only as owner will not surface a policy gap that a real application role would hit.
• Never ship a restrictive-only policy set — at least one permissive policy has to exist first; restrictive policies alone combine to zero accessible rows for everyone.
• Check with_check independently of qual on ALL and UPDATE policies — a NULL there doesn't mean no write check exists, it means the USING expression is reused for it.
• Audit BYPASSRLS role grants on their own schedule — a role with that attribute bypasses every policy on every table, and none of that is visible from pg_policies alone.
• Confirm FORCE ROW LEVEL SECURITY status directly against the table definition — the view describes policies, not whether the owner is currently subject to them.

References

• PostgreSQL Documentation — pg_policies — column reference for the row-level security policy catalog view.
• PostgreSQL Documentation — CREATE POLICY — full syntax and per-command semantics for defining row-level security policies.
• PostgreSQL Documentation — Row Security Policies — the DDL chapter covering ENABLE/FORCE ROW LEVEL SECURITY, default-deny behavior, and bypass rules.
• PostgreSQL Wiki — the community-maintained hub for PostgreSQL user documentation, administration guides, and tutorials.