supabase-rls-fix

Supabase RLS Fix Skill

When to Use This Skill

Activate this skill when you encounter:

• "relation does not exist" errors with user/tenant tables
• "Tenant or user not found" errors in background jobs
• System processes failing due to missing user context
• RLS policies blocking scheduled tasks
• Need to add RLS to new tables

Quick Fixes

🚨 Fix 1: Missing user_tenants Table

If you get: ERROR: relation "user_tenants" does not exist

Create compatibility view:

-- Run in Supabase SQL Editor
DROP VIEW IF EXISTS user_tenants CASCADE;
CREATE OR REPLACE VIEW user_tenants AS
SELECT * FROM tenant_memberships;

GRANT SELECT ON user_tenants TO authenticated;
GRANT SELECT ON user_tenants TO anon;

🚨 Fix 2: System Process Failures

If background jobs fail with "Tenant or user not found":

Update the function to handle system processes:

CREATE OR REPLACE FUNCTION get_current_user_tenant_ids()
RETURNS UUID[] AS $$
DECLARE
  current_user_id_str TEXT;
  current_user_uuid UUID;
  tenant_ids UUID[];
BEGIN
  -- Get current user setting (may be null for system processes)
  current_user_id_str := current_setting('app.current_user_id', true);

  -- If no user context (system process/background job), return empty array
  IF current_user_id_str IS NULL OR current_user_id_str = '' THEN
    RETURN ARRAY[]::UUID[];
  END IF;

  -- Try to convert to UUID
  BEGIN
    current_user_uuid := current_user_id_str::UUID;
  EXCEPTION WHEN OTHERS THEN
    RETURN ARRAY[]::UUID[];
  END;

  -- Get user's tenant IDs
  SELECT ARRAY_AGG(tenant_id) INTO tenant_ids
  FROM tenant_memberships
  WHERE user_id = current_user_uuid;

  RETURN COALESCE(tenant_ids, ARRAY[]::UUID[]);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Permanent Solution: Standardized RLS Functions

Step 1: Create Helper Functions

-- Helper 1: Detect system processes
CREATE OR REPLACE FUNCTION is_system_process()
RETURNS BOOLEAN AS $$
BEGIN
  -- Check if explicitly marked as system process
  IF current_setting('app.is_system', true) = 'true' THEN
    RETURN true;
  END IF;

  -- Check if no user context (also system process)
  IF current_setting('app.current_user_id', true) IS NULL OR
     current_setting('app.current_user_id', true) = '' THEN
    RETURN true;
  END IF;

  RETURN false;
END;
$$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER;

-- Helper 2: Universal access checker
CREATE OR REPLACE FUNCTION can_access_row(
  p_user_id UUID DEFAULT NULL,
  p_tenant_id UUID DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
  v_current_user_id UUID;
  v_current_tenant_id UUID;
BEGIN
  -- System processes always have access
  IF is_system_process() THEN
    RETURN true;
  END IF;

  -- Get current user/tenant from session
  BEGIN
    v_current_user_id := current_setting('app.current_user_id', true)::UUID;
  EXCEPTION WHEN OTHERS THEN
    v_current_user_id := NULL;
  END;

  BEGIN
    v_current_tenant_id := current_setting('app.current_tenant_id', true)::UUID;
  EXCEPTION WHEN OTHERS THEN
    v_current_tenant_id := NULL;
  END;

  -- Check user match
  IF p_user_id IS NOT NULL AND v_current_user_id = p_user_id THEN
    RETURN true;
  END IF;

  -- Check tenant match
  IF p_tenant_id IS NOT NULL AND v_current_tenant_id = p_tenant_id THEN
    RETURN true;
  END IF;

  -- Check if user belongs to tenant
  IF p_tenant_id IS NOT NULL AND v_current_user_id IS NOT NULL THEN
    RETURN EXISTS (
      SELECT 1 FROM tenant_memberships
      WHERE user_id = v_current_user_id
      AND tenant_id = p_tenant_id
      AND status = 'ACTIVE'
    );
  END IF;

  RETURN false;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;