Writing your first policy set: one schema, end to end

Policy tutorials usually show fragments. This one builds an entire authorization layer for a realistic small app - a recipe-sharing site - from empty schema through tested, indexed, named policies, with every statement runnable and every result stated before you run it.

The first policy set you write teaches you more than any documentation read passively, but only if it's complete enough to include the boring parts: indexes, naming, verification, the public/private boundary. Fragments leave those out, so beginners assemble their first real project from pieces that never covered them — and the gaps surface later as findings.

Here is the whole thing, start to finish, for a deliberately realistic app: RecipeBox — anyone can browse recipes; signed-in members publish recipes and keep private meal plans. That's the entire product spec, and it contains every authorization shape a small app actually needs: deliberate public reads, user-owned rows, and writes constrained against forgery. Every block runs as printed against current Postgres/Supabase — all were executed during this article's preparation, with results stated before each. Work through it in order; each step's output is the next step's input, and by the end you'll have a reference implementation to copy from for every future table.

The app we're building policies for

Two sentences of access model, written down first because policies implement documents like this:

• Recipes are publicly readable by everyone, including logged-out visitors. Only the author may modify or delete their own recipes.
• Meal plans are fully private per user. Nobody sees anyone else's plan, ever.

Notice what's absent: no admin roles yet, no teams, no moderation queue. First policy sets should match current reality plus obvious near-future — patterns generalize later (multi-tenant extensions when RecipeBox grows workspaces).

Also notice what the two sentences contain that SQL never will: intent. Every clause below traces to one of these lines, which means every future "should users see X?" question has an authoritative answer to check against. When the product grows and the answers change, you update the sentence first, then the policies — keeping documentation and enforcement synchronized instead of letting them drift into contradiction.

Step one: tables with RLS enabled from birth

Protection ships with the table, not after it:

create table recipes (
  id uuid primary key default gen_random_uuid(),
  author_id uuid not null,
  title text not null,
  instructions text not null,
  is_published boolean not null default false,
  created_at timestamptz not null default now()
);

alter table recipes enable row level security;

create table meal_plans (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null,
  week_start date not null,
  plan jsonb not null default '{}',
  unique (user_id, week_start)
);

alter table meal_plans enable row level security;

Both tables are now deny-all for API callers — nothing is readable or writable until policies say so. That interim silence is safe to ship behind; features will announce which policies they need by failing loudly in development.

It's worth pausing on how different this default feels from other stacks, where data is world-readable unless you build protection. Postgres inverts it: once the flag is on, absence of policies means absence of access. The evaluation model reference covers the mechanics; the practical consequence here is ordering freedom — you can enable RLS the moment tables exist and write policies at whatever pace features demand, without ever passing through an exposed state. The only mistake available at this step is skipping the alter table lines, which is why they sit inside the same migration as table creation rather than "later."

Step two: public reads done deliberately

Recipes are meant to be public — but published recipes, not drafts. The distinction costs one clause:

create policy "recipes_public_read"
  on recipes for select
  to anon, authenticated
  using (
    is_published = true
    or author_id = (select auth.uid())
  );

Read this as Postgres will evaluate it: any caller, anonymous or signed in, may see a recipe if it's published or they're the author. Authors see their own drafts; the world sees finished work. One policy, both audiences targeted explicitly, no reliance on defaults.

The to anon, authenticated line matters as much as the expression. Policies apply only to listed roles, and listing both means logged-out visitors (acting as anon) and members (acting as authenticated) share one read rule. Omitting anon here would leave logged-out browsers with empty pages — the classic first-set bug, since everything works while you're signed in during development.

Also internalize what "public" costs: your anon key ships in every browser bundle by design (public by design), so this policy is a promise to the entire internet. Published recipes are exactly that; drafts are excluded by the very same clause. Deliberate public access looks like this — narrow, filtered, written down.

Verification before moving on:

