Row-Level Security (RLS) Policies Documentation

Version: 2.0.0 Last Updated: December 18, 2025 Status: Production Active

Table of Contents

1. Overview
2. Multi-Tenant Data Isolation Strategy
3. Authentication Context
4. Core RLS Policy Patterns
5. Client-Based Access Control
6. Company-Based Access Control
7. Portal-Based Access Control
8. Table-Specific RLS Documentation
9. How to Add New RLS Policies
10. Testing RLS Policies
11. Performance Optimization
12. Common Security Patterns
13. Troubleshooting

---

Overview

Row-Level Security (RLS) is a PostgreSQL feature that restricts which rows users can access in a table. Petunia uses RLS to enforce multi-tenant data isolation at the database level, ensuring users can only access data belonging to their organizations.

Why RLS?

• Defense in Depth: Even if application-level checks fail, the database enforces access control
• Zero Trust: Database doesn't trust the application layer with access decisions
• Compliance: Meets SOC 2 and GDPR requirements for data isolation
• Performance: Database-native filtering is faster than application-level filtering

Current Status

As of December 2025, RLS is enabled on all core business tables:

Core Business Tables:

• Contact - Customer contact information
• Client - Business client records
• Company - Organization/company records
• Message - Communication messages
• Lead - Sales lead records (Company + Client + Portal access)

Portal & Communication Tables (Added December 18, 2025):

• UserPortalAccess - Junction table for user-portal relationships
• Portal - Portal configuration and settings
• Conversation - Chat/messaging conversations

Infrastructure Tables:

• client_api_keys - API authentication keys
• platform_key_usage - API usage tracking
• _prisma_migrations - Migration tracking (service role only)

Migration Files:

• /prisma/migrations/20251205_apply_core_rls_policies/migration.sql
• /prisma/migrations/20251205_apply_api_key_usage_rls/migration.sql
• /prisma/migrations/20251213_apply_lead_rls_policies/migration.sql
• /prisma/migrations/20251218_apply_portal_access_rls/migration.sql
• /prisma/migrations/20251218_fix_rls_policy_warnings/migration.sql
• /prisma/migrations/20251218_fix_rls_for_all_policies/migration.sql
• /supabase/migrations/20251205_apply_core_rls_policies.sql
• /supabase/migrations/20251205_apply_api_key_usage_rls.sql

---

Multi-Tenant Data Isolation Strategy

Petunia implements a hybrid multi-tenancy model with two isolation dimensions:

1. Company-Based Isolation (Primary)

Users belong to Companies via the UserCompany junction table:

model UserCompany {
  id        String   @id
  userId    String
  companyId String
  role      String   @default("USER")
  createdAt DateTime @default(now())
  updatedAt DateTime
}

Access Pattern:

• Users can only access data where table.companyId matches their UserCompany.companyId
• Companies are the primary tenant boundary
• Most tables use companyId for isolation

2. Client-Based Isolation (Secondary)

Users can also have direct access to specific Clients via UserClient:

model UserClient {
  id        String   @id
  userId    String
  clientId  String
  role      String
  createdAt DateTime @default(now())
  updatedAt DateTime
}

Access Pattern:

• Users can access data where table.clientId matches their UserClient.clientId
• Used for granular access to specific business locations
• Typically combines with Company-based access using OR

3. Portal-Based Isolation (Tertiary)

For portal-specific features, users access via UserPortalAccess:

model UserPortalAccess {
  id        String   @id
  userId    String
  portalId  String
  role      String   @default("user")
  createdAt DateTime @default(now())
  updatedAt DateTime
}

Access Pattern:

• Used for portal-specific features (inbox, autoresponder, etc.)
• Clients have one Portal per location
• Combines with Client/Company-based access

Isolation Hierarchy

User
├─ UserCompany (1:N) → Company
│  └─ Clients (1:N) → Client
│     └─ Portal (1:1) → Portal
└─ UserClient (1:N) → Client (direct access)
   └─ Portal (1:1) → Portal

---

Authentication Context

RLS policies use Supabase's authentication context to identify the current user.

Supabase Auth Helpers

Two helper functions extract user information from JWT claims:

auth.uid() - Get Current User ID

CREATE FUNCTION auth.uid() RETURNS uuid
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
  WITH claims AS (
    SELECT COALESCE(current_setting('request.jwt.claims', true), '{}')::jsonb AS jwt
  )
  SELECT NULLIF(
    COALESCE(
      (SELECT jwt ->> 'sub' FROM claims),
      (SELECT jwt ->> 'user_id' FROM claims),
      (SELECT jwt ->> 'userId' FROM claims)
    ),
    ''
  )::uuid;
$$;

Returns: UUID of the authenticated user from JWT sub claim

auth.role() - Get Current User Role

CREATE FUNCTION auth.role() RETURNS text
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
  WITH claims AS (
    SELECT COALESCE(current_setting('request.jwt.claims', true), '{}')::jsonb AS jwt
  )
  SELECT COALESCE(
    (SELECT jwt ->> 'role' FROM claims),
    current_setting('role', true),
    'anon'
  );
