work-club-manager/backend/WorkClub.Infrastructure/Migrations/add-rls-policies.sql

-- Enable Row-Level Security on all tenant-scoped tables

ALTER TABLE clubs ENABLE ROW LEVEL SECURITY;
ALTER TABLE members ENABLE ROW LEVEL SECURITY;
ALTER TABLE work_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE shifts ENABLE ROW LEVEL SECURITY;
ALTER TABLE shift_signups ENABLE ROW LEVEL SECURITY;

-- Create tenant_isolation policies for tables with direct tenant_id column

CREATE POLICY tenant_isolation ON clubs
FOR ALL
USING ("TenantId" = current_setting('app.current_tenant_id', true)::text);

CREATE POLICY tenant_isolation ON members
FOR ALL
USING ("TenantId" = current_setting('app.current_tenant_id', true)::text);

CREATE POLICY tenant_isolation ON work_items
FOR ALL
USING ("TenantId" = current_setting('app.current_tenant_id', true)::text);

CREATE POLICY tenant_isolation ON shifts
FOR ALL
USING ("TenantId" = current_setting('app.current_tenant_id', true)::text);

-- Special policy for shift_signups (no direct tenant_id, uses subquery via shifts)

CREATE POLICY tenant_isolation ON shift_signups
FOR ALL
USING ("ShiftId" IN (SELECT "Id" FROM shifts WHERE "TenantId" = current_setting('app.current_tenant_id', true)::text));

-- Create bypass_rls_policy for app_admin role

CREATE POLICY bypass_rls_policy ON clubs
FOR ALL TO app_admin
USING (true);

CREATE POLICY bypass_rls_policy ON members
FOR ALL TO app_admin
USING (true);

CREATE POLICY bypass_rls_policy ON work_items
FOR ALL TO app_admin
USING (true);

CREATE POLICY bypass_rls_policy ON shifts
FOR ALL TO app_admin
USING (true);

CREATE POLICY bypass_rls_policy ON shift_signups
FOR ALL TO app_admin
USING (true);