Choosing Row‑Level Security for Per‑User Data in Supabase

Decision

When you expose per‑user data through Supabase, the safest place to enforce ownership is the database itself using Postgres Row‑Level Security (RLS). RLS becomes the single, auditable boundary for CRUD operations performed by users. Edge Functions or server‑side code that runs with the service_role key should be reserved for privileged workflows that cannot be expressed in pure SQL.

Constraints

• Each data row must contain a stable user_id that matches the authenticated Supabase auth.uid().
• Only authenticated users may perform CRUD; unauthenticated requests must be denied.
• Privileged operations (e.g., cross‑user data aggregation, admin jobs) must run on a trusted server with the service_role key and never expose that key to the client.
• Policies must be explicitly tested for both positive and negative cases.

Supported Options

Option	Security Boundary	Best Use	Main Cost
Postgres RLS	Database	Direct user CRUD	Policy design & negative testing
Edge Function proxy	Function / Server	Complex validation or orchestration	Extra hop & server runtime
service_role server code	Trusted server	Admin jobs & cross‑user workflows	Key exposure risk; bypasses RLS

Trade‑offs

• RLS keeps the Supabase client API simple, but every new table requires a policy and tests. Mis‑configured policies can expose data or overly restrict access.
• Edge Functions add an extra network hop and runtime cost, but allow you to hide secrets (API keys, third‑party tokens) and coordinate multi‑step logic that the database cannot express.
• service_role is the most powerful and dangerous option. It bypasses RLS entirely, so any mistake can grant full access to all data. Use only in a tightly controlled server environment and never expose the key to clients.

Concrete Implementation

Below is a minimal schema for a todos table that enforces per‑user ownership using RLS. The policies are written so that auth.uid() (the authenticated Supabase user ID) is always compared to the row’s user_id.

-- 1. Create the table with a default user_id
CREATE TABLE public.todos (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL DEFAULT auth.uid(),
  title       TEXT NOT NULL,
  completed   BOOLEAN NOT NULL DEFAULT FALSE
);

-- 2. Enable RLS on the table
ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;

-- 3. Policy: only authenticated users can read their own rows
CREATE POLICY todos_select_own
  ON public.todos
  FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id);

-- 4. Policy: only authenticated users can insert rows for themselves
CREATE POLICY todos_insert_own
  ON public.todos
  FOR INSERT
  TO authenticated
  WITH CHECK (auth.uid() = user_id);

-- 5. Policy: only authenticated users can update their own rows
CREATE POLICY todos_update_own
  ON public.todos
  FOR UPDATE
  TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- 6. Policy: only authenticated users can delete their own rows
CREATE POLICY todos_delete_own
  ON public.todos
  FOR DELETE
  TO authenticated
  USING (auth.uid() = user_id);

Key points:

• The WITH CHECK clause on INSERT and UPDATE prevents a malicious client from assigning a different user_id.
• All policies target the authenticated role, which Supabase maps to users who have logged in.
• Because auth.uid() is a built‑in function, you cannot override it from client code.

Validation Steps

1. Policy inspection: In the Supabase SQL editor run:

   SELECT * FROM pg_policies WHERE tablename = 'todos';
   

   Check that four policies exist and that each references auth.uid().
2. Positive test – per‑user CRUD:
   • Create two test users, A and B, and sign in separately in two browser windows or using supabase client.
   • Each user inserts a row. Verify that SELECT * FROM public.todos; returns only their own row.
3. Negative test – cross‑user access:
   • From user B, attempt to UPDATE or DELETE user A’s row. The operation should affect 0 rows or return an error.
   • From an unauthenticated session, try a SELECT and confirm it is denied.
4. Run the same tests in a staging environment after any policy change to catch regressions.

Edge Function or Service‑Role Usage

Only when you need to call external services, perform multi‑step workflows, or run admin jobs should you consider an Edge Function or service‑role server code. For example, an Edge Function could:

• Validate a file upload to a third‑party storage provider.
• Enrich data with a third‑party API before inserting into the database.

In such cases, the function should still rely on the database’s RLS for any data mutations, and it should never write data that bypasses the user_id check.

Limitations & Practical Checks

• RLS does not grant SQL privileges. If you restrict table access to specific roles, make sure the authenticated role has SELECT/INSERT/UPDATE/DELETE permissions.
• Service‑role keys must never be exposed to the browser or mobile bundles. Store them in a secure environment variable on a trusted server or in a Supabase Edge Function’s runtime secret store.
• Complex business logic that requires multiple database calls or external secrets should be handled in an Edge Function to keep the database policies simple.

Conclusion

For most per‑user CRUD scenarios, Postgres RLS is the most secure, auditable, and efficient boundary. Edge Functions and service‑role code are valuable extensions but carry higher risk and complexity. By designing your policies around auth.uid() and validating them with both positive and negative tests, you can confidently expose user data through Supabase while keeping the database the single source of truth for authorization.