supabase-rls-policies

Files (1)

• SKILL.md

SKILL.md (first lines)

# Supabase Row-Level Security Policies

> Defends against **OWASP ASI03 (Identity & Privilege Abuse)** in
> Supabase-backed applications by enforcing least-privilege at the
> Postgres row level. Supabase's auth model produces a JWT carrying the
> authenticated user's UUID; PostgREST (Supabase's API layer) propagates
> that JWT into the `auth.uid()` function inside the database, which
> RLS policies use to scope queries. Done right, the database refuses
> to return rows the user shouldn't see -- even if the application
> layer has a bug. Done wrong, RLS is a false sense of security: the
> service-role key bypasses RLS entirely, missing policies leave gaps,
> and USING-vs-WITH-CHECK confusion ships read-only-looking restrictions
> that allow writes.

**Tradeoff:** Per-row policy enforcement adds Postgres planner overhead
and shifts authorization logic from the application layer into SQL
policies. Worth it: RLS is the database-layer expression of
least-privilege that keeps working when the application is compromised,
when a service-role-key bypass leaks, or when a future code path
forgets to apply the equivalent application-layer check.

## Why this matters

RLS aligns with **GDPR Article 32 (Security of Processing)** --
specifically the "appropriate technical and organisational measures to
ensure a level of security appropriate to the risk" obligation -- and
**SOC 2 Trust Services Criterion CC6.1 (Logical and Physical Access
Controls)** -- "the entity implements logical access security
software, infrastructure, and architectures over protected information
assets." Postgres RLS is the canonical defense-in-depth layer for both
controls: even if the application is compromised, the database enforces
row-level authorization for any identity-scoped data.

---

## Pattern 1 -- Enable RLS on every table holding user-scoped data

The first move on any table that holds PII, financial data, tenant-
scoped records, or anything else identity-bound:

```sql
ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
```

After enabling, the table is inaccessible through the Data API (the
`anon` and `authenticated` roles) until at least one policy is created.
That's the safe default. The table owner (`postgres`, which the SQL
editor uses) and BYPASSRLS roles such as `service_role` still see every
row, so a policy check run as either of them proves nothing. Adding the
`ENABLE ROW LEVEL SECURITY` without immediately authoring policies is
acceptable -- it fails closed.

For every policy you author, document the threat model in a comment
above the SQL: who is this policy for, what access does it grant, why
is the scoping safe?

---

## Pattern 2 -- Authoring policies with USING (read) and WITH CHECK (write)

The two clauses serve different roles:

- **USING** -- filters rows VISIBLE to the operation (applies to
  SELECT, UPDATE, DELETE).
- **WITH CHECK** -- validates rows being WRITTEN by the operation
  (applies to INSERT, UPDATE).

For SELECT-only policies, only USING applies. For INSERT-only policies,
only WITH CHECK applies. For UPDATE, BOTH apply -- USING filters which
rows the user can attempt to update (the row must be visible to be
modified), and WITH CHECK validates the row's post-update state.

```sql
-- Read your own posts.
CREATE POLICY "select_own_posts"
ON public.posts
FOR SELECT
USING (author_id = auth.uid());

-- Insert posts where you are the author.
CREATE POLICY "insert_own_posts"
ON public.posts
FOR INSERT
WITH CHECK (author_id = auth.uid());

-- Update your own posts, and ensure you're still the author after the update.
CREATE POLICY "update_own_posts"
ON public.posts
FOR UPDATE
USING (author_id = auth.uid())
WITH CHECK (author_id = auth.uid());

-- Delete your own posts.
CREATE POLICY "delete_own_posts"
ON public.posts
FOR DELETE
USING (author_id = auth.uid());
```

Per-operation policies are clearer than `FOR ALL` policies because
each operation's intent is reviewable in isolation. A new contributor
modifying one operation's authz can't accidentally widen authz on
other operations.

---

## Pattern 3 -- Service-role-key usage is server-side only, never shipped to clients

Supabase issues two API keys:

- **Anon key** (`sb_publishable_*` or older `eyJ...` JWT with role `anon`) --
  safe to ship in client code. Subject to RLS.
- **Service-role key** (`sb_secret_*` or older `eyJ...` JWT with role
  `service_role`) -- **BYPASSES RLS ENTIRELY**. Anything done with this
  key has full database authority.

The service-role key belongs in server-side environment variables only.

From MILTONADINA/DevOPs, MIT.

More from MILTONADINA/DevOPs