Common Postgres Row-Level-Security footguns

-- This will cause full table scans even with an index on title
CREATE POLICY user_documents ON documents
FOR SELECT
USING (owner_id = current_user_id() AND title ILIKE '%search%');

-- Use LEAKPROOF functions or move complex logic out of policies
CREATE OR REPLACE FUNCTION safe_ilike(text, text) 
RETURNS boolean 
LANGUAGE sql 
LEAKPROOF 
AS $$ SELECT $1 ILIKE $2 $$;

CREATE POLICY user_documents ON documents
FOR SELECT
USING (owner_id = current_user_id() AND safe_ilike(title, '%search%'));

CREATE POLICY complex_access ON orders
USING (
  EXISTS (
    SELECT 1 FROM user_permissions up 
    JOIN departments d ON up.dept_id = d.id 
    WHERE up.user_id = current_user_id() 
    AND d.region = orders.region
  )
);

-- Move complexity to a LEAKPROOF function
CREATE OR REPLACE FUNCTION user_has_region_access(region_name text)
RETURNS boolean
LANGUAGE sql
LEAKPROOF
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_permissions up 
    JOIN departments d ON up.dept_id = d.id 
    WHERE up.user_id = current_user_id() 
    AND d.region = region_name
  );
$$;

CREATE POLICY simple_access ON orders
USING (user_has_region_access(region));

-- Always index columns used in policies
CREATE INDEX ON orders(tenant_id);
CREATE INDEX ON orders(owner_id);
CREATE INDEX ON orders(tenant_id, owner_id); -- composite for AND conditions

-- Force RLS even for table owners
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE sensitive_data FORCE ROW LEVEL SECURITY;

-- Never grant BYPASSRLS to application roles
-- Test with dedicated non-superuser accounts

-- Create proper test user
CREATE ROLE test_user;
GRANT app_user TO test_user;

-- Test as the actual application user
SET ROLE test_user;
SET app.tenant_id = 'test-tenant-id';
-- Run your tests here
RESET ROLE;

-- Created by superuser - bypasses ALL RLS policies!
CREATE VIEW all_patient_data AS 
SELECT * FROM patients;

-- Postgres 15+: Use SECURITY INVOKER
CREATE VIEW patient_data 
WITH (security_invoker = true)
AS SELECT * FROM patients;

-- Older versions: Use SECURITY DEFINER with explicit RLS
CREATE OR REPLACE FUNCTION get_patient_data()
RETURNS TABLE(id uuid, name text, doctor_id uuid)
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
BEGIN
  -- Explicitly check RLS in SECURITY DEFINER functions
  IF NOT row_security_active('patients') THEN
    RAISE EXCEPTION 'Row security must be active';
  END IF;
  
  RETURN QUERY SELECT p.id, p.name, p.doctor_id FROM patients p;
END;
$$ LANGUAGE plpgsql;

-- Timing reveals if forbidden patients exist
SELECT COUNT(*) FROM patients 
WHERE condition = 'rare_disease' 
AND tenant_id = current_setting('app.tenant_id')::uuid;

-- Table owner still sees everything!
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Force RLS for everyone, including owners
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Users can INSERT data they can't see!
CREATE POLICY tenant_data ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

-- Useless with PgBouncer - all connections share same user
CREATE POLICY user_data ON orders
USING (owner_id = current_user);

-- Use application-controlled session variables
-- App sets per transaction:
SET app.user_id = 'user-uuid';
SET app.tenant_id = 'tenant-uuid';

-- Policy reads from session:
CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

-- Prevent clients from setting app.* directly
REVOKE ALL ON SCHEMA pg_catalog FROM app_user;
-- Only allow trusted server code to set these variables

-- This INSERT fails even if customer exists
INSERT INTO orders (customer_id, tenant_id) 
VALUES ('existing-customer-id', 'my-tenant');
-- ERROR: insert or update on table "orders" violates foreign key constraint

-- Parent table needs SELECT policy for FK checks
CREATE POLICY customer_fk_visibility ON customers
FOR SELECT
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- This reveals that email exists in ANY tenant
CREATE UNIQUE INDEX users_email_unique ON users(email);
-- INSERT fails with "duplicate key" even for other tenants

-- Scope uniqueness to tenant
CREATE UNIQUE INDEX users_email_per_tenant 
ON users(tenant_id, lower(email));

-- Temporarily disable RLS to test
SET row_security = off;
-- Run your query to see if RLS is the issue
SET row_security = on;

-- Or check if policies are active
SELECT row_security_active('table_name');

-- Users can see SSN in their own records
SELECT * FROM users WHERE tenant_id = current_setting('app.tenant_id')::uuid;

-- Option 1: Column privileges
REVOKE SELECT (ssn, salary) ON users FROM app_user;

-- Option 2: Secure views
CREATE VIEW users_safe AS
SELECT id, name, email, 
       CASE WHEN has_role('hr_role') 
            THEN ssn 
            ELSE 'XXX-XX-' || right(ssn, 4) 
       END as ssn_masked
FROM users;

-- Materialized view bypasses RLS
CREATE MATERIALIZED VIEW order_summary AS
SELECT * FROM orders; -- Contains all tenants' data!

-- Background job exports everything
COPY (SELECT * FROM orders) TO '/tmp/backup.csv';

-- Apply filtering in materialized views
CREATE MATERIALIZED VIEW tenant_order_summary AS
SELECT tenant_id, COUNT(*), SUM(amount)
FROM orders 
GROUP BY tenant_id;

-- Use RLS-aware exports
COPY (
  SELECT * FROM orders 
  WHERE tenant_id = 'specific-tenant'
) TO '/tmp/tenant_backup.csv';

-- These policies are OR-ed - users get access if EITHER is true
CREATE POLICY user_own_data ON orders
USING (owner_id = current_user_id());

CREATE POLICY admin_all_data ON orders  
USING (has_role('admin')); -- Oops, too broad!

-- Option 1: Use restrictive policies (AND-ed)
CREATE POLICY tenant_restriction ON orders
AS RESTRICTIVE
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Option 2: Combine logic in single policy
CREATE POLICY combined_access ON orders
USING (
  tenant_id = current_setting('app.tenant_id')::uuid
  AND (
    owner_id = current_user_id() 
    OR has_role('tenant_admin')
  )
);