What Is Row Level Security in Practice? Recipes That Protect User Rows

Answer first, access control that lives in the database #

Row Level Security, usually called RLS, is a Postgres feature that filters every row by policy before returning it. Instead of trusting each client query to add the right user filter, you attach rules to the table itself. Every select, insert, update, and delete is checked against those rules for the current user. If no policy allows the row, the row is invisible, as if it never existed.

In practice this means enabling RLS on user tables, writing owner-only recipes that compare the row owner to the authenticated user, keeping the browser on the RLS-constrained anon key, and reserving the RLS-bypassing service role for server code. It is the standard way to protect user rows in a managed database.

Picture a filing cabinet where every drawer has its own lock keyed to its owner, instead of one guard at the front door who must recognize every face. App-layer checks are the guard: diligent, but one distracted moment exposes everything. RLS is the locks on the drawers. Even when the guard waves someone through by mistake, the wrong drawers stay shut.

Why does enforcement belong in the database? #

Enforcement belongs in the Postgres database because app-layer checks fail open: one new endpoint, one forgotten filter, or one clever client query exposes rows meant to be private. Postgres RLS policies attach to tables and apply to every query, so a missing app filter degrades gracefully. Scope: per the Supabase and Postgres docs, policies decide which rows each role may touch on every query. Constraint: keep app checks for UX, but let RLS be the unbypassable last line.

Application code is easy to get right once and wrong the next time. A new endpoint, a forgotten filter, or a clever client query can expose rows that were meant to be private. When access control lives only in the app layer, every code path must be perfect, and code paths multiply with every feature.

