AI App Backend

Row-level security explained: keeping each user’s data private

How row-level security works in Postgres, how to write policies that keep each user’s rows private, how Supabase and Firebase apply the same idea, and the mistakes that leak data.

Floot ·

Key Takeaways

  • RLS is a filter the database applies to every query. Once enabled on a table, Postgres adds your policy’s condition to each SELECT, UPDATE and DELETE, and checks each INSERT and UPDATE against it, for every role the policy applies to.
  • Enabled with no policy means nobody sees anything. Postgres uses default-deny: if RLS is on and no policy matches, no rows are visible or changeable. That is safe, and it is also why a new table can look “empty”.
  • Owners and superusers skip it unless you force it. Table owners normally bypass RLS, and superusers and roles with BYPASSRLS always do. FORCE ROW LEVEL SECURITY makes the owner subject to the policies too.
  • Supabase depends on it. Supabase’s docs say to enable RLS on every table in an exposed schema such as public, because otherwise any role with a grant can read or write the table from the browser.
  • Test with two accounts, every time. Sign in as user A, try to read and change user B’s rows by editing IDs, and confirm both fail. OWASP ranks broken access control as the top risk in its 2025 list.

Quick-Start Prioritization Framework

SituationUse RLS?Why
Browser or mobile app queries the database directly (Supabase, PostgREST)Yes, requiredThe database is the only gatekeeper between users and rows
All access goes through your own server endpointsRecommended as a second layerCatches a missing WHERE clause in your code
Single-user internal tool, no public accessOptionalLow benefit, but cheap insurance
Firestore or Firebase StorageUse Security Rules insteadSame idea, different system
Admin scripts and background jobsUse a separate role that bypasses it deliberatelyKeeps jobs working without weakening user policies

Every multi-user app has the same promise to keep: you see your data and nobody else’s. Most leaks happen not because someone broke the encryption, but because a query returned rows it shouldn’t have. Row-level security (RLS) moves that promise into the database itself, so a forgotten WHERE clause can’t expose another customer’s records. This guide explains what RLS is, how Postgres enforces it, how to write and test policies, and where it fits next to the checks in your own code.

The problem RLS solves

Imagine a notes app. The endpoint that lists notes runs SELECT * FROM notes WHERE owner_id = $1, with the signed-in user’s ID. That works. Then someone adds a search endpoint and forgets the WHERE clause. Now search returns everyone’s notes.

OWASP’s 2025 Top 10 puts broken access control at number one and says every application in its tested data had some form of it. One of its named examples is the insecure direct object reference: changing an ID in a request to view or edit someone else’s record.

Two places to enforce access

You can enforce “users see only their own rows” in application code, by adding the right condition to every query, or in the database, by attaching a policy to the table. Application checks are flexible but have to be remembered in every query. Database policies are applied automatically to every query against the table.

Why it matters more with AI-written code

Coding agents add endpoints quickly. Each new query is another chance to forget a condition. A policy on the table protects the data no matter which endpoint, script or agent-written query touches it.

How Postgres row-level security works

RLS has been part of Postgres for years and the current documentation (PostgreSQL 18) describes the rules below. You turn it on per table, then add policies.

Turning it on

ALTER TABLE notes ENABLE ROW LEVEL SECURITY; switches it on. From then on, Postgres says all normal access to the table must be allowed by a policy. If no policy exists, a default-deny policy applies, meaning no rows are visible or can be modified.

USING and WITH CHECK

A policy has up to two conditions. USING decides which existing rows a query can see or target with SELECT, UPDATE and DELETE. WITH CHECK decides which rows may be written by INSERT and UPDATE. If you leave out WITH CHECK, Postgres uses the USING condition for both.

That split lets you say “everyone can read all rows, but only edit their own” with two policies: one FOR SELECT USING (true), and one for changes USING (owner = current user).

Permissive and restrictive policies

Policies are permissive by default, and several permissive policies combine with OR: a row is visible if any of them allows it. Restrictive policies (AS RESTRICTIVE) combine with AND: every one must pass. Use restrictive policies for blanket conditions, such as “never show rows where deleted_at is set”.

Who bypasses it

Table owners normally bypass RLS. Superusers and roles with the BYPASSRLS attribute always do. ALTER TABLE notes FORCE ROW LEVEL SECURITY; makes the owner follow the policies as well. Postgres also notes that unique, primary key and foreign key checks always bypass RLS, and that TRUNCATE isn’t subject to it.

Worked example: a notes app with shared team notes

Here is a realistic set of rules and the policies that express them. The table is notes(id, owner_id, team_id, body, created_at). Users belong to teams through team_members(team_id, user_id). The function current_user_id() stands for however your platform exposes the signed-in user; in Supabase it is auth.uid().

  • Rule 1, read: a user can read notes they own, and notes shared with a team they belong to. Policy: FOR SELECT USING (owner_id = current_user_id() OR team_id IN (SELECT team_id FROM team_members WHERE user_id = current_user_id())).
  • Rule 2, create: a user can only create notes owned by themselves. Policy: FOR INSERT WITH CHECK (owner_id = current_user_id()).
  • Rule 3, edit: a user can edit only their own notes, and can’t hand a note to someone else. Policy: FOR UPDATE USING (owner_id = current_user_id()) WITH CHECK (owner_id = current_user_id()).
  • Rule 4, delete: only the owner. Policy: FOR DELETE USING (owner_id = current_user_id()).

