Reviewing the schema you didn't write

Inherited or AI-generated schemas arrive looking finished, which makes reviewing them harder, not easier. This article gives developers an ordered method: read the catalog first, distrust four specific shapes, check the indirect surfaces, then test behavior before signing off.

Sooner or later every developer inherits a database they didn't design: a project generated with AI assistance, a codebase from a departed contractor, the agency handover, the open-source backend you cloned at midnight. The schema works — that's established. What's unknown is what it permits, and unlike bugs, permission problems don't surface through normal use. Nobody files a ticket saying "your table returned my neighbor's rows too efficiently."

The method below turns an afternoon into a defensible verdict on any Supabase schema you didn't write. It's ordered deliberately: catalog before code, structure before behavior, because each stage tells you where the next stage needs to look harder — and it ends with the questions only the product's owner can answer, which is the part no tool can do for you. Everything cited runs against current Postgres; the queries were executed during this article's preparation.

Start from the catalog, not the code

Application code lies about databases by omission — ORMs show entities, not policies; migrations folders show intent, not current state. The Postgres catalog shows what actually exists, and three queries produce the complete protection inventory:

Every public table with its RLS flag:

select c.relname,
       c.relrowsecurity as rls_enabled,
       c.relforcerowsecurity as force_rls
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
order by c.relname;

Every policy, with its full definition:

select tablename, policyname, permissive, roles, cmd, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, cmd, policyname;

Every security-definer function — the privilege elevators:

select p.proname, p.proconfig
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef;

Run all three before reading a single line of application code. The output reorders your attention immediately: tables with rls_enabled = false outrank everything else (that window is live now); write policies with null with_check come next; and the function list tells you whether any code path can bypass row security entirely. This triage takes minutes and converts "audit this project" from an emotion into a worklist.

Two companion inventories complete the picture. First, grants — which roles hold which privileges on your tables:

select table_name, privilege_type
from information_schema.role_table_grants
where grantee = 'authenticated' and table_schema = 'public'
order by table_name;

Grants and policies are separate gates — both must pass — so a table with broad grants and no RLS flag is exposed through exactly one missing layer. Second, if the project uses Storage, list buckets with their visibility flags; a public bucket is exposure regardless of how careful every table policy is, because objects in it are served without auth checks.

The point of inventorying before reading: schemas hide their own structure. A migrations folder shows what changed when; an ORM shows what the app touches. Neither shows the union of what exists. The catalog does, and ten minutes of queries replaces hours of code archaeology that still might miss a table nobody's code references anymore.

Read policies as a reviewer

With the dump in hand, resist the instinct to evaluate policies one at a time. Policies are not independent — permissive ones combine with OR — so the unit of review is the (table, command) pair, with all its same-command policies merged into one expression:

visible = P1 OR P2 OR ... AND restrictive gates
writable = WITH CHECK clauses, evaluated on new row state

Concretely, for each table ask three questions in order:

1. What does the union admit? For each permissive policy, mentally tag the rows it alone would admit. Any policy whose condition is broader than intended widens everyone's access regardless of how narrow its siblings are.
2. What constrains writes? Every INSERT and UPDATE policy must carry a WITH CHECK; if the column is null in your dump, that path accepts whatever shape arrives. On updates, confirm both halves exist — USING guards the target row, CHECK guards the result.
3. What did the author compare against? Identity checks should reference (select auth.uid()) — a server-verified value. A policy comparing against a plain column or a request parameter delegates trust to whoever forms the query.

This reading habit catches most defects without running anything. It also produces better questions than "is this secure?" — instead: "which policy admits rows I didn't intend?" and "what happens when this condition is null?" Those have answers; "secure" doesn't.

Distrust these specific shapes

Four patterns account for the overwhelming majority of defects in generated or hastily-written schemas. Each has a signature visible right in the catalog dump:

The last one deserves unpacking because it's the least intuitive. A SECURITY DEFINER function executes as its owner — typically the powerful role that owns your tables. If its body references documents unqualified and someone can influence the session's search_path, the name can resolve to an object they control. The standard hardening pins the path:

-- `public.documents` here stands for whichever table the function
-- you are reviewing actually reads; the point is the qualification.
create function public_count()
returns bigint
language sql
security definer
set search_path = ''
as $$
  select count(*) from public.documents;
$$;

Note the interaction, verified empirically while preparing this article: pinning search_path = '' forces every relation in the body to be schema-qualified — creation fails otherwise, which is the discipline working as designed. When you see a definer function with no proconfig entry at all, add it to your findings with priority: it is both a hijack surface and a sign the author hadn't considered execution context.

Walking the table row by row as review notes: a tautology finding usually traces to debugging leftovers or "make it work" prompts — check git history if you want confirmation, but fix regardless of origin story. A null with_check on an update policy is subtler than its insert cousin: reads may be perfectly scoped while updates let rows leave their scope, so test the transfer case explicitly. Bare auth.uid() calls are performance findings rather than security ones — correct today, degraded at scale — and worth batching into their own cleanup PR. And each unhygienic definer function needs both the path pinned and an intent question answered: what is this function for, and does it need definer rights at all? Half of them don't; they inherited SECURITY DEFINER from a template.

Views and helpers: the indirect surfaces

Direct table access isn't the only path data takes to clients. Two indirections routinely bypass careful table-level review:

Views. By default a Postgres view executes with its owner's privileges — historically meaning views silently bypassed caller policies entirely. Modern Postgres supports opt-in invoker semantics, and the difference is observable: in our verification, a default view over a protected table returned both fixture rows to a restricted caller while the security_invoker variant returned only the permitted one. Check what your schema ships:

select c.relname, c.reloptions
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'v';

Views without security_invoker=true in their options deserve a finding — either fix them or document why owner-rights exposure is intended. The mechanics are in the evaluation model reference.

Definer functions as API surface. Every function from the L1 inventory is callable by whichever roles hold execute rights — often everyone. For each, establish: what does it expose beyond policy scope, who can call it, and does it validate inputs before touching privileged paths? An unprotected definer function is a policy-shaped hole exactly the size of its own logic.

Both surfaces share a property that makes them easy to forget during review: they appear in application code as ordinary queries. Nothing flags "this SELECT went through a view with elevated privileges." Only the catalog reveals the elevation, which is why this stage follows the catalog stage, not replaces it.

Then test behavior

Reading finds structural defects; testing confirms outcomes. The full methodology lives in our testing guide, so here's just the minimum pass for an inherited schema:

1. Impersonate each role the app uses (anon, authenticated) with crafted claims, and record what each can see on sensitive tables.
2. Run the five adversarial probes between two accounts — anonymous read, forged write, cross-tenant read, ownership transfer, silent delete. Each maps to one clause and names its own fix.
3. Call the definer functions as a restricted user and check whether results exceed that user's policy-scoped access. If calling public_count() as anon returns numbers anon cannot derive through policies, understand why before accepting it.

Record expected results before each probe — literally write down "expect empty" or "expect Alice's two rows" — then compare. The discipline sounds pedantic until the first time a probe returns something plausible but wrong: rows from the wrong workspace, counts that match a broader audience than intended. Pre-written expectations are what make those near-misses visible; without them, any non-error response reads as success. Discrepancies between the catalog reading and observed behavior are the highest-value findings of all — they usually mean something outside the obvious path (view, function, trigger) mediates access, and you've found the schema's hidden wiring.

Questions only the owner can answer

Structural review and behavioral testing converge on a residue that no query can resolve: intent. These are the questions to bring to whoever owns the product — and when the answer is "I don't know," that's a finding too, because undocumented access decisions are how drift becomes invisible:

