| 1 | -- Database tests for web/supabase/migrations/20260909000113_cloud_facts_publication.sql. |
| 2 | -- |
| 3 | -- These run real statements as the real roles (anon, authenticated, |
| 4 | -- service_role) against a real PostgreSQL 15+ database; nothing here inspects |
| 5 | -- SQL text. pgTAP is not required — failures raise, and ON_ERROR_STOP aborts. |
| 6 | -- |
| 7 | -- createdb cw_facts_test |
| 8 | -- psql -v ON_ERROR_STOP=1 -d cw_facts_test -f web/supabase/tests/cloud_facts.test.sql |
| 9 | -- |
| 10 | -- Run it against a disposable local database only: it creates the anon / |
| 11 | -- authenticated / service_role roles when they are missing (service_role needs |
| 12 | -- BYPASSRLS, as on Supabase, which needs a superuser connection), applies the |
| 13 | -- migration, and ends with ROLLBACK so the database is left untouched. |
| 14 | -- |
| 15 | -- The base64 blobs below are syntactically valid placeholders, not keys and |
| 16 | -- not signatures: the database never verifies a signature, clients do, against |
| 17 | -- keys pinned in the binary and in web/lib/cloud-facts/keys.ts. |
| 18 | |
| 19 | \set ON_ERROR_STOP on |
| 20 | |
| 21 | begin; |
| 22 | |
| 23 | -- --------------------------------------------------------------------------- |
| 24 | -- Roles and schema under test |
| 25 | -- --------------------------------------------------------------------------- |
| 26 | |
| 27 | do $roles$ |
| 28 | begin |
| 29 | if not exists (select 1 from pg_roles where rolname = 'anon') then |
| 30 | create role anon nologin noinherit; |
| 31 | end if; |
| 32 | if not exists (select 1 from pg_roles where rolname = 'authenticated') then |
| 33 | create role authenticated nologin noinherit; |
| 34 | end if; |
| 35 | if not exists (select 1 from pg_roles where rolname = 'service_role') then |
| 36 | create role service_role nologin noinherit bypassrls; |
| 37 | end if; |
| 38 | if not (select rolbypassrls from pg_roles where rolname = 'service_role') then |
| 39 | raise exception 'service_role must have BYPASSRLS for the publisher path to work'; |
| 40 | end if; |
| 41 | execute format('grant anon, authenticated, service_role to %I', current_user); |
| 42 | end |
| 43 | $roles$; |
| 44 | |
| 45 | \ir ../migrations/20260909000113_cloud_facts_publication.sql |
| 46 | |
| 47 | -- --------------------------------------------------------------------------- |
| 48 | -- Fixture helpers (rolled back with everything else) |
| 49 | -- --------------------------------------------------------------------------- |
| 50 | |
| 51 | create function public.tst_ts(t timestamptz) returns text |
| 52 | language sql as $$ |
| 53 | select to_char(t at time zone 'utc', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') |
| 54 | $$; |
| 55 | |
| 56 | -- The shape web/scripts/facts-publish.mjs signs, minus the parts the database |
| 57 | -- does not constrain. |
| 58 | create function public.tst_payload( |
| 59 | p_channel text, p_version bigint, p_published timestamptz, |
| 60 | p_not_after timestamptz default null, p_applies text default '*' |
| 61 | ) returns jsonb |
| 62 | language sql as $$ |
| 63 | select jsonb_strip_nulls(jsonb_build_object( |
| 64 | 'schema_version', 1, |
| 65 | 'channel', p_channel, |
| 66 | 'facts_version', p_version, |
| 67 | 'published_at', public.tst_ts(p_published), |
| 68 | 'not_after', public.tst_ts(p_not_after), |
| 69 | 'applies_to', p_applies)) |
| 70 | || jsonb_build_object('models', '[]'::jsonb, 'provider_defaults', '{}'::jsonb, |
| 71 | 'release', null, 'announcements', '[]'::jsonb) |
| 72 | $$; |
| 73 | |
| 74 | -- encode(..., 'base64') wraps at 76 columns; the publisher never emits newlines |
| 75 | -- and neither may a hand-written insert. |
| 76 | create function public.tst_b64(p jsonb) returns text |
| 77 | language sql as $$ |
| 78 | select translate(encode(convert_to(p::text, 'utf8'), 'base64'), E'\n', '') |
| 79 | $$; |
| 80 | |
| 81 | create function public.tst_release( |
| 82 | p_slug text, p_version bigint, |
| 83 | p_published timestamptz default null, p_not_after timestamptz default null, |
| 84 | p_applies text default '*', p_key text default 'cwf-test-registry', |
| 85 | p_payload jsonb default null, p_b64 text default null, |
| 86 | p_sig text default null, p_sigs jsonb default '[]'::jsonb |
| 87 | ) returns void |
| 88 | language plpgsql as $$ |
| 89 | declare |
| 90 | pub timestamptz := date_trunc('second', coalesce(p_published, now() - interval '1 minute')); |
| 91 | expires timestamptz := date_trunc('second', p_not_after); |
| 92 | payload jsonb := coalesce(p_payload, public.tst_payload(p_slug, p_version, pub, expires, p_applies)); |
| 93 | inserted int; |
| 94 | begin |
| 95 | insert into public.facts_release ( |
| 96 | channel_id, facts_version, schema_version, envelope_version, applies_to, key_id, |
| 97 | payload_b64, sig_b64, sigs, payload, published_at, not_after, published_by, notes) |
| 98 | select c.id, p_version, 1, 1, p_applies, p_key, |
| 99 | coalesce(p_b64, public.tst_b64(payload)), coalesce(p_sig, repeat('A', 86) || '=='), |
| 100 | p_sigs, payload, pub, expires, 'operator@example.test', 'internal note' |
| 101 | from public.facts_channel c |
| 102 | where c.scope = 'global' and c.slug = p_slug; |
| 103 | get diagnostics inserted = row_count; |
| 104 | if inserted <> 1 then |
| 105 | raise exception 'fixture channel % is missing', p_slug; |
| 106 | end if; |
| 107 | end |
| 108 | $$; |
| 109 | |
| 110 | -- --------------------------------------------------------------------------- |
| 111 | -- Fixtures, written the way the publisher writes them: as service_role |
| 112 | -- --------------------------------------------------------------------------- |
| 113 | |
| 114 | do $fixtures$ |
| 115 | begin |
| 116 | set local role service_role; |
| 117 | insert into public.facts_key (key_id, scope, algorithm, public_key, status) |
| 118 | values ('cwf-test-registry', 'global', 'ed25519', repeat('A', 43) || '=', 'active'); |
| 119 | insert into public.facts_channel (scope, slug, visibility) values |
| 120 | ('global', 'stable', 'public'), |
| 121 | ('global', 'beta', 'private'), |
| 122 | ('global', 'future', 'public'), |
| 123 | ('global', 'expired', 'public'), |
| 124 | ('global', 'rollback', 'public'); |
| 125 | perform public.tst_release('stable', 7); |
| 126 | perform public.tst_release('beta', 3); |
| 127 | perform public.tst_release('future', 2, now() + interval '1 hour'); |
| 128 | perform public.tst_release('expired', 4, now() - interval '2 hours', now() - interval '1 hour'); |
| 129 | perform public.tst_release('rollback', 1); |
| 130 | perform public.tst_release('rollback', 2); |
| 131 | reset role; |
| 132 | end |
| 133 | $fixtures$; |
| 134 | |
| 135 | -- --------------------------------------------------------------------------- |
| 136 | -- 1. The publishable-key read path returns exactly one verifiable row |
| 137 | -- --------------------------------------------------------------------------- |
| 138 | |
| 139 | do $published_read$ |
| 140 | declare |
| 141 | rows_seen int; |
| 142 | v_release_id uuid; v_facts_version bigint; |
| 143 | v_schema_version int; v_envelope_version int; v_applies_to text; v_key_id text; |
| 144 | v_payload_b64 text; v_sig_b64 text; v_sigs jsonb; v_payload_sha256 text; |
| 145 | v_published_at timestamptz; v_not_after timestamptz; |
| 146 | v_payload jsonb; |
| 147 | begin |
| 148 | set local role anon; |
| 149 | -- Exactly the query web/lib/cloud-facts.ts sends over PostgREST. |
| 150 | select count(*) into rows_seen |
| 151 | from public.facts_current f where f.channel = 'stable' and f.scope = 'global'; |
| 152 | select f.release_id, f.facts_version, f.schema_version, |
| 153 | f.envelope_version, f.applies_to, f.key_id, f.payload_b64, f.sig_b64, |
| 154 | f.sigs, f.payload_sha256, f.published_at, f.not_after |
| 155 | into v_release_id, v_facts_version, v_schema_version, |
| 156 | v_envelope_version, v_applies_to, v_key_id, v_payload_b64, v_sig_b64, |
| 157 | v_sigs, v_payload_sha256, v_published_at, v_not_after |
| 158 | from public.facts_current f |
| 159 | where f.channel = 'stable' and f.scope = 'global' |
| 160 | limit 1; |
| 161 | reset role; |
| 162 | |
| 163 | if rows_seen <> 1 then |
| 164 | raise exception 'expected exactly 1 current row for stable, got %', rows_seen; |
| 165 | end if; |
| 166 | if v_facts_version <> 7 or v_schema_version <> 1 or v_envelope_version <> 1 |
| 167 | or v_applies_to <> '*' or v_key_id <> 'cwf-test-registry' or v_sigs <> '[]'::jsonb |
| 168 | or v_release_id is null or v_not_after is not null or v_published_at is null then |
| 169 | raise exception 'facts_current row does not carry the published contract: v=% schema=% envelope=% applies=% key=% sigs=%', |
| 170 | v_facts_version, v_schema_version, v_envelope_version, v_applies_to, v_key_id, v_sigs; |
| 171 | end if; |
| 172 | -- The digest is derived from the decoded signed bytes, not supplied. |
| 173 | if v_payload_sha256 is distinct from encode(sha256(decode(v_payload_b64, 'base64')), 'hex') |
| 174 | or v_payload_sha256 !~ '^[a-f0-9]{64}$' then |
| 175 | raise exception 'payload_sha256 % does not hash the decoded payload', v_payload_sha256; |
| 176 | end if; |
| 177 | v_payload := convert_from(decode(v_payload_b64, 'base64'), 'utf8')::jsonb; |
| 178 | if v_payload -> 'channel' <> to_jsonb('stable'::text) |
| 179 | or v_payload -> 'facts_version' <> to_jsonb(7::bigint) |
| 180 | or (v_payload ->> 'published_at')::timestamptz <> v_published_at then |
| 181 | raise exception 'signed payload disagrees with the row metadata'; |
| 182 | end if; |
| 183 | if v_sig_b64 !~ '^[A-Za-z0-9+/]{86}==$' then |
| 184 | raise exception 'envelope columns are not well formed'; |
| 185 | end if; |
| 186 | raise notice 'ok 1: facts_current serves one verifiable head row to anon'; |
| 187 | end |
| 188 | $published_read$; |
| 189 | |
| 190 | -- --------------------------------------------------------------------------- |
| 191 | -- 2. anon and authenticated cannot write anything, or read operator columns |
| 192 | -- --------------------------------------------------------------------------- |
| 193 | |
| 194 | do $write_refusals$ |
| 195 | declare |
| 196 | role_name text; |
| 197 | stmt text; |
| 198 | refused boolean; |
| 199 | releases_before bigint; |
| 200 | releases_after bigint; |
| 201 | begin |
| 202 | select count(*) into releases_before from public.facts_release; |
| 203 | foreach role_name in array array['anon', 'authenticated'] loop |
| 204 | foreach stmt in array array[ |
| 205 | 'insert into public.facts_channel (scope, slug, visibility) values (''global'', ''rogue'', ''public'')', |
| 206 | 'update public.facts_channel set visibility = ''public''', |
| 207 | 'delete from public.facts_channel', |
| 208 | 'insert into public.facts_key (key_id, public_key) values (''cwf-rogue'', ''x'')', |
| 209 | 'update public.facts_key set status = ''active''', |
| 210 | 'select public_key from public.facts_key', |
| 211 | 'insert into public.facts_release (channel_id, facts_version, applies_to, key_id, payload_b64, sig_b64, payload, published_at) values (gen_random_uuid(), 99, ''*'', ''cwf-test-registry'', ''e30='', ''x'', ''{}''::jsonb, now())', |
| 212 | 'update public.facts_release set status = ''revoked''', |
| 213 | 'delete from public.facts_release', |
| 214 | 'select payload from public.facts_release', |
| 215 | 'select published_by from public.facts_release', |
| 216 | 'select notes from public.facts_release', |
| 217 | 'select revoke_reason from public.facts_release', |
| 218 | 'select created_at from public.facts_channel' |
| 219 | ] loop |
| 220 | execute format('set local role %I', role_name); |
| 221 | refused := false; |
| 222 | begin |
| 223 | execute stmt; |
| 224 | exception when insufficient_privilege then |
| 225 | refused := true; |
| 226 | end; |
| 227 | reset role; |
| 228 | if not refused then |
| 229 | raise exception 'role % was not refused: %', role_name, stmt; |
| 230 | end if; |
| 231 | end loop; |
| 232 | |
| 233 | -- The read view is not a write surface either. |
| 234 | execute format('set local role %I', role_name); |
| 235 | refused := false; |
| 236 | begin |
| 237 | execute 'insert into public.facts_current (channel, facts_version) values (''stable'', 99)'; |
| 238 | exception when others then |
| 239 | refused := true; |
| 240 | end; |
| 241 | reset role; |
| 242 | if not refused then |
| 243 | raise exception 'role % was allowed to insert into facts_current', role_name; |
| 244 | end if; |
| 245 | end loop; |
| 246 | |
| 247 | select count(*) into releases_after from public.facts_release; |
| 248 | if releases_before <> releases_after then |
| 249 | raise exception 'refused writes still changed facts_release (% -> %)', releases_before, releases_after; |
| 250 | end if; |
| 251 | raise notice 'ok 2: anon and authenticated are refused every write and every operator column'; |
| 252 | end |
| 253 | $write_refusals$; |
| 254 | |
| 255 | -- --------------------------------------------------------------------------- |
| 256 | -- 3. Private, future-dated and expired releases never reach the public read |
| 257 | -- --------------------------------------------------------------------------- |
| 258 | |
| 259 | do $exclusions$ |
| 260 | declare |
| 261 | hidden_channel text; |
| 262 | anon_rows int; |
| 263 | service_rows int; |
| 264 | anon_channels int; |
| 265 | anon_beta_releases int; |
| 266 | begin |
| 267 | foreach hidden_channel in array array['beta', 'future', 'expired'] loop |
| 268 | set local role anon; |
| 269 | select count(*) into anon_rows from public.facts_current where channel = hidden_channel; |
| 270 | reset role; |
| 271 | set local role service_role; |
| 272 | select count(*) into service_rows from public.facts_current where channel = hidden_channel; |
| 273 | reset role; |
| 274 | if anon_rows <> 0 then |
| 275 | raise exception 'channel % leaked % row(s) to anon', hidden_channel, anon_rows; |
| 276 | end if; |
| 277 | -- service_role bypasses RLS, so this proves the view predicate itself. |
| 278 | if service_rows <> 0 then |
| 279 | raise exception 'channel % leaked % row(s) through the view predicate', hidden_channel, service_rows; |
| 280 | end if; |
| 281 | end loop; |
| 282 | |
| 283 | set local role anon; |
| 284 | select count(*) into anon_channels from public.facts_channel where slug = 'beta'; |
| 285 | select count(*) into anon_beta_releases from public.facts_release where facts_version = 3; |
| 286 | reset role; |
| 287 | if anon_channels <> 0 or anon_beta_releases <> 0 then |
| 288 | raise exception 'private channel is visible on the base tables: % channel row(s), % release row(s)', |
| 289 | anon_channels, anon_beta_releases; |
| 290 | end if; |
| 291 | raise notice 'ok 3: private, future-dated and expired releases are excluded from both surfaces'; |
| 292 | end |
| 293 | $exclusions$; |
| 294 | |
| 295 | -- --------------------------------------------------------------------------- |
| 296 | -- 4. Revoking the head does not silently roll back to an older version |
| 297 | -- --------------------------------------------------------------------------- |
| 298 | |
| 299 | do $revocation$ |
| 300 | declare |
| 301 | served bigint; |
| 302 | rows_seen int; |
| 303 | refused_code text; |
| 304 | begin |
| 305 | set local role anon; |
| 306 | select facts_version into served from public.facts_current where channel = 'rollback'; |
| 307 | reset role; |
| 308 | if served is distinct from 2::bigint then |
| 309 | raise exception 'expected the head version 2 before revocation, got %', coalesce(served::text, 'no row'); |
| 310 | end if; |
| 311 | |
| 312 | set local role service_role; |
| 313 | update public.facts_release r |
| 314 | set status = 'revoked', revoked_at = now(), revoke_reason = 'fixture revocation' |
| 315 | from public.facts_channel c |
| 316 | where c.id = r.channel_id and c.slug = 'rollback' and r.facts_version = 2; |
| 317 | reset role; |
| 318 | |
| 319 | set local role anon; |
| 320 | select count(*) into rows_seen from public.facts_current where channel = 'rollback'; |
| 321 | reset role; |
| 322 | if rows_seen <> 0 then |
| 323 | raise exception 'revoking the head served % row(s); version 1 must not come back', rows_seen; |
| 324 | end if; |
| 325 | |
| 326 | -- The supported repair is a higher version, not un-revocation or mutation. |
| 327 | set local role service_role; |
| 328 | refused_code := null; |
| 329 | begin |
| 330 | update public.facts_release r |
| 331 | set status = 'published', revoked_at = null, revoke_reason = null |
| 332 | from public.facts_channel c |
| 333 | where c.id = r.channel_id and c.slug = 'rollback' and r.facts_version = 2; |
| 334 | exception when others then refused_code := sqlstate; |
| 335 | end; |
| 336 | if refused_code is distinct from '23001' then |
| 337 | raise exception 'un-revoking a release was not refused (sqlstate %)', coalesce(refused_code, 'none'); |
| 338 | end if; |
| 339 | |
| 340 | refused_code := null; |
| 341 | begin |
| 342 | update public.facts_release r |
| 343 | set payload_b64 = 'QUFBQQ==' |
| 344 | from public.facts_channel c |
| 345 | where c.id = r.channel_id and c.slug = 'rollback' and r.facts_version = 1; |
| 346 | exception when others then refused_code := sqlstate; |
| 347 | end; |
| 348 | if refused_code is distinct from '23001' then |
| 349 | raise exception 'mutating a signed column was not refused (sqlstate %)', coalesce(refused_code, 'none'); |
| 350 | end if; |
| 351 | |
| 352 | perform public.tst_release('rollback', 3); |
| 353 | reset role; |
| 354 | |
| 355 | set local role anon; |
| 356 | select count(*) into rows_seen from public.facts_current where channel = 'rollback'; |
| 357 | select facts_version into served from public.facts_current where channel = 'rollback'; |
| 358 | reset role; |
| 359 | if rows_seen <> 1 or served is distinct from 3::bigint then |
| 360 | raise exception 'republishing above the revoked head served % row(s) at version %', |
| 361 | rows_seen, coalesce(served::text, 'none'); |
| 362 | end if; |
| 363 | raise notice 'ok 4: a revoked head blocks delivery, stays revoked, and is repaired by a higher version'; |
| 364 | end |
| 365 | $revocation$; |
| 366 | |
| 367 | -- --------------------------------------------------------------------------- |
| 368 | -- 5. Versions are unique and monotonic per channel; the read stays one row |
| 369 | -- --------------------------------------------------------------------------- |
| 370 | |
| 371 | do $monotonic$ |
| 372 | declare |
| 373 | code text; |
| 374 | rows_seen int; |
| 375 | served bigint; |
| 376 | stale bigint; |
| 377 | begin |
| 378 | set local role service_role; |
| 379 | foreach stale in array array[3::bigint, 7::bigint] loop |
| 380 | code := null; |
| 381 | begin |
| 382 | perform public.tst_release('stable', stale); |
| 383 | exception when others then code := sqlstate; |
| 384 | end; |
| 385 | if code is distinct from '23505' then |
| 386 | raise exception 'republishing stable version % was not refused (sqlstate %)', stale, coalesce(code, 'none'); |
| 387 | end if; |
| 388 | end loop; |
| 389 | perform public.tst_release('stable', 8); |
| 390 | reset role; |
| 391 | |
| 392 | set local role anon; |
| 393 | select count(*), max(facts_version) into rows_seen, served |
| 394 | from public.facts_current where channel = 'stable' and scope = 'global'; |
| 395 | reset role; |
| 396 | if rows_seen <> 1 or served is distinct from 8::bigint then |
| 397 | raise exception 'expected one row at version 8 after publishing, got % row(s) at %', |
| 398 | rows_seen, coalesce(served::text, 'none'); |
| 399 | end if; |
| 400 | raise notice 'ok 5: facts_version is unique and monotonic, and the read stays deterministic at one row'; |
| 401 | end |
| 402 | $monotonic$; |
| 403 | |
| 404 | -- --------------------------------------------------------------------------- |
| 405 | -- 6. Rows whose metadata, bytes, size or signatures do not hold are rejected |
| 406 | -- --------------------------------------------------------------------------- |
| 407 | |
| 408 | do $consistency$ |
| 409 | declare |
| 410 | pub timestamptz := date_trunc('second', now() - interval '1 minute'); |
| 411 | code text; |
| 412 | rows_seen int; |
| 413 | served bigint; |
| 414 | field text; |
| 415 | candidate jsonb; |
| 416 | begin |
| 417 | set local role service_role; |
| 418 | |
| 419 | -- The signed payload's channel must be the channel it is published on. |
| 420 | code := null; |
| 421 | begin |
| 422 | perform public.tst_release('stable', 20, p_published => pub, |
| 423 | p_payload => public.tst_payload('beta', 20, pub)); |
| 424 | exception when others then code := sqlstate; |
| 425 | end; |
| 426 | if code is distinct from '23514' then |
| 427 | raise exception 'cross-channel payload accepted (sqlstate %)', coalesce(code, 'none'); |
| 428 | end if; |
| 429 | |
| 430 | -- Outer facts_version must repeat the signed one. |
| 431 | code := null; |
| 432 | begin |
| 433 | perform public.tst_release('stable', 21, p_published => pub, |
| 434 | p_payload => public.tst_payload('stable', 22, pub)); |
| 435 | exception when others then code := sqlstate; |
| 436 | end; |
| 437 | if code is distinct from '23514' then |
| 438 | raise exception 'facts_version mismatch accepted (sqlstate %)', coalesce(code, 'none'); |
| 439 | end if; |
| 440 | |
| 441 | -- SQL CHECK accepts NULL unless the full predicate is explicitly true. |
| 442 | -- Missing signed metadata must not pass through three-valued SQL logic. |
| 443 | foreach field in array array['facts_version', 'schema_version', 'applies_to', 'published_at'] loop |
| 444 | for candidate in |
| 445 | select public.tst_payload('stable', 22, pub) - field |
| 446 | union all |
| 447 | select public.tst_payload('stable', 22, pub) || jsonb_build_object(field, null) |
| 448 | loop |
| 449 | code := null; |
| 450 | begin |
| 451 | perform public.tst_release('stable', 22, p_published => pub, p_payload => candidate); |
| 452 | exception when others then code := sqlstate; |
| 453 | end; |
| 454 | if code is distinct from '23514' then |
| 455 | raise exception 'missing/null signed % accepted (sqlstate %)', field, coalesce(code, 'none'); |
| 456 | end if; |
| 457 | end loop; |
| 458 | end loop; |
| 459 | |
| 460 | -- payload must be the decode of payload_b64. |
| 461 | code := null; |
| 462 | begin |
| 463 | perform public.tst_release('stable', 23, p_published => pub, |
| 464 | p_b64 => public.tst_b64(public.tst_payload('stable', 23, pub, null, '>=1.0.0'))); |
| 465 | exception when others then code := sqlstate; |
| 466 | end; |
| 467 | if code is distinct from '23514' then |
| 468 | raise exception 'payload divergent from the signed bytes accepted (sqlstate %)', coalesce(code, 'none'); |
| 469 | end if; |
| 470 | |
| 471 | -- Newline-wrapped base64 (what encode() produces) is not canonical. |
| 472 | code := null; |
| 473 | begin |
| 474 | perform public.tst_release('stable', 24, p_published => pub, |
| 475 | p_b64 => encode(convert_to(public.tst_payload('stable', 24, pub)::text, 'utf8'), 'base64')); |
| 476 | exception when others then code := sqlstate; |
| 477 | end; |
| 478 | if code is distinct from '23514' then |
| 479 | raise exception 'newline-wrapped payload_b64 accepted (sqlstate %)', coalesce(code, 'none'); |
| 480 | end if; |
| 481 | |
| 482 | -- Bounded size: a self-consistent payload above MAX_PAYLOAD_BYTES. |
| 483 | code := null; |
| 484 | begin |
| 485 | perform public.tst_release('stable', 25, p_published => pub, |
| 486 | p_payload => public.tst_payload('stable', 25, pub) || jsonb_build_object('pad', repeat('x', 600000))); |
| 487 | exception when others then code := sqlstate; |
| 488 | end; |
| 489 | if code is distinct from '23514' then |
| 490 | raise exception 'oversized payload_b64 accepted (sqlstate %)', coalesce(code, 'none'); |
| 491 | end if; |
| 492 | |
| 493 | -- A signature must at least be 64 bytes of canonical base64. |
| 494 | code := null; |
| 495 | begin |
| 496 | perform public.tst_release('stable', 26, p_published => pub, p_sig => repeat('A', 40)); |
| 497 | exception when others then code := sqlstate; |
| 498 | end; |
| 499 | if code is distinct from '23514' then |
| 500 | raise exception 'malformed sig_b64 accepted (sqlstate %)', coalesce(code, 'none'); |
| 501 | end if; |
| 502 | |
| 503 | -- Extra rotation signatures: bounded count and well-formed elements. |
| 504 | code := null; |
| 505 | begin |
| 506 | perform public.tst_release('stable', 27, p_published => pub, |
| 507 | p_sigs => (select jsonb_agg(jsonb_build_object('key_id', 'cwf-rotation', 'sig_b64', repeat('A', 86) || '==')) |
| 508 | from generate_series(1, 8))); |
| 509 | exception when others then code := sqlstate; |
| 510 | end; |
| 511 | if code is distinct from '23514' then |
| 512 | raise exception 'more than seven extra signatures accepted (sqlstate %)', coalesce(code, 'none'); |
| 513 | end if; |
| 514 | |
| 515 | code := null; |
| 516 | begin |
| 517 | perform public.tst_release('stable', 28, p_published => pub, |
| 518 | p_sigs => '[{"key_id": "bad id", "sig_b64": "AAAA"}]'::jsonb); |
| 519 | exception when others then code := sqlstate; |
| 520 | end; |
| 521 | if code is distinct from '23514' then |
| 522 | raise exception 'malformed extra signature accepted (sqlstate %)', coalesce(code, 'none'); |
| 523 | end if; |
| 524 | |
| 525 | -- Expiry must follow publication. |
| 526 | code := null; |
| 527 | begin |
| 528 | perform public.tst_release('stable', 29, p_published => pub, p_not_after => pub - interval '1 hour'); |
| 529 | exception when others then code := sqlstate; |
| 530 | end; |
| 531 | if code is distinct from '23514' then |
| 532 | raise exception 'not_after before published_at accepted (sqlstate %)', coalesce(code, 'none'); |
| 533 | end if; |
| 534 | |
| 535 | -- An unregistered key cannot be referenced. |
| 536 | code := null; |
| 537 | begin |
| 538 | perform public.tst_release('stable', 30, p_published => pub, p_key => 'cwf-not-registered'); |
| 539 | exception when others then code := sqlstate; |
| 540 | end; |
| 541 | if code is distinct from '23503' then |
| 542 | raise exception 'unregistered key_id accepted (sqlstate %)', coalesce(code, 'none'); |
| 543 | end if; |
| 544 | |
| 545 | -- Only global-scope channels may be public. |
| 546 | code := null; |
| 547 | begin |
| 548 | insert into public.facts_channel (scope, slug, visibility) values ('org-acme', 'stable', 'public'); |
| 549 | exception when others then code := sqlstate; |
| 550 | end; |
| 551 | if code is distinct from '23514' then |
| 552 | raise exception 'a non-global channel was made public (sqlstate %)', coalesce(code, 'none'); |
| 553 | end if; |
| 554 | reset role; |
| 555 | |
| 556 | -- None of the rejected inserts moved the channel high-water mark. |
| 557 | set local role anon; |
| 558 | select count(*), max(facts_version) into rows_seen, served |
| 559 | from public.facts_current where channel = 'stable' and scope = 'global'; |
| 560 | reset role; |
| 561 | if rows_seen <> 1 or served is distinct from 8::bigint then |
| 562 | raise exception 'rejected publications disturbed the channel: % row(s) at version %', |
| 563 | rows_seen, coalesce(served::text, 'none'); |
| 564 | end if; |
| 565 | raise notice 'ok 6: metadata, byte, size, signature, key and scope constraints all hold'; |
| 566 | end |
| 567 | $consistency$; |
| 568 | |
| 569 | -- --------------------------------------------------------------------------- |
| 570 | -- 7. The exposed surface is exactly the contract (no wildcard exposure) |
| 571 | -- --------------------------------------------------------------------------- |
| 572 | |
| 573 | do $surface$ |
| 574 | declare |
| 575 | view_columns text[]; |
| 576 | expected text[] := array[ |
| 577 | 'channel', 'scope', 'release_id', 'facts_version', 'schema_version', |
| 578 | 'envelope_version', 'applies_to', 'key_id', 'payload_b64', 'sig_b64', |
| 579 | 'sigs', 'payload_sha256', 'published_at', 'not_after']; |
| 580 | reader_role text; |
| 581 | hidden text; |
| 582 | visible text; |
| 583 | guarded text; |
| 584 | options text[]; |
| 585 | begin |
| 586 | select array_agg(column_name::text order by ordinal_position) into view_columns |
| 587 | from information_schema.columns |
| 588 | where table_schema = 'public' and table_name = 'facts_current'; |
| 589 | if view_columns is distinct from expected then |
| 590 | raise exception 'facts_current exposes % but the reader contract is %', view_columns, expected; |
| 591 | end if; |
| 592 | |
| 593 | select c.reloptions into options |
| 594 | from pg_class c join pg_namespace n on n.oid = c.relnamespace |
| 595 | where n.nspname = 'public' and c.relname = 'facts_current' and c.relkind = 'v'; |
| 596 | if options is null or not ('security_invoker=true' = any(options)) then |
| 597 | raise exception 'facts_current is not a security_invoker view (reloptions %)', options; |
| 598 | end if; |
| 599 | |
| 600 | foreach guarded in array array['facts_channel', 'facts_key', 'facts_release'] loop |
| 601 | if not (select relrowsecurity from pg_class c join pg_namespace n on n.oid = c.relnamespace |
| 602 | where n.nspname = 'public' and c.relname = guarded) then |
| 603 | raise exception 'row level security is not enabled on public.%', guarded; |
| 604 | end if; |
| 605 | end loop; |
| 606 | |
| 607 | foreach reader_role in array array['anon', 'authenticated'] loop |
| 608 | if not has_table_privilege(reader_role, 'public.facts_current', 'select') then |
| 609 | raise exception 'role % cannot read facts_current', reader_role; |
| 610 | end if; |
| 611 | if has_table_privilege(reader_role, 'public.facts_key', 'select') then |
| 612 | raise exception 'role % can read the key registry', reader_role; |
| 613 | end if; |
| 614 | foreach guarded in array array['public.facts_channel', 'public.facts_key', 'public.facts_release', 'public.facts_current'] loop |
| 615 | if has_table_privilege(reader_role, guarded, 'insert') |
| 616 | or has_table_privilege(reader_role, guarded, 'update') |
| 617 | or has_table_privilege(reader_role, guarded, 'delete') then |
| 618 | raise exception 'role % holds a write privilege on %', reader_role, guarded; |
| 619 | end if; |
| 620 | end loop; |
| 621 | foreach hidden in array array['payload', 'published_by', 'notes', 'revoked_at', 'revoke_reason', 'created_at'] loop |
| 622 | if has_column_privilege(reader_role, 'public.facts_release', hidden, 'select') then |
| 623 | raise exception 'role % can read operator column facts_release.%', reader_role, hidden; |
| 624 | end if; |
| 625 | end loop; |
| 626 | foreach visible in array array['payload_b64', 'sig_b64', 'sigs', 'payload_sha256', 'published_at', 'not_after'] loop |
| 627 | if not has_column_privilege(reader_role, 'public.facts_release', visible, 'select') then |
| 628 | raise exception 'role % cannot read published column facts_release.%', reader_role, visible; |
| 629 | end if; |
| 630 | end loop; |
| 631 | end loop; |
| 632 | |
| 633 | if not has_table_privilege('service_role', 'public.facts_release', 'insert') |
| 634 | or not has_table_privilege('service_role', 'public.facts_release', 'update') |
| 635 | or not has_table_privilege('service_role', 'public.facts_key', 'insert') then |
| 636 | raise exception 'the publisher role cannot publish'; |
| 637 | end if; |
| 638 | if has_table_privilege('service_role', 'public.facts_release', 'delete') then |
| 639 | raise exception 'the publisher role can delete publication history'; |
| 640 | end if; |
| 641 | raise notice 'ok 7: grants, policies and the view surface match the delivery contract'; |
| 642 | end |
| 643 | $surface$; |
| 644 | |
| 645 | select 'cloud facts storage: all assertions passed' as result; |
| 646 | |
| 647 | rollback; |
| 648 |