RLS changes the default. As stated in the Postgres docs (https://www.postgresql.org/docs/current/ddl-rowsecurity.html), policies attach to tables and apply to all queries including those from table owners, unless explicitly bypassed. A missing app filter degrades gracefully because the database still refuses rows outside the policy. Supabase enables this posture by default: its RLS guide (https://supabase.com/docs/guides/database/postgres/row-level-security) treats policies as the mechanism that decides which rows each role may touch, evaluated on every query.

It also simplifies multi-tenant reasoning. Ownership becomes a table property expressed once, rather than a convention repeated in every handler. New endpoints inherit protection instead of needing to remember it.

What is the core recipe for owner-only rows? #

Most apps start with the same recipe. Each user-owned table has an owner column holding the user id. RLS is enabled on the table. Then policies grant scoped access per operation. Supabase auth (https://supabase.com/docs/guides/auth) supplies the authenticated identity that policies compare against, so ownership binds to login state rather than request parameters.

-- Owner-only access for a user table
alter table todos enable row level security;

create policy "owners read own rows"
  on todos for select
  using (owner_id = auth.uid());

create policy "owners insert own rows"
  on todos for insert
  with check (owner_id = auth.uid());

create policy "owners update own rows"
  on todos for update
  using (owner_id = auth.uid());

create policy "owners delete own rows"
  on todos for delete
  using (owner_id = auth.uid());

A select policy allows reading rows where the owner matches the authenticated user. An insert policy allows creating rows only when the new row owner matches that user. Update and delete policies add the same ownership check, often with additional constraints on which columns may change. Reads and writes stay symmetric, so a user can never borrow another identity to plant or steal a row.

Two details make or break this recipe. First, enable RLS before writing policies, and deny by default so a table without a matching policy returns nothing. An enabled table with zero policies is a locked room, which is the safe failure mode. Second, bind ownership server-side from the auth context rather than trusting a client-supplied value. The policy should compare against the authenticated identity, not a field the caller chose. The BYOB Supabase deep dive (https://byob.studio/blog/byob-supabase-integration-deep-dive) shows the agent generating exactly this shape automatically when tables hold user data.

How does the anon versus service role split work safely? #

Managed Postgres setups expose two different credential levels, and confusing them is the most common RLS mistake. The anon key is the public, client-safe credential. Requests made with it are constrained by RLS policies. The service role is a privileged server-only key that bypasses RLS entirely. Same database, two doors, wildly different locks.

The rule is simple. Browsers and client components use the anon path, where RLS protects every row. Trusted server code uses the service role only for operations that genuinely need cross-user access, such as admin jobs, migrations, or controlled aggregations. Those server paths must add their own authorization checks, because the database will not add any. With great bypass comes great responsibility, and the responsibility is yours.

Never embed the service-role key in client bundles, mobile apps, or public env modules. If it leaks, RLS no longer constrains whoever holds it. Store it as a server secret, scope its use narrowly, and audit where it is referenced. Rotate it the moment you suspect exposure. A leaked service key is a front door with no guard and no drawer locks.

How do you test policies and avoid team pitfalls? #

Test RLS policies in BYOB Supabase flows in 4 checks: insert as one test user, confirm a second user cannot read the row, confirm unauthenticated requests see nothing, and confirm updates cannot transfer ownership. Prerequisites: test users for each role plus coverage of all four operations. Constraint: test views and functions as the restricted role, and never toggle RLS off for debugging without re-enabling it.

Test policies as each role in addition to an admin. Insert a row as one test user, confirm a second user cannot read it, confirm unauthenticated requests see nothing, and confirm updates cannot transfer ownership to someone else. Cover all four operations, because teams often test reads and forget writes, and a writable-but-unreadable hole is still a hole.

Watch for common pitfalls. Policies that call unstable functions can behave unexpectedly across queries. Overly broad permissive policies can combine in surprising ways, since permissive policies union together. Missing delete policies can strand data that nobody can remove. And toggling RLS off for debugging and forgetting to re-enable it silently removes the safety net. Every one of these has bitten a real team. Checklists exist because memory does not scale.

Views and functions need the same scrutiny as tables. A view that joins user rows can expose data its base policies would hide if it runs with elevated rights, so prefer functions that respect the caller, and test views as the restricted role rather than as owner. Scheduled jobs that aggregate across users belong on the service role with narrow audited code paths. Each bypass should carry a named reason plus a test proving it touches only what it claims.

Make RLS part of code review for every migration that touches user data. Each new table needs an owner model, enabled RLS, policies for the operations the app uses, and a negative test proving isolation. Require the negative test in the migration request itself, beside the policy SQL, so reviewers see the proof without hunting. Policies without tests rot. Tests without policies are theater. Ship both together, always. Isolation you can prove beats isolation you assume. With that checklist, Row Level Security becomes routine rather than remarkable. Locks on every drawer, tested with every change, and user rows stay protected even as the app grows past what any single reviewer can hold in mind.

What are the trade-offs? #

The post recommends enabling RLS on user tables, owner only select plus insert policies tied to the authenticated user, browsers on the RLS constrained anon key, service role reserved for server code, and per role tests. That follows the Supabase RLS plus auth docs, Postgres policy docs, and BYOB Supabase plus auth guides cited above.

Pick app only checks when data is public, single tenant internal, or no cross user read risk exists to justify the policy layer.

Who this is for (and who should skip it) #

Row Level Security fits Postgres tables holding per-user rows where a missing client filter must never leak data, using owner-only policies plus anon-role separation. Skip RLS for shared public tables with no ownership concept or fully trusted server boundaries. Limit: policies add SQL to review, debugging gets harder when a missing row is a policy, and migration order matters.

This fits if your Postgres tables hold per user rows and you want enforcement inside the database so a missing client filter cannot leak data. Owner only policies plus anon role separation give a durable safety net.

Skip RLS for shared public tables with no ownership concept or where you fully control access in a trusted server boundary. Use it where user rows matter.

• Best for developers protecting user rows with owner-only policies inside Postgres itself.
• Best for startups separating anon and service roles so browsers never bypass checks.
• Best for freelancers shipping multi-tenant client apps with safe defaults.

What we learned building this #

For Postgres apps we connect Supabase through OAuth project selection and generate Row Level Security policies when creating user tables, enabling RLS and scoping select and insert to the authenticated user. The integration is documented in the BYOB Supabase deep dive (https://byob.studio/blog/byob-supabase-integration-deep-dive) and reflects how the AI operates the database without ever seeing the access token. We keep browser code on the RLS constrained anon key and reserve the service role for server routes only.