Row-Level Security Tenancy

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
 
CREATE POLICY invoices_tenant_select ON invoices
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);
 
CREATE POLICY invoices_tenant_insert ON invoices
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);
 
CREATE POLICY invoices_tenant_update ON invoices
  FOR UPDATE
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

BEGIN;
 
CREATE TABLE tenants (
  tenant_id  uuid PRIMARY KEY,
  tier       text NOT NULL CHECK (tier IN ('starter', 'business', 'enterprise'))
);
 
CREATE TABLE files (
  tenant_id  uuid NOT NULL REFERENCES tenants (tenant_id),
  file_id    bigint GENERATED ALWAYS AS IDENTITY,
  name       text NOT NULL,
  PRIMARY KEY (tenant_id, file_id)
);
 
ALTER TABLE files ENABLE ROW LEVEL SECURITY;
ALTER TABLE files FORCE ROW LEVEL SECURITY;
 
-- Standard tenant isolation policy
CREATE POLICY files_tenant_isolation ON files
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);
 
-- Platform support read-only bypass for enterprise tier tickets only
CREATE ROLE support_reader;
CREATE POLICY files_support_read ON files
  FOR SELECT
  TO support_reader
  USING (
    EXISTS (
      SELECT 1 FROM tenants t
      WHERE t.tenant_id = files.tenant_id
        AND t.tier = 'enterprise'
    )
  );
 
GRANT SELECT ON files TO support_reader;
GRANT support_reader TO support_agent;
 
COMMIT;

-- Verify RLS enabled on all tenant tables
SELECT c.relname, c.relrowsecurity, c.relforcerowsecurity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
  AND c.relname NOT LIKE 'pg_%';