Skill Supabase Rls

skill-supabase-rls

Authorization gate

Use this skill for projects the user owns or is authorized to modify. For production databases, do not run migrations, resets, destructive SQL, or policy changes without explicit approval in the current task.

Record:

• Project and environment: local, staging, preview, or production.
• Tables, schemas, buckets, and tenants in scope.
• Whether changes are advisory, migration-ready, or approved for execution.

Core rules

1. Enable RLS on every user-facing table in exposed schemas.
2. Browser clients must use anon/user auth, never service-role keys.
3. Service-role keys are allowed only in server functions, controllers, jobs, or trusted backends.
4. Policies must include user ownership, tenant membership, or explicit role checks.
5. Admin cross-tenant reads should go through backend routes or RPC with explicit authorization.
6. Add indexes that match policy predicates and dashboard filters.
7. Avoid new select('*') reads in application code when sensitive columns exist.
8. Test positive and negative access cases before claiming security is complete.
9. Remember that after RLS is enabled, API access with browser-safe keys returns no rows until explicit policies exist.
10. For SQL-created tables in exposed schemas, enable RLS deliberately; do not rely on dashboard defaults.

Severity and gates

• Critical: service-role key exposed to client code, RLS disabled on user-facing sensitive tables, cross-tenant read/write, public write access to sensitive data. Block release.
• High: missing tenant predicate, admin bypass without server-side authorization, storage bucket policy exposing private files, unsafe SECURITY DEFINER function. Block merge until fixed or risk-accepted.
• Medium: missing policy indexes, broad select('*'), ambiguous anonymous access, missing negative tests. Require ticketed remediation.
• Low/Info: naming, documentation, policy simplification, performance tuning without direct exposure. Track without blocking.

Policy patterns

Use direct ownership when a row belongs to a user:

sqlCopy code
CREATE POLICY "users_read_own_rows" ON table_name
  FOR SELECT
  TO authenticated
  USING ((SELECT auth.uid()) = user_id);

Use membership checks for tenant/workspace data:

sqlCopy code
CREATE POLICY "members_read_tenant_rows" ON table_name
  FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1
      FROM tenant_members tm
      WHERE tm.tenant_id = table_name.tenant_id
        AND tm.user_id = (SELECT auth.uid())
    )
  );

Use WITH CHECK for write policies:

sqlCopy code
CREATE POLICY "members_create_tenant_rows" ON table_name
  FOR INSERT
  TO authenticated
  WITH CHECK (
    EXISTS (
      SELECT 1
      FROM tenant_members tm
      WHERE tm.tenant_id = table_name.tenant_id
        AND tm.user_id = (SELECT auth.uid())
        AND tm.role IN ('owner', 'admin')
    )
  );

Add supporting indexes:

sqlCopy code
CREATE INDEX IF NOT EXISTS idx_table_name_tenant_id ON table_name(tenant_id);
CREATE INDEX IF NOT EXISTS idx_table_name_user_id ON table_name(user_id);
CREATE INDEX IF NOT EXISTS idx_tenant_members_user_tenant ON tenant_members(user_id, tenant_id);

Validation queries

Check exposed tables without RLS:

sqlCopy code
SELECT n.nspname AS schema_name, c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname IN ('public', 'storage')
  AND c.relrowsecurity = false
ORDER BY 1, 2;

List policies for review:

sqlCopy code
SELECT schemaname, tablename, policyname, roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname IN ('public', 'storage')
ORDER BY schemaname, tablename, policyname;

For access tests, prove both cases:

• Positive: an authorized user can read/write only the intended row or object.
• Negative: another user or tenant cannot read/write the same row or object.

Related files

• Read RULES.md for detailed SQL patterns.
• Read TROUBLESHOOTING.md for common RLS failures and diagnostics.

Related skills

• skill-saas-factory
• skill-saas-core-limits
• skill-saas-security-scan
• skill-security-hooks