What happens at query time

When user 17 runs SELECT * FROM notes, Postgres runs it as if it said SELECT * FROM notes WHERE (owner_id = 17 OR team_id IN (…user 17’s teams…)). If an endpoint forgets its own WHERE clause, the result is still limited to user 17’s notes.

Why rule 3 has both clauses

Without WITH CHECK on the update policy, Postgres would fall back to the USING clause, which happens to be the same here. Writing it out makes the intent explicit: a user can’t change owner_id to another person’s ID and push a note into their account.

RLS in Supabase

Supabase lets the browser query Postgres directly through its Data API, which makes RLS the main line of defence rather than a backup.

Enable it on every exposed table

Supabase’s docs say to enable RLS on every table in an exposed schema such as public. Without it, any role with a grant can read or write the table. They also recommend revoking default grants from the anon and authenticated roles and granting back only what each role needs, because policies don’t remove existing grants.

The standard policy

Supabase’s own example is: create policy “Individuals can view their own todos.” on todos for select to authenticated using ((select auth.uid()) = user_id); auth.uid() returns the signed-in user’s ID, or null when nobody is signed in. Write one policy per operation and name the role with to.

Keys that skip RLS

Supabase’s secret keys and the service_role key bypass RLS when the request carries no user token. They belong on a server only. A service_role key in frontend code gives every visitor full access to every table.

Views and performance

Views bypass RLS by default; on Postgres 15 and later Supabase recommends setting security_invoker = true on them. For speed, index the columns your policies filter on, and wrap auth.uid() in (select …) so Postgres evaluates it once per statement instead of once per row.

The same idea outside Postgres

Document databases enforce per-user access with their own rule languages. The thinking is the same: decide which records a signed-in user may touch, and let the backend refuse everything else.

Firebase Security Rules

Firestore’s documented pattern for user-owned data is a rule on /users/{userId} that allows read, update and delete only if request.auth.uid == userId. One difference trips people up: Firebase says rules are not filters. A query is rejected outright if it could return a document the user isn’t allowed to read, so the query itself must include a matching where clause.

Checks in your own endpoints

If every request goes through your server, you can enforce access in code: read the user from the session, never from the request body, and include it in every query. RLS underneath is then a safety net, not the only control.

How to test your policies

A policy you haven’t tested is a guess. These checks take ten minutes and catch most mistakes.

  • Create two normal users, A and B, and give each a few rows.
  • As A, list rows. Confirm you see only A’s rows plus anything legitimately shared.
  • As A, request one of B’s rows directly by ID. Expect nothing back, not an error that reveals it exists.
  • As A, try to update and delete B’s row by ID. Expect zero rows affected.
  • As A, try to insert a row with owner_id set to B. Expect a policy violation.
  • As A, try to update one of your own rows and change owner_id to B. Expect a policy violation.
  • Sign out and repeat the read. Expect nothing.
  • Repeat after every schema change. Supabase documents running database tests with pgTAP through supabase test db if you want this automated.

Common mistakes

Almost every RLS leak comes from one of these.

A new table without RLS

The first tables get careful policies. Three weeks later the agent adds an invoices table and nobody enables RLS on it. Make “enable RLS and add policies” part of every migration, and periodically list tables where it is off.

Policies based on data users can edit

A policy such as “admins see everything” that reads an is_admin column from a profile table the user can update lets anyone make themselves admin. Store roles where users can’t write, and protect that table with its own policy.

Admin keys in the browser

Any key that bypasses RLS, such as a service role key or a direct connection string with the table owner’s credentials, must stay on the server. If it ships to the browser, your policies no longer matter.

Testing as the table owner

If you test in a SQL console connected as the owner or a superuser, RLS doesn’t apply and every query “works”. Test as a normal signed-in user, or use FORCE ROW LEVEL SECURITY.

Forgetting views and functions

Views and security-definer functions run with their owner’s privileges and can return rows the caller couldn’t select directly. Review them as carefully as tables.

If you’d rather not write policies by hand

If you’d rather not manage database policies yourself, Floot gives each app its own Postgres database, sign-in with roles and server endpoints, so the access rules can live in endpoint code, and the agent you build with can check them with two test accounts in the live preview.

Frequently Asked Questions

Does row-level security slow down queries?

It adds the policy condition to each query, so the cost is similar to writing that WHERE clause yourself. Index the columns policies filter on, and in Supabase wrap auth.uid() in a subselect so it is evaluated once per statement.

Do I still need permission checks in my API if I use RLS?

It is wise to have both. Your endpoints should read the user from the session and validate input; RLS catches the query someone forgot to filter. The two layers fail in different ways.

Why does my table look empty after enabling RLS?

Postgres uses default-deny. With RLS on and no policy that matches your role and operation, no rows are visible. Add a SELECT policy for the signed-in role.

Can admins see everything with RLS on?

Yes, if you write a policy that allows it, for example one that checks the user’s role in a table only admins can change. Server-side jobs can use a separate role that bypasses RLS deliberately.

Is row-level security the same as encryption?

No. Encryption protects data if storage or traffic is intercepted. RLS decides which rows a logged-in query may return. You want both.

Does MySQL or Firestore have row-level security?

Firestore uses Security Rules, which play the same role for documents. For databases without built-in row policies, enforce access in your server code on every query.

Sources