$$;

Returns: Role from JWT claims (authenticated, anon, or service_role)

Supabase Roles

• authenticated - Logged-in users (most API requests)
• anon - Anonymous users (public endpoints)
• service_role - Backend services (bypasses RLS)

---

Core RLS Policy Patterns

All RLS policies follow standardized patterns for consistency and security.

Pattern 1: SELECT Policy (View Access)

Users can view records if they have Company OR Client access:

CREATE POLICY "Users can view contacts for their companies" ON "Contact"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Contact"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "Contact"."clientId"
      AND ucl."userId" = (select auth.uid())::text
    )
  );

Key Points:

• FOR SELECT USING - Controls which rows users can read
• EXISTS subquery - Efficient check for user membership
• (select auth.uid())::text - Wrapped in subquery for performance
• OR logic - Users get access via Company OR Client membership

Pattern 2: INSERT Policy (Create Access)

Users can create records if they have Company OR Client access:

CREATE POLICY "Users can insert contacts for their companies" ON "Contact"
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Contact"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "Contact"."clientId"
      AND ucl."userId" = (select auth.uid())::text
    )
  );

Key Points:

• FOR INSERT WITH CHECK - Validates new rows before insertion
• Same access logic as SELECT for consistency
• Prevents users from creating records in unauthorized tenants

Pattern 3: UPDATE Policy (Modify Access)

Users can update records they can already see:

CREATE POLICY "Users can update contacts for their companies" ON "Contact"
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Contact"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "Contact"."clientId"
      AND ucl."userId" = (select auth.uid())::text
    )
  );

Key Points:

• FOR UPDATE USING - Controls which existing rows can be modified
• Typically mirrors SELECT policy logic
• Use WITH CHECK if you need to validate updated values

Pattern 4: DELETE Policy (Remove Access)

Similar to UPDATE, controls which rows can be deleted:

CREATE POLICY "Users can delete API keys for their clients" ON public.client_api_keys
  FOR DELETE USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      JOIN "Client" c ON c."companyId" = uc."companyId"
      WHERE c."id" = client_api_keys.client_id
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = client_api_keys.client_id
      AND ucl."userId" = (select auth.uid())::text
    )
  );

Pattern 5: Service Role Bypass

Backend services need full access to all data:

CREATE POLICY "Service role full access - Contact" ON "Contact"
  FOR ALL USING ((select auth.role()) = 'service_role');

Key Points:

• FOR ALL - Applies to all operations (SELECT, INSERT, UPDATE, DELETE)
• auth.role() = 'service_role' - Only service role bypasses RLS
• Always include this policy for operational flexibility

Pattern 6: Creator-Only Access

Users can only modify records they created:

CREATE POLICY "Users can insert companies" ON "Company"
  FOR INSERT WITH CHECK (
    (select auth.uid())::text = "createdByUserId"
  );

CREATE POLICY "Users can update their companies" ON "Company"
  FOR UPDATE USING (
    (select auth.uid())::text = "createdByUserId"
    OR EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Company"."id"
      AND uc."userId" = (select auth.uid())::text
    )
  );

Use Cases:

• Prevent unauthorized company creation
• Allow creators to maintain their companies
• Combine with membership checks for team access

---

Client-Based Access Control

When to Use Client-Based Isolation

Use clientId-based RLS when:

• Data belongs to a specific business location
• Users need granular access to individual clients
• Multi-location businesses need separate access control
• Portal-specific features require client context

Example: Contact Table

-- SELECT: Users can view contacts if they have access to the client
CREATE POLICY "Users can view contacts for their companies" ON "Contact"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Contact"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "Contact"."clientId"
      AND ucl."userId" = (select auth.uid())::text
    )
  );

Example: Client API Keys

-- SELECT: Users can view API keys for clients they manage
CREATE POLICY "Users can view API keys for their clients" ON public.client_api_keys
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      JOIN "Client" c ON c."companyId" = uc."companyId"
      WHERE c."id" = client_api_keys.client_id
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = client_api_keys.client_id
      AND ucl."userId" = (select auth.uid())::text
    )
  );

Key Differences:

• Requires JOIN through Client table for company-based access
• Direct clientId match for client-based access
• More complex but provides granular control

---

Company-Based Access Control

When to Use Company-Based Isolation

Use companyId-based RLS when:

• Data is shared across all clients in an organization
• Users should have company-wide access
• Data naturally belongs to the organization level
• Simplicity and performance are priorities

Example: Company Table

-- SELECT: Users can only view companies they belong to
CREATE POLICY "Users can view their companies" ON "Company"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Company"."id"
      AND uc."userId" = (select auth.uid())::text
    )
  );

-- INSERT: Users can create companies (become creator)
CREATE POLICY "Users can insert companies" ON "Company"
  FOR INSERT WITH CHECK (
    (select auth.uid())::text = "createdByUserId"
  );

-- UPDATE: Creators and members can update
CREATE POLICY "Users can update their companies" ON "Company"
  FOR UPDATE USING (
    (select auth.uid())::text = "createdByUserId"
    OR EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Company"."id"
      AND uc."userId" = (select auth.uid())::text
    )
  );

