Files
faunosteo/supabase/tests/012_workspaces.sql
bchanot 7bf4fb6079 feat(supabase): workspaces M1, one personal workspace per account
V1-1.1 (contract V1-1_OWNERS_ANIMALS.md §4, §8, §9). Tables workspaces and workspace_members, created with the profile by trigger through one atomic, idempotent write path; deferred invariant (a personal workspace has exactly its account as holder); backfill of existing profiles; access helpers caller_workspace_ids (active profile, confirmed email) and writable_workspace_ids; RLS select for members only; no privilege for anon or service_role. log_event keeps x-correlation-id only as a short token. pgTAP 012 (85 checks), census and types updated, config test.
2026-10-25 19:53:16 +01:00

459 lines
26 KiB
PL/PgSQL

-- V1-1.1 Workspaces (M1): creation with the account, uniqueness, the commit
-- invariant, the backfill, isolation between accounts, refusals for every
-- other actor, privileges and the definer census, the audit correlation cap.
-- Contract: docs/development/V1-1_OWNERS_ANIMALS.md §4, §8, §9, §12.1.
-- Actors: A and B confirmed and active; C unconfirmed; D deactivated; S a
-- platform owner; E an empty account deleted; F and G profiles created with
-- the trigger disabled (backfill, invariant).
begin;
select plan(85);
insert into auth.users (id, email, email_confirmed_at) values
('50000000-0000-0000-0000-00000000000a', 'alice-ws@test.local', now()),
('50000000-0000-0000-0000-00000000000b', 'bob-ws@test.local', now()),
('50000000-0000-0000-0000-00000000000c', 'carol-ws@test.local', null),
('50000000-0000-0000-0000-00000000000d', 'dave-ws@test.local', now()),
('50000000-0000-0000-0000-000000000005', 'staff-ws@test.local', now()),
('50000000-0000-0000-0000-00000000000e', 'empty-ws@test.local', now());
update public.profiles set active = false where id = '50000000-0000-0000-0000-00000000000d';
select private.assign_platform_role(null, '50000000-0000-0000-0000-000000000005', 'owner');
-- Fixture readers usable from every role (pg_temp objects belong to postgres).
create function pg_temp.ws(p_user uuid) returns uuid language sql stable security definer
as $$ select id from public.workspaces where personal_user_id = p_user $$;
create function pg_temp.act_as(p_user uuid) returns text language sql
as $$ select set_config('request.jwt.claims',
json_build_object('sub', p_user, 'role', 'authenticated')::text, true) $$;
create function pg_temp.writable() returns setof uuid language sql stable security definer
as $$ select private.writable_workspace_ids() $$;
create table pg_temp.audit_ids (key text primary key, id uuid not null);
grant execute on function pg_temp.ws(uuid) to anon, authenticated, service_role;
grant execute on function pg_temp.act_as(uuid) to anon, authenticated, service_role;
grant execute on function pg_temp.writable() to anon, authenticated, service_role;
-- ---------------------------------------------------------------------------
-- 1. Schema
-- ---------------------------------------------------------------------------
select has_table('public', 'workspaces', 'table public.workspaces exists');
select has_table('public', 'workspace_members', 'table public.workspace_members exists');
select enum_has_labels('public', 'workspace_kind', array['personal'], 'workspace_kind: personal only (cabinet comes with its step)');
select enum_has_labels('public', 'workspace_member_role', array['holder'], 'workspace_member_role: holder only (no unused cabinet role)');
select ok((select relrowsecurity from pg_class where oid = 'public.workspaces'::regclass), 'RLS enabled on workspaces');
select ok((select relrowsecurity from pg_class where oid = 'public.workspace_members'::regclass), 'RLS enabled on workspace_members');
select is(
(select string_agg(tablename || ':' || tableowner, ',' order by tablename collate "C")
from pg_tables where schemaname = 'public' and tablename in ('workspaces', 'workspace_members')),
'workspace_members:postgres,workspaces:postgres', 'both tables belong to postgres (the migration role)'
);
-- ---------------------------------------------------------------------------
-- 2. Creation with the account (sign-up chain auth.users -> profiles -> workspace)
-- ---------------------------------------------------------------------------
select is(
(select count(*) from public.workspaces where personal_user_id::text like '50000000-%'),
6::bigint, 'every new account got a workspace, confirmed or not'
);
select results_eq(
$$ select kind::text, personal_user_id from public.workspaces
where personal_user_id = '50000000-0000-0000-0000-00000000000a' $$,
$$ values ('personal'::text, '50000000-0000-0000-0000-00000000000a'::uuid) $$,
'A has one personal workspace of its own'
);
select results_eq(
$$ select user_id, member_role::text from public.workspace_members
where workspace_id = pg_temp.ws('50000000-0000-0000-0000-00000000000a') $$,
$$ values ('50000000-0000-0000-0000-00000000000a'::uuid, 'holder'::text) $$,
'the workspace of A has exactly A as holder'
);
select is(
(select count(*) from public.workspace_members m join public.workspaces w on w.id = m.workspace_id
where w.personal_user_id::text like '50000000-%' and m.user_id = w.personal_user_id and m.member_role = 'holder'),
6::bigint, 'every fixture workspace has its account as holder'
);
select isnt(
pg_temp.ws('50000000-0000-0000-0000-00000000000a'), pg_temp.ws('50000000-0000-0000-0000-00000000000b'),
'A and B have two distinct workspaces'
);
-- A sign-up that fails after the chain ran leaves no account, profile or workspace.
select throws_ok(
$$ do $b$ begin
insert into auth.users (id, email, email_confirmed_at)
values ('50000000-0000-0000-0000-000000000099', 'boom-ws@test.local', now());
raise exception 'boom' using errcode = 'P0001';
end $b$ $$,
'P0001', 'boom', 'a sign-up transaction that fails after the trigger raises'
);
select is(
(select count(*) from public.workspaces where personal_user_id = '50000000-0000-0000-0000-000000000099'),
0::bigint, 'no workspace survives the failed sign-up'
);
select is(
(select count(*) from public.profiles where id = '50000000-0000-0000-0000-000000000099'),
0::bigint, 'no profile survives the failed sign-up'
);
-- ---------------------------------------------------------------------------
-- 3. Uniqueness and idempotence
-- ---------------------------------------------------------------------------
select is(private.ensure_personal_workspace('50000000-0000-0000-0000-00000000000a'), false,
'a replayed creation for A writes nothing and says so');
select is(
(select count(*) from public.workspaces where personal_user_id = '50000000-0000-0000-0000-00000000000a'),
1::bigint, 'A still has one workspace after the replay'
);
select is(
(select count(*) from public.workspace_members where user_id = '50000000-0000-0000-0000-00000000000a'),
1::bigint, 'A still has one membership after the replay'
);
select throws_ok(
$$ insert into public.workspaces (kind, personal_user_id) values ('personal', '50000000-0000-0000-0000-00000000000a') $$,
'23505', null, 'a second personal workspace for A is refused by the unique key'
);
select throws_ok(
$$ insert into public.workspaces (kind, personal_user_id) values ('personal', null) $$,
'23514', null, 'a personal workspace without an account is refused'
);
select throws_ok(
$$ insert into public.workspaces (kind, personal_user_id) values ('personal', '50000000-0000-0000-0000-0000000000ff') $$,
'23503', null, 'a personal workspace for an unknown account is refused'
);
select throws_ok(
$$ insert into public.workspace_members (workspace_id, user_id, member_role)
values (gen_random_uuid(), '50000000-0000-0000-0000-00000000000a', 'holder') $$,
'23503', null, 'a membership of an unknown workspace is refused'
);
select throws_ok(
$$ insert into public.workspace_members (workspace_id, user_id, member_role)
values (pg_temp.ws('50000000-0000-0000-0000-00000000000a'), '50000000-0000-0000-0000-00000000000a', 'holder') $$,
'23505', null, 'a duplicate membership is refused by the primary key'
);
-- ---------------------------------------------------------------------------
-- 4. Backfill of accounts created without the trigger (migration path)
-- ---------------------------------------------------------------------------
alter table public.profiles disable trigger profiles_create_personal_workspace;
insert into auth.users (id, email, email_confirmed_at) values
('50000000-0000-0000-0000-00000000000f', 'backfill-ws@test.local', now()),
('50000000-0000-0000-0000-000000000010', 'invariant-ws@test.local', now());
alter table public.profiles enable trigger profiles_create_personal_workspace;
select is(pg_temp.ws('50000000-0000-0000-0000-00000000000f'), null, 'F has no workspace before the backfill');
select is(private.backfill_personal_workspaces(), 2, 'the backfill creates the two missing workspaces');
select is(private.backfill_personal_workspaces(), 0, 'a second backfill creates nothing');
select results_eq(
$$ select m.user_id, m.member_role::text from public.workspace_members m
where m.workspace_id = pg_temp.ws('50000000-0000-0000-0000-00000000000f') $$,
$$ values ('50000000-0000-0000-0000-00000000000f'::uuid, 'holder'::text) $$,
'the backfilled workspace of F has F as holder'
);
-- ---------------------------------------------------------------------------
-- 5. Commit invariant (checked here statement by statement)
-- ---------------------------------------------------------------------------
set constraints workspaces_personal_holder, workspace_members_personal_holder immediate;
delete from public.workspaces where personal_user_id = '50000000-0000-0000-0000-000000000010';
select is(pg_temp.ws('50000000-0000-0000-0000-000000000010'), null, 'deleting a personal workspace takes its membership along');
select throws_ok(
$$ insert into public.workspaces (kind, personal_user_id) values ('personal', '50000000-0000-0000-0000-000000000010') $$,
'23514', 'personal workspace must have exactly its account as holder',
'a personal workspace without its holder cannot be committed'
);
select throws_ok(
$$ insert into public.workspace_members (workspace_id, user_id, member_role)
values (pg_temp.ws('50000000-0000-0000-0000-00000000000a'), '50000000-0000-0000-0000-00000000000b', 'holder') $$,
'23514', 'personal workspace must have exactly its account as holder',
'B cannot be added to the personal workspace of A, even by the table owner'
);
select throws_ok(
$$ delete from public.workspace_members where user_id = '50000000-0000-0000-0000-00000000000a' $$,
'23514', 'personal workspace must have exactly its account as holder',
'the holder of a personal workspace cannot be removed alone'
);
select throws_ok(
$$ update public.workspaces set personal_user_id = '50000000-0000-0000-0000-000000000010'
where personal_user_id = '50000000-0000-0000-0000-00000000000a' $$,
'23514', 'personal workspace must have exactly its account as holder',
'a personal workspace cannot be handed to another account'
);
select is(private.ensure_personal_workspace('50000000-0000-0000-0000-000000000010'), true,
'the single write path passes the invariant (both rows in one statement)');
set constraints all deferred;
-- ---------------------------------------------------------------------------
-- 6. Reading: each confirmed active account reads its own workspace only
-- ---------------------------------------------------------------------------
set local role authenticated;
select pg_temp.act_as('50000000-0000-0000-0000-00000000000a');
select results_eq(
$$ select id, kind::text, personal_user_id from public.workspaces $$,
$$ values (pg_temp.ws('50000000-0000-0000-0000-00000000000a'), 'personal'::text,
'50000000-0000-0000-0000-00000000000a'::uuid) $$,
'A reads exactly its workspace'
);
select results_eq(
$$ select workspace_id, user_id, member_role::text from public.workspace_members $$,
$$ values (pg_temp.ws('50000000-0000-0000-0000-00000000000a'), '50000000-0000-0000-0000-00000000000a'::uuid, 'holder'::text) $$,
'A reads exactly its membership (no RLS recursion)'
);
select is(
(select count(*) from public.workspaces where id = pg_temp.ws('50000000-0000-0000-0000-00000000000b')),
0::bigint, 'A reads nothing of the workspace of B by id'
);
select is(
(select count(*) from public.workspaces where personal_user_id = '50000000-0000-0000-0000-00000000000b'),
0::bigint, 'A naming B as personal_user_id reads nothing'
);
select is(
(select count(*) from public.workspace_members where user_id = '50000000-0000-0000-0000-00000000000b'
or workspace_id = pg_temp.ws('50000000-0000-0000-0000-00000000000b')),
0::bigint, 'A reads no membership of B'
);
select results_eq(
$$ select private.caller_workspace_ids() $$,
$$ values (pg_temp.ws('50000000-0000-0000-0000-00000000000a')) $$,
'a direct call of the helper returns only what A may already read'
);
select results_eq(
$$ select pg_temp.writable() $$,
$$ values (pg_temp.ws('50000000-0000-0000-0000-00000000000a')) $$,
'A may write in its workspace (holder)'
);
select pg_temp.act_as('50000000-0000-0000-0000-00000000000b');
select results_eq(
$$ select id from public.workspaces $$,
$$ values (pg_temp.ws('50000000-0000-0000-0000-00000000000b')) $$,
'B reads exactly its workspace'
);
select is(
(select count(*) from public.workspaces where id = pg_temp.ws('50000000-0000-0000-0000-00000000000a')),
0::bigint, 'B reads nothing of the workspace of A'
);
-- ---------------------------------------------------------------------------
-- 7. Refused readers: unconfirmed, deactivated, platform staff
-- ---------------------------------------------------------------------------
select pg_temp.act_as('50000000-0000-0000-0000-00000000000c');
select is((select count(*) from public.workspaces), 0::bigint, 'C (email unconfirmed) reads no workspace, not even its own');
select is((select count(*) from public.workspace_members), 0::bigint, 'C reads no membership');
select is((select count(*) from private.caller_workspace_ids()), 0::bigint, 'C has no workspace for the helper');
select is((select count(*) from pg_temp.writable()), 0::bigint, 'C may write nowhere');
select pg_temp.act_as('50000000-0000-0000-0000-00000000000d');
select is((select count(*) from public.workspaces), 0::bigint, 'D (deactivated) reads no workspace, not even its own');
select is((select count(*) from public.workspace_members), 0::bigint, 'D reads no membership');
select is((select count(*) from pg_temp.writable()), 0::bigint, 'D may write nowhere');
select pg_temp.act_as('50000000-0000-0000-0000-000000000005');
select results_eq(
$$ select id from public.workspaces $$,
$$ values (pg_temp.ws('50000000-0000-0000-0000-000000000005')) $$,
'S (platform owner) reads its own workspace only: no platform-role branch'
);
select is(
(select count(*) from public.workspace_members where user_id <> '50000000-0000-0000-0000-000000000005'),
0::bigint, 'S reads no membership of another account'
);
reset role;
-- The confirmation flow: confirming the email opens the existing workspace.
update auth.users set email_confirmed_at = now() where id = '50000000-0000-0000-0000-00000000000c';
set local role authenticated;
select pg_temp.act_as('50000000-0000-0000-0000-00000000000c');
select results_eq(
$$ select id from public.workspaces $$,
$$ values (pg_temp.ws('50000000-0000-0000-0000-00000000000c')) $$,
'once its email is confirmed, C reads the workspace created at sign-up'
);
-- ---------------------------------------------------------------------------
-- 8. Refused writes and calls for an authenticated caller
-- ---------------------------------------------------------------------------
select pg_temp.act_as('50000000-0000-0000-0000-00000000000a');
select throws_ok(
$$ insert into public.workspaces (kind, personal_user_id) values ('personal', '50000000-0000-0000-0000-00000000000a') $$,
'42501', 'permission denied for table workspaces', 'A cannot create a workspace'
);
select throws_ok(
$$ insert into public.workspace_members (workspace_id, user_id, member_role)
values (pg_temp.ws('50000000-0000-0000-0000-00000000000b'), '50000000-0000-0000-0000-00000000000a', 'holder') $$,
'42501', 'permission denied for table workspace_members', 'A cannot add itself to the workspace of B'
);
select throws_ok(
$$ update public.workspaces set personal_user_id = '50000000-0000-0000-0000-00000000000b' $$,
'42501', 'permission denied for table workspaces', 'A cannot rewrite a workspace'
);
select throws_ok(
$$ update public.workspace_members set member_role = 'holder' $$,
'42501', 'permission denied for table workspace_members', 'A cannot rewrite a membership'
);
select throws_ok(
$$ delete from public.workspaces $$,
'42501', 'permission denied for table workspaces', 'A cannot delete a workspace'
);
select throws_ok(
$$ delete from public.workspace_members $$,
'42501', 'permission denied for table workspace_members', 'A cannot delete a membership'
);
select throws_ok(
$$ select private.ensure_personal_workspace('50000000-0000-0000-0000-00000000000b') $$,
'42501', null, 'A cannot call the creation path, for itself or anyone'
);
select throws_ok(
$$ select private.backfill_personal_workspaces() $$,
'42501', null, 'A cannot run the backfill'
);
select throws_ok(
$$ select private.writable_workspace_ids() $$,
'42501', null, 'A cannot call the write helper directly'
);
reset role;
-- ---------------------------------------------------------------------------
-- 9. anon and service_role hold no privilege (refused by SQL, not by RLS)
-- ---------------------------------------------------------------------------
set local role anon;
select throws_ok($$ select id from public.workspaces $$, '42501', 'permission denied for table workspaces', 'anon cannot read workspaces');
select throws_ok($$ select user_id from public.workspace_members $$, '42501', 'permission denied for table workspace_members', 'anon cannot read memberships');
select throws_ok($$ select private.caller_workspace_ids() $$, '42501', null, 'anon cannot call the access helper');
reset role;
set local role service_role;
select throws_ok($$ select id from public.workspaces $$, '42501', 'permission denied for table workspaces', 'service_role cannot read workspaces despite BYPASSRLS');
select throws_ok($$ select user_id from public.workspace_members $$, '42501', 'permission denied for table workspace_members', 'service_role cannot read memberships');
select throws_ok(
$$ insert into public.workspace_members (workspace_id, user_id, member_role)
values (pg_temp.ws('50000000-0000-0000-0000-00000000000a'), '50000000-0000-0000-0000-00000000000b', 'holder') $$,
'42501', 'permission denied for table workspace_members', 'service_role cannot add a member'
);
select throws_ok($$ select private.backfill_personal_workspaces() $$, '42501', null, 'service_role cannot run the backfill');
reset role;
select is(
(select count(*) from (values ('public.workspaces'), ('public.workspace_members')) t(name)
cross join (values ('anon'), ('service_role')) r(role)
cross join (values ('select'), ('insert'), ('update'), ('delete'), ('truncate'), ('references'), ('trigger')) p(priv)
where has_table_privilege(r.role, t.name, p.priv)),
0::bigint, 'anon and service_role hold no table privilege on either table'
);
select is(
(select count(*) from (values ('public.workspaces'), ('public.workspace_members')) t(name)
cross join (values ('insert'), ('update'), ('delete'), ('truncate'), ('references'), ('trigger')) p(priv)
where has_table_privilege('authenticated', t.name, p.priv)),
0::bigint, 'authenticated holds no write privilege on either table'
);
select ok(
has_column_privilege('authenticated', 'public.workspaces', 'personal_user_id', 'select')
and has_column_privilege('authenticated', 'public.workspace_members', 'member_role', 'select'),
'authenticated selects the granted columns (RLS then filters rows)'
);
-- ---------------------------------------------------------------------------
-- 10. Policies and definer functions
-- ---------------------------------------------------------------------------
select is(
(select string_agg(tablename || ':' || policyname || ':' || cmd || ':' || array_to_string(roles, '+'), ','
order by tablename collate "C")
from pg_policies where schemaname = 'public' and tablename in ('workspaces', 'workspace_members')),
'workspace_members:workspace_members_select_member:SELECT:authenticated,workspaces:workspaces_select_member:SELECT:authenticated',
'one SELECT policy per table, for authenticated only; no write policy'
);
select is(
(select count(*) from pg_policies where schemaname = 'public' and tablename in ('workspaces', 'workspace_members')
and (qual not like '%caller_workspace_ids()%' or qual like '%platform%' or qual like '%support%')),
0::bigint, 'both policies go through caller_workspace_ids() and name no platform role (D16)'
);
select is(
(select string_agg(p.proname::text, ',' order by p.proname::text collate "C")
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'private' and p.proname in ('backfill_personal_workspaces', 'caller_workspace_ids',
'check_personal_workspace', 'create_personal_workspace', 'ensure_personal_workspace', 'writable_workspace_ids')
and p.prosecdef and p.proconfig = array['search_path=""']
and pg_get_userbyid(p.proowner) = 'postgres'),
'backfill_personal_workspaces,caller_workspace_ids,check_personal_workspace,create_personal_workspace,ensure_personal_workspace,writable_workspace_ids',
'the six workspace functions are security definer, owned by postgres, search_path empty'
);
select ok(
(select p.prosecdef and p.proconfig = array['search_path=""'] from pg_proc p
where p.oid = 'private.log_event(uuid, text, text, uuid, uuid, uuid, jsonb)'::regprocedure),
'log_event keeps security definer and its empty search_path'
);
select is(
(select string_agg(r.role || ':' || p.proname::text, ',' order by r.role, p.proname::text collate "C")
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
cross join (values ('anon'), ('authenticated'), ('service_role')) r(role)
where n.nspname = 'private' and p.proname in ('backfill_personal_workspaces', 'caller_workspace_ids',
'check_personal_workspace', 'create_personal_workspace', 'ensure_personal_workspace', 'writable_workspace_ids')
and has_function_privilege(r.role, p.oid, 'execute')),
'authenticated:caller_workspace_ids',
'EXECUTE: authenticated on the policy helper only; anon and service_role on none'
);
select ok(
has_function_privilege('service_role', 'private.log_event(uuid, text, text, uuid, uuid, uuid, jsonb)', 'execute')
and not has_function_privilege('authenticated', 'private.log_event(uuid, text, text, uuid, uuid, uuid, jsonb)', 'execute'),
'log_event keeps the privileges of the foundation (service_role yes, authenticated no)'
);
select is(
(select count(*) from pg_trigger where not tgisinternal and tgdeferrable and tginitdeferred
and tgname in ('workspaces_personal_holder', 'workspace_members_personal_holder')),
2::bigint, 'the invariant triggers are deferred to commit'
);
-- ---------------------------------------------------------------------------
-- 11. Account deletion: an empty account takes its workspace along (D9a)
-- ---------------------------------------------------------------------------
set constraints workspaces_personal_holder, workspace_members_personal_holder immediate;
select lives_ok(
$$ delete from auth.users where id = '50000000-0000-0000-0000-00000000000e' $$,
'deleting an empty account passes the invariant'
);
select is(pg_temp.ws('50000000-0000-0000-0000-00000000000e'), null, 'its workspace is gone');
select is(
(select count(*) from public.workspace_members where user_id = '50000000-0000-0000-0000-00000000000e'),
0::bigint, 'its membership is gone'
);
set constraints all deferred;
-- ---------------------------------------------------------------------------
-- 12. Audit correlation id: kept only as a short token (contract §10, N9)
-- ---------------------------------------------------------------------------
select set_config('request.headers', '{"x-correlation-id":"req-7f3a-01"}', true);
insert into pg_temp.audit_ids values ('valid', private.log_event(null, 'workspace.probed', 'workspace'));
select set_config('request.headers', json_build_object('x-correlation-id', repeat('a', 64))::text, true);
insert into pg_temp.audit_ids values ('max', private.log_event(null, 'workspace.probed', 'workspace'));
select set_config('request.headers', json_build_object('x-correlation-id', repeat('a', 65))::text, true);
insert into pg_temp.audit_ids values ('long', private.log_event(null, 'workspace.probed', 'workspace'));
select set_config('request.headers', json_build_object('x-correlation-id', repeat('x', 8192))::text, true);
insert into pg_temp.audit_ids values ('huge', private.log_event(null, 'workspace.probed', 'workspace'));
select set_config('request.headers', '{"x-correlation-id":"a b"}', true);
insert into pg_temp.audit_ids values ('space', private.log_event(null, 'workspace.probed', 'workspace'));
select set_config('request.headers', '{"x-correlation-id":"abc\n"}', true);
insert into pg_temp.audit_ids values ('newline', private.log_event(null, 'workspace.probed', 'workspace'));
select set_config('request.headers', '{"x-correlation-id":{"nested":"x"}}', true);
insert into pg_temp.audit_ids values ('object', private.log_event(null, 'workspace.probed', 'workspace'));
select set_config('request.headers', '{}', true);
insert into pg_temp.audit_ids values ('absent', private.log_event(null, 'workspace.probed', 'workspace'));
select set_config('request.headers', '', true);
select is(
(select correlation_id from audit.audit_logs where id = (select id from pg_temp.audit_ids where key = 'valid')),
'req-7f3a-01', 'a short token is kept'
);
select is(
(select char_length(correlation_id) from audit.audit_logs where id = (select id from pg_temp.audit_ids where key = 'max')),
64, 'a 64-character token is kept'
);
select is(
(select string_agg(i.key, ',' order by i.key collate "C") from pg_temp.audit_ids i
join audit.audit_logs a on a.id = i.id where a.correlation_id is null),
'absent,huge,long,newline,object,space',
'an oversized, malformed or absent correlation id is stored as null'
);
select throws_ok(
$$ insert into audit.audit_logs (action, resource_type, correlation_id) values ('workspace.probed', 'workspace', repeat('a', 65)) $$,
'23514', null, 'the CHECK refuses an oversized correlation id on a new row, whoever writes it'
);
select * from finish();
rollback;