Policy sprawl: when forty policies contradict each other
Permissive policies accumulate per feature and widen access via OR. How sprawl grows, how to read it safely, and a verified before/after consolidation method.
Nobody writes forty policies on purpose. They accrete — one per feature, one per exception, one per incident — and because permissive policies OR together, each addition widens the door. This article shows how to read an accumulated policy set correctly and how to consolidate it without changing who sees what.
A policy set is a growth artifact. It records every moment someone needed an access pattern and didn't find one already covering them: the marketing export feature, the read-only auditor, the integration account, the emergency fix that stayed. Each addition was reasonable. None of them came with a removal. And underneath them all sits one piece of PostgreSQL mechanics with outsized consequences: permissive policies combine with logical OR, so the effective access of any table is the union of everything anyone ever asked for.
This article builds a realistic sprawl, demonstrates exactly what the union does, then walks a consolidation to a minimal policy set — verifying at every step that effective access stays byte-for-byte identical. The method scales from ten policies to forty unchanged; only the patience required differs.
How sprawl accumulates, mechanically
Sprawl follows the shape of your org chart and your incident history:
| Trigger | Typical addition | What it actually grants |
|---|---|---|
| New feature needs read access | One narrow select policy | That pattern — plus nothing stops others matching it later |
| Integration partner request | A policy keyed to their identifier | A permanent second-class authorization model in policy form |
| Incident hotfix | Wider clause "temporarily" | A widening that outlives the incident by quarters |
| Different author, same table | Parallel policy expressing similar intent slightly differently | Redundant rows in the union — or subtly wider ones |
Two properties make the accumulation invisible. First, additions are individually reviewable and usually correct — the problem only exists in aggregate, and no diff shows aggregates. Second, there is no deletion pressure symmetrical to creation: features die, integrations end, hotfixes complete, but their policies remain, because removing a policy feels risky while adding one felt safe. The asymmetry guarantees growth.
The arithmetic of OR, made concrete
Four personas, three invoices, four policies — the canonical mess, runnable as printed:
create table members (
workspace_id uuid not null,
user_id uuid not null,
member_role text not null,
primary key (workspace_id, user_id)
);
create table invoices (
id uuid primary key,
workspace_id uuid not null,
owner_id uuid not null,
status text not null,
total_cents int not null
);
alter table members enable row level security;
create policy "members_read"
on members for select to authenticated
using (user_id = (select auth.uid()));
alter table invoices enable row level security;
-- Four permissive policies, accreted over three sprints:
create policy "invoices_select_owner"
on invoices for select to authenticated
using ((select auth.uid()) = owner_id);
create policy "invoices_select_public_status"
on invoices for select to authenticated
using (status = 'public');
create policy "invoices_select_workspace_editors"
on invoices for select to authenticated
using (exists (
select 1 from members m
where m.workspace_id = invoices.workspace_id
and m.user_id = (select auth.uid())
and m.member_role = 'editor'
));
create policy "invoices_select_workspace_viewers"
on invoices for select to authenticated
using (exists (
select 1 from members m
where m.workspace_id = invoices.workspace_id
and m.user_id = (select auth.uid())
and m.member_role = 'viewer'
));
Read as intent, each line is sensible. Read as Postgres evaluates it, Alice's visible set is:
owner-policy rows OR public-status rows OR editor-membership rows OR viewer-membership rows
Fixture data: a private draft owned by Alice (workspace A), a public invoice also owned by Alice, another workspace's private draft owned by Dave. Impersonating each persona against this schema (harness mechanics per our testing guide) produced these row counts as executed:
| Persona | Relationship to data | Rows visible | Via which branches |
|---|---|---|---|
| Alice | Owner in workspace A | 2 | Her draft + the public one |
| Bob | Editor in workspace A | 2 | Both workspace-A invoices, via membership |
| Carol | Viewer in workspace A | 2 | Identical to Bob's view |
| Dave | Editor in workspace B | 2 | His own draft plus the global public invoice |
Dave's row is the one to stare at. Nobody decided that an editor in workspace B should see workspace-A's public invoices; no meeting approved cross-workspace public reads. It fell out of status = 'public' being evaluated globally the day that policy shipped. Sprawl's signature is decisions like this one — real grants nobody chose, discoverable only by computing the union.
The table also shows Bob and Carol: two policies, two roles, zero difference in effect. One of those policies is dead weight in the union — exactly the redundancy consolidation exists to remove.
A trap worth naming while we're here, because it is the most common way sprawl hides dead policies: those membership-check policies only work because members carries its own select policy letting users read their own rows. Subqueries inside a policy are evaluated under the caller's policies for the referenced table — if members were locked down with RLS and no policy yet (the default-deny interim state from the default-open window), every editor/viewer EXISTS branch would silently evaluate false forever, and the table would behave as owner-plus-public only. Nothing errors; the policies just never fire. When consolidating, this is why step 1 measures behavior instead of reading clauses: dead branches and live branches look identical in the catalog.
"Contradicting" policies can't — that's the problem
Teams describing their sprawled tables often say the policies "contradict." For permissive policies they cannot: contradiction requires AND semantics, and permissive accumulation has none. Two policies saying "only owners" and "only editors" don't conflict — they merge into "owners or editors," which may be wider than either author intended but is never narrower than both.
Three genuinely different shapes hide inside most so-called contradictions:
| Shape | Mechanics | Correct reading |
|---|---|---|
| Permissive + permissive | OR union | Widest wins, per row |
| Permissive + restrictive | Restrictive intersects the union | The only narrowing tool |
| Write policy missing WITH CHECK | Falls back to USING (or unconstrained) | Read logic silently governs writes |
The third row is where "contradiction" reports usually originate: a select policy carefully scoping rows while the insert path admits anything. That is not contradictory policy — it is a missing half, catalogued in the missing-WITH-CHECK rule, and consolidation must address it separately rather than assuming tidier selects fix writes.
Finding your own sprawl
Two catalog queries locate the work. First, the volume scan — which tables carry competing policy sets:
select tablename, cmd, count(*) as policy_count
from pg_policies
where schemaname = 'public'
group by tablename, cmd
having count(*) >= 3
order by policy_count desc;
Every row is a command surface whose effective predicate is an OR-chain at least that long. Second, annotate what remains: PostgreSQL supports commenting policies directly, and the comments survive in the catalog for the next reader —
comment on policy "invoices_select_owner" on invoices
is 'Owners read their own invoices';
select p.polname, d.description
from pg_policy p
left join pg_description d on d.objoid = p.oid
where p.polname = 'invoices_select_owner';
(Both statements executed during this article's preparation, as shown.) Uncommented policies in a count-≥3 table are, by definition of the situation, policies nobody can explain — the first candidates for consolidation.
Reading a catalog dump the way the database will
The detector query finds where to look; pg_policies shows what you're dealing with. Here is the actual dump for the fixture's invoices table, exactly as executed, before consolidation:
tablename | policyname | permissive | cmd | qual
----------+-----------------------------------+------------+--------+--------------------------------------------------
invoices | invoices_select_owner | PERMISSIVE | SELECT | (( SELECT auth.uid() AS uid) = owner_id)
invoices | invoices_select_public_status | PERMISSIVE | SELECT | (status = 'public'::text)
invoices | invoices_select_workspace_editors | PERMISSIVE | SELECT | EXISTS( SELECT 1 FROM members m WHERE ...)
invoices | invoices_select_workspace_viewers | PERMISSIVE | SELECT | EXISTS( SELECT 1 FROM members m WHERE ...)
Reading it column by column, with the evaluation model open:
- permissive — every row here says PERMISSIVE, so all four quals join one big OR. This is the column that tells you the table's effective predicate is "row 1 OR row 2 OR row 3 OR row 4."
- cmd — all four say SELECT, confirming they compete in the same union rather than governing different operations.
- roles (not shown,
{authenticated}throughout) — same audience; nothing here differs by role. - qual — read each expression as a standalone grant: "this condition alone admits these rows." The two EXISTS rows deserve special scrutiny: identical shape, differing only in the role literal — textbook redundancy, and after measuring, provably so.
Forty policies read this way stop being intimidating and become arithmetic. The dump is long; the logic is four columns wide.
The consolidation method, verified end to end
The goal: fewer policies, identical effective access. Never "tidier rules that mostly match" — the whole value of this exercise is that access need not change for clarity to improve.
Step 1 — capture current truth. For each persona you can name (owner, editor, viewer, outsider, anonymous), record the exact rows visible today. The impersonation harness turns this into four queries and a results table. Do not skip to reading policies and inferring — the point of the exercise is that inference failed somewhere already.
For the fixture above, the recorded baseline was exactly the matrix just shown: Alice 2, Bob 2, Carol 2, Dave 2 — with Dave's two including the cross-workspace public read nobody had consciously approved. (If your own baseline surprises you here, that surprise is the finding; consolidate from measured reality.)
Step 2 — classify each policy into the target shape. Most sprawl collapses into one select policy with OR'd legitimate conditions, plus optional restrictive gates for hard limits:
drop policy "invoices_select_owner" on invoices;
drop policy "invoices_select_public_status" on invoices;
drop policy "invoices_select_workspace_editors" on invoices;
drop policy "invoices_select_workspace_viewers" on invoices;
create policy "invoices_select"
on invoices for select to authenticated
using (
(select auth.uid()) = owner_id
or status = 'public'
or exists (
select 1 from members m
where m.workspace_id = invoices.workspace_id
and m.user_id = (select auth.uid())
and m.member_role in ('viewer', 'editor')
)
);
Notice the honest trade visible in this block: viewers and editors merge into one membership branch (in ('viewer','editor')) because step 1 proved they saw the same rows. Where roles differ in effect, they stay separate branches. Consolidation compresses proven-redundant logic, never distinct behavior.
Step 3 — re-measure and diff. Re-run the identical persona harness and compare row sets, not counts alone. As executed against this fixture, the before/after comparison across all four personas matched exactly — the verification query returned zero differences:
-- `matrix_results` is the harness's own results table from step 1 —
-- (label text, persona text, invoice_id uuid) — captured once before
-- consolidation as 'before' and once after as 'after'.
-- Rows granted before but not after (must be empty),
-- and symmetrically, after-but-not-before:
select persona, invoice_id from matrix_results where label = 'before'
except
select persona, invoice_id from matrix_results where label = 'after';
Empty result in both directions: consolidation complete, access provably unchanged, policy count reduced from four to one. The write side gets the same treatment — one insert policy, one update policy with both clauses, one delete — until each command has exactly one definition whose name matches its behavior.
Two practical notes from running this on real-shaped schemas. First, keep the before/after captures in the PR itself: reviewers can see the equivalence proof rather than trusting your claim of it, which converts the most contentious part of the change into the least discussed. Second, expect the diff to occasionally reveal a live grant nobody remembered writing — the baseline step measures reality, and reality sometimes includes an audience branch added during an incident two years ago. Surface those in the PR description rather than silently preserving them; consolidation is the cheapest moment your team will ever have to re-decide them.
Step 4 — leave a comment. One line on each surviving policy stating the access model in words, using the comment on policy syntax shown above. The next sprint's hotfix author reads comments before catalogs.
Keeping the floor clean
Consolidation without guardrails regrows. Three habits hold the line:
- Naming as contract:
<table>_<command>or<table>_<command>_<audience>. A name that can't be spoken as a sentence signals a policy that won't survive review. - The count query in CI: rerun the detector query post-migration; a rising count requires a comment justifying why the union needed widening.
- Deletion in pairs: any feature removal PR deletes its policies the same week. Policies outliving their feature are pure attack surface with documentation debt attached.
And the review questions each surviving policy should answer in one glance, from its name and comment alone: which table, which command, which audience, granting what? Any policy requiring a git archaeology session to explain has already failed the next reviewer — and sprawl is, more than anything else, the accumulated interest on reviews skipped.
For teams who want the accumulation monitored rather than audited — new policies flagged as they appear, drift caught between deploys — that is continuous-monitoring territory, and the drift alerts reference describes exactly what transitions get surfaced.
Common questions
Should restrictive policies be consolidated too?
Yes, but separately — they AND together rather than OR, so multiple restrictive policies multiply conditions instead of widening them. Consolidate restrictive policies only when their conditions are genuinely redundant; when two restrictive gates express different hard limits, keeping both is correct, and merging them would loosen whichever limit disappeared.
Is one policy per command always the right target?
It is the right default, not a law. Some models genuinely need several audiences with disjoint logic — keep them, named clearly, now knowing each contributes its full width to the union. The failure mode isn't multiplicity; it is unexamined multiplicity.
Can consolidating break performance?
Usually improves it: one OR-chain plans once, and branches keep their supporting indexes (BitmapOr handles the union). But re-run your EXPLAIN checks after — consolidation occasionally merges predicates in ways that deserve fresh statistics, per the indexing guide's plan shapes.
How do I handle policies targeting different roles?
Consolidate within a role/command pair. An anon select policy and an authenticated select policy never merge — they serve different callers by design. Group by (tablename, cmd, roles) first; sprawl lives within groups.
Should old policies just be deleted if I can't find their owner?
Measure, then decide. Run the persona harness without that policy present (in staging): if some persona loses rows, the policy is load-bearing — find or invent its owner and document it. If nothing changes, delete with confidence and note the removal in the migration message.
Curious how many policies your tables are carrying? Run the free scan — paste your app URL and see per-table findings, including permissive accumulation, with proposed remediation SQL to review.
RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.