-- ADR-0131: Keycloak identities map to stable game accounts, and the grant broker takes -- strictly increasing grant serials for them. Logout raises the world's accepted serial -- past every issued grant and expires the account's current control, so outstanding -- grants are stale and a live session closes at its next renewal. CREATE TABLE evolution.account_identities ( issuer text NOT NULL CHECK (octet_length(issuer) BETWEEN 1 AND 256), subject text NOT NULL CHECK (octet_length(subject) BETWEEN 1 AND 255), account_id uuid NOT NULL UNIQUE CHECK (account_id <> '00000000-0000-0000-0000-000000000000'), last_serial evolution.u64 NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT clock_timestamp(), PRIMARY KEY (issuer, subject) ); REVOKE ALL ON evolution.account_identities FROM PUBLIC; -- The account for (issuer, subject), created on first use, its next grant serial (above -- both the last one issued and the newest one the world accepted) and the region holding -- its active life, if any. CREATE FUNCTION evolution.issue_account_grant(p_world evolution.u64, p_issuer text, p_subject text) RETURNS TABLE(grant_account uuid, grant_serial evolution.u64, home_region evolution.u64) LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ DECLARE r evolution.account_identities%ROWTYPE; accepted numeric; next_serial numeric; BEGIN IF p_world IS NULL OR p_world = 0 OR p_issuer IS NULL OR p_subject IS NULL OR octet_length(p_issuer) NOT BETWEEN 1 AND 256 OR octet_length(p_subject) NOT BETWEEN 1 AND 255 THEN RAISE EXCEPTION 'invalid account identity' USING ERRCODE = '22023'; END IF; INSERT INTO evolution.account_identities(issuer, subject, account_id) VALUES (p_issuer, p_subject, gen_random_uuid()) ON CONFLICT (issuer, subject) DO NOTHING; SELECT * INTO r FROM evolution.account_identities x WHERE x.issuer = p_issuer AND x.subject = p_subject FOR UPDATE; SELECT c.serial INTO accepted FROM evolution.account_controls c WHERE c.world_id = p_world AND c.account_id = r.account_id; next_serial := greatest(r.last_serial, coalesce(accepted, 0)) + 1; IF next_serial > 18446744073709551615 THEN RAISE EXCEPTION 'grant serials exhausted' USING ERRCODE = 'EVL05'; END IF; UPDATE evolution.account_identities x SET last_serial = next_serial WHERE x.issuer = p_issuer AND x.subject = p_subject; grant_account := r.account_id; grant_serial := next_serial; SELECT l.region_id INTO home_region FROM evolution.life_reservations l WHERE l.world_id = p_world AND l.account_id = r.account_id AND l.rejection = 0 AND l.ended_commit IS NULL; RETURN NEXT; END $$; -- Logout: every grant issued so far becomes stale and the current control expires now. -- False when the identity never had an account (nothing to revoke). CREATE FUNCTION evolution.revoke_account_grants(p_world evolution.u64, p_issuer text, p_subject text) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ DECLARE r evolution.account_identities%ROWTYPE; BEGIN IF p_world IS NULL OR p_world = 0 OR p_issuer IS NULL OR p_subject IS NULL THEN RAISE EXCEPTION 'invalid account identity' USING ERRCODE = '22023'; END IF; SELECT * INTO r FROM evolution.account_identities x WHERE x.issuer = p_issuer AND x.subject = p_subject FOR UPDATE; IF NOT FOUND THEN RETURN false; END IF; INSERT INTO evolution.account_controls AS a (world_id, account_id, serial, generation) VALUES (p_world, r.account_id, greatest(r.last_serial, 1), 1) ON CONFLICT (world_id, account_id) DO UPDATE SET serial = greatest(a.serial, EXCLUDED.serial), expires_at = CASE WHEN a.region_id IS NULL THEN NULL ELSE least(a.expires_at, clock_timestamp()) END; RETURN true; END $$; REVOKE ALL ON FUNCTION evolution.issue_account_grant(evolution.u64, text, text), evolution.revoke_account_grants(evolution.u64, text, text) FROM PUBLIC; GRANT EXECUTE ON FUNCTION evolution.issue_account_grant(evolution.u64, text, text), evolution.revoke_account_grants(evolution.u64, text, text) TO :"runtime_role"; INSERT INTO evolution.schema_migrations(version, sha256) VALUES (39, :'migration_sha_39');