Security & Row-Level Security

Why this matters: the threat model

When you wire up the SDK you ship two values into the browser: a data URL and a public anon key. The anon key is not a secret. It travels in your client bundle, it's visible in every request, and anyone can read it. That is by design.

Security does not come from hiding the key. It comes from Row-Level Security — policies, written as ordinary Postgres SQL, that the database evaluates on the server for every single query before any row is returned. The key just identifies which backend you're talking to; the policies decide what that request is allowed to see and change.

This is why the rule is simple and absolute: every table the anon key can reach should have RLS enabled, with policies. A table is in exactly one of three states:

• RLS off — the table is fully open. Any request with the anon key can read and write every row. This is almost never what you want for user data.
• RLS on, no policy — the table is fully closed. No row is visible or writable through the data gateway (the owner console still sees it). Safe, but useless until you add a policy.
• RLS on, with policies — access is exactly what your policies allow. This is the target state for every user-facing table.

How enforcement works

The mechanism behind the policies is worth understanding, because it's what makes the SQL you write correct.

• Every app query runs as a low-privilege Postgres role scoped to your backend's own schema. It cannot reach any other backend's schema — isolation is structural, not just policy-based.
• The signed-in user's id is exposed to your policies as current_setting('app.user_id', true). It's a TEXT value, and it is NULL when the request is signed out — the SDK only sets it after sign-in. Cast it to your column's type in the policy (::uuid, ::text, and so on).
• Policies are ordinary Postgres SQL. You write them in migrations — the console's SQL tab, or by letting the agent call apply_migration — never as runtime calls.

The core pattern

This is the pattern that covers most apps: a table where each row belongs to one user, and only that user can see or change it. Two steps — enable RLS, then add the policy.

-- 1) Enable RLS. WITHOUT THIS, policies do nothing.
ALTER TABLE todos ENABLE ROW LEVEL SECURITY;

-- 2) Owner-only: a row is visible/editable only to the user who owns it.
CREATE POLICY "todos are private to their owner" ON todos
  USING (user_id = current_setting('app.user_id', true)::uuid)
  WITH CHECK (user_id = current_setting('app.user_id', true)::uuid);

Two clauses are doing two different jobs, and the distinction matters:

• USING decides which existing rows are visible — it filters SELECT, and gates which rows an UPDATE or DELETE is allowed to touch.
• WITH CHECK validates the rows you're allowed to write to — it gates the new or changed row on INSERT and UPDATE. Without it a user could insert a row stamped with someone else's user_id.

Note the cast: current_setting('app.user_id', true) returns TEXT, so you cast it to the column's type — ::uuid here. And note what happens when the request is signed out: current_setting returns NULL, the comparison user_id = NULL is never true, no row matches, and access is denied. Signed-out safety is automatic.

Recipes

Auto-stamp the owner (so clients can't spoof it)

Instead of trusting the client to send the right user_id, give the column a default that fills it from the signed-in user. The client never sets it at all:

ALTER TABLE todos
  ALTER COLUMN user_id SET DEFAULT current_setting('app.user_id', true)::uuid;

Now insert({ title: 'Buy milk' }) with no user_id lands a row owned by whoever is signed in. Pair this with the owner-only policy above and there is no way for a client to claim a row it doesn't own.

Public read, owner write

For things like a blog or a public feed, anyone may read every row, but only the owner may create, change, or delete their own. Because the commands differ, write a separate policy per command with FOR SELECT / FOR INSERT / FOR UPDATE / FOR DELETE:

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "anyone can read" ON posts
  FOR SELECT USING (true);

CREATE POLICY "owner can insert" ON posts
  FOR INSERT WITH CHECK (user_id = current_setting('app.user_id', true)::uuid);

CREATE POLICY "owner can modify" ON posts
  FOR UPDATE USING (user_id = current_setting('app.user_id', true)::uuid);

CREATE POLICY "owner can delete" ON posts
  FOR DELETE USING (user_id = current_setting('app.user_id', true)::uuid);

The FOR SELECT policy with USING (true) makes every row publicly readable. The write policies each scope a single command to the owner. Splitting per command is the tool you reach for any time read access and write access aren't the same — scope each command exactly as tightly as it needs to be.

The safety net: advisors

You will get a policy wrong at some point — everyone does. The backend ships an advisors scanner that reads your schema and flags the real RLS mistakes before they reach users. Among what it catches:

• Policies exist but RLS is off. A table has policies that do nothing because ENABLE ROW LEVEL SECURITY was never run — so the table is wide open despite looking protected. Fix: ALTER TABLE … ENABLE ROW LEVEL SECURITY;.
• Tables reachable by the anon key with no RLS or policy. The fully-open case — user data anyone can read or write.
• A mutable function search_path — a security warning on functions whose search path can be manipulated.

Run the advisors before every ship. It's the fastest way to catch the gap between "I wrote a policy" and "the policy is actually enforced." See Scan your backend with advisors.

Putting it together: a ship checklist

• For every user-facing table: ENABLE ROW LEVEL SECURITY and at least one policy.
• Owner-only tables: one policy with both USING and WITH CHECK, casting current_setting('app.user_id', true) to the column type.
• Auto-stamp user_id with a column default so clients can't spoof ownership.
• Mixed access: split into FOR SELECT / INSERT / UPDATE / DELETE policies.
• Test as a signed-in user and a signed-out user — not from the console (it bypasses RLS).
• Private vendor keys go in the Secrets vault, never a table or the client.
• Run the advisors and clear every RLS finding before deploying.