begin;
set local role anon;
insert into recipes (author_id, title, instructions, is_published)
values ('11111111-1111-1111-1111-111111111111', 'test', 'x', true);
rollback;

Expected: rejection — new row violates row-level security policy. Public read does not mean anonymous write. This single check catches the most common first-policy-set mistake before it ships.

The error message itself is worth reading twice, because it's Postgres telling you which layer refused: a WITH CHECK clause evaluated your attempted row and rejected it. Nothing was partially written, nothing needs cleanup — the database is transactional about authorization the way it is about everything else.

Step three: ownership for personal data

Meal plans are the simplest possible case: each row belongs to exactly one user:

create policy "meal_plans_select_own"
  on meal_plans for select
  to authenticated
  using ((select auth.uid()) = user_id);

create policy "meal_plans_insert_own"
  on meal_plans for insert
  to authenticated
  with check ((select auth.uid()) = user_id);

create policy "meal_plans_update_own"
  on meal_plans for update
  to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

create policy "meal_plans_delete_own"
  on meal_plans for delete
  to authenticated
  using ((select auth.uid()) = user_id);

Four policies, one per command, all comparing against (select auth.uid()) — the server-verified identity from the request's token. Three details worth internalizing rather than copying:

The (select ...) wrapper is performance hygiene, evaluated once per statement instead of per row; identical values, better plans (the mechanics).

Update carries both clauses. USING admits only your own rows as targets; WITH CHECK requires the result to still be yours. With just the first half, a user could reassign their plan to someone else — harmless here, catastrophic when the same shape governs invoices.

Insert has only WITH CHECK. No prior row exists to test, so the write contract lives entirely in the check clause. Forgetting it is the classic first-set mistake.

And one habit worth forming now: write the four policies in command order — select, insert, update, delete — every time. Reviewers learn your rhythm; missing commands become visible gaps rather than assumptions. A table whose delete policy is genuinely unnecessary (append-only audit data) should say so in a comment rather than by absence, because absence reads identically to oversight in any catalog dump.

Step four: writes that can't be forged

Recipes need author-scoped writes mirroring the meal-plan set, with one twist — publishing is also an author action, so the published flag needs no special handling beyond staying inside author-owned updates:

create policy "recipes_insert"
  on recipes for insert
  to authenticated
  with check (author_id = (select auth.uid()));

create policy "recipes_update"
  on recipes for update
  to authenticated
  using (author_id = (select auth.uid()))
  with check (author_id = (select auth.uid()));

create policy "recipes_delete"
  on recipes for delete
  to authenticated
  using (author_id = (select auth.uid()));

Now attempt the forgery — Alice trying to publish a recipe attributed to Bob:

begin;
set local role authenticated;
select set_config('request.jwt.claims',
  '{"sub":"11111111-1111-1111-1111-111111111111"}', true);
insert into recipes (author_id, title, instructions)
values ('22222222-2222-2222-2222-222222222222', 'stolen', 'nope');
rollback;

Expected: rejected. The impersonation harness (explained fully in our testing guide) proves the database enforces authorship regardless of what any client sends — which is the property that makes everything else about the app trustworthy.

That rejection deserves one more moment of attention, because its shape teaches the model. The error names row-level security, not a missing permission or syntax problem — meaning the statement reached Postgres fine and the write contract refused it specifically. Compare that to what happens with a malformed payload (different error, earlier layer) or a revoked grant (permission denied before policies even evaluate). Learning these layers' distinct voices turns future debugging from guesswork into triage: each error message points at exactly one gate in the chain.

Step five: indexes and naming

Every policy predicate column gets an index; every policy gets its name as documentation:

create index recipes_author_id_idx on recipes (author_id);
create index recipes_is_published_idx on recipes (is_published);
create index meal_plans_user_id_idx on meal_plans (user_id);

comment on policy "recipes_public_read" on recipes is
  'Public app: everyone reads published; authors also read drafts';