1. For each table: who is supposed to see rows belonging to someone else? Owners-only is the default assumption; every exception — editors, teammates, public listings — should be stated in one sentence per audience.
2. Which tables are legitimately anonymous-readable, and with what filters? Public drafts versus published rows, internal flags excluded, counts hidden or not.
3. What writes happen server-side on behalf of users? Each answer maps to either a definer function or a service-role call site that must authenticate first.
4. Is any data regulated (health, payments, children's data)? Regulatory scope changes the severity math on every other finding.
5. What did the previous author believe was true? If a handover doc or chat log claims "everything is locked down by RLS," compare against your catalog dump — the delta between documented belief and catalog reality is usually where the surprises live.

Asking these after the technical review is deliberate: concrete findings ("this table returns all users' rows anonymously") extract answers that abstract questions ("is isolation important to you?") never do. Owners who can't answer question 1 for a given table have effectively told you the table needs owner-only policies until decided — a safe default that costs nothing today and saves an incident later.

Turn findings into fixes that land

A review ends productively when each finding carries severity, evidence, and a proposed statement. RowShield's format works well anywhere:

• Finding: documents table readable via anon key.
• Evidence: probe response returning N rows; catalog showing no anon-targeted policy.
• Proposed remediation: enable RLS plus the ownership select policy, as SQL ready to review.
• Blast radius: which app features touch the table, so QA knows what to expect.

Two postures make fixes stick. First, propose statements rather than descriptions — "add using ((select auth.uid()) = owner_id)" merges faster than "restrict reads." Second, convert every accepted fix into a regression test the same week; the denial tests from the tenant-isolation playbook exist precisely so this audit never needs repeating from zero. And because schemas drift after sign-off, schedule re-runs — or let continuous monitoring watch the catalog while you sleep, which is the division of labor our scans are built around.

Finally, deliver the report as a prioritized worklist rather than a document. Open windows and leaked material go first regardless of effort. Structural defects (missing checks, tautologies) follow, batched by table so fixes travel together. Performance shapes (bare auth calls, missing indexes on policy columns) close the list, because they compound slowly while exposure compounds immediately. A review that ends with a ranked, statement-level worklist gets executed; one that ends with observations gets filed — and a schema you didn't write, left unfixed, is a schema you've now co-signed.

Common questions

How long should a thorough review take?

For a typical small app — ten to thirty tables — the catalog stages take under an hour, behavioral testing another hour or two, and writing findings the rest of an afternoon. Complexity scales with policy count and the number of definer functions, not with table count alone.

The schema came from a reputable AI tool. Still review?

Yes — reputation describes average quality, and averages don't protect your users. Generated schemas benefit from the same properties that make them productive: fast, consistent, plausible. None of those properties include knowledge of your intent or verification against adversarial access, which are the two things this review exists to supply.

What if I find RLS disabled on a table nobody uses?

Treat it as live exposure anyway. "Nobody uses it" means nobody you know about — the table is exposed through the API to anyone holding the public key, and unused-but-readable is precisely what bulk exfiltration looks like from outside. Enable RLS, break nothing, close the window.

Can I automate parts of this review?

Most of it, and you should. Every catalog query above is scriptable; the shape-detection section maps directly to lint rules; the behavioral probes translate into CI tests. What resists automation is intent — knowing that drafts were meant to be editor-visible — which is why the human role narrows to judgment rather than disappearing. Our scanner automates the conformance half end-to-end, starting with the free scan.

The schema has zero policies but RLS enabled everywhere. Is that good?

It's safe but unfinished: default-deny means nothing leaks, yet every client-facing feature touching those tables must be broken or bypassing through server code. Check which tables have application traffic (API logs, or simply whether the product works), then treat each locked-but-used table as a finding: either write its policy set deliberately or confirm it's genuinely server-only.

---

Audit the outside-facing half automatically: run the free scan — paste your app URL and pair your catalog review with proof of what the public internet can reach today.

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