aurganize-backend/infrastructure/docker/init-scripts/05-test-rls.sql

-- ==========================================
-- 05: TEST ROW-LEVEL SECURITY (OPTIONAL)
-- ==========================================
-- This script tests RLS functionality
-- Comment out this file in production!
-- Runs as: postgres (superuser)

\echo ''
\echo '⚠️  WARNING: This is a test script!'
\echo '⚠️  Remove or comment out for production!'
\echo ''
\echo '🧪 Testing Row-Level Security...'

-- ==========================================
-- Connect to development database
-- ==========================================
\c aurganize_dev

-- ==========================================
-- CREATE TEST TABLE
-- ==========================================
\echo ''
\echo '📋 Creating test table...'

DROP TABLE IF EXISTS rls_test CASCADE;

CREATE TABLE rls_test (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL,
    data TEXT
);

\echo '  ✅ Table rls_test created'

-- ==========================================
-- APPLY RLS POLICY
-- ==========================================
\echo ''
\echo '🔒 Applying RLS policy...'

SELECT create_tenant_isolation_policy('rls_test');

\echo '  ✅ RLS policy applied'

-- ==========================================
-- INSERT TEST DATA
-- ==========================================
\echo ''
\echo '📝 Inserting test data...'

INSERT INTO rls_test (tenant_id, data) VALUES
    ('550e8400-e29b-41d4-a716-446655440000', 'Tenant A - Record 1'),
    ('550e8400-e29b-41d4-a716-446655440000', 'Tenant A - Record 2'),
    ('660e8400-e29b-41d4-a716-446655440001', 'Tenant B - Record 1'),
    ('660e8400-e29b-41d4-a716-446655440001', 'Tenant B - Record 2');

\echo '  ✅ Inserted 4 records (2 per tenant)'

-- ==========================================
-- TEST AS SUPERUSER (Should see all)
-- ==========================================
\echo ''
\echo '👑 Testing as superuser (should see ALL records)...'

SELECT COUNT(*) AS total_records FROM rls_test;

\echo ''

-- ==========================================
-- TEST AS APPLICATION USER
-- ==========================================
\echo ''
\echo '👤 Testing as application user...'
\echo ''

-- Reconnect as application user
\c aurganize_dev aurganize_backend_api

-- ==========================================
-- TEST 1: Set Tenant A Context
-- ==========================================
\echo '🔧 Test 1: Setting Tenant A context...'
SELECT set_tenant_context('550e8400-e29b-41d4-a716-446655440000'::UUID);

\echo '📊 Querying (should see 2 Tenant A records only):'
SELECT id, tenant_id, data FROM rls_test ORDER BY data;

\echo ''

-- Verify current tenant
\echo '🔍 Current tenant:'
SELECT get_current_tenant() AS current_tenant;

\echo ''

-- ==========================================
-- TEST 2: Switch to Tenant B
-- ==========================================
\echo '🔧 Test 2: Switching to Tenant B context...'
SELECT set_tenant_context('660e8400-e29b-41d4-a716-446655440001'::UUID);

\echo '📊 Querying (should see 2 Tenant B records only):'
SELECT id, tenant_id, data FROM rls_test ORDER BY data;

\echo ''

-- ==========================================
-- TEST 3: Try to Insert with Wrong Tenant ID
-- ==========================================
\echo '🧪 Test 3: Attempting to insert with WRONG tenant_id (should FAIL)...'

DO $$
BEGIN
    INSERT INTO rls_test (tenant_id, data) 
    VALUES ('550e8400-e29b-41d4-a716-446655440000', 'Hacked!');
    
    RAISE EXCEPTION 'ERROR: Insert succeeded when it should have failed!';
EXCEPTION
    WHEN others THEN
        IF SQLERRM LIKE '%violates row-level security policy%' THEN
            RAISE NOTICE '  ✅ PASS: Insert correctly blocked by RLS';
        ELSE
            RAISE NOTICE '  ❌ FAIL: Unexpected error: %', SQLERRM;
        END IF;
END;
$$;

\echo ''

-- ==========================================
-- TEST 4: Insert with Correct Tenant ID
-- ==========================================
\echo '🧪 Test 4: Inserting with CORRECT tenant_id (should SUCCEED)...'

INSERT INTO rls_test (tenant_id, data) 
VALUES ('660e8400-e29b-41d4-a716-446655440001', 'Valid insert');

\echo '  ✅ PASS: Insert succeeded'

\echo ''
\echo '📊 Query again (should now see 3 Tenant B records):'
SELECT id, tenant_id, data FROM rls_test ORDER BY data;

\echo ''

-- ==========================================
-- TEST 5: Try to Update to Different Tenant
-- ==========================================
\echo '🧪 Test 5: Attempting to UPDATE to different tenant_id (should FAIL)...'

DO $$
BEGIN
    UPDATE rls_test 
    SET tenant_id = '550e8400-e29b-41d4-a716-446655440000'
    WHERE data = 'Valid insert';
    
    RAISE EXCEPTION 'ERROR: Update succeeded when it should have failed!';
EXCEPTION
    WHEN others THEN
        IF SQLERRM LIKE '%violates row-level security policy%' THEN
            RAISE NOTICE '  ✅ PASS: Update correctly blocked by RLS';
        ELSE
            RAISE NOTICE '  ❌ FAIL: Unexpected error: %', SQLERRM;
        END IF;
END;
$$;

\echo ''

-- ==========================================
-- TEST 6: Try to See All Records
-- ==========================================
\echo '🧪 Test 6: Verifying data is still filtered...'
\echo '📊 Count (should still be 3 Tenant B records only):'

SELECT COUNT(*) AS visible_records FROM rls_test;

\echo ''

-- ==========================================
-- CLEANUP
-- ==========================================
\echo '🧹 Cleaning up...'

-- Reconnect as superuser
\c aurganize_dev postgres

-- Drop test table
DROP TABLE IF EXISTS rls_test CASCADE;

\echo '  ✅ Test table dropped'

-- ==========================================
-- TEST SUMMARY
-- ==========================================
\echo ''
\echo '=========================================='
\echo '✅ RLS TESTING COMPLETE!'
\echo '=========================================='
\echo ''
\echo 'Test Results:'
\echo '  ✅ Tenant isolation works correctly'
\echo '  ✅ Cannot insert with wrong tenant_id'
\echo '  ✅ Can insert with correct tenant_id'
\echo '  ✅ Cannot update to different tenant_id'
\echo '  ✅ Queries are properly filtered'
\echo ''
\echo '🔒 Row-Level Security is functioning as expected!'
\echo ''
\echo '⚠️  REMINDER: Remove or comment out this test'
\echo '              script for production deployment!'
\echo ''