Choosing Between Permissive and Restrictive Row Level Security Policies in Supabase

Decision and constraints

You need to enforce data isolation in a Supabase-backed application so that each authenticated user can only see and modify their own rows. The decision is whether to implement this with permissive RLS policies (OR‑combined) or restrictive policies (AND‑combined). Constraints include:

• Security must hold regardless of client code.
• Performance should stay predictable as the table grows.
• Implementation should be simple to audit and maintain.

Comparison of supported options

Policy type	How it works	Typical use case	Main advantage	Main drawback
Permissive	Access is granted if any policy evaluates to TRUE. Policies are additive.	Granting broad access to roles (e.g., admin, public) where multiple conditions can independently allow the operation.	Easy to compose; you can add a new policy without removing existing ones.	Risk of unintentionally widening access if a permissive policy is too broad.
Restrictive	Access is granted only if all policies evaluate to TRUE. Policies are conjunctive.	Enforcing strict rules such as “owner‑only” where every condition must be satisfied.	Provides a clear, minimal privilege set; harder to accidentally over‑grant.	Requires careful design; missing a condition blocks legitimate access.

Trade‑offs

For owner‑only access, a restrictive approach is usually safer because it forces every condition (e.g., valid JWT auth.uid() matches user_id) to be true. A permissive setup would need a single policy that already encodes the owner check; adding another permissive policy later could unintentionally bypass the owner check. The downside is that restrictive policies can become harder to read when many conditions are required, but for a simple owner check the readability remains high.

Concrete implementation: owner‑only read/write policy

The following steps show how to enable RLS and create a restrictive policy that limits SELECT, INSERT, UPDATE, and DELETE to rows where the user_id column matches the authenticated user’s ID.

1. Enable RLS on the table

Run this SQL in the Supabase SQL editor (requires database admin privileges).

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

2. Create a restrictive policy

The policy uses auth.uid() to compare the JWT subject with the user_id column. All four commands are combined in a single policy for brevity.

CREATE POLICY owner_access ON profiles
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

3. Verify the policy

1. Obtain a user JWT (e.g., via supabase.auth.signIn) and attempt to read rows with the JS client:

const { data, error } = await supabase.from('profiles').select('*');
// Expect data only for rows where user_id matches the JWT.

1. Repeat the request using the anon key (no JWT). The response should be empty or an error, because no permissive policy exists for the anonymous role.

const anonClient = createClient(SUPABASE_URL, SUPABASE_ANON_KEY);
const { data, error } = await anonClient.from('profiles').select('*');
// Expect empty data or permission denied.

1. Confirm that a service_role key bypasses RLS:

const adminClient = createClient(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY);
const { data, error } = await adminClient.from('profiles').select('*');
// Expect all rows regardless of user_id.

Limitations and practical checks

• RLS is disabled by default; forgetting to ENABLE ROW LEVEL SECURITY leaves the table open if the API is exposed.
• Complex policies that include subqueries or joins can cause the planner to re‑evaluate the policy for each row, potentially degrading performance on large tables. Keep policies simple (e.g., direct column comparison).
• Recursive policies (where a policy queries the same table it protects) can lead to infinite loops. Avoid referencing the protected table in the policy definition unless you use a security barrier view or a separate helper table.
• To check that RLS is active, run:

SELECT relrowsecurity FROM pg_class WHERE relname = 'profiles';

The result should be true. Additionally, you can examine active policies with:

SELECT policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies WHERE tablename = 'profiles';

When to choose each approach

• Use permissive policies when you need to grant access based on any of several independent conditions (e.g., admin OR team member OR public read).
• Use restrictive policies when every condition must hold for access (e.g., owner‑only, multi‑tenant where tenant_id AND user_id must match).

By following the steps above you can enforce owner‑only data access in Supabase with a clear, auditable restrictive RLS policy.