Example: Client Table

-- SELECT: Users can view clients if they belong to the company
CREATE POLICY "Users can view clients for their companies" ON "Client"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Client"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "Client"."id"
      AND ucl."userId" = (select auth.uid())::text
    )
  );

-- INSERT: Only company members can create clients
CREATE POLICY "Users can insert clients for their companies" ON "Client"
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Client"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
  );

Key Differences:

• Simpler queries (no JOINs needed)
• Broader access scope (all company data)
• Better performance for company-wide queries

---

Portal-Based Access Control

When to Use Portal-Based Isolation

Use portalId-based RLS when:

• Data is specific to a portal (location/business unit)
• Users access features through UserPortalAccess junction table
• Conversations, inbox messages, or portal-specific settings

Access Pattern

Users access portal data through the UserPortalAccess junction table:

EXISTS (
  SELECT 1 FROM "UserPortalAccess" upa
  WHERE upa."portalId" = "YourTable"."portalId"
  AND upa."userId" = (select auth.uid())::text
)

Consolidated Policy Pattern (December 2025)

Important: As of December 18, 2025, we use consolidated policies that combine user access with service role bypass in a single policy. This eliminates Supabase warnings about multiple permissive policies.

-- ✅ CORRECT: Combined policy (no warnings)
CREATE POLICY "YourTable select policy" ON "YourTable"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserPortalAccess" upa
      WHERE upa."portalId" = "YourTable"."portalId"
      AND upa."userId" = (select auth.uid())::text
    )
    OR (select auth.role()) = 'service_role'
  );

-- ❌ DEPRECATED: Separate policies (causes warnings)
CREATE POLICY "Users can view..." ON "YourTable" FOR SELECT USING (...);
CREATE POLICY "Service role full access" ON "YourTable" FOR ALL USING (...);

---

Table-Specific RLS Documentation

This section documents the RLS policies for each table with RLS enabled.

UserPortalAccess

Purpose: Junction table controlling user access to portals. Critical for multi-tenant isolation.

Policies:

Policy Name	Action	Description
UserPortalAccess select policy	SELECT	Users can view their own access records OR service role can view all
UserPortalAccess insert policy	INSERT	Service role only (users don't self-grant access)
UserPortalAccess update policy	UPDATE	Service role only
UserPortalAccess delete policy	DELETE	Service role only

SQL:

-- SELECT: Users see own records, service role sees all
CREATE POLICY "UserPortalAccess select policy" ON "UserPortalAccess"
  FOR SELECT USING (
    "userId" = (select auth.uid())::text
    OR (select auth.role()) = 'service_role'
  );

-- INSERT/UPDATE/DELETE: Service role only
CREATE POLICY "UserPortalAccess insert policy" ON "UserPortalAccess"
  FOR INSERT WITH CHECK ((select auth.role()) = 'service_role');

CREATE POLICY "UserPortalAccess update policy" ON "UserPortalAccess"
  FOR UPDATE USING ((select auth.role()) = 'service_role');

CREATE POLICY "UserPortalAccess delete policy" ON "UserPortalAccess"
  FOR DELETE USING ((select auth.role()) = 'service_role');

Why:

• Users should only see their own access records (not other users' permissions)
• Prevents privilege escalation (users can't grant themselves portal access)
• Service role manages access during onboarding and admin operations

Related Tables:

• Portal (via portalId)
• User (via userId)

---

Portal

Purpose: Portal configuration and settings for each business location.

Policies:

Policy Name	Action	Description
Portal select policy	SELECT	Users with portal access OR service role
Portal update policy	UPDATE	Users with portal access OR service role
Portal insert policy	INSERT	Service role only (portals created during onboarding)
Portal delete policy	DELETE	Service role only

SQL:

-- SELECT: Users with access via UserPortalAccess
CREATE POLICY "Portal select policy" ON "Portal"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserPortalAccess" upa
      WHERE upa."portalId" = "Portal"."id"
      AND upa."userId" = (select auth.uid())::text
    )
    OR (select auth.role()) = 'service_role'
  );

-- UPDATE: Same access as SELECT
CREATE POLICY "Portal update policy" ON "Portal"
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM "UserPortalAccess" upa
      WHERE upa."portalId" = "Portal"."id"
      AND upa."userId" = (select auth.uid())::text
    )
    OR (select auth.role()) = 'service_role'
  );

-- INSERT/DELETE: Service role only
CREATE POLICY "Portal insert policy" ON "Portal"
  FOR INSERT WITH CHECK ((select auth.role()) = 'service_role');

CREATE POLICY "Portal delete policy" ON "Portal"
  FOR DELETE USING ((select auth.role()) = 'service_role');

Why:

• Users can only view/edit portals they have access to
• Portal creation happens during onboarding (service role)
• Portal deletion is admin-only operation

Related Tables:

• UserPortalAccess (access control)
• Client (1:1 relationship)
• Conversation (portal owns conversations)

---

Conversation

Purpose: Chat and messaging conversations belonging to portals.

