From weekend prototype to production: hardening a Supabase app

The gap between a working prototype and a defensible product is a checklist, not a rewrite. This article gives founders and developers that checklist in order - with every row-level-security step shown as complete SQL - so nothing gets skipped because nobody knew it existed.

Prototypes are honest about features and silent about posture. Your app creates accounts, saves data, renders dashboards — and none of that says anything about what happens when someone other than a happy user connects. Production readiness, for the database layer, means being able to answer three questions without hedging: who can read each table, who can write into whose rows, and how you know both answers are still true after the next deploy.

This article is that answer, sequenced. Each stage builds on the last; every SQL block runs as printed (each was executed during preparation); and the whole sequence fits inside a focused day for a small app. It assumes the prototype already works — auth flows included — because hardening a moving feature set wastes effort; freeze features for the day if you can. The companion piece on prototype-to-production specifics covers platform concerns beyond authorization; here we go deep on the layer that protects your users from each other.

Stage zero: define what you're protecting

Before running queries, spend ten minutes writing down the access model in plain sentences. Per table: who reads it? Who creates rows, and can they only create their own? Who updates or deletes, and under what limits? Which tables are legitimately public, and with what filters?

This sounds bureaucratic. It isn't — it's the spec your policies will implement, and having it written turns later stages from judgment calls into conformance checks. A three-table notes app needs three sentences. A marketplace needs a page. Either way, write it before touching the schema, because the most common hardening failure isn't technical at all: it's discovering mid-migration that nobody ever decided whether collaborators should see drafts.

The sentences also become your review artifacts. When a stakeholder asks "can users see each other's data?", the answer stops being a shrug and becomes a quote from a document — plus, eventually, a pointer at passing tests. Founders who write this page once reuse it in security questionnaires, enterprise sales calls, and their own 2 a.m. incident triage; the words cost minutes and keep paying.

Stage one: inventory and triage

Now measure reality against the model you just wrote. The catalog produces the gap analysis:

select c.relname,
       c.relrowsecurity as rls_enabled,
       count(p.policyname) as policy_count
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_policies p
       on p.schemaname = n.nspname and p.tablename = c.relname
where n.nspname = 'public' and c.relkind = 'r'
group by c.relname, c.relrowsecurity
order by c.relname;

Sort the output into three buckets by risk rather than alphabetically:

Prototype projects typically land almost entirely in the second bucket, sometimes with a couple of open windows where an assistant-generated migration skipped protection. Nothing here requires blame; the catalog doesn't do blame, it does state.

Triage discipline for this stage: don't fix while triaging. The temptation is to enable RLS on the first open table you see — but enabling before its policies exist breaks whatever feature uses it, mid-inventory, and now you're debugging your own hardening. Complete the three-bucket sort first; then work buckets in order with the full picture in hand. Open windows do jump the queue, but they jump into stage two's process rather than skipping it.

Stage two: write the policy set, completely

For each user-owned table, this is the full pattern — four policies covering all four commands, both clauses wherever both apply. The blocks from here on run against the prototype's two working tables; if you are following along in a scratch database rather than your own project, create them first:

create table documents (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null,
  title text not null,
  owner_id uuid not null
);

create table workspace_members (
  workspace_id uuid not null,
  user_id uuid not null,
  primary key (workspace_id, user_id)
);

alter table documents enable row level security;

create policy "documents_select"
  on documents for select
  to authenticated
  using ((select auth.uid()) = owner_id);

create policy "documents_insert"
  on documents for insert
  to authenticated
  with check ((select auth.uid()) = owner_id);

create policy "documents_update"
  on documents for update
  to authenticated
  using ((select auth.uid()) = owner_id)
  with check ((select auth.uid()) = owner_id);

create policy "documents_delete"
  on documents for delete
  to authenticated
  using ((select auth.uid()) = owner_id);

Details worth understanding rather than copying blindly:

Both halves of update matter. USING decides which existing rows may change; WITH CHECK decides whether the resulting row stays legal. With only USING, users reassign ownership — moving rows out of their scope into someone else's account — in one successful statement. The check clause closes exactly that door.

Insert has no USING. There's no prior row to test, so the entire contract lives in WITH CHECK. Requiring the resulting owner_id to match the caller's verified identity means users cannot plant rows attributed to others, even ones they'll never be able to read afterward.

