RESTRICTIVE policies: the modifier people accidentally grant with

The semantics, precisely

For a given command and role, Postgres collects the applicable policies. Permissive ones combine with OR — any match admits the row. Restrictive ones combine with AND — every one must pass. The final predicate is (any permissive) AND (all restrictive).

Consequences worth memorising: a table with only RESTRICTIVE policies denies everything, because the permissive side of the conjunction is empty-false. And a RESTRICTIVE policy with USING (true) narrows nothing — which is why RowShield suppresses that specific combination as a non-finding rather than crying wolf.

Flavour attaches to individual policies, not tables: one command on one role can mix both kinds freely, and the planner folds them into the single conjunction described above. Restrictive clauses also carry their own command and role scope, so a restrictive SELECT never touches UPDATE behaviour. Keeping that scope in mind prevents the common surprise where a global-looking restriction turns out to bind one command only.

The audit query

See the full policy mix per table, per command, with the flavour attached:

Filter the output to one table at a time when auditing a live incident: interleaving flavours across tables makes omissions harder to see than a focused listing does.

SELECT tablename, policyname, permissive, cmd, roles, qual
FROM pg_catalog.pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, cmd, permissive DESC;

Using RESTRICTIVE well

Its honest use case is cross-cutting constraints layered over broad grants — a soft-delete filter, a plan-tier boundary, a maintenance window. Grant broadly with permissive owner policies, then narrow globally with one restrictive clause:

Name restrictive policies for what they exclude rather than what they allow: hide_soft_deleted tells the next reader the clause subtracts, while an allow-style name on a restrictive row invites exactly the misreading this page describes.

CREATE POLICY "owners_read_projects"
  ON public.projects FOR SELECT TO authenticated
  USING (owner_id = (SELECT auth.uid()));

CREATE POLICY "hide_soft_deleted"
  ON public.projects FOR SELECT TO authenticated
  AS RESTRICTIVE
  USING (deleted_at IS NULL);

How detection treats flavour

RowShield evaluates tautologies per flavour: a constant-true condition is flagged on PERMISSIVE policies, where it grants everything, and ignored on RESTRICTIVE ones, where it narrows nothing. Flavour-blind checkers produce exactly the false alarms that teach teams to ignore scanners.

As with every rule, findings participate in drift tracking — a policy flipped from RESTRICTIVE to PERMISSIVE in a refactor is a behavioural change and alerts as such.

Common false leads

The frequent wrong turn is additive tightening: a team reacts to overexposure by adding a RESTRICTIVE policy while leaving the original broad permissive one standing. That works only when the restrictive clause genuinely intersects the loose grant away — if it restates a condition the broad policy already implied, access is unchanged and the review closes on a no-op. Narrowing permissive coverage requires rewriting the permissive side, never stacking beside it.

Its mirror image: believing a lone RESTRICTIVE policy protects a fresh table. With no permissive policy present, default deny already governs and the restrictive clause never evaluates. The table is safe by absence, not by the clause — and the next engineer who adds a permissive grant inherits the restrictive filter unknowingly, which is at least better than inheriting nothing.

Verifying the combination from outside

Behaviour beats derivation here. Probe the table as anon and as an authenticated user, and craft one row that satisfies the permissive condition but fails the restrictive one: it should stay hidden. Reading the plan with EXPLAIN as the affected role shows both predicates folded into the scan filter, which confirms the combination in a way catalog text alone cannot.

Frequently asked

Can a RESTRICTIVE policy alone secure a table?

No. With no permissive policy present, default deny governs and restrictive clauses never even evaluate; restrictive modifies grants, it never creates them. A table secured only by a RESTRICTIVE policy is secure by absence, and the first permissive addition changes its meaning silently.

Why is USING (true) RESTRICTIVE not flagged?

It grants nothing and conceals nothing — a constant true on the restrictive side narrows every result set by exactly nothing, and the permissive side decides alone. Flagging it would generate the noise that trains teams to dismiss scanners, so the suppression is documented alongside the other deliberate non-findings.

Do multiple restrictive policies slow queries?

Each adds a predicate evaluated per row like any other filter, so cost tracks the predicates rather than the flavour. Index the columns they reference and wrap volatile functions so they evaluate once; the performance findings cover the indexing side on schedule.