Policies:

Policy Name	Action	Description
Conversation select policy	SELECT	Users with portal access OR service role
Conversation insert policy	INSERT	Users with portal access OR service role
Conversation update policy	UPDATE	Users with portal access OR service role
Conversation delete policy	DELETE	Service role only

SQL:

-- SELECT: Via portal access
CREATE POLICY "Conversation select policy" ON "Conversation"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserPortalAccess" upa
      WHERE upa."portalId" = "Conversation"."portalId"
      AND upa."userId" = (select auth.uid())::text
    )
    OR (select auth.role()) = 'service_role'
  );

-- INSERT: Via portal access
CREATE POLICY "Conversation insert policy" ON "Conversation"
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM "UserPortalAccess" upa
      WHERE upa."portalId" = "Conversation"."portalId"
      AND upa."userId" = (select auth.uid())::text
    )
    OR (select auth.role()) = 'service_role'
  );

-- UPDATE: Via portal access
CREATE POLICY "Conversation update policy" ON "Conversation"
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM "UserPortalAccess" upa
      WHERE upa."portalId" = "Conversation"."portalId"
      AND upa."userId" = (select auth.uid())::text
    )
    OR (select auth.role()) = 'service_role'
  );

-- DELETE: Service role only
CREATE POLICY "Conversation delete policy" ON "Conversation"
  FOR DELETE USING ((select auth.role()) = 'service_role');

Why:

• Users can only view conversations for portals they have access to
• Users can create and update conversations (for messaging features)
• Deletion is service role only (data retention policies)

Related Tables:

• Portal (via portalId)
• Message (conversation contains messages)

---

Lead (Updated December 2025)

Purpose: Sales lead records with triple-tier access (Company + Client + Portal).

Policies:

Policy Name	Action	Description
Lead select policy	SELECT	Company OR Client OR Portal access, plus service role
Lead insert policy	INSERT	Same access requirements
Lead update policy	UPDATE	Same access requirements
Lead delete policy	DELETE	Same access requirements

SQL:

-- SELECT: Triple-tier access (Company OR Client OR Portal)
CREATE POLICY "Lead select policy" ON "Lead"
  FOR SELECT USING (
    -- Company-level access
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Lead"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
    OR
    -- Client-level access
    EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "Lead"."clientId"
      AND ucl."userId" = (select auth.uid())::text
    )
    OR
    -- Portal-level access
    EXISTS (
      SELECT 1 FROM "UserPortalAccess" upa
      WHERE upa."portalId" = "Lead"."portalId"
      AND upa."userId" = (select auth.uid())::text
    )
    OR (select auth.role()) = 'service_role'
  );

Why:

• Leads can be accessed via any of the three access paths
• Provides flexibility for different organization structures
• Company owners see all leads; location managers see their location's leads

Related Tables:

• Company (via companyId)
• Client (via clientId)
• Portal (via portalId)

---

How to Add New RLS Policies

Follow this step-by-step process to add RLS to a new table.

Step 1: Identify Tenant Isolation Strategy

Determine how data should be isolated:

Questions to ask:

1. Does this table have a companyId column?
2. Does this table have a clientId column?
3. Should users see all data in their company?
4. Should users only see data for specific clients?
5. Should creators have special privileges?

Common Patterns:

• Company-scoped: Analytics, settings, company metadata
• Client-scoped: Contacts, messages, reviews, locations
• Hybrid: Most business data (allow both company and client access)
• User-scoped: User preferences, sessions, notifications

Step 2: Create Migration File

Create a new Prisma migration:

# Create timestamped migration file
touch prisma/migrations/$(date +%Y%m%d%H%M%S)_add_rls_table_name/migration.sql

Naming Convention:

• YYYYMMDD_add_rls_<table_name>/migration.sql
• Example: 20251213_add_rls_leads/migration.sql

Step 3: Write RLS Policies

Use the standard template below and customize for your table.

Template: Hybrid (Company + Client) Isolation

-- Enable RLS on the table
ALTER TABLE "YourTable" ENABLE ROW LEVEL SECURITY;

-- Drop existing policies (idempotent)
DROP POLICY IF EXISTS "Users can view records for their companies" ON "YourTable";
DROP POLICY IF EXISTS "Users can insert records for their companies" ON "YourTable";
DROP POLICY IF EXISTS "Users can update records for their companies" ON "YourTable";
DROP POLICY IF EXISTS "Users can delete records for their companies" ON "YourTable";
DROP POLICY IF EXISTS "Service role full access - YourTable" ON "YourTable";

-- SELECT: Users can view records they have access to
CREATE POLICY "Users can view records for their companies" ON "YourTable"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "YourTable"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "YourTable"."clientId"
      AND ucl."userId" = (select auth.uid())::text
    )
  );

-- INSERT: Users can create records for their companies/clients
CREATE POLICY "Users can insert records for their companies" ON "YourTable"
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "YourTable"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "YourTable"."clientId"
      AND ucl."userId" = (select auth.uid())::text
    )
  );

