Supabase Row Level Security: Minimal Design and Operational Guide

Requirements

Supabase Row Level Security (RLS) must enforce per‑user data isolation directly in PostgreSQL so that the application layer does not need to add manual WHERE clauses. The solution should:

• Use JWT claims (e.g., user_id, role) supplied by Supabase Auth’s GoTrue service to decide visibility.
• Allow service roles or trusted admin processes to bypass RLS when they present a valid service_role key.
• Work without application‑level filtering, keeping the trust boundary at the database.

Smallest Suitable Design

The minimal implementation consists of two steps:

1. Enable RLS on the target table.
2. Create a single policy that uses the auth.uid() function (which extracts the user ID from the JWT) to compare against a column that stores the owner ID.

SQL example (run in the Supabase SQL editor or via psql with sufficient privileges):

-- Enable RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Create a policy that restricts rows to the owning user
CREATE POLICY profiles_owner_policy
ON profiles
FOR ALL
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

Here user_id is a uuid column in profiles that stores the owner’s identifier. The policy applies to SELECT, INSERT, UPDATE, and DELETE operations.

Trust and Data Boundaries

The database trusts the JWT signed by Supabase Auth’s GoTrue service. Consequently:

• Application servers must never alter the token; they should forward it unchanged to the Supabase client libraries.
• Any direct database connection (e.g., a script, admin tool, or third‑party service) must present either a valid user JWT or the service_role key to bypass RLS. Presenting no token or an invalid token results in a 401 error.
• The service_role key grants super‑user‑like access; it should be stored only in trusted environments (CI/CD pipelines, admin dashboards) and never exposed to end‑users.

Operational Checks

To verify that the policy behaves as intended:

1. List defined policies: SELECT * FROM pg_policies WHERE tablename = 'profiles';
2. Test with an authenticated user: obtain a JWT via the Supabase client (supabase.auth.signIn({ email, password })), then run SELECT * FROM profiles; and confirm only rows where user_id = auth.uid() are returned.
3. Test with an anonymous request (no token): the same query should return zero rows.
4. Test with the service_role key: initialize a Supabase client with supabase.createClient(url, service_role_key) and verify that all rows are visible, confirming the bypass.
5. Monitor for sequential scans that may indicate missing indexes: SELECT schemaname, relname, seq_scan FROM pg_stat_user_tables WHERE relname = 'profiles'; A high seq_scan relative to idx_scan suggests the planner cannot use an index on the user_id column.

Failure Modes

Common ways the design can break:

• Mis‑written USING/WITH CHECK: Using OR conditions or referencing non‑deterministic functions can cause the planner to ignore indexes, leading to either data leakage (if the condition is too permissive) or lock‑out (if it is too restrictive).
• Compromised JWT signing key: If an attacker extracts the GoTrue signing secret, they can forge valid tokens and escalate privileges.
• Network partition preventing token validation: Supabase edge functions that validate JWTs may become unavailable, causing authentication requests to return 401 even for legitimate users.
• Missing index on the policy column: Without an index on user_id, each query may perform a full table scan, degrading performance as the table grows.

Conditions That Would Change the Design

The minimal policy may need to evolve when:

1. Cross‑tenant sharing is required: Add a tenant_id column and extend the policy to USING (auth.uid() = user_id OR (auth.role() = 'service_role' AND tenant_id = current_tenant_id())) or similar, depending on the sharing model.
2. Offline sync or edge caching: If the client must filter data locally before sending to the server, the trust boundary moves to the application layer, and RLS may be relaxed or complemented with client‑side checks.
3. Performance‑critical workloads: For read‑heavy reporting, consider materialized views or indexed views that pre‑filter data, allowing the planner to use index‑only scans while still enforcing RLS through a wrapper policy that checks the view’s security barrier.

Example Configuration and Verification

Below is a end‑to‑end checklist you can follow in a fresh Supabase project:

1. Create a table:

CREATE TABLE documents (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id uuid NOT NULL,
    title text,
    content text,
    created_at timestamptz DEFAULT now()
);

2. Enable RLS and attach the policy:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY doc_owner_policy
ON documents
FOR ALL
USING (auth.uid() = owner_id)
WITH CHECK (auth.uid() = owner_id);

3. Insert a row as user A (via Supabase client):

// JavaScript example
const { data, error } = await supabase
  .from('documents')
  .insert([{ owner_id: supabase.auth.user().id, title: 'Private note' }]);

4. Attempt to read the row as user B (different JWT): the query should return empty.
5. Read with the service_role key: the row should appear, confirming the bypass.
6. Check index usage: EXPLAIN ANALYZE SELECT * FROM documents WHERE owner_id = auth.uid(); Look for an Index Scan on owner_id; if you see a Seq Scan, create an index: CREATE INDEX idx_documents_owner ON documents(owner_id);

Limitations and Practical Verification

While the described design satisfies the core requirements, be aware of these limits:

• RLS adds planner overhead; keep policies simple (single equality check) and index the columns used in USING and WITH CHECK.
• Supabase Auth JWTs have a limited TTL (default 60 minutes). Long‑running background jobs must refresh tokens; otherwise, policy enforcement will suddenly fail with 401 errors.
• The service_role key bypasses RLS entirely. Treat it as a secret credential; rotate it if exposure is suspected and restrict its use to trusted networks or secret‑management systems.

To verify that your setup remains safe over time, schedule a periodic job that:

1. Runs SELECT * FROM pg_policies; to confirm no unexpected policies have been added.
2. Executes a read‑only query as a known user and asserts that the row count matches the expected owner‑only count.
3. Checks the pg_stat_user_tables view for rising seq_scan values and triggers an alert if a threshold is exceeded.