CREATE TABLE invoices (
id uuid PRIMARY KEY DEFAULT uuidv7(),
tenant_id uuid NOT NULL,
amount_cents bigint NOT NULL CHECK (amount_cents > 0),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX invoices_tenant_created ON invoices (tenant_id, created_at);
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
-- Missing tenant setting returns NULL, so no rows match (fail closed).
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);
CREATE ROLE app_user LOGIN; -- not the table owner, no BYPASSRLS
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;
-- Per request, transaction-scoped (safe behind PgBouncer transaction mode):
BEGIN;
SELECT set_config('app.tenant_id', '0192f0c1-7a4e-7cc0-9d1a-2b3c4d5e6f70', true);
SELECT id, amount_cents FROM invoices ORDER BY created_at DESC LIMIT 20;
COMMIT;