🔐 Supabase Row-Level Security (RLS) Policies

Scope: MVP - Data Security Layer Status: Critical for multi-tenant isolation Database: PostgreSQL with RLS

Overview

Supabase RLS policies enforce tenant isolation at the database level, ensuring complete data separation between customers without application-level filtering.

Multi-Tenant Architecture

Core Principle

-- Every table has tenant_id
-- Every query automatically filtered by tenant
-- No data leakage possible

Tenant Identification

-- JWT contains tenant_id claim
CREATE OR REPLACE FUNCTION auth.tenant_id()
RETURNS UUID AS $$
  SELECT (current_setting('request.jwt.claims', true)::json->>'
    tenant_id')::UUID;
$$ LANGUAGE SQL STABLE;

-- Alternative: from auth.users metadata
CREATE OR REPLACE FUNCTION auth.tenant_id_from_user()
RETURNS UUID AS $$
  SELECT (auth.jwt() -> 'user_metadata' ->> 'tenant_id')::UUID;
$$ LANGUAGE SQL STABLE;

RLS Policy Templates

1. Basic Tenant Isolation

-- Enable RLS on table
ALTER TABLE policies ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their tenant's data
CREATE POLICY "Tenant Isolation" ON policies
  FOR ALL
  USING (tenant_id = auth.tenant_id());

-- Policy: Service role bypasses RLS
CREATE POLICY "Service Role Full Access" ON policies
  FOR ALL
  TO service_role
  USING (true);

2. Role-Based Access

-- Users table with roles
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('admin', 'manager', 'viewer')),
  tenant_id UUID NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- RLS policies by role
CREATE POLICY "Admins can manage users" ON users
  FOR ALL
  TO authenticated
  USING (
    tenant_id = auth.tenant_id() AND
    EXISTS (
      SELECT 1 FROM users u
      WHERE u.id = auth.uid()
      AND u.role = 'admin'
      AND u.tenant_id = auth.tenant_id()
    )
  );

CREATE POLICY "Users can view their tenant users" ON users
  FOR SELECT
  TO authenticated
  USING (tenant_id = auth.tenant_id());

CREATE POLICY "Users can update themselves" ON users
  FOR UPDATE
  TO authenticated
  USING (id = auth.uid())
  WITH CHECK (id = auth.uid() AND tenant_id = auth.tenant_id());

3. Document Access Control

-- Documents with ownership
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content JSONB,
  owner_id UUID REFERENCES users(id),
  shared_with UUID[], -- Array of user IDs
  is_public BOOLEAN DEFAULT false,
  tenant_id UUID NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Complex document access
CREATE POLICY "Document access" ON documents
  FOR SELECT
  TO authenticated
  USING (
    tenant_id = auth.tenant_id() AND (
      -- Owner can see
      owner_id = auth.uid() OR
      -- Shared users can see
      auth.uid() = ANY(shared_with) OR
      -- Public documents in tenant
      is_public = true OR
      -- Admins can see all
      EXISTS (
        SELECT 1 FROM users
        WHERE id = auth.uid()
        AND role = 'admin'
        AND tenant_id = auth.tenant_id()
      )
    )
  );

MVP Table Policies

1. Compliance Assessments

CREATE TABLE compliance_assessments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  framework TEXT NOT NULL,
  score NUMERIC(5,2),
  evidence JSONB,
  assessed_by UUID REFERENCES users(id),
  tenant_id UUID NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- View policy
CREATE POLICY "View assessments" ON compliance_assessments
  FOR SELECT TO authenticated
  USING (tenant_id = auth.tenant_id());

-- Create policy (managers and admins)
CREATE POLICY "Create assessments" ON compliance_assessments
  FOR INSERT TO authenticated
  WITH CHECK (
    tenant_id = auth.tenant_id() AND
    EXISTS (
      SELECT 1 FROM users
      WHERE id = auth.uid()
      AND role IN ('admin', 'manager')
      AND tenant_id = auth.tenant_id()
    )
  );

2. Tasks and Assignments

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  description TEXT,
  assigned_to UUID REFERENCES users(id),
  priority TEXT CHECK (priority IN ('critical', 'high', 'medium', 'low')),
  status TEXT DEFAULT 'pending',
  due_date DATE,
  tenant_id UUID NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Everyone can view tasks
CREATE POLICY "View tasks" ON tasks
  FOR SELECT TO authenticated
  USING (tenant_id = auth.tenant_id());

-- Assigned users can update their tasks
CREATE POLICY "Update assigned tasks" ON tasks
  FOR UPDATE TO authenticated
  USING (
    tenant_id = auth.tenant_id() AND
    assigned_to = auth.uid()
  )
  WITH CHECK (
    tenant_id = auth.tenant_id() AND
    assigned_to = auth.uid()
  );

-- Managers can manage all tasks
CREATE POLICY "Managers manage tasks" ON tasks
  FOR ALL TO authenticated
  USING (
    tenant_id = auth.tenant_id() AND
    EXISTS (
      SELECT 1 FROM users
      WHERE id = auth.uid()
      AND role IN ('admin', 'manager')
      AND tenant_id = auth.tenant_id()
    )
  );

3. Question Bank with Sharing

CREATE TABLE questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  canonical_question TEXT NOT NULL,
  answer JSONB,
  is_global BOOLEAN DEFAULT false, -- GetCimple provided
  tenant_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Complex policy for global + tenant questions
CREATE POLICY "Access questions" ON questions
  FOR SELECT TO authenticated
  USING (
    -- Global questions available to all
    is_global = true OR
    -- Tenant questions only for tenant
    (tenant_id = auth.tenant_id() AND is_global = false)
  );

-- Only tenant questions can be modified
CREATE POLICY "Modify tenant questions" ON questions
  FOR ALL TO authenticated
  USING (
    tenant_id = auth.tenant_id() AND
    is_global = false
  )
  WITH CHECK (
    tenant_id = auth.tenant_id() AND
    is_global = false
  );

Security Functions

1. Secure Tenant Creation

CREATE OR REPLACE FUNCTION create_tenant_and_admin(
  tenant_name TEXT,
  admin_email TEXT,
  admin_password TEXT
) RETURNS JSON AS $$
DECLARE
  new_tenant_id UUID;
  new_user_id UUID;
BEGIN
  -- Create tenant
  INSERT INTO tenants (name)
  VALUES (tenant_name)
  RETURNING id INTO new_tenant_id;

  -- Create admin user
  INSERT INTO auth.users (
    email, encrypted_password, email_confirmed_at, raw_user_meta_data
  )
  VALUES (
    admin_email,
    crypt(admin_password, gen_salt('bf')),
    NOW(),
    jsonb_build_object('tenant_id', new_tenant_id)
  )
  RETURNING id INTO new_user_id;

  -- Create user profile
  INSERT INTO users (id, email, role, tenant_id)
  VALUES (new_user_id, admin_email, 'admin', new_tenant_id);

  RETURN json_build_object(
    'tenant_id', new_tenant_id,
    'user_id', new_user_id,
    'message', 'Tenant and admin created successfully'
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

2. Cross-Tenant Security Check

-- Function to verify no cross-tenant access
CREATE OR REPLACE FUNCTION verify_tenant_isolation(
  table_name TEXT,
  user_id UUID
) RETURNS BOOLEAN AS $$
DECLARE
  user_tenant UUID;
  other_tenant_count INTEGER;
BEGIN
  -- Get user's tenant
  SELECT tenant_id INTO user_tenant
  FROM users WHERE id = user_id;

  -- Check if user can see other tenant data
  EXECUTE format('
    SELECT COUNT(*) FROM %I
    WHERE tenant_id != $1
  ', table_name)
  INTO other_tenant_count
  USING user_tenant;

  -- Should always be 0
  RETURN other_tenant_count = 0;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Testing RLS Policies

1. Unit Tests

-- Test tenant isolation
DO $$
DECLARE
  tenant1_id UUID := gen_random_uuid();
  tenant2_id UUID := gen_random_uuid();
  user1_id UUID := gen_random_uuid();
  user2_id UUID := gen_random_uuid();
BEGIN
  -- Create test data
  INSERT INTO users (id, email, role, tenant_id)
  VALUES
    (user1_id, 'user1@test.com', 'admin', tenant1_id),
    (user2_id, 'user2@test.com', 'admin', tenant2_id);

  INSERT INTO policies (title, tenant_id)
  VALUES
    ('Tenant 1 Policy', tenant1_id),
    ('Tenant 2 Policy', tenant2_id);

  -- Set session to user1
  PERFORM set_config('request.jwt.claims',
    json_build_object('tenant_id', tenant1_id, 'sub', user1_id)::text, true);

  -- User1 should only see 1 policy
  IF (SELECT COUNT(*) FROM policies) != 1 THEN
    RAISE EXCEPTION 'RLS policy failed: user can see other tenant data';
  END IF;

  RAISE NOTICE 'RLS tests passed';
END $$;

2. Integration Tests

// Supabase client tests
import { createClient } from '@supabase/supabase-js'

describe('RLS Policies', () => {
  it('enforces tenant isolation', async () => {
    // Login as tenant1 user
    const {
      data: { user },
    } = await supabase.auth.signIn({
      email: 'user1@tenant1.com',
      password: 'password',
    })

    // Try to access all policies
    const { data: policies } = await supabase.from('policies').select('*')

    // Should only see tenant1 policies
    expect(
      policies.every((p) => p.tenant_id === user.user_metadata.tenant_id)
    ).toBe(true)
  })

  it('prevents cross-tenant updates', async () => {
    // Try to update another tenant's data
    const { error } = await supabase
      .from('policies')
      .update({ title: 'Hacked!' })
      .eq('id', 'other-tenant-policy-id')

    expect(error.code).toBe('42501') // Insufficient privilege
  })
})

Performance Considerations

1. Index Strategy

-- Always index tenant_id
CREATE INDEX idx_policies_tenant_id ON policies(tenant_id);
CREATE INDEX idx_users_tenant_id ON users(tenant_id);
CREATE INDEX idx_tasks_tenant_id ON tasks(tenant_id);

-- Composite indexes for common queries
CREATE INDEX idx_tasks_tenant_assigned ON tasks(tenant_id, assigned_to);
CREATE INDEX idx_documents_tenant_owner ON documents(tenant_id, owner_id);

2. Query Optimization

-- Bad: Filtering after fetch
SELECT * FROM policies WHERE tenant_id = 'uuid';

-- Good: RLS automatically applies filter
SELECT * FROM policies; -- tenant_id filter applied by RLS

-- Monitor RLS performance
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM policies;

Migration Safety

1. Adding RLS to Existing Tables

-- Safe migration process
BEGIN;
  -- 1. Add tenant_id if missing
  ALTER TABLE existing_table
    ADD COLUMN tenant_id UUID;

  -- 2. Backfill tenant_id
  UPDATE existing_table
    SET tenant_id = (SELECT tenant_id FROM users WHERE ...);

  -- 3. Make NOT NULL
  ALTER TABLE existing_table
    ALTER COLUMN tenant_id SET NOT NULL;

  -- 4. Enable RLS
  ALTER TABLE existing_table ENABLE ROW LEVEL SECURITY;

  -- 5. Add policies
  CREATE POLICY "Tenant Isolation" ON existing_table
    FOR ALL USING (tenant_id = auth.tenant_id());

  -- 6. Test before commit
  -- Run tests here
COMMIT;

Monitoring & Alerts

1. RLS Bypass Detection

-- Log any service_role usage
CREATE OR REPLACE FUNCTION log_service_role_access()
RETURNS TRIGGER AS $$
BEGIN
  IF current_user = 'service_role' THEN
    INSERT INTO audit_log (action, table_name, user_role, timestamp)
    VALUES (TG_OP, TG_TABLE_NAME, current_user, NOW());
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. Failed Access Attempts

-- Monitor RLS violations
CREATE TABLE rls_violations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  table_name TEXT,
  operation TEXT,
  attempted_at TIMESTAMPTZ DEFAULT NOW()
);

Checklist for New Tables

• Add tenant_id UUID NOT NULL
• Create index on tenant_id
• Enable RLS: ALTER TABLE x ENABLE ROW LEVEL SECURITY
• Add tenant isolation policy
• Add role-based policies if needed
• Test with multiple tenants
• Add to migration scripts
• Document special access rules

---

Related Documents:

• Database Design MVP
• Authentication MVP
• API Design MVP