Database RLS & Privilege Escalation

The UI is not the security boundary. Your route guard can hide the admin panel perfectly and still leave the database happy to let any logged-in user write is_admin = true on their own row.

The mistake in one sentence: the app hides the admin panel from non-admins in the UI, but the database itself will still let any logged-in user write is_admin = true on their own row, because nobody locked that column down at the data layer.

Paste into your AI chat

Copy the entire guide as markdown to use as context in Cursor, Claude, ChatGPT, or any AI tool.

Why AI tools generate this

A route guard is the obvious answer to “only admins should see the admin page.” It just is not the whole answer.

Client-side route guards are the easy, obvious thing to build when you ask an AI assistant to make sure only admins see the admin page, and they are not wrong to exist. They are just not sufficient on their own, and that distinction rarely gets surfaced unless you specifically ask for it. A model asked to protect the admin route will confidently produce a <ProtectedRoute requireAdmin> wrapper and consider the job done, because from a pure UX standpoint, it is.

The database side compounds this in a specific, subtle way: RLS policies are row-scoped by default, not column-scoped. An assistant generating a policy from “users should be able to update their profile” has no reason to reach for the more advanced, less commonly known column-level GRANT/REVOKE syntax needed to close the gap. It is not part of the make-the-feature-work prompt.

The three ways this goes wrong

1. A policy that restricts the row, not the columns

USING (auth.uid() = id) says which row you may update. It says nothing about which columns.

2. Escalating to admin from the browser console

One line, the public anon key, any logged-in account. The row-level check passes because it is their own row.

3. Policies that exist nowhere in your repo

RLS rules authored in Supabase Studio are invisible to grep, to static analysis, and to every code review.

Why it’s dangerous

One missing column restriction is enough to hand any signup account the whole database.

  • Full privilege escalation from a standard account

    Any user who signs up gets a path to becoming an admin, with no exploit needed beyond knowing SQL or the client library.

  • It cascades

    Once is_admin is true, every other RLS policy gated on that same flag opens up too: inventory, other users’ data, billing details, audit logs. One missing column restriction becomes total compromise.

  • The client-side guard gives false confidence

    Because the UI correctly hides admin features from non-admins, manual QA and casual code review both look clean. The gap only shows up if someone writes to the database directly, bypassing the UI.

  • It is invisible in the repo

    If the vulnerable policy lives only in a cloud dashboard, grep-based review, static analysis and AI code review of the checked-in code will all miss it. There is nothing in the repo to look at.

How to check your own app

For Supabase or any other RLS-backed Postgres setup. Read what your UPDATE policies actually restrict, then check the column grants underneath them.

sql
-- 1. List every UPDATE policy on every table, and read what it actually restricts
SELECT schemaname, tablename, policyname, cmd, qual, with_check
FROM pg_policies
WHERE cmd = 'UPDATE'
ORDER BY tablename;

-- 2. Specifically check column-level grants on any table with a privilege
--    or role column — does `authenticated` have UPDATE on that column?
SELECT table_name, column_name, privilege_type, grantee
FROM information_schema.column_privileges
WHERE table_schema = 'public'
  AND column_name IN ('is_admin', 'role', 'is_staff', 'tier', 'credit_balance')
  AND grantee IN ('authenticated', 'anon');
-- If `authenticated` shows UPDATE on any of these, that's the bug.

Then confirm the repo actually has those policies checked in, and find where privileged columns are being sent from client code:

bash
# 3. Confirm your repo actually has these policies checked in as SQL files —
#    if grep finds nothing, your real security rules exist only in a dashboard
grep -rl "CREATE POLICY" --include="*.sql" .
# If this is empty or thin relative to your table count, export your live
# policies (`supabase db dump` or equivalent) and commit them.

# 4. Grep your own client code for any place a "privileged" column is sent in
#    an update payload from client-side code — even if the column SHOULD be
#    locked down, this tells you where the risk is highest
grep -rn "is_admin\|role:\|is_staff" --include="*.tsx" --include="*.ts" src/ client/src/

If you have a second factor (email OTP, TOTP, SMS) in front of sensitive data, check whether it is enforced only by your frontend and middleware, or whether it is actually encoded into the session token the database checks. If your API and your database would both accept a password-only session with no evidence the second factor was ever completed, the second factor is UX, not security.

The fix

Treat RLS, or whatever your server-side authorization layer is, as the actual security boundary, and treat the UI guard as a nice-to-have for user experience only. Four concrete moves:

Column grants

