Database Migration Rollback Strategy

-- Capture all existing policies for affected tables
SELECT 
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual AS using_expression,
  with_check
FROM pg_policies
WHERE schemaname = 'public'
  AND tablename IN ('table1', 'table2') -- List affected tables
ORDER BY tablename, policyname;

-- Migration
CREATE POLICY "new_policy" ON table_name FOR SELECT USING (...);

-- Rollback
DROP POLICY IF EXISTS "new_policy" ON table_name;

# Save current state
psql -c "SELECT * FROM get_all_rls_policies()" > pre_migration_policies.sql

-- Step 1: Drop the problematic policy
DROP POLICY IF EXISTS "problematic_policy" ON affected_table;

-- Step 2: Recreate the original policy
CREATE POLICY "original_policy" ON affected_table
  FOR SELECT
  USING (original_using_expression);

BEGIN;

-- Drop new policies
DROP POLICY IF EXISTS "new_policy_1" ON table1;
DROP POLICY IF EXISTS "new_policy_2" ON table2;

-- Recreate original policies
CREATE POLICY "original_policy_1" ON table1 FOR SELECT USING (...);
CREATE POLICY "original_policy_2" ON table2 FOR SELECT USING (...);

COMMIT;

-- Create a function to capture current RLS state
CREATE OR REPLACE FUNCTION get_all_rls_policies()
RETURNS TABLE (
  schema_name text,
  table_name text,
  policy_name text,
  policy_cmd text,
  policy_roles text[],
  using_expr text,
  with_check_expr text,
  create_statement text
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  RETURN QUERY
  SELECT 
    p.schemaname::text,
    p.tablename::text,
    p.policyname::text,
    p.cmd::text,
    p.roles::text[],
    p.qual::text,
    p.with_check::text,
    format(
      'CREATE POLICY %I ON %I.%I FOR %s TO %s USING (%s)%s;',
      p.policyname,
      p.schemaname,
      p.tablename,
      p.cmd,
      array_to_string(p.roles, ', '),
      COALESCE(p.qual::text, 'true'),
      CASE WHEN p.with_check IS NOT NULL 
           THEN format(' WITH CHECK (%s)', p.with_check::text)
           ELSE '' END
    )
  FROM pg_policies p
  WHERE p.schemaname = 'public'
  ORDER BY p.tablename, p.policyname;
END;
$$;

-- Get policies for specific tables as rollback SQL
SELECT format(
  E'-- Rollback for %I\nDROP POLICY IF EXISTS %I ON %I;\nCREATE POLICY %I ON %I\n  FOR %s\n  USING (%s)%s;\n',
  tablename,
  policyname,
  tablename,
  policyname,
  tablename,
  cmd,
  COALESCE(qual::text, 'true'),
  CASE WHEN with_check IS NOT NULL 
       THEN format(E'\n  WITH CHECK (%s)', with_check::text)
       ELSE '' END
) AS rollback_sql
FROM pg_policies
WHERE schemaname = 'public'
  AND tablename = 'your_table_name'
ORDER BY policyname;

-- Generate DROP statements for policies created after a specific date
-- (Requires tracking policy creation dates separately)
SELECT format('DROP POLICY IF EXISTS %I ON %I;', policyname, tablename)
FROM pg_policies
WHERE schemaname = 'public'
  AND policyname LIKE 'new_prefix_%';

-- 1. Verify no recursion risks
SELECT * FROM get_rls_recursion_risks();
-- Expected: 0 rows

-- 2. Check policy counts per table
SELECT tablename, COUNT(*) as policy_count
FROM pg_policies 
WHERE schemaname = 'public'
GROUP BY tablename
ORDER BY policy_count;

-- 3. Verify UPDATE policies have WITH CHECK
SELECT tablename, policyname
FROM pg_policies
WHERE schemaname = 'public'
  AND cmd = 'UPDATE'
  AND with_check IS NULL;
-- Expected: 0 rows (or known exceptions)

-- 4. Test multi-tenant isolation
-- Run as org1_user, should not see org2 data
SELECT COUNT(*) FROM your_table WHERE organization_id = 'org2_id';
-- Expected: 0

# Run RLS-specific tests
npm run test -- --grep "RLS"

# Run multi-tenant isolation tests
npm run test -- tests/rls/

-- Rollback: [Policy Name] on [Table Name]
-- Date: YYYY-MM-DD
-- Reason: [Brief explanation]

BEGIN;

-- Remove problematic policy
DROP POLICY IF EXISTS "new_policy_name" ON table_name;

-- Restore original policy
CREATE POLICY "original_policy_name" ON table_name
  FOR [SELECT|INSERT|UPDATE|DELETE|ALL]
  USING (original_using_expression)
  WITH CHECK (original_with_check_expression);

COMMIT;

-- Verification
SELECT * FROM pg_policies WHERE tablename = 'table_name';

-- Rollback: Migration YYYYMMDD_description
-- Date: YYYY-MM-DD
-- Affected Tables: table1, table2, table3
-- Reason: [Brief explanation]

BEGIN;

-- ============================================
-- Table 1: table1
-- ============================================
DROP POLICY IF EXISTS "new_table1_select" ON table1;
DROP POLICY IF EXISTS "new_table1_insert" ON table1;
DROP POLICY IF EXISTS "new_table1_update" ON table1;
DROP POLICY IF EXISTS "new_table1_delete" ON table1;

CREATE POLICY "old_table1_select" ON table1 FOR SELECT USING (...);
CREATE POLICY "old_table1_insert" ON table1 FOR INSERT WITH CHECK (...);
CREATE POLICY "old_table1_update" ON table1 FOR UPDATE USING (...) WITH CHECK (...);
CREATE POLICY "old_table1_delete" ON table1 FOR DELETE USING (...);

-- ============================================
-- Table 2: table2
-- ============================================
-- ... repeat pattern ...

COMMIT;

-- Verification
SELECT tablename, COUNT(*) 
FROM pg_policies 
WHERE tablename IN ('table1', 'table2', 'table3')
GROUP BY tablename;

-- Rollback: Helper Function [function_name]
-- Date: YYYY-MM-DD
-- Reason: [Brief explanation]

-- Step 1: Drop dependent policies first
DROP POLICY IF EXISTS "policy_using_function" ON affected_table;

-- Step 2: Drop new function
DROP FUNCTION IF EXISTS new_function_name(args);

-- Step 3: Recreate original function
CREATE OR REPLACE FUNCTION original_function_name(args)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  -- Original function body
$$;

-- Step 4: Recreate policies using original function
CREATE POLICY "policy_using_function" ON affected_table
  FOR SELECT
  USING (original_function_name(auth.uid()));