Supabase Row-Level Security in Production: The Complete Guide for Multi-Tenant Apps

Enabling RLS with no policies means ZERO rows are returned for any query — including your own authenticated requests. Always write your SELECT policy immediately after enabling RLS.

-- Enable RLS on a table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
 
-- Until you add a policy, this returns 0 rows:
SELECT * FROM documents; -- returns nothing
 
-- Add a basic policy immediately:
CREATE POLICY "Users see their own documents"
  ON documents FOR SELECT
  USING (auth.uid() = user_id);

-- auth.uid() returns the UUID from the verified JWT
-- It returns NULL for unauthenticated (anon) requests
 
SELECT auth.uid(); -- returns your user UUID if authenticated
 
-- Check current role (anon vs authenticated)
SELECT current_user; -- 'anon' or 'authenticated'

auth.uid() returns NULL for requests using the anon key without a logged-in user. Always test your policies with both authenticated and unauthenticated requests.

-- Incomplete: only controls visibility, not writes
CREATE POLICY "Users see their documents"
  ON documents FOR SELECT
  USING (auth.uid() = user_id);
 
-- Complete: controls both reads AND writes
CREATE POLICY "Users manage their own documents"
  ON documents
  USING (auth.uid() = user_id)          -- what you can read/delete
  WITH CHECK (auth.uid() = user_id);    -- what you can insert/update

Without WITH CHECK on INSERT, a user can insert a row with user_id set to someone else's UUID — effectively writing data into another user's namespace. Always include WITH CHECK on INSERT and UPDATE policies.

-- organisations table
CREATE TABLE organisations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- membership junction table
CREATE TABLE org_members (
  org_id UUID REFERENCES organisations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member', -- 'admin' | 'member'
  PRIMARY KEY (org_id, user_id)
);
 
-- your data table
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID REFERENCES organisations(id) ON DELETE CASCADE,
  created_by UUID REFERENCES auth.users(id),
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
 
-- SELECT: members of the org can read all org documents
CREATE POLICY "Org members can read documents"
  ON documents FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM org_members
      WHERE org_members.org_id = documents.org_id
        AND org_members.user_id = auth.uid()
    )
  );
 
-- INSERT: members can create documents in their org
CREATE POLICY "Org members can create documents"
  ON documents FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM org_members
      WHERE org_members.org_id = documents.org_id
        AND org_members.user_id = auth.uid()
    )
  );
 
-- DELETE: only admins can delete
CREATE POLICY "Org admins can delete documents"
  ON documents FOR DELETE
  USING (
    EXISTS (
      SELECT 1 FROM org_members
      WHERE org_members.org_id = documents.org_id
        AND org_members.user_id = auth.uid()
        AND org_members.role = 'admin'
    )
  );

-- Without this index, the EXISTS subquery does a sequential scan
CREATE INDEX idx_org_members_user_org
  ON org_members (user_id, org_id);
 
-- Also index the foreign key on your data table
CREATE INDEX idx_documents_org_id ON documents (org_id);

-- Create a function that adds org_ids to the JWT
CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event JSONB)
RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE
  claims JSONB;
  org_ids UUID[];
BEGIN
  -- Fetch this user's org memberships
  SELECT ARRAY_AGG(org_id) INTO org_ids
  FROM org_members
  WHERE user_id = (event->>'user_id')::UUID;
 
  claims := event->'claims';
  claims := jsonb_set(claims, '{org_ids}', to_jsonb(org_ids));
  RETURN jsonb_set(event, '{claims}', claims);
END;
$$;
 
-- Register the hook in Supabase Dashboard:
-- Authentication > Hooks > Custom Access Token Hook

-- Now your policy can check the JWT claim directly — no subquery
CREATE POLICY "Org members can read documents (JWT)"
  ON documents FOR SELECT
  USING (
    org_id = ANY(
      ARRAY(SELECT jsonb_array_elements_text(
        (auth.jwt()->'org_ids')
      )::UUID)
    )
  );

JWT custom claims eliminate per-row subqueries entirely. For applications with large datasets and frequent reads, this is the correct production approach.

-- Test as a specific user (replace UUID with a real user ID)
SET LOCAL role = 'authenticated';
SET LOCAL request.jwt.claims = '{"sub": "user-uuid-here", "role": "authenticated"}';
 
-- Now run your query — it will be evaluated with that user's context
SELECT * FROM documents;
 
-- Test as anonymous
SET LOCAL role = 'anon';
SELECT * FROM documents; -- should return 0 rows if anon is blocked

Never expose the service role key in client-side code or environment variables accessible to the browser. It bypasses all RLS policies. Treat it like a database root password.

# Server-side only — uses service role key to bypass RLS
from supabase import create_client
 
supabase_admin = create_client(
    os.environ['SUPABASE_URL'],
    os.environ['SUPABASE_SERVICE_ROLE_KEY']  # server-side only
)
 
# Client-side — uses anon key, RLS applies
supabase_client = create_client(
    os.environ['SUPABASE_URL'],
    os.environ['NEXT_PUBLIC_SUPABASE_ANON_KEY']  # safe for browser
)

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;
 
-- Embeddings table with org isolation
CREATE TABLE document_embeddings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
  org_id UUID REFERENCES organisations(id) ON DELETE CASCADE,
  embedding VECTOR(1536),
  content_chunk TEXT
);
 
ALTER TABLE document_embeddings ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY "Org members see their embeddings"
  ON document_embeddings FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM org_members
      WHERE org_members.org_id = document_embeddings.org_id
        AND org_members.user_id = auth.uid()
    )
  );
 
-- Semantic search is now automatically org-scoped:
SELECT content_chunk, 1 - (embedding <=> query_embedding) AS similarity
FROM document_embeddings
ORDER BY embedding <=> query_embedding
LIMIT 5;
-- RLS ensures this only returns chunks from the user's org

RLS applies to pgvector similarity searches automatically. The <=> operator query goes through normal PostgreSQL row filtering — so your RLS policies protect embeddings the same way they protect regular rows.