Files
aevolution/migrations/0002_region_fencing.sql
dingoandClaude Opus 5.5 2496649a9b Complete M7.3 fenced region ownership
ADR-0060 and schema v2 add database-time region leases and a fenced head
advance. Acquire/renew/release/advance lock the lease row, check holder,
epoch and expiry with clock_timestamp() after the lock wait, and the
advance compares the exact predecessor. Holders are DB-generated tokens;
epochs never wrap or repeat. The runtime group role gets EXECUTE on five
SECURITY DEFINER functions only - no table, provisioning or CREATE rights.
Migration history admits clean->v2, v1->v2 and a v2 no-op, and refuses
privileged runtime roles.

Host side: portable LeaseAuthority fences from send-time deadlines on
CLOCK_BOOTTIME, keeps the earlier deadline on unknown renewal and stays
fenced until a newer epoch. Selected timings: 20s lease, 5s renew, 2s
margin, 2s renew and 5s commit deadlines. Linux evo::postgres wraps the
functions with typed LeaseStoreError results.

Validated: Windows CTest 55/55; WSL Release, clang-tidy and ASan/UBSan
private-PostgreSQL suites (contenders, expiry, paused/queued stale
writers, unknown renewal, exhaustion, bypass denial, upgrade path).
Three SQL mutations were each caught. Docker/Gitea CI, remote TLS and
cluster runs were not executed. No director, host or deployment added.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-09-28 11:14:32 -04:00

215 lines
10 KiB
PL/PgSQL

-- M7.3 fenced region ownership (ADR-0060). Owner-only definitions; the runtime
-- role receives schema USAGE and EXECUTE on the guarded functions, never tables.
-- Every mutation locks the region lease row first and decides expiry with the
-- database clock read after that lock. Clients never supply timestamps.
SELECT pg_catalog.set_config('evolution.runtime_role', :'runtime_role', true);
DO $$
DECLARE
runtime name := pg_catalog.current_setting('evolution.runtime_role');
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles r WHERE r.rolname = runtime
AND NOT r.rolsuper AND NOT r.rolcreaterole AND NOT r.rolcreatedb
AND NOT r.rolreplication AND NOT r.rolbypassrls)
OR runtime = current_user
OR pg_catalog.pg_has_role(runtime, current_user, 'MEMBER') THEN
RAISE EXCEPTION 'runtime role must exist, be unprivileged and not inherit the owner';
END IF;
END $$;
CREATE TABLE evolution.region_heads (
world_id evolution.u64 NOT NULL,
region_id evolution.u64 NOT NULL,
commit_sequence evolution.u64 NOT NULL,
commit_id uuid NOT NULL,
digest bytea NOT NULL CHECK (octet_length(digest) = 32),
end_tick evolution.u64 NOT NULL,
published_epoch evolution.u64 NOT NULL,
PRIMARY KEY (world_id, region_id),
FOREIGN KEY (world_id, region_id) REFERENCES evolution.region_leases,
-- Genesis is sequence 0, tick 0, provisioned outside any lease epoch.
CHECK ((commit_sequence = 0) = (end_tick = 0)),
CHECK ((commit_sequence = 0) = (published_epoch = 0))
);
REVOKE ALL ON evolution.region_heads FROM PUBLIC;
-- Owner-only provisioning; recovery and runtime can never create genesis.
CREATE FUNCTION evolution.provision_region(p_world evolution.u64, p_region evolution.u64,
p_frame evolution.u64, p_genesis_commit uuid, p_genesis_digest bytea)
RETURNS void LANGUAGE sql SECURITY INVOKER SET search_path = pg_catalog, pg_temp AS $$
INSERT INTO evolution.region_leases(world_id, region_id, frame_id)
VALUES (p_world, p_region, p_frame);
INSERT INTO evolution.region_heads(world_id, region_id, commit_sequence, commit_id,
digest, end_tick, published_epoch)
VALUES (p_world, p_region, 0, p_genesis_commit, p_genesis_digest, 0, 0);
$$;
CREATE FUNCTION evolution.acquire_region_lease(p_world evolution.u64,
p_region evolution.u64, p_duration_ms integer)
RETURNS TABLE (lease_holder uuid, lease_epoch evolution.u64, region_frame evolution.u64,
granted_ms integer)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
DECLARE
l evolution.region_leases%ROWTYPE;
t timestamptz;
BEGIN
IF p_world IS NULL OR p_region IS NULL OR p_duration_ms IS NULL
OR p_duration_ms NOT BETWEEN 1000 AND 60000 THEN
RAISE EXCEPTION 'invalid lease request' USING ERRCODE = '22023';
END IF;
SELECT * INTO l FROM evolution.region_leases r
WHERE r.world_id = p_world AND r.region_id = p_region FOR UPDATE;
IF NOT FOUND THEN
RAISE EXCEPTION 'region not provisioned' USING ERRCODE = 'EVL04';
END IF;
t := clock_timestamp();
IF l.holder IS NOT NULL AND l.expires_at > t THEN
RAISE EXCEPTION 'region lease held' USING ERRCODE = 'EVL01';
END IF;
IF l.epoch = 18446744073709551615 THEN
RAISE EXCEPTION 'lease epochs exhausted' USING ERRCODE = 'EVL05';
END IF;
UPDATE evolution.region_leases r
SET epoch = l.epoch + 1, holder = gen_random_uuid(),
expires_at = t + p_duration_ms * interval '1 millisecond'
WHERE r.world_id = p_world AND r.region_id = p_region
RETURNING r.holder, r.epoch, r.frame_id INTO lease_holder, lease_epoch, region_frame;
granted_ms := p_duration_ms;
RETURN NEXT;
END $$;
-- Locks the lease row and requires the exact unexpired holder/epoch. Internal:
-- callable only by the owner and the SECURITY DEFINER functions below.
CREATE FUNCTION evolution.check_region_lease(p_world evolution.u64, p_region evolution.u64,
p_holder uuid, p_epoch evolution.u64)
RETURNS timestamptz LANGUAGE plpgsql SECURITY INVOKER
SET search_path = pg_catalog, pg_temp AS $$
DECLARE
l evolution.region_leases%ROWTYPE;
t timestamptz;
BEGIN
IF p_world IS NULL OR p_region IS NULL OR p_holder IS NULL OR p_epoch IS NULL THEN
RAISE EXCEPTION 'invalid lease request' USING ERRCODE = '22023';
END IF;
SELECT * INTO l FROM evolution.region_leases r
WHERE r.world_id = p_world AND r.region_id = p_region FOR UPDATE;
IF NOT FOUND THEN
RAISE EXCEPTION 'region not provisioned' USING ERRCODE = 'EVL04';
END IF;
-- Read after the lock wait: a queued stale writer sees current database time.
t := clock_timestamp();
IF l.holder IS DISTINCT FROM p_holder OR l.epoch <> p_epoch OR l.expires_at <= t THEN
RAISE EXCEPTION 'region lease fenced' USING ERRCODE = 'EVL02';
END IF;
RETURN t;
END $$;
CREATE FUNCTION evolution.renew_region_lease(p_world evolution.u64, p_region evolution.u64,
p_holder uuid, p_epoch evolution.u64, p_duration_ms integer)
RETURNS integer LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp AS $$
DECLARE
t timestamptz;
BEGIN
IF p_duration_ms IS NULL OR p_duration_ms NOT BETWEEN 1000 AND 60000 THEN
RAISE EXCEPTION 'invalid lease request' USING ERRCODE = '22023';
END IF;
-- Renewal never revives an expired or superseded lease.
t := evolution.check_region_lease(p_world, p_region, p_holder, p_epoch);
UPDATE evolution.region_leases r
SET expires_at = t + p_duration_ms * interval '1 millisecond'
WHERE r.world_id = p_world AND r.region_id = p_region;
RETURN p_duration_ms;
END $$;
CREATE FUNCTION evolution.release_region_lease(p_world evolution.u64, p_region evolution.u64,
p_holder uuid, p_epoch evolution.u64)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp AS $$
BEGIN
PERFORM evolution.check_region_lease(p_world, p_region, p_holder, p_epoch);
-- The epoch is retained so the next acquisition can never reuse it.
UPDATE evolution.region_leases r SET holder = NULL, expires_at = NULL
WHERE r.world_id = p_world AND r.region_id = p_region;
END $$;
CREATE FUNCTION evolution.load_region_head(p_world evolution.u64, p_region evolution.u64)
RETURNS TABLE (region_frame evolution.u64, head_sequence evolution.u64, head_commit uuid,
head_digest text, head_tick evolution.u64)
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
BEGIN
RETURN QUERY SELECT l.frame_id, h.commit_sequence, h.commit_id,
encode(h.digest, 'hex'), h.end_tick
FROM evolution.region_heads h
JOIN evolution.region_leases l USING (world_id, region_id)
WHERE h.world_id = p_world AND h.region_id = p_region;
IF NOT FOUND THEN
RAISE EXCEPTION 'region not provisioned' USING ERRCODE = 'EVL04';
END IF;
END $$;
-- The fenced transition kernel. M7.4/M7.5 publication wraps it with manifest,
-- decision and outbox rows in the same transaction; the lease lock is taken first.
CREATE FUNCTION evolution.advance_region_head(p_world evolution.u64, p_region evolution.u64,
p_holder uuid, p_epoch evolution.u64, p_expected_sequence evolution.u64,
p_expected_commit uuid, p_expected_digest bytea, p_commit uuid, p_digest bytea,
p_end_tick evolution.u64)
RETURNS TABLE (region_frame evolution.u64, head_sequence evolution.u64, head_commit uuid,
head_digest text, head_tick evolution.u64)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
DECLARE
h evolution.region_heads%ROWTYPE;
BEGIN
IF p_expected_sequence IS NULL OR p_expected_commit IS NULL OR p_commit IS NULL
OR p_end_tick IS NULL OR p_commit = p_expected_commit
OR octet_length(p_expected_digest) IS DISTINCT FROM 32
OR octet_length(p_digest) IS DISTINCT FROM 32 THEN
RAISE EXCEPTION 'invalid head advance' USING ERRCODE = '22023';
END IF;
PERFORM evolution.check_region_lease(p_world, p_region, p_holder, p_epoch);
SELECT * INTO h FROM evolution.region_heads r
WHERE r.world_id = p_world AND r.region_id = p_region FOR UPDATE;
IF NOT FOUND THEN
RAISE EXCEPTION 'region not provisioned' USING ERRCODE = 'EVL04';
END IF;
IF h.commit_sequence <> p_expected_sequence OR h.commit_id <> p_expected_commit
OR h.digest <> p_expected_digest THEN
RAISE EXCEPTION 'region head moved' USING ERRCODE = 'EVL03';
END IF;
IF h.commit_sequence = 18446744073709551615 THEN
RAISE EXCEPTION 'commit sequences exhausted' USING ERRCODE = 'EVL05';
END IF;
IF p_end_tick <= h.end_tick OR p_end_tick - h.end_tick > 64 THEN
RAISE EXCEPTION 'invalid head advance' USING ERRCODE = '22023';
END IF;
UPDATE evolution.region_heads r
SET commit_sequence = h.commit_sequence + 1, commit_id = p_commit, digest = p_digest,
end_tick = p_end_tick, published_epoch = p_epoch
WHERE r.world_id = p_world AND r.region_id = p_region;
RETURN QUERY SELECT l.frame_id, n.commit_sequence, n.commit_id, encode(n.digest, 'hex'),
n.end_tick
FROM evolution.region_heads n
JOIN evolution.region_leases l USING (world_id, region_id)
WHERE n.world_id = p_world AND n.region_id = p_region;
END $$;
REVOKE ALL ON FUNCTION
evolution.provision_region(evolution.u64, evolution.u64, evolution.u64, uuid, bytea),
evolution.acquire_region_lease(evolution.u64, evolution.u64, integer),
evolution.check_region_lease(evolution.u64, evolution.u64, uuid, evolution.u64),
evolution.renew_region_lease(evolution.u64, evolution.u64, uuid, evolution.u64, integer),
evolution.release_region_lease(evolution.u64, evolution.u64, uuid, evolution.u64),
evolution.load_region_head(evolution.u64, evolution.u64),
evolution.advance_region_head(evolution.u64, evolution.u64, uuid, evolution.u64,
evolution.u64, uuid, bytea, uuid, bytea, evolution.u64)
FROM PUBLIC;
GRANT USAGE ON SCHEMA evolution TO :"runtime_role";
GRANT EXECUTE ON FUNCTION
evolution.acquire_region_lease(evolution.u64, evolution.u64, integer),
evolution.renew_region_lease(evolution.u64, evolution.u64, uuid, evolution.u64, integer),
evolution.release_region_lease(evolution.u64, evolution.u64, uuid, evolution.u64),
evolution.load_region_head(evolution.u64, evolution.u64),
evolution.advance_region_head(evolution.u64, evolution.u64, uuid, evolution.u64,
evolution.u64, uuid, bytea, uuid, bytea, evolution.u64)
TO :"runtime_role";
INSERT INTO evolution.schema_migrations(version, sha256) VALUES (2, :'migration_sha_2');