Row-Level Security vs App-Layer Authorization: Which Wins?

In the row-level security vs app-layer authorization debate, the practical answer is: put the rule that decides which rows a user may touch in the database with row-level security (RLS), and keep workflow rules — "only the assignee can mark this done", "send an email when approved" — in application code. App-layer checks fail open when someone forgets one on a new route; RLS is applied to every query regardless of which code path issued it. That difference is why data-isolation rules belong in Postgres.

The failure mode that decides it

Broken access control is the top category in the OWASP Top 10 (2021). The typical bug is not exotic: an endpoint that loads a record by ID and forgets to check that the record belongs to the caller. In an app-layer model, every query path needs that check:

// app-layer: correct only if every route remembers this line
const { data } = await db.from("requests").select("*")
  .eq("id", id)
  .eq("organization_id", user.orgId); // forget this once → cross-tenant leak

With RLS the database appends the rule to every query, including ones written next year by someone who never read the security docs:

create policy requests_select on public.requests for select to authenticated
using (organization_id = (select public.current_user_org()));

Now select * from requests where id = $1 returns nothing for a row in another organization. The forgotten filter is no longer a breach — it's just a slightly less efficient query.

Side by side

The "default when forgotten" row is the one that matters. Security that depends on every developer remembering every time is security that will eventually fail.

When RLS is the right tool

• Multi-tenant SaaS. Tenant isolation is the canonical case: one predicate (organization_id = my org) on every table. See the multi-tenant RLS guide.
• Clients talk to the database directly. With Supabase, the browser can query tables through the Data API using the public anon key. Supabase's docs put it bluntly: a table in an exposed schema without RLS is readable and writable by any role with a grant on it. Here RLS isn't a choice; it's the only gate.
• Per-user visibility. "Requesters see their own requests; agents see their team's; admins see the organization's" is a clean using clause.
• Realtime. Change streams deliver rows to subscribers; RLS decides which subscriber receives which row.

When the app layer is the right tool

• Workflow and state transitions. "A request can move from resolved back to in_progress only by the requester within 7 days" is expressible in SQL (a trigger can enforce it) but is often clearer as validated server code. If you put it in the app, make it a single Server Action that every UI path calls.
• Side effects. Sending an email, calling an AI model, posting a webhook — these happen in server code after an authorized write.
• Input validation. Shape and range checks with a schema library (Zod, in our case) at the boundary.
• Rate limits and quotas that depend on external services.

Combining them well

The pattern we use in LetRelay:

1. RLS on every table, including ones "only the server touches". If a table has no policies, it has no rows for API roles, which is the right default.
2. Server Actions for trusted writes, using the user's own session. The action validates input with Zod, then writes through the user's client — so RLS still applies. The action adds workflow logic; the database still enforces isolation.
3. The service-role key only where a write must cross the user's permissions — creating an organization at sign-up, a scheduled job, a webhook receiver. It bypasses RLS entirely, so it is server-only, never in a NEXT_PUBLIC_ variable, and used in as few places as possible.
4. UI mirrors, never decides. Hide buttons a role can't use, so people aren't confused — but assume a determined user can call anything the UI can.
5. Database triggers for invariants that must hold regardless of caller: stamping first_response_at, filling organization_id, preventing a user from changing their own role.

Making RLS fast

Policies are just predicates Postgres adds to your query, and slow predicates make every query slow. Three habits, all from Supabase's RLS guidance:

• Wrap auth functions in a sub-select. (select auth.uid()) instead of auth.uid() lets the planner evaluate it once per statement (an initPlan) rather than once per row. Same for your own helpers: (select public.current_user_org()).
• Index the columns policies filter on. An unindexed organization_id or team_id turns every read into a sequential scan.
• Name the role. to authenticated makes the policy's scope explicit, so it isn't evaluated for roles it doesn't apply to.

Helper functions like current_user_org() are usually security definer so they can read profiles without recursing into that table's own policies. Supabase warns that such functions bypass RLS and are callable over the API if they live in an exposed schema. Keep them tiny and make sure they only ever return the caller's own facts — their organization, role or team — never a lookup by an argument the caller controls. Pin search_path on every one.

Testing access rules

App-layer tests check that a route returns 403. RLS tests check the rule itself, which covers every route at once. A useful suite, run against a real database:

1. Create two organizations, each with a requester, an agent and an admin.
2. For each role, attempt select, insert, update and delete on every table, against rows in the other organization.
3. Assert zero rows returned and zero rows changed.
4. Repeat within an organization for the role boundaries (requester reading another requester's request, agent reading another team's queue).

LetRelay runs checks like these as signed-in users on every verification run. They catch the class of bug that code review misses: a new table added without a policy, or a policy that is correct for select but missing for update. For the role model itself, see role-based access for internal tools.

Debugging RLS

The main complaint about RLS is that failures are silent — a query returns an empty list instead of an error. Tactics:

• Test policies in SQL by setting the role and JWT claims in a transaction, then running the query.
• On insert and update, a with check failure does raise an error ("new row violates row-level security policy"); read it as "the row you're writing wouldn't be visible to you".
• Keep policies short and named after what they allow: requests_select_own, requests_select_team. Several simple permissive policies are easier to reason about than one long boolean expression.

FAQ

Is row-level security enough on its own?

For data isolation, it's the strongest single layer. You still need input validation, rate limiting and workflow rules in server code, and you must keep privileged keys off the client.

Does RLS slow down queries?

Policies add predicates, so they can — especially if they call functions per row or filter on unindexed columns. Wrap auth functions in (select …), index policy columns, and the overhead is usually small.

Can I use RLS with an ORM?

Yes, if the ORM connects with a role that RLS applies to and passes the user's identity (for example, via JWT claims or a session variable). If it connects as a superuser or table owner with bypassrls, policies are skipped.

What's the biggest RLS mistake?

Creating a table and forgetting to enable RLS on it. In Supabase, that table may be readable and writable through the public API. Enable RLS in the same migration that creates the table.

Sources

• OWASP Top 10 — A01 Broken Access Control
• Supabase — Row Level Security
• PostgreSQL — Row Security Policies
• PostgreSQL — CREATE POLICY
• OWASP — Authorization Cheat Sheet