-- UPDATE: Users can update records they can see
CREATE POLICY "Users can update records for their companies" ON "YourTable"
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "YourTable"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "YourTable"."clientId"
      AND ucl."userId" = (select auth.uid())::text
    )
  );

-- DELETE: Users can delete records they can see (optional)
CREATE POLICY "Users can delete records for their companies" ON "YourTable"
  FOR DELETE USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "YourTable"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
    OR EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "YourTable"."clientId"
      AND ucl."userId" = (select auth.uid())::text
    )
  );

-- Service role bypass (REQUIRED)
CREATE POLICY "Service role full access - YourTable" ON "YourTable"
  FOR ALL USING ((select auth.role()) = 'service_role');

-- Grant permissions to authenticated users
GRANT SELECT, INSERT, UPDATE, DELETE ON "YourTable" TO authenticated;

Template: Company-Only Isolation

ALTER TABLE "YourTable" ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view their records" ON "YourTable";
DROP POLICY IF EXISTS "Users can insert records" ON "YourTable";
DROP POLICY IF EXISTS "Users can update their records" ON "YourTable";
DROP POLICY IF EXISTS "Service role full access - YourTable" ON "YourTable";

CREATE POLICY "Users can view their records" ON "YourTable"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "YourTable"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
  );

CREATE POLICY "Users can insert records" ON "YourTable"
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "YourTable"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
  );

CREATE POLICY "Users can update their records" ON "YourTable"
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "YourTable"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
  );

CREATE POLICY "Service role full access - YourTable" ON "YourTable"
  FOR ALL USING ((select auth.role()) = 'service_role');

GRANT SELECT, INSERT, UPDATE ON "YourTable" TO authenticated;

Step 4: Add Supabase Auth Helpers (if needed)

If running in a non-Supabase environment (local Postgres), include helpers:

-- Ensure auth schema exists
DO $do$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'auth') THEN
    CREATE SCHEMA auth;
  END IF;
END
$do$;

-- Create auth.uid() if it doesn't exist
DO $do$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE p.proname = 'uid'
      AND n.nspname = 'auth'
  ) THEN
    EXECUTE $fn$
      CREATE FUNCTION auth.uid() RETURNS uuid
      LANGUAGE sql STABLE
      SECURITY DEFINER
      SET search_path = ''
      AS $$
        WITH claims AS (
          SELECT COALESCE(current_setting('request.jwt.claims', true), '{}')::jsonb AS jwt
        )
        SELECT NULLIF(
          COALESCE(
            (SELECT jwt ->> 'sub' FROM claims),
            (SELECT jwt ->> 'user_id' FROM claims),
            (SELECT jwt ->> 'userId' FROM claims)
          ),
          ''
        )::uuid;
      $$;
    $fn$;
  END IF;
END
$do$;

-- Create auth.role() if it doesn't exist
DO $do$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE p.proname = 'role'
      AND n.nspname = 'auth'
  ) THEN
    EXECUTE $fn$
      CREATE FUNCTION auth.role() RETURNS text
      LANGUAGE sql STABLE
      SECURITY DEFINER
      SET search_path = ''
      AS $$
        WITH claims AS (
          SELECT COALESCE(current_setting('request.jwt.claims', true), '{}')::jsonb AS jwt
        )
        SELECT COALESCE(
          (SELECT jwt ->> 'role' FROM claims),
          current_setting('role', true),
          'anon'
        );
      $$;
    $fn$;
  END IF;
END
$do$;

-- Ensure Supabase roles exist
DO $do$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'authenticated') THEN
    CREATE ROLE authenticated NOLOGIN;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'anon') THEN
    CREATE ROLE anon NOLOGIN;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'service_role') THEN
    CREATE ROLE service_role NOLOGIN;
  END IF;
END
$do$;

Step 5: Apply Migration

# Apply Prisma migration
npx prisma migrate deploy

# Or apply directly to Supabase
psql $DATABASE_URL -f prisma/migrations/YYYYMMDD_add_rls_table_name/migration.sql

Step 6: Mirror to Supabase Migrations (if applicable)

If using Supabase Studio or separate Supabase migrations:

# Copy to Supabase migrations folder
cp prisma/migrations/YYYYMMDD_add_rls_table_name/migration.sql \
   supabase/migrations/YYYYMMDD_add_rls_table_name.sql

Step 7: Test (See Testing Section)

Run RLS policy tests to verify isolation works correctly.

---

Testing RLS Policies

Comprehensive testing is critical to ensure RLS policies work as intended.

Automated Test Suite

Run the automated RLS test script:

# Run RLS policy tests
npm run test:rls

# Or directly with tsx
tsx scripts/test-rls-policies.ts

Location: /scripts/test-rls-policies.ts

Test Categories

The test suite verifies:

1. User Data Isolation - Users can only see their own User record
2. Contact Isolation - Users can only see contacts from their companies/clients
3. Cross-User Access - Users cannot access other users' data
4. Service Client Bypass - Service role can access all data
5. RLS Enabled Check - All tables have RLS enabled
6. RLS Policies Exist - Policies are correctly created

Manual Testing