Revoke blanket UPDATE, grant back only self-editable columns.

Split the table

Privileged fields in a table only the service role can write.

Commit policies

Export live RLS to SQL in the repo so reviewers can read it.

Second factor

Require the elevated assurance level in RLS, not just the router.

1. Lock down privileged columns separately from the row policy

Revoke blanket UPDATE, then grant back only the columns a user should be able to self-edit. Everything else now needs the service-role key, which is exactly what you want.

sql
-- Revoke blanket UPDATE, then grant back only the safe, self-editable columns
REVOKE UPDATE ON profiles FROM authenticated;
GRANT UPDATE (full_name, avatar_url, email_preferences) ON profiles TO authenticated;

-- is_admin, role, stripe_customer_id, credit_balance etc. now require the
-- service-role key (server-side only) to change — exactly what you want.

2. Split privileged fields into their own table

When column-level grants get unwieldy, change the shape of the problem.

A dedicated user_roles table that only your backend service-role key can write to is often simpler to reason about than fine-grained column grants spread across a wide profiles table. One table, one rule: nothing holding the anon or authenticated role can write to it at all.

3. Version-control every policy

A rule you cannot read in a pull request is a rule nobody is reviewing.

Export live policies to a SQL file in your repo (supabase db dump --schema public > docs/rls-policies.sql, or your platform equivalent) and keep it current. Treat undocumented dashboard-only policies as a standing security debt item until they are captured in the repo.

4. Enforce a second factor at the data layer, not just the UI

If your platform can encode assurance level into the session token, require the elevated level in your API middleware and in your RLS policies, not just in a frontend redirect.

sql
-- Example: require a second-factor-verified session for sensitive reads,
-- enforced at the RLS layer, not just checked by a page redirect
CREATE POLICY "Staff can read cases, second factor required"
ON cases FOR SELECT
USING (
  is_staff(auth.uid())
  AND (auth.jwt() ->> 'aal') = 'aal2'
);

Checklist

Walk this before you ship any table carrying a privilege, role, tier or balance column.

  • Every table with a privileged/role/tier column has that column locked down with explicit REVOKE/GRANT, separate from the row-level USING policy.
  • All RLS policies (or equivalent server-side access rules) are exported and version-controlled in the repo, not left dashboard-only.
  • Client-side route guards are documented as UX-only, with a comment pointing at the real enforcement layer, so nobody mistakes them for security.
  • If you have a second factor (OTP/MFA), it is checked at the API and/or database layer using session-encoded assurance level, not only by a frontend redirect.
  • You have run the column-privilege query above against every table that has a privilege/role/tier/balance column.
  • New tables get an explicit “who can write which columns” review before shipping, not an assumed-safe default.

Prompt your AI assistant

Paste this to have your AI tool audit your database access-control layer, and report rather than fix.

text
Audit this repository's database access-control layer for privilege
escalation via unrestricted column writes.

If this project uses Supabase or another RLS-backed Postgres setup:

1. List every table that has a privilege/role/tier/balance-style column
   (e.g. is_admin, role, is_staff, tier, credit_balance, stripe_customer_id).
   For each one, check whether the table's UPDATE row-level-security policy
   restricts by ROW ONLY (e.g. `USING (auth.uid() = id)`) with no
   corresponding column-level GRANT/REVOKE restricting which columns the
   `authenticated` or `anon` role can actually write. Flag any table where a
   normal user could plausibly run `UPDATE <table> SET <privileged column> =
   ... WHERE id = auth.uid()` and have it succeed.

2. Check whether this repository has the live RLS policies checked in as SQL
   files (search for `CREATE POLICY`). If policies referenced by the app
   aren't found in the repo, flag that the real security rules may only
   exist in a cloud dashboard and are unreviewable from the code.

3. If there's a second-factor / MFA / OTP flow, trace whether the elevated
   session state it produces is checked anywhere other than a frontend route
   guard or redirect — specifically, is it checked in API middleware and/or
   in the RLS policies themselves? Flag it if the only enforcement is a
   client-side redirect, since that's bypassable by calling the API or
   database directly.

4. Find every client-side "admin-only" or "role-gated" UI component and
   confirm there is a matching SERVER-SIDE check (API middleware or RLS) for
   every action that component exposes — not just a route redirect.

For each finding: table/file, the exact escalation an attacker could perform,
and a fix (column-level GRANT/REVOKE statements, a migration file, or a
second-factor enforcement point) using this project's existing conventions.
Do not apply any fixes yet — just report.

Want the full guide as context for your AI coding assistant?