Introduction PostgreSQL row-level security (RLS) restricts which rows users can access based on policies. However, certain query patterns, misconfigured roles, or overlooked security functions can bypass RLS, potentially exposing sensitive data to unauthorized users. Detecting and preventing these bypasses is critical for compliance and data protection.

Symptoms - Users can see data belonging to other tenants in multi-tenant applications - Security audit logs show unexpected cross-tenant data access - `SECURITY DEFINER` functions returning data without RLS enforcement - Superuser or table owner accessing all rows despite RLS policies - Application logs show data inconsistencies between direct queries and API responses

Common Causes - Table owner or superuser bypassing RLS (RLS does not apply to them) - `SECURITY DEFINER` functions that disable RLS by running as table owner - Missing `ENABLE ROW LEVEL SECURITY` on tables - Policies using `current_user` instead of session-level variables - Views created without RLS that expose underlying table data

Step-by-Step Fix 1. **Verify RLS is enabled on all required tables": ```sql SELECT schemaname, tablename, rowsecurity AS rls_enabled, forcerowsecurity AS rls_forced FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename; ```

  1. 1.**Check existing RLS policies":
  2. 2.```sql
  3. 3.SELECT
  4. 4.schemaname,
  5. 5.tablename,
  6. 6.policyname,
  7. 7.permissive,
  8. 8.roles,
  9. 9.cmd,
  10. 10.qual,
  11. 11.with_check
  12. 12.FROM pg_policies
  13. 13.ORDER BY tablename, policyname;
  14. 14.`
  15. 15.**Fix SECURITY DEFINER functions to respect RLS":
  16. 16.```sql
  17. 17.-- BAD: function runs as owner (bypasses RLS)
  18. 18.CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
  19. 19.RETURNS SETOF orders
  20. 20.LANGUAGE sql SECURITY DEFINER -- Runs as function owner
  21. 21.AS $$ SELECT * FROM orders WHERE customer_id = user_id; $$;

-- GOOD: function runs as caller (respects RLS) CREATE OR REPLACE FUNCTION get_user_orders(user_id INT) RETURNS SETOF orders LANGUAGE sql SECURITY INVOKER -- Runs as calling user AS $$ SELECT * FROM orders WHERE customer_id = user_id; $$;

-- Or use row_level_security parameter ALTER FUNCTION get_user_orders(INT) SET row_security = on; ```

  1. 1.**Ensure FORCE ROW LEVEL SECURITY for table owner":
  2. 2.```sql
  3. 3.ALTER TABLE orders FORCE ROW LEVEL SECURITY;
  4. 4.-- Now even the table owner is subject to RLS policies
  5. 5.`
  6. 6.**Create proper tenant-isolation policies":
  7. 7.```sql
  8. 8.-- Set the tenant ID in the session
  9. 9.SET LOCAL app.tenant_id = 'tenant_123';

-- Create the policy CREATE POLICY tenant_isolation ON orders FOR ALL USING (tenant_id = current_setting('app.tenant_id')); ```

  1. 1.**Audit potential RLS bypasses":
  2. 2.```sql
  3. 3.-- Find SECURITY DEFINER functions
  4. 4.SELECT
  5. 5.n.nspname AS schema_name,
  6. 6.p.proname AS function_name,
  7. 7.pg_get_function_arguments(p.oid) AS arguments
  8. 8.FROM pg_proc p
  9. 9.JOIN pg_namespace n ON n.oid = p.pronamespace
  10. 10.WHERE p.prosecdef = true
  11. 11.AND n.nspname NOT IN ('pg_catalog', 'information_schema');
  12. 12.`

Prevention - Enable `FORCE ROW LEVEL SECURITY` on all RLS-protected tables - Review all `SECURITY DEFINER` functions for RLS bypass potential - Use `SET row_security = on` on all functions that query protected tables - Regularly audit RLS policies with automated policy comparison - Use application-level connection roles that do not own the tables - Test RLS by connecting as different roles and verifying data access - Monitor for queries returning unexpected row counts across tenants