Test 1: Verify RLS is Enabled

-- Check which tables have RLS enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
AND tablename NOT LIKE '_prisma%'
ORDER BY tablename;

Expected: rowsecurity = true for all business tables

Test 2: Verify Policies Exist

-- List all RLS policies
SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Expected: Each table should have:

• SELECT policy
• INSERT policy
• UPDATE policy
• DELETE policy (optional)
• Service role bypass policy

Test 3: Test User Isolation

-- As authenticated user, try to query all users
-- Should only return current user or get permission denied
SELECT id, email FROM "User" LIMIT 10;

-- As service role, should see all users
SET ROLE service_role;
SELECT id, email FROM "User" LIMIT 10;
RESET ROLE;

Test 4: Test Company Isolation

-- Get current user's companies
SELECT c."id", c."name"
FROM "Company" c
JOIN "UserCompany" uc ON uc."companyId" = c."id"
WHERE uc."userId" = (select auth.uid())::text;

-- Try to access a company the user doesn't belong to
-- Should return 0 rows
SELECT * FROM "Company" WHERE id = 'unauthorized-company-id';

Test 5: Test Client Isolation

-- Get current user's clients
SELECT cl."id", cl."name"
FROM "Client" cl
JOIN "UserClient" ucl ON ucl."clientId" = cl."id"
WHERE ucl."userId" = (select auth.uid())::text;

-- Try to access a client the user doesn't have access to
-- Should return 0 rows
SELECT * FROM "Client" WHERE id = 'unauthorized-client-id';

Integration Tests

Write integration tests for critical business flows:

// tests/integration/auth-tenancy.test.ts
import { prisma } from '@/lib/database';
import { createClient } from '@/lib/utils/supabase/server';

describe('RLS Multi-Tenant Isolation', () => {
  it('should prevent users from accessing other companies data', async () => {
    const supabase = await createClient();

    // Get user's companies
    const { data: userCompanies } = await supabase
      .from('UserCompany')
      .select('companyId');

    const authorizedCompanyIds = userCompanies?.map(uc => uc.companyId) || [];

    // Query all contacts
    const { data: contacts } = await supabase
      .from('Contact')
      .select('id, companyId');

    // Verify all returned contacts belong to user's companies
    const unauthorizedContacts = contacts?.filter(
      c => c.companyId && !authorizedCompanyIds.includes(c.companyId)
    );

    expect(unauthorizedContacts).toHaveLength(0);
  });

  it('should allow service role to bypass RLS', async () => {
    const serviceClient = createServiceClient();

    // Service role should see all data
    const { data: allUsers } = await serviceClient
      .from('User')
      .select('id');

    expect(allUsers).not.toBeNull();
    expect(allUsers!.length).toBeGreaterThan(0);
  });
});

Load Testing

Test RLS performance under realistic load:

# Use pgbench or k6 for load testing
# Ensure RLS doesn't significantly degrade query performance

# Example: Measure query time with RLS
EXPLAIN ANALYZE
SELECT * FROM "Contact"
WHERE "companyId" IN (
  SELECT "companyId" FROM "UserCompany" WHERE "userId" = 'test-user-id'
);

---

Performance Optimization

RLS policies can impact query performance if not optimized properly.

Optimization Technique 1: Subquery Wrapping

Problem: Functions like auth.uid() and auth.role() get re-evaluated for every row.

Solution: Wrap function calls in subqueries:

-- ❌ BAD: Re-evaluated per row (545 warnings)
WHERE uc."userId" = auth.uid()::text

-- ✅ GOOD: Evaluated once per query
WHERE uc."userId" = (select auth.uid())::text

Performance Impact:

• Before: 545 RLS performance warnings
• After: 0 warnings
• Query time reduction: ~30-50% on large tables

Optimization Technique 2: Index Junction Tables

Ensure foreign keys in junction tables are indexed:

-- UserCompany indexes
CREATE INDEX IF NOT EXISTS idx_usercompany_userid ON "UserCompany"("userId");
CREATE INDEX IF NOT EXISTS idx_usercompany_companyid ON "UserCompany"("companyId");

-- UserClient indexes
CREATE INDEX IF NOT EXISTS idx_userclient_userid ON "UserClient"("userId");
CREATE INDEX IF NOT EXISTS idx_userclient_clientid ON "UserClient"("clientId");

Why: RLS policies use EXISTS subqueries that scan junction tables

Optimization Technique 3: Minimize EXISTS Clauses

Problem: Multiple EXISTS clauses can slow down queries.

Solution: Combine conditions when possible:

-- ❌ SLOWER: Two separate EXISTS checks
EXISTS (SELECT 1 FROM "UserCompany" WHERE ...)
OR EXISTS (SELECT 1 FROM "UserClient" WHERE ...)

-- ✅ FASTER: Combined when relationships allow
EXISTS (
  SELECT 1 FROM "UserCompany" uc
  JOIN "Client" c ON c."companyId" = uc."companyId"
  WHERE c."id" = "Contact"."clientId"
  AND uc."userId" = (select auth.uid())::text
)

