Supabase Row Level Security: Architecture Note for Per‑User Data Access

Requirements

The goal is to enforce per‑user data access directly inside the PostgreSQL database so that application code does not need to filter rows. In a multi‑tenant SaaS setting each authenticated user must see only rows that belong to them, identified by an owner_id column that stores the user’s UUID. The solution must work with Supabase Auth, rely on the JWT issued by that service, and avoid moving authorization logic to the application tier.

Smallest Suitable Design

Enable Row Level Security on the target table and attach a single policy that uses the auth.uid() function supplied by Supabase. The policy should cover all DML operations (SELECT, INSERT, UPDATE, DELETE) with matching USING and WITH CHECK clauses.

-- 1. Enable RLS (run once per table)
ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

-- 2. Create a simple owner‑based policy
CREATE POLICY user_owns_rows
ON your_table
FOR ALL
USING (auth.uid() = owner_id)
WITH CHECK (auth.uid() = owner_id);

Place this SQL in the Supabase SQL editor or apply it via supabase db push after committing to your migration folder. No additional columns, triggers, or application middleware are required.

Trust/Data Boundaries

The trust boundary sits between Supabase Auth and the PostgreSQL instance. When a client authenticates, Supabase Auth issues a JWT that is verified by the Auth service before the connection is handed to Postgres. The database treats the auth.uid() function as trustworthy because it reads the verified JWT claims. Consequently, any request that reaches Postgres without a valid token will have auth.uid() return NULL, causing the policy to reject access.

Operational Checks

To verify that policies are working as expected:

1. Policy hit statistics – query the system view:

SELECT *
FROM pg_stat_user_policies
WHERE relid = 'your_table'::regclass;

This shows sel, ins, upd, del counters for the policy. Increase in counters after legitimate operations confirms the policy is being evaluated.

1. Failed access logging – enable logging of RLS denials in Postgres (e.g., log_min_error_statement = error) and watch for messages like:

RLS: permission denied for relation "your_table"

Set up an alert on the frequency of such messages to catch mis‑configurations or attempted bypasses.

1. End‑to‑end test with two users – using the Supabase JS client:

// User A signs in
const { data: sessionA } = await supabase.auth.signIn({ email: 'a@example.com', password: '…' });
// Attempt to read a row owned by User B
const { data, error } = await supabase
  .from('your_table')
  .select('*')
  .eq('id', 'known‑b‑row‑uuid');
// Expected: data = [] and error contains "RLS policy failed"

Repeat with User B’s session; the same query should return the row.

1. Local verification with Supabase CLI – start a local stack, push migrations, and invoke Auth functions:

supabase start
supabase db push
# In another terminal, obtain a JWT via the Auth API
curl -s -X POST "http://localhost:54321/auth/v1/token?grant_type=password&email=test@example.com&password=secret" | jq .access_token
# Use the token in a psql connection to test the policy
PGUSER=postgres PGHOST=localhost PGPORT=54322 PGDATABASE=postgres psql -c "SET jwt.token = ''; SELECT * FROM your_table WHERE id = '…';"

If the policy works, the query returns no rows for a token that does not match the owner_id.

Failure Modes and Redesign Triggers

• Missing or nullable owner_id – If the column is absent or can be NULL, the auth.uid() = owner_id condition may evaluate to UNKNOWN, allowing rows to leak. Ensure the column is NOT NULL and populated at insert time (e.g., via a BEFORE INSERT trigger or application default).
• Direct database access bypassing Auth – A user who connects to Postgres with a service role or superuser token will see auth.uid() return NULL, but if they explicitly set a custom GUC or use SET ROLE they could circumvent the policy. Limit direct DB connections to trusted services and audit role usage.
• Changing authentication provider – Moving to an external OIDC provider that does not issue JWTs compatible with Supabase Auth would break the auth.uid() function. In that case, redesign the policy to read the user identifier from a trusted claim (e.g., request.jwt.claims.sub) or introduce a mapping table.
• Performance pressure from complex policies – Policies that join many tables or use non‑indexed columns can degrade query throughput. Keep the policy simple, index the owner_id column, and consider partitioning if row counts grow very large.
• Regulatory need for encryption – RLS does not encrypt data at rest. If compliance requires column‑level encryption (e.g., PCI‑DSS), supplement RLS with pgcrypto or an application‑side encryption layer.

Limitations and Practical Verification

RLS operates at the query layer; it does not protect against data leakage via side channels (e.g., timing attacks) or through extensions that bypass the planner. Regularly review pg_stat_user_policies for unexpected spikes and pair RLS with network‑level controls (VPC, IP allow‑list).

To confirm that a policy is active after a change, run:

ALTER TABLE your_table DISABLE ROW LEVEL SECURITY; -- test bypass
ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;   -- re‑enable
SELECT * FROM pg_stat_user_policies WHERE relid = 'your_table'::regclass;

The counters should reset to zero after disabling and increase again after re‑enabling for legitimate traffic.

When to Re‑visit the Design

Re‑evaluate the RLS approach if any of the following occur:

• The data model evolves to require hierarchical ownership (e.g., team‑based access) that cannot be expressed with a simple owner_id equality.
• Performance profiling shows the policy contributing significantly to query latency despite indexing.
• Compliance mandates encrypt specific fields at rest, necessitating an additional encryption layer.
• You plan to deprecate Supabase Auth in favor of a custom authentication system that does not populate the auth.uid() GUC.

In each case, the smallest suitable design may need to be replaced by a more elaborate policy set, a row‑level encryption scheme, or a hybrid approach where the application enforces additional checks.