PostgreSQL 19 FORCE ROW LEVEL SECURITY と ON CONFLICT DO SELECT によるマルチテナント分離設計

CREATE SCHEMA IF NOT EXISTS sovereign_auth;

CREATE OR REPLACE FUNCTION sovereign_auth.current_tenant_id()
RETURNS uuid LANGUAGE sql STABLE PARALLEL SAFE AS $$
  SELECT NULLIF(current_setting('app.current_tenant_id', true), '')::uuid;
$$;

CREATE TABLE IF NOT EXISTS sovereign_auth.micro_users (
    user_id       UUID NOT NULL DEFAULT uuidv7(),
    tenant_id     UUID NOT NULL,
    provider      VARCHAR(32) NOT NULL,
    subject_hash  BYTEA NOT NULL CHECK (octet_length(subject_hash) = 32),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (tenant_id, user_id),
    UNIQUE (tenant_id, provider, subject_hash)
);

ALTER TABLE sovereign_auth.micro_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE sovereign_auth.micro_users FORCE ROW LEVEL SECURITY;

CREATE POLICY micro_users_tenant_isolation ON sovereign_auth.micro_users
    AS RESTRICTIVE FOR ALL TO PUBLIC
    USING (tenant_id = (SELECT sovereign_auth.current_tenant_id()))
    WITH CHECK (tenant_id = (SELECT sovereign_auth.current_tenant_id()));

-- 0-Byte WAL Idempotent Sign-In / Registration in PostgreSQL 19
INSERT INTO sovereign_auth.micro_users (tenant_id, provider, subject_hash)
VALUES ($1, $2, $3)
ON CONFLICT (tenant_id, provider, subject_hash) DO SELECT
RETURNING user_id, tenant_id, created_at;