The indexes serve double duty — policy predicates and ordinary queries (WHERE author_id = ...) share them. The comment becomes visible in catalog dumps, teaching future maintainers (and future you) the intent without archaeology. Names already follow <table>_<audience/command> convention throughout; consistency there makes every future catalog dump self-explaining.

Skip any of this and the cost arrives quietly. Unindexed predicate columns turn every policy-filtered query into a scan as tables grow — the app stays correct but gets slower, and nobody connects the slowdown to a missing index on author_id. Inconsistent names leave future-you reading raw expressions to understand intent. Neither breaks anything today, which is precisely why first sets should include them: habits formed under zero pressure survive the pressure that comes later.

One more catalog habit completes the step: read your own set back the way a reviewer will.

select tablename, policyname, cmd, with_check is not null as has_write_check
from pg_policies
where schemaname = 'public'
order by tablename, cmd;

Six policies across two tables, every write row showing true in the check column. Reading your own set through the catalog — rather than trusting the migration file — catches typos in names, policies attached to unexpected tables, and anything that failed silently. Thirty seconds per migration — and anything the catalog lists that no line of the access model asked for is a drift candidate worth investigating before it becomes precedent.

Step six: prove it works

The full battery, two personas plus anonymous, expected results stated first:

Each line is one impersonation query following the harness pattern from steps two and four. Eight green checks and RecipeBox's authorization layer is not just written but demonstrated — the difference between "should be secure" and "is proven isolated," which is precisely the gap between prototype and production posture.

Save the battery as a file next to your migrations. Running it after every schema change takes two minutes; running it in CI turns it into a regression gate that outlives everyone's memory of why each policy exists. When a future feature breaks one of these checks — and eventually one will — the failing line names both the table and the broken promise, which is most of the debugging done before it starts. The battery also doubles as documentation for new contributors: eight lines stating exactly what this app's authorization promises, executable on arrival.

When features grow — favorites, comments, collections — each new table repeats this exact loop: enable at creation, write the four-command set (or deliberate subset), index predicates, name consistently, add the new probes. Only one question is genuinely new each time: whose row is it? A rating or a favorite belongs to its author and repeats the ownership shape directly; a comment or a photo whose visibility inherits from the parent recipe joins that parent's rule inside its select policy. The schema grows; the three questions stay the same — who reads, who writes, what constrains writes. First sets are hard because everything is new; second sets are fast because the template exists.

Common questions

Why four separate policies instead of one FOR ALL?

One FOR ALL policy works mechanically but couples four decisions into one expression, and forgetting WITH CHECK under it silently leaves writes leaning on read logic. Separate per-command policies make each contract explicit and reviewable independently — worth the extra ten lines.

Should authors be able to unpublish? Where's that?

They already can: an update flipping is_published = false passes the author-owned update policy. The public-read policy then stops serving the row immediately. Notice how the capability needed no additional code — the model composes — which is exactly what a good policy set feels like.

Is this pattern enough for production?

For a single-user-per-row model, yes — this is production-grade, provided stage-six tests run in CI rather than once. Multi-team sharing adds membership branches; regulated data adds audit trails; neither changes the foundation shown here, which is why learning it completely pays forward.

What about the auth.users table — do I need policies there?

No. The auth schema is managed by Supabase and not exposed through the API to clients; user profile data you want readable belongs in your own profiles table with its own policies (typically select for everyone or for authenticated, update own). Treat auth internals as platform territory — reference users by id in your policies, never extend them.

How do I handle a feature that doesn't fit any pattern here?

Write down the access sentence first and work backward to clauses. "Only contributors see pending submissions" becomes an EXISTS branch over a contributors table inside a select policy. "Anyone with the link can view" becomes a share-token column compared against a request parameter — carefully, since that trades identity for capability. When no clause shape feels right, that's a signal the data model wants another table or column, not a more exotic policy.

---

Check your own project's policy inventory against this reference implementation: run the free scan — paste your app URL and compare, finding by finding.

RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.