Note: Only combine when it doesn't change logic

Optimization Technique 4: Use current_setting for Static Values

For values that don't change during the query:

-- Service role check (static for entire query)
WHERE (select current_setting('request.jwt.claims', true)::jsonb ->> 'role') = 'service_role'

Monitoring RLS Performance

Check for performance warnings in logs:

-- Check for RLS warnings in Postgres logs
SELECT * FROM pg_stat_statements
WHERE query LIKE '%auth.uid()%'
ORDER BY mean_exec_time DESC;

Warning Signs:

• Queries taking >100ms with RLS
• High CPU usage on junction table scans
• Sequential scans instead of index scans

---

Common Security Patterns

Pattern 1: Restrict Migration Table Access

-- Lock down _prisma_migrations table to service role only
ALTER TABLE public._prisma_migrations ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Service role only - _prisma_migrations" ON public._prisma_migrations;

CREATE POLICY "Service role only - _prisma_migrations" ON public._prisma_migrations
  FOR ALL
  USING (
    current_setting('request.jwt.claims', true)::jsonb ->> 'role' = 'service_role'
  )
  WITH CHECK (
    current_setting('request.jwt.claims', true)::jsonb ->> 'role' = 'service_role'
  );

REVOKE ALL ON public._prisma_migrations FROM PUBLIC;
REVOKE ALL ON public._prisma_migrations FROM authenticated;
REVOKE ALL ON public._prisma_migrations FROM anon;

Why: Prevents exposing migration history via PostgREST

Pattern 2: Read-Only Access

-- Users can read but not modify certain tables
CREATE POLICY "Users can view analytics" ON "AnalyticsRollup"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "AnalyticsRollup"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
  );

-- No INSERT/UPDATE/DELETE policies = read-only
GRANT SELECT ON "AnalyticsRollup" TO authenticated;

Use Cases: Analytics tables, audit logs, system metadata

Pattern 3: Conditional Access by Role

-- Different access based on user role in company
CREATE POLICY "Managers can view all clients" ON "Client"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Client"."companyId"
      AND uc."userId" = (select auth.uid())::text
      AND uc."role" IN ('admin', 'manager')
    )
  );

CREATE POLICY "Agents can view assigned clients" ON "Client"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserClient" ucl
      WHERE ucl."clientId" = "Client"."id"
      AND ucl."userId" = (select auth.uid())::text
    )
  );

Use Cases: Role-based access control (RBAC), hierarchical permissions

Pattern 4: Time-Based Access

-- Users can only modify recent records
CREATE POLICY "Users can update recent messages" ON "Message"
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM "Contact" c
      JOIN "UserCompany" uc ON uc."companyId" = c."companyId"
      WHERE c."id" = "Message"."contactId"
      AND uc."userId" = (select auth.uid())::text
    )
    AND "Message"."createdAt" > NOW() - INTERVAL '24 hours'
  );

Use Cases: Prevent editing old records, compliance requirements

Pattern 5: Attribute-Based Access Control

-- Users can only access active records
CREATE POLICY "Users can view active clients" ON "Client"
  FOR SELECT USING (
    "Client"."status" = 'ACTIVE'
    AND EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Client"."companyId"
      AND uc."userId" = (select auth.uid())::text
    )
  );

Use Cases: Hide deleted records, status-based filtering

Pattern 6: Join-Based Access

-- Access through related tables
CREATE POLICY "Users can view messages for their contacts" ON "Message"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "Contact" c
      JOIN "UserCompany" uc ON uc."companyId" = c."companyId"
      WHERE c."id" = "Message"."contactId"
      AND uc."userId" = (select auth.uid())::text
    )
  );

Use Cases: Nested data structures, relational access control

---

Troubleshooting

Issue 1: Users Can't See Any Data

Symptoms:

• Queries return 0 rows
• Error: "PGRST116: The result contains 0 rows"

Diagnosis:

-- Check if user has company membership
SELECT * FROM "UserCompany" WHERE "userId" = 'user-id';

-- Check if user has client membership
SELECT * FROM "UserClient" WHERE "userId" = 'user-id';

-- Check if RLS is enabled
SELECT tablename, rowsecurity FROM pg_tables WHERE tablename = 'YourTable';

-- Check if policies exist
SELECT * FROM pg_policies WHERE tablename = 'YourTable';

Solutions:

1. Verify user has UserCompany or UserClient records
2. Check auth.uid() returns correct user ID
3. Verify policies use correct column names
4. Check if authenticated role has GRANT permissions

Issue 2: Service Role Can't Access Data

Symptoms:

• Backend operations fail with permission denied
• Service role queries return 0 rows

Diagnosis:

-- Check if service role bypass policy exists
SELECT * FROM pg_policies
WHERE tablename = 'YourTable'
AND policyname LIKE '%service%';

-- Verify service role is set correctly
SELECT current_setting('request.jwt.claims', true)::jsonb ->> 'role';

Solutions:

1. Add service role bypass policy
2. Verify SUPABASE_SERVICE_ROLE_KEY is used for service client
3. Check if policy uses auth.role() = 'service_role'