A naming convention pays for itself immediately: <table>_<command> (or with an audience suffix when several policies legitimately coexist) makes every future catalog dump self-describing, and mismatched names become visible drift signals. Add a one-line comment on policy statement per policy capturing the intent sentence from stage zero — the next reviewer, including you in six months, reads comments before clauses.

Tables shared across a workspace extend the select branch with a membership subquery instead of replacing ownership. The composite reads like this:

-- Widen the existing policy in place. ALTER, not DROP-then-CREATE:
-- dropping would leave the table with no select policy for a moment.
alter policy "documents_select"
  on documents
  using (
    (select auth.uid()) = owner_id
    or exists (
      select 1 from workspace_members m
      where m.workspace_id = documents.workspace_id
        and m.user_id = (select auth.uid())
    )
  );

alter table workspace_members enable row level security;

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

Ownership still admits rows directly; membership adds the sharing dimension on top. The membership table needs its own read policy — the second statement above — so users can see their own memberships without seeing everyone's. That dependency matters more than it looks, because subqueries evaluate under the caller's policies too: a members table with RLS enabled and no select policy silently empties every EXISTS branch built on it, with no error anywhere. Tables that are genuinely public get a deliberate anon-targeted policy with its filters, never an accident of omission; anonymous access done deliberately covers that design in full.

Stage three: performance hygiene while you're in there

Policies run on every query forever; make them cheap once:

create index documents_owner_id_idx on documents (owner_id);

Index every column a policy compares against — owners, tenants, workspace keys. Wrap identity calls as (select auth.uid()) so Postgres evaluates them once per statement instead of per row; the plan-level difference is documented in our performance guide. If any table carries restrictive gates, confirm they reference indexed columns too. Fifteen minutes here prevents the classic arc where adding RLS quietly degrades every list view until somebody blames the framework.

The habit generalizes: policy columns deserve the same indexing attention as foreign keys, because they are foreign keys in disguise — predicates matching user identifiers against stored columns on every single query. Where one index serves both a foreign key and its policy, that's one index doing two jobs; where they diverge, add what's missing and let EXPLAIN arbitrate.

Also decide now about FORCE ROW LEVEL SECURITY: enabling it subjects the table owner to policies as well, which matters when backend scripts share the owner role with humans doing dashboard surgery. For tables where even privileged mistakes must respect isolation, add the line — it costs nothing and removes a standing exception you'd otherwise have to remember.

Verification for this stage is one query per sensitive table, run as a test persona:

begin;
set local role authenticated;
select set_config('request.jwt.claims', '{"sub":"11111111-1111-1111-1111-111111111111"}', true);
explain (costs off)
  select * from documents where workspace_id = 'aaaaaaaa-0000-0000-0000-00000000000a';
rollback;

Reading the plan: you want the policy predicate to appear in an Index Cond (policy served by index), not merely a Filter after a sequential scan. If the JWT-derived value appears inline in a per-row Filter, the (select ...) wrapper is missing; if there's no index candidate at all, stage three isn't done. Two minutes of plan-reading per table now buys years of flat latency later.

Stage four: storage and server paths

Two surfaces outside table RLS routinely undo an otherwise hardened app:

Buckets. Every bucket gets an explicit visibility decision and, for private buckets, object-level policies scoped by path. The standard own-folder pattern keeps users inside a prefix derived from their identity:

create policy "avatars_read"
  on storage.objects for select
  to authenticated
  using (bucket_id = 'avatars');

create policy "avatars_upload_own_folder"
  on storage.objects for insert
  to authenticated
  with check (
    bucket_id = 'avatars'
    and (storage.foldername(name))[1] = (select auth.uid())::text
  );

Uploads into another user's folder now reject at the policy layer — verified behavior, and the same WITH CHECK thinking from table land applied to object paths. Public buckets are legitimate for genuinely public assets; audit them with the same suspicion as public tables, because public-bucket exposure is served without authentication by definition.

Service credentials. Inventory every place the service-role key exists: environment files, function configs, build artifacts. It belongs exclusively behind server boundaries, and its call sites should fit on one screen with justifications attached. If grep finds it anywhere client-reachable, that finding outranks everything else on this page — rotation guidance is in service-role leakage.

Run the inventory against built output, not just source: framework env prefixes, bundler plugins, and copied snippets have all shipped credentials that never appeared in a repository. The check is two greps and takes less time than explaining its absence afterward.

Stage five: prove it

Claims become claims-with-evidence through the two-account battery: anonymous reads return empty; forged inserts reject; cross-tenant reads return nothing; ownership transfers fail; deletes stay in scope. All five are copy-pasteable in the tenant-isolation playbook, with expected results stated per probe. Run them against staging first, then production — environments drift independently, and production is where the proof matters.

This is the stage most easily talked out of, because verification feels redundant when everything was written carefully an hour ago. That feeling is the point of failure: transcription errors, forgotten tables, and misread requirements survive careful work precisely because care does not check itself. Twenty minutes of battery beats hours of post-launch forensics on the one occasion it finds something.

Score the run as a table, because a table converts anxiety into worklist:

Every failure names its fix; every pass earns a line in your launch document. Convert every fix made during stages two through four into a denial test in your suite. Hardening that isn't regression-tested decays on the next busy sprint; tests make the posture survive its authors.

The launch-week trap: changes that bypass the checklist

Hardened apps regress through unchecklisted channels. Knowing them in advance is most of the defense:

• Hotfix branches skip review under time pressure and ship schema changes directly — often with "temporary" policy relaxations nobody reverts.
• Dashboard edits change policies from the SQL editor without touching git, so the repo's migrations no longer describe production.
• Restores and environment copies replace current state with whatever existed at snapshot time, including pre-hardening protection.
• "Quick" data fixes by teammates connecting as the owner role quietly establish a habit of bypass-grade access.

Each channel has the same signature: production state diverges from what stages two through five verified, with nothing in CI to notice. That's why the final stage isn't optional even though it comes after "prove it" — proof has a timestamp, and launch week is precisely when timestamps start expiring fastest.

Stage six: keep it true

Production readiness is a property maintained over time, not achieved once. Three habits carry it forward: the catalog snapshot joins CI so unprotected tables fail builds; policy changes require the same review as code changes; and monitoring watches between deploys for the changes that bypass pipelines entirely — restores, manual edits, hotfix branches.

Concretely, continuous coverage means knowing about four transition classes without anyone having to remember to look: RLS flags flipping on or off; policies appearing, disappearing, or widening (a new permissive policy is a union expansion); write policies losing their checks; and key material appearing where it doesn't belong. Human review catches these when it happens to be looking; automation catches them when they happen. RowShield exists to automate that class of watching, beginning with a free scan that establishes your baseline in minutes.

Ship when all six stages have answers written down. That document — brief, concrete, evidence-linked — is the real deliverable of hardening: not the absence of risk, but the presence of knowledge about which risks you've accepted and which you've closed.

Common questions

How long does this take for a small app?

A three-to-eight-table project typically completes all stages in a day: the inventory in minutes, policy writing in a couple of hours, tests and storage another hour or two. Larger schemas scale mostly in stage two, since the per-table pattern repeats.

Can I ship with some tables still locked-but-policyless?

Yes — enabled with zero policies is safe default-deny, appropriate for backend-only tables awaiting rules. Ship it deliberately and track it; the failure mode is months of interim becoming permanent, which is why the inventory belongs in CI rather than memory.

Do I need to redo this after every feature?

No — you need the inventory after every feature. New tables enter through the same pipeline (enable, write four policies, index, test), which takes minutes per table when it's habitual. The heavy work was making the pipeline exist; afterwards it's maintenance.

What's the difference between this and a security audit?

Scope and independence. This checklist covers your authorization layer self-serve; an audit adds external perspective, compliance framing, and breadth across infrastructure. They complement each other — teams that run this sequence arrive at audits with evidence organized and low-severity noise already cleared, which shortens both.

We use an ORM that generates policies too. Covered?

Partially. Generated policies deserve identical review — same tautology and missing-check risks, per the vibe-coded posture piece — but generation plus verification is strictly better than generation alone. The checklist above is the verification side.

---

Establish your baseline in minutes: run the free scan — paste your app URL and see exactly which stage your project is really at, findings included.

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