Row-Level Security

ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.orders FORCE ROW LEVEL SECURITY;
 
CREATE POLICY orders_tenant_isolation ON app.orders
  FOR ALL
  TO app_api
  USING (tenant_id = current_setting('app.tenant_id')::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

-- Application sets tenant per request (connection pool safe with SET LOCAL)
BEGIN;
SET LOCAL app.tenant_id = '42';
SELECT * FROM app.orders;
COMMIT;

CREATE TABLE app.invoices (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  amount numeric(12,2) NOT NULL
);
 
ALTER TABLE app.invoices ENABLE ROW LEVEL SECURITY;
 
-- Read-only policy for analysts
CREATE POLICY invoices_select ON app.invoices
  FOR SELECT TO app_ro
  USING (tenant_id = ANY (current_setting('app.allowed_tenants')::bigint[]));
 
-- App write policy
CREATE POLICY invoices_write ON app.invoices
  FOR INSERT TO app_api
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);
 
CREATE POLICY invoices_update ON app.invoices
  FOR UPDATE TO app_api
  USING (tenant_id = current_setting('app.tenant_id')::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

ALTER TABLE t ENABLE ROW LEVEL SECURITY;   -- owner bypasses unless FORCE
ALTER TABLE t FORCE ROW LEVEL SECURITY;    -- owner subject to policies too

-- Session variable (common with ORMs)
SET LOCAL app.tenant_id = '99';
 
-- JWT claim via custom GUC set in connection startup (application hook)
-- current_setting('request.jwt.claim.tenant_id', true)
 
-- Subquery policy for org hierarchy
USING (tenant_id IN (SELECT tenant_id FROM app.user_tenants WHERE user_id = current_user_id()))