Issue 3: Query Performance Degradation

Symptoms:

• Queries slow after enabling RLS
• Timeout errors on large tables

Diagnosis:

-- Check for performance warnings
EXPLAIN ANALYZE
SELECT * FROM "YourTable" WHERE ...;

-- Check if indexes exist on junction tables
SELECT indexname FROM pg_indexes
WHERE tablename IN ('UserCompany', 'UserClient');

Solutions:

1. Wrap auth.uid() in subqueries: (select auth.uid())
2. Add indexes to junction table foreign keys
3. Use EXISTS instead of IN subqueries
4. Consider materializing user permissions for complex policies

Issue 4: Policies Not Applying

Symptoms:

• Users can see data they shouldn't
• RLS appears disabled

Diagnosis:

-- Verify RLS is enabled
SELECT tablename, rowsecurity FROM pg_tables WHERE tablename = 'YourTable';

-- Check policy permissiveness
SELECT policyname, permissive FROM pg_policies WHERE tablename = 'YourTable';

-- Test with explicit role
SET ROLE authenticated;
SELECT * FROM "YourTable";
RESET ROLE;

Solutions:

1. Run ALTER TABLE "YourTable" ENABLE ROW LEVEL SECURITY;
2. Verify policies are PERMISSIVE (default)
3. Check if multiple policies are conflicting
4. Ensure GRANT statements executed

Issue 5: Can't Insert/Update Data

Symptoms:

• INSERT fails with permission denied
• UPDATE returns 0 rows affected

Diagnosis:

-- Check INSERT policy exists
SELECT * FROM pg_policies
WHERE tablename = 'YourTable'
AND cmd = 'INSERT';

-- Check UPDATE policy exists
SELECT * FROM pg_policies
WHERE tablename = 'YourTable'
AND cmd = 'UPDATE';

-- Check GRANT permissions
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'YourTable';

Solutions:

1. Add FOR INSERT WITH CHECK policy
2. Add FOR UPDATE USING policy
3. Run GRANT INSERT, UPDATE ON "YourTable" TO authenticated;
4. Verify user has company/client membership for the target record

Issue 6: Orphaned JWT / Invalid Session

Symptoms:

• Error: "User from sub claim in JWT does not exist"
• Auth errors despite having valid token

Diagnosis:

// Check if user exists in Supabase auth
const { data: { user }, error } = await supabase.auth.getUser();
console.log('Auth error:', error?.message);

// Check if user exists in database
const dbUser = await prisma.user.findUnique({
  where: { email: user?.email }
});

Solutions:

1. Clear invalid session: await supabase.auth.signOut()
2. Re-authenticate user
3. Check if user was deleted from Supabase auth but JWT still exists
4. Verify authId matches between User table and Supabase auth

Debugging Tools

-- Enable RLS debugging (verbose logs)
SET client_min_messages = DEBUG1;

-- See query execution plan with RLS
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM "Contact" LIMIT 100;

-- Check active policies for a table
\d+ "Contact"

-- View auth context
SELECT
  current_user,
  current_setting('request.jwt.claims', true)::jsonb ->> 'sub' as user_id,
  current_setting('request.jwt.claims', true)::jsonb ->> 'role' as role;

---

References

Documentation

• PostgreSQL RLS Documentation
• Supabase RLS Guide
• Prisma + Supabase RLS

Related Documentation

• /TESTING.md - Complete testing guidelines
• /docs/security/README.md - Security audit documentation
• /docs/ARCHITECTURE.md - System architecture overview
• /docs/auth-patterns.md - Authentication patterns

Key Files

• /scripts/test-rls-policies.ts - Automated RLS tests
• /lib/auth/server.ts - Server-side auth helpers
• /lib/utils/supabase/server.ts - Supabase client utilities
• /prisma/migrations/20251205_apply_core_rls_policies/migration.sql - Core RLS policies
• /prisma/migrations/20251205_apply_api_key_usage_rls/migration.sql - API key RLS policies
• /prisma/migrations/20251213_apply_lead_rls_policies/migration.sql - Lead RLS policies
• /prisma/migrations/20251218_apply_portal_access_rls/migration.sql - Portal access RLS policies
• /prisma/migrations/20251218_fix_rls_policy_warnings/migration.sql - Consolidated policy fixes
• /prisma/migrations/20251218_fix_rls_for_all_policies/migration.sql - FOR ALL policy fixes

Migration History

• December 5, 2025: Applied core RLS policies (Contact, Client, Company, Message)
• December 5, 2025: Applied API key RLS policies (client_api_keys, platform_key_usage)
• December 13, 2025: Applied Lead RLS policies (triple-tier access: Company + Client + Portal)
• December 18, 2025: Added RLS to UserPortalAccess, Portal, Conversation tables
• December 18, 2025: Consolidated 50+ RLS policies to eliminate Supabase performance warnings
• December 18, 2025: Fixed FOR ALL policy overlap issues

---

Document Version: 2.0.0 Last Reviewed: December 18, 2025 Next Review: March 18, 2026 (Quarterly)