-- V1-1.2 Clients (M2): fields and text rules, idempotent creates, revisions, -- archive, search, isolation between workspaces, refusals for every other -- actor, caps and write budget, minimised audit, privileges and census. -- Contract: docs/development/V1-1_OWNERS_ANIMALS.md §5, §9, §10, §12.1. -- Actors: A and B confirmed and active; C unconfirmed; D deactivated after -- creating a client; S a platform owner; E (cap) and F (budget) confirmed; -- G an account without clients. begin; select plan(130); insert into auth.users (id, email, email_confirmed_at) values ('60000000-0000-0000-0000-00000000000a', 'alice-cl@test.local', now()), ('60000000-0000-0000-0000-00000000000b', 'bob-cl@test.local', now()), ('60000000-0000-0000-0000-00000000000c', 'carol-cl@test.local', null), ('60000000-0000-0000-0000-00000000000d', 'dave-cl@test.local', now()), ('60000000-0000-0000-0000-000000000005', 'staff-cl@test.local', now()), ('60000000-0000-0000-0000-00000000000e', 'cap-cl@test.local', now()), ('60000000-0000-0000-0000-00000000000f', 'budget-cl@test.local', now()), ('60000000-0000-0000-0000-000000000010', 'empty-cl@test.local', now()); select private.assign_platform_role(null, '60000000-0000-0000-0000-000000000005', 'owner'); 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.client(p_id uuid) returns public.clients language sql stable security definer as $$ select c from public.clients c where c.id = p_id $$; create function pg_temp.budget(p_ws uuid) returns text language sql stable security definer as $$ select coalesce((select hour_count || '/' || day_count from private.workspace_write_budgets where workspace_id = p_ws), 'none') $$; create function pg_temp.audit_count(p_resource uuid) returns bigint language sql stable security definer as $$ select count(*) from audit.audit_logs where resource_id = p_resource $$; -- The whole error of a statement, run as the calling role: SQLSTATE, message, detail, hint. create function pg_temp.err(p_sql text) returns text language plpgsql as $$ declare v_state text; v_msg text; v_detail text; v_hint text; begin execute p_sql; return 'no error'; exception when others then get stacked diagnostics v_state = returned_sqlstate, v_msg = message_text, v_detail = pg_exception_detail, v_hint = pg_exception_hint; return v_state || '|' || v_msg || '|' || coalesce(v_detail, '') || '|' || coalesce(v_hint, ''); end; $$; -- SQLSTATE and message only: the real image appends a hint to table-privilege -- refusals ("Grant the required privileges ..."), PGlite does not. create function pg_temp.denial(p_sql text) returns text language sql as $$ select split_part(pg_temp.err(p_sql), '|', 1) || '|' || split_part(pg_temp.err(p_sql), '|', 2) $$; create table pg_temp.ids (key text primary key, id uuid not null); grant all on pg_temp.ids to authenticated, anon, service_role; grant execute on function pg_temp.ws(uuid), pg_temp.act_as(uuid), pg_temp.client(uuid), pg_temp.budget(uuid), pg_temp.audit_count(uuid), pg_temp.err(text), pg_temp.denial(text) to authenticated, anon, service_role; -- --------------------------------------------------------------------------- -- 1. Schema, privileges, census -- --------------------------------------------------------------------------- select has_table('public', 'clients', 'table public.clients exists'); select ok((select relrowsecurity from pg_class where oid = 'public.clients'::regclass), 'RLS enabled on clients'); select enum_has_labels('public', 'client_kind', array['person', 'organization'], 'client_kind: person, organization'); select is((select tableowner::text from pg_tables where schemaname = 'public' and tablename = 'clients'), 'postgres', 'clients belongs to postgres'); select is( (select count(*) from (values ('anon'), ('service_role')) r(role) cross join (values ('select'), ('insert'), ('update'), ('delete'), ('truncate'), ('references'), ('trigger')) p(priv) where has_table_privilege(r.role, 'public.clients', p.priv)), 0::bigint, 'anon and service_role hold no privilege on clients' ); select is( (select count(*) from (values ('insert'), ('update'), ('delete'), ('truncate'), ('references'), ('trigger')) p(priv) where has_table_privilege('authenticated', 'public.clients', p.priv)), 0::bigint, 'authenticated holds no write privilege on clients' ); select ok(has_column_privilege('authenticated', 'public.clients', 'display_name', 'select') and not has_column_privilege('authenticated', 'public.clients', 'create_request_digest', 'select'), 'authenticated reads the record columns, never the request digest'); select is( (select count(*) from (values ('anon'), ('authenticated'), ('service_role')) r(role) cross join (values ('select'), ('insert'), ('update'), ('delete')) p(priv) where has_table_privilege(r.role, 'private.workspace_write_budgets', p.priv)), 0::bigint, 'no API role touches the budget table' ); select is( (select string_agg(policyname || ':' || cmd || ':' || array_to_string(roles, '+'), ',') from pg_policies where schemaname = 'public' and tablename = 'clients'), 'clients_select_member:SELECT:authenticated', 'one SELECT policy on clients, for authenticated' ); select is( (select count(*) from pg_policies where schemaname = 'public' and tablename = 'clients' and (qual not like '%caller_workspace_ids()%' or qual like '%platform%')), 0::bigint, 'the clients policy goes through caller_workspace_ids() and names no platform role' ); select is( (select string_agg(p.proname || ':' || case when p.prosecdef then 'definer' else 'invoker' end, ',' order by p.proname::text collate "C") from pg_proc p join pg_namespace n on n.oid = p.pronamespace where p.proname in ('create_client', 'update_client', 'set_client_archived', 'search_clients', 'fold_text', 'is_clean_text', 'consume_write_budget') and p.proconfig = array['search_path=""'] and pg_get_userbyid(p.proowner) = 'postgres'), 'consume_write_budget:definer,create_client:definer,fold_text:invoker,is_clean_text:invoker,search_clients:invoker,set_client_archived:definer,update_client:definer', 'definer and invoker as designed; search_path empty; owner postgres' ); select is( (select string_agg(r.role || ':' || p.proname::text, ',' order by r.role, p.proname::text collate "C") from pg_proc p cross join (values ('anon'), ('authenticated'), ('service_role')) r(role) where p.proname in ('create_client', 'update_client', 'set_client_archived', 'search_clients', 'fold_text', 'is_clean_text', 'consume_write_budget') and has_function_privilege(r.role, p.oid, 'execute')), 'authenticated:create_client,authenticated:fold_text,authenticated:search_clients,authenticated:set_client_archived,authenticated:update_client', 'EXECUTE: authenticated on the four RPCs and the fold; anon and service_role on none' ); -- --------------------------------------------------------------------------- -- 2. Creation by A: fields, normalisation, derived keys -- --------------------------------------------------------------------------- set local role authenticated; select pg_temp.act_as('60000000-0000-0000-0000-00000000000a'); insert into pg_temp.ids values ('lac', (public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000a'), '61000000-0000-0000-0000-000000000001', 'organization', 'Écurie du Lac', 'contact@ecurie-lac.test', '+33612345678', '1 chemin du Lac', null, '69000', 'Lyon', 'FR') ->> 'id')::uuid), ('marie', (public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000a'), '61000000-0000-0000-0000-000000000002', 'person', 'Marie Dupont', 'MARIE@Example.TEST', null, null, null, null, 'Évian') ->> 'id')::uuid), ('ab', (public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000a'), '61000000-0000-0000-0000-000000000003', 'organization', 'A_B Haras') ->> 'id')::uuid), ('axb', (public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000a'), '61000000-0000-0000-0000-000000000004', 'organization', 'AxB Haras') ->> 'id')::uuid), ('pct', (public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000a'), '61000000-0000-0000-0000-000000000005', 'person', '100% Cheval') ->> 'id')::uuid), ('bsl', (public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000a'), '61000000-0000-0000-0000-000000000006', 'person', 'Back\slash') ->> 'id')::uuid); select results_eq( $$ select kind::text, display_name, email, phone, city, country, revision, archived_at is null from public.clients where id = (select id from pg_temp.ids where key = 'lac') $$, $$ values ('organization'::text, 'Écurie du Lac'::text, 'contact@ecurie-lac.test'::text, '+33612345678'::text, 'Lyon'::text, 'FR'::text, 1, true) $$, 'A reads the created organization with revision 1' ); select is((pg_temp.client((select id from pg_temp.ids where key = 'marie'))).email, 'marie@example.test', 'the email is stored lower-case'); select is((pg_temp.client((select id from pg_temp.ids where key = 'lac'))).name_key, 'ecurie du lac', 'name_key folds accents and case'); select is((pg_temp.client((select id from pg_temp.ids where key = 'lac'))).phone_digits, '33612345678', 'phone_digits drops the +'); select is((pg_temp.client((select id from pg_temp.ids where key = 'ab'))).country, null, 'no country is set by default'); select is((select count(*) from public.clients), 6::bigint, 'A reads its six clients'); -- --------------------------------------------------------------------------- -- 3. Text rules: refused with the bare code, nothing echoed -- --------------------------------------------------------------------------- create function pg_temp.try_create(p_kind text, p_name text, p_email text default null, p_phone text default null, p_postal text default null, p_country text default null) returns text language sql as $$ select pg_temp.err(format( 'select public.create_client(%L, gen_random_uuid(), %L, %L, %L, %L, null, null, %L, null, %L)', pg_temp.ws('60000000-0000-0000-0000-00000000000a'), p_kind, p_name, p_email, p_phone, p_postal, p_country)) $$; grant execute on function pg_temp.try_create(text, text, text, text, text, text) to authenticated; select is(pg_temp.try_create('person', ''), 'P0001|invalid_input||', 'an empty name is refused'); select is(pg_temp.try_create('person', null), 'P0001|invalid_input||', 'a missing name is refused'); select is(pg_temp.try_create('person', ' Lead'), 'P0001|invalid_input||', 'a leading space is refused'); select is(pg_temp.try_create('person', e'Tab\there'), 'P0001|invalid_input||', 'a tab (control character) is refused'); select is(pg_temp.try_create('person', e'Line\u2028sep'), 'P0001|invalid_input||', 'U+2028 is refused'); select is(pg_temp.try_create('person', e'C1\u0085'), 'P0001|invalid_input||', 'a C1 control (U+0085) is refused'); select is(pg_temp.try_create('person', repeat('x', 121)), 'P0001|invalid_input||', '121 characters are refused'); select is(pg_temp.try_create('person', repeat('é', 121)), 'P0001|invalid_input||', '121 accented characters (code points) are refused'); select is(pg_temp.try_create('robot', 'Kind'), 'P0001|invalid_input||', 'an unknown kind is refused'); select is(pg_temp.try_create(null, 'Kind'), 'P0001|invalid_input||', 'a missing kind is refused'); select is(pg_temp.try_create('person', 'Mail', 'not-an-email'), 'P0001|invalid_input||', 'a malformed email is refused'); select is(pg_temp.try_create('person', 'Mail', 'a b@c.test'), 'P0001|invalid_input||', 'an email with a space is refused'); select is(pg_temp.try_create('person', 'Mail', repeat('a', 247) || '@ex.test'), 'P0001|invalid_input||', 'a 255-character email is refused'); select is(pg_temp.try_create('person', 'Mail', repeat('a', 246) || '@ex.test'), 'no error', 'a 254-character email is accepted'); select is(pg_temp.try_create('person', 'Phone', null, '0612345678'), 'P0001|invalid_input||', 'a national phone (not E.164) is refused'); select is(pg_temp.try_create('person', 'Postal', null, null, repeat('9', 17)), 'P0001|invalid_input||', 'a 17-character postal code is refused'); select is(pg_temp.try_create('person', 'Country', null, null, null, 'fr'), 'P0001|invalid_input||', 'a lower-case country code is refused'); select is(pg_temp.try_create('person', 'Country', null, null, null, 'FRA'), 'P0001|invalid_input||', 'a three-letter country code is refused'); select is(pg_temp.try_create('person', repeat('é', 120)), 'no error', '120 accented characters are accepted'); select is((select count(*) from public.clients), 8::bigint, 'only the two valid creates were written'); -- --------------------------------------------------------------------------- -- 4. Idempotent create: replay, normalised replay, divergent replay -- --------------------------------------------------------------------------- select is(pg_temp.budget(pg_temp.ws('60000000-0000-0000-0000-00000000000a')), '8/8', 'eight writes counted so far'); select is( (public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000a'), '61000000-0000-0000-0000-000000000002', 'person', 'Marie Dupont', 'marie@example.test', null, null, null, null, 'Évian') ->> 'id')::uuid, (select id from pg_temp.ids where key = 'marie'), 'a replay (email in another case) returns the first client' ); select is(pg_temp.audit_count((select id from pg_temp.ids where key = 'marie')), 1::bigint, 'the replay writes no audit row'); select is(pg_temp.budget(pg_temp.ws('60000000-0000-0000-0000-00000000000a')), '8/8', 'the replay consumes no budget'); select is( pg_temp.err($$ select public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000a'), '61000000-0000-0000-0000-000000000002', 'person', 'Marie Durand') $$), 'P0001|request_conflict||', 'the same request id with other content is refused' ); select is((select count(*) from public.clients where display_name like 'Marie%'), 1::bigint, 'no divergent client was created'); -- --------------------------------------------------------------------------- -- 5. Update, revision, archive -- --------------------------------------------------------------------------- select is( public.update_client((select id from pg_temp.ids where key = 'marie'), 1, 'person', 'Marie Dupont', 'marie@example.test', '+41791234567', null, null, '1800', 'Vevey', 'CH') ->> 'revision', '2', 'an update at the current revision gives revision 2'); select is( pg_temp.err($$ select public.update_client((select id from pg_temp.ids where key = 'marie'), 1, 'person', 'Marie D.', null, null, null, null, null, null, null) $$), 'P0001|conflict||', 'a stale revision is refused' ); select is((pg_temp.client((select id from pg_temp.ids where key = 'marie'))).city, 'Vevey', 'the stale update overwrote nothing'); select is( pg_temp.err($$ select public.update_client((select id from pg_temp.ids where key = 'marie'), null, 'person', 'Marie D.', null, null, null, null, null, null, null) $$), 'P0001|conflict||', 'a missing revision is refused' ); select is( public.update_client((select id from pg_temp.ids where key = 'marie'), 2, 'person', 'Marie Dupont', 'MARIE@example.test', '+41791234567', null, null, '1800', 'Vevey', 'CH') ->> 'revision', '2', 'an update that changes nothing keeps the revision'); select is(pg_temp.audit_count((select id from pg_temp.ids where key = 'marie')), 2::bigint, 'and writes no audit row'); select is( pg_temp.err($$ select public.update_client((select id from pg_temp.ids where key = 'marie'), 2, 'person', '', null, null, null, null, null, null, null) $$), 'P0001|invalid_input||', 'an update breaking a rule is refused' ); select is(public.set_client_archived((select id from pg_temp.ids where key = 'axb'), true) ->> 'revision', '2', 'archiving adds one revision'); select ok((pg_temp.client((select id from pg_temp.ids where key = 'axb'))).archived_at is not null, 'the client is archived'); select is(public.set_client_archived((select id from pg_temp.ids where key = 'axb'), true) ->> 'revision', '2', 'archiving again changes nothing'); select is( pg_temp.err($$ select public.update_client((select id from pg_temp.ids where key = 'axb'), 1, 'organization', 'AxB', null, null, null, null, null, null, null) $$), 'P0001|archived||', 'an archived client refuses edits, archived reported before the stale revision' ); select is(pg_temp.err($$ select public.set_client_archived((select id from pg_temp.ids where key = 'axb'), null) $$), 'P0001|invalid_input||', 'a missing archive state is refused'); -- --------------------------------------------------------------------------- -- 6. Search: folding, escaping, phone formats, filters, keyset pages -- --------------------------------------------------------------------------- create function pg_temp.names(p_query text, p_archived boolean default false, p_size integer default 25, p_after_key text default null, p_after_id uuid default null) returns text language sql as $$ select coalesce(string_agg(s.display_name, '|' order by s.ord), '') from public.search_clients(pg_temp.ws('60000000-0000-0000-0000-00000000000a'), p_query, p_archived, p_size, p_after_key, p_after_id) with ordinality s(id, kind, display_name, city, phone, email, archived_at, name_key, current_animal_count, ord) $$; grant execute on function pg_temp.names(text, boolean, integer, text, uuid) to authenticated; select is(pg_temp.names(''), '100% Cheval|A_B Haras|Back\slash|Écurie du Lac|' || repeat('é', 120) || '|Mail|Marie Dupont', 'an empty term lists the active clients in name order (archived left out)'); select is(pg_temp.names('', true), 'AxB Haras', 'the archived filter lists the archived client only'); select is(pg_temp.names('ECU'), 'Écurie du Lac', 'search folds case and accents'); select is(pg_temp.names('vevey'), 'Marie Dupont', 'search reads the city'); select is(pg_temp.names('EXAMPLE.test'), 'Marie Dupont', 'search reads the email'); select is(pg_temp.names('_'), 'A_B Haras', 'an underscore is a character, not a wildcard'); select is(pg_temp.names('%'), '100% Cheval', 'a percent sign is a character, not a wildcard'); select is(pg_temp.names('\'), 'Back\slash', 'a backslash is a character'); select is(pg_temp.names('06 12'), 'Écurie du Lac', 'a national phone fragment finds the number'); select is(pg_temp.names('0612345678'), 'Écurie du Lac', 'a full national number finds it'); select is(pg_temp.names('+33 6 12'), 'Écurie du Lac', 'an international fragment finds it'); select is(pg_temp.names('061'), '', 'fewer than four digits is not a phone search'); select is(pg_temp.names(repeat('z', 100)), '', 'a 100-character term is accepted'); select is(pg_temp.names('', false, 2), '100% Cheval|A_B Haras', 'page 1 of 2'); select is(pg_temp.names('', false, 2, (pg_temp.client((select id from pg_temp.ids where key = 'ab'))).name_key, (select id from pg_temp.ids where key = 'ab')), 'Back\slash|Écurie du Lac', 'page 2 continues after the cursor, no gap, no repeat'); select is(pg_temp.names('', false, 2, (pg_temp.client((select id from pg_temp.ids where key = 'marie'))).name_key, (select id from pg_temp.ids where key = 'marie')), '', 'after the last client the page is empty'); select is(pg_temp.err($$ select pg_temp.names(repeat('z', 101)) $$), 'P0001|invalid_input||', 'a 101-character term is refused'); select is(pg_temp.err($$ select pg_temp.names('', false, 0) $$), 'P0001|invalid_input||', 'page size 0 is refused'); select is(pg_temp.err($$ select pg_temp.names('', false, 101) $$), 'P0001|invalid_input||', 'page size 101 is refused'); select is(pg_temp.err($$ select pg_temp.names('', false, 25, 'a') $$), 'P0001|invalid_input||', 'a cursor without its id is refused'); select is(pg_temp.err($$ select pg_temp.names('', null) $$), 'P0001|invalid_input||', 'a missing archive filter is refused'); -- Restore: editable again, back in the default list. select is(public.set_client_archived((select id from pg_temp.ids where key = 'axb'), false) ->> 'revision', '3', 'restoring adds one revision'); select is(public.update_client((select id from pg_temp.ids where key = 'axb'), 3, 'organization', 'AxB Haras', null, null, null, null, null, 'Annecy', null) ->> 'revision', '4', 'a restored client is editable'); -- D creates a client while active (used after its deactivation). select pg_temp.act_as('60000000-0000-0000-0000-00000000000d'); insert into pg_temp.ids values ('dave', (public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000d'), gen_random_uuid(), 'person', 'Client de Dave') ->> 'id')::uuid); reset role; update public.profiles set active = false where id = '60000000-0000-0000-0000-00000000000d'; -- --------------------------------------------------------------------------- -- 7. Isolation: the same not_found for a foreign and an unknown id -- --------------------------------------------------------------------------- set local role authenticated; select pg_temp.act_as('60000000-0000-0000-0000-00000000000b'); select is((select count(*) from public.clients), 0::bigint, 'B reads no client'); select is((select count(*) from public.clients where id = (select id from pg_temp.ids where key = 'lac')), 0::bigint, 'B naming a client of A reads nothing'); select is((select count(*) from public.search_clients(pg_temp.ws('60000000-0000-0000-0000-00000000000a'))), 0::bigint, 'B searching the workspace of A finds nothing'); select is(pg_temp.err($$ select public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000a'), gen_random_uuid(), 'person', 'X') $$), pg_temp.err($$ select public.create_client(gen_random_uuid(), gen_random_uuid(), 'person', 'X') $$), 'B creating in the workspace of A gets the error of an unknown workspace'); select is(pg_temp.err($$ select public.create_client(gen_random_uuid(), gen_random_uuid(), 'person', 'X') $$), '42501|not_found||', 'that error is 42501 not_found, no detail, no hint'); select is(pg_temp.err($$ select public.update_client((select id from pg_temp.ids where key = 'lac'), 1, 'person', 'X', null, null, null, null, null, null, null) $$), pg_temp.err($$ select public.update_client(gen_random_uuid(), 1, 'person', 'X', null, null, null, null, null, null, null) $$), 'B updating a client of A gets the error of an unknown client'); select is(pg_temp.err($$ select public.set_client_archived((select id from pg_temp.ids where key = 'lac'), true) $$), '42501|not_found||', 'B archiving a client of A gets not_found'); select is(pg_temp.err($$ select public.update_client((select id from pg_temp.ids where key = 'lac'), 1, 'person', '', null, null, null, null, null, null, null) $$), '42501|not_found||', 'invalid content on a foreign id still answers not_found (authorisation first)'); select is(pg_temp.denial($$ insert into public.clients (workspace_id, kind, display_name, create_request_id, create_request_digest) values (pg_temp.ws('60000000-0000-0000-0000-00000000000b'), 'person', 'Direct', gen_random_uuid(), '\x00') $$), '42501|permission denied for table clients', 'a direct insert is refused by privilege'); select is(pg_temp.denial($$ update public.clients set display_name = 'Direct' $$), '42501|permission denied for table clients', 'a direct update is refused by privilege'); select is(pg_temp.denial($$ delete from public.clients $$), '42501|permission denied for table clients', 'a direct delete is refused by privilege'); select pg_temp.act_as('60000000-0000-0000-0000-00000000000c'); select is((select count(*) from public.clients), 0::bigint, 'C (unconfirmed) reads no client'); select is(pg_temp.err($$ select public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000c'), gen_random_uuid(), 'person', 'X') $$), '42501|not_found||', 'C cannot create, even in its own workspace'); select pg_temp.act_as('60000000-0000-0000-0000-00000000000d'); select is((select count(*) from public.clients), 0::bigint, 'D (deactivated) no longer reads its own client'); select is((select count(*) from public.search_clients(pg_temp.ws('60000000-0000-0000-0000-00000000000d'))), 0::bigint, 'D finds nothing through search'); select is(pg_temp.err($$ select public.set_client_archived((select id from pg_temp.ids where key = 'dave'), true) $$), '42501|not_found||', 'D cannot write its own client'); select pg_temp.act_as('60000000-0000-0000-0000-000000000005'); select is((select count(*) from public.clients), 0::bigint, 'S (platform owner) reads no client: no platform-role branch'); select is(pg_temp.err($$ select public.update_client((select id from pg_temp.ids where key = 'lac'), 1, 'person', 'X', null, null, null, null, null, null, null) $$), '42501|not_found||', 'S cannot write a client of A'); reset role; set local role anon; select is(pg_temp.denial($$ select id from public.clients $$), '42501|permission denied for table clients', 'anon cannot read clients'); select is(pg_temp.err($$ select public.create_client(gen_random_uuid(), gen_random_uuid(), 'person', 'X') $$), '42501|permission denied for function create_client||', 'anon cannot execute create_client'); select is(pg_temp.err($$ select * from public.search_clients(gen_random_uuid()) $$), '42501|permission denied for function search_clients||', 'anon cannot execute search_clients'); reset role; set local role service_role; select is(pg_temp.denial($$ select id from public.clients $$), '42501|permission denied for table clients', 'service_role cannot read clients despite BYPASSRLS'); select is(pg_temp.err($$ select public.update_client(gen_random_uuid(), 1, 'person', 'X', null, null, null, null, null, null, null) $$), '42501|permission denied for function update_client||', 'service_role cannot execute update_client'); select is(pg_temp.err($$ select private.consume_write_budget(gen_random_uuid()) $$), '42501|permission denied for function consume_write_budget||', 'service_role cannot touch the budget'); reset role; set local role authenticated; select pg_temp.act_as('60000000-0000-0000-0000-00000000000a'); select is(pg_temp.err($$ select private.consume_write_budget(pg_temp.ws('60000000-0000-0000-0000-00000000000a')) $$), '42501|permission denied for function consume_write_budget||', 'A cannot call the budget directly'); select is(pg_temp.err($$ select private.is_clean_text('x', 1) $$), '42501|permission denied for function is_clean_text||', 'A cannot call the text rule directly'); reset role; -- --------------------------------------------------------------------------- -- 8. Write budget per workspace (fixed UTC windows), refused writes not counted -- --------------------------------------------------------------------------- insert into private.workspace_write_budgets values (pg_temp.ws('60000000-0000-0000-0000-00000000000f'), date_trunc('hour', now(), 'UTC'), 999, date_trunc('day', now(), 'UTC'), 999); set local role authenticated; select pg_temp.act_as('60000000-0000-0000-0000-00000000000f'); insert into pg_temp.ids values ('f1', (public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000f'), '62000000-0000-0000-0000-000000000001', 'person', 'Budget One') ->> 'id')::uuid); select is(pg_temp.budget(pg_temp.ws('60000000-0000-0000-0000-00000000000f')), '1000/1000', 'the 1 000th write of the hour passes'); select is(pg_temp.err($$ select public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000f'), gen_random_uuid(), 'person', 'Budget Two') $$), 'P0001|rate_limited||', 'the 1 001st write of the hour is refused'); select is(pg_temp.budget(pg_temp.ws('60000000-0000-0000-0000-00000000000f')), '1000/1000', 'the refused write consumed nothing'); select is((public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000f'), '62000000-0000-0000-0000-000000000001', 'person', 'Budget One') ->> 'id')::uuid, (select id from pg_temp.ids where key = 'f1'), 'a replay at the limit still answers'); select is(public.set_client_archived((select id from pg_temp.ids where key = 'f1'), false) ->> 'revision', '1', 'a no-op at the limit still answers'); select is(pg_temp.err($$ select public.set_client_archived((select id from pg_temp.ids where key = 'f1'), true) $$), 'P0001|rate_limited||', 'an archive at the limit is refused'); reset role; update private.workspace_write_budgets set hour_start = hour_start - interval '1 hour' where workspace_id = pg_temp.ws('60000000-0000-0000-0000-00000000000f'); set local role authenticated; select pg_temp.act_as('60000000-0000-0000-0000-00000000000f'); select lives_ok($$ select public.set_client_archived((select id from pg_temp.ids where key = 'f1'), true) $$, 'a new UTC hour restarts the hourly count'); select is(pg_temp.budget(pg_temp.ws('60000000-0000-0000-0000-00000000000f')), '1/1001', 'hour restarted at 1, the day keeps counting'); reset role; update private.workspace_write_budgets set day_count = 5000 where workspace_id = pg_temp.ws('60000000-0000-0000-0000-00000000000f'); set local role authenticated; select pg_temp.act_as('60000000-0000-0000-0000-00000000000f'); select is(pg_temp.err($$ select public.set_client_archived((select id from pg_temp.ids where key = 'f1'), false) $$), 'P0001|rate_limited||', 'the 5 001st write of the UTC day is refused'); select is(pg_temp.budget(pg_temp.ws('60000000-0000-0000-0000-00000000000f')), '1/5000', 'and consumed nothing'); select is(pg_temp.budget(pg_temp.ws('60000000-0000-0000-0000-00000000000b')), 'none', 'the budget of another workspace is untouched'); reset role; -- --------------------------------------------------------------------------- -- 9. Cap: 20 000 clients per workspace -- --------------------------------------------------------------------------- insert into public.clients (workspace_id, kind, display_name, create_request_id, create_request_digest) select pg_temp.ws('60000000-0000-0000-0000-00000000000e'), 'person', 'Cap ' || g, gen_random_uuid(), '\x00' from generate_series(1, 19999) g; set local role authenticated; select pg_temp.act_as('60000000-0000-0000-0000-00000000000e'); select lives_ok($$ select public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000e'), '63000000-0000-0000-0000-000000000001', 'person', 'Cap last') $$, 'the 20 000th client is accepted'); select is(pg_temp.err($$ select public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000e'), gen_random_uuid(), 'person', 'Cap over') $$), 'P0001|limit_reached||', 'the 20 001st client is refused'); select lives_ok($$ select public.create_client(pg_temp.ws('60000000-0000-0000-0000-00000000000e'), '63000000-0000-0000-0000-000000000001', 'person', 'Cap last') $$, 'a replay at the cap still answers'); reset role; -- --------------------------------------------------------------------------- -- 10. Audit: ids only, hidden from platform staff (N9) -- --------------------------------------------------------------------------- select is( (select string_agg(action, ',' order by occurred_at, action) from audit.audit_logs where resource_id = (select id from pg_temp.ids where key = 'axb')), 'client.archived,client.created,client.restored,client.updated', 'each write of a client leaves one audit row' ); select is( (select count(*) from audit.audit_logs a join pg_temp.ids i on i.id = a.resource_id where a.metadata <> jsonb_build_object('workspace_id', pg_temp.ws('60000000-0000-0000-0000-00000000000a')) and i.key in ('lac', 'marie', 'ab', 'axb', 'pct', 'bsl')), 0::bigint, 'audit metadata is the workspace id only' ); select is( (select count(*) from audit.audit_logs a join pg_temp.ids i on i.id = a.resource_id where a.actor_user_id is distinct from '60000000-0000-0000-0000-00000000000a' and i.key in ('lac', 'marie', 'axb')), 0::bigint, 'the actor is the caller' ); select is( (select count(*) from audit.audit_logs where action like 'client.%' and (metadata::text ~* '(lac|marie|dupont|vevey|example|cheval|haras)' or correlation_id is not null)), 0::bigint, 'no name, contact or place in the audit rows' ); set local role authenticated; select pg_temp.act_as('60000000-0000-0000-0000-000000000005'); select is((select count(*) from audit.audit_logs where action like 'client.%'), 0::bigint, 'S reads no client audit row'); select ok((select count(*) from audit.audit_logs where action like 'platform_role.%') > 0, 'S still reads platform audit rows'); reset role; -- --------------------------------------------------------------------------- -- 11. Integrity and account deletion (D9a) -- --------------------------------------------------------------------------- select throws_ok( $$ insert into public.clients (workspace_id, kind, display_name, create_request_id, create_request_digest) values (gen_random_uuid(), 'person', 'Orphan', gen_random_uuid(), '\x00') $$, '23503', null, 'a client of an unknown workspace is refused by the foreign key' ); select throws_ok( $$ insert into public.clients (workspace_id, kind, display_name, create_request_id, create_request_digest) values (pg_temp.ws('60000000-0000-0000-0000-00000000000a'), 'person', 'Twice', '61000000-0000-0000-0000-000000000001', '\x00') $$, '23505', null, 'a request id is unique per workspace' ); select throws_ok( $$ delete from auth.users where id = '60000000-0000-0000-0000-00000000000a' $$, '23503', null, 'an account whose workspace holds clients cannot be deleted (D9a)' ); select is((select count(*) from public.clients where workspace_id = pg_temp.ws('60000000-0000-0000-0000-00000000000a')), 8::bigint, 'its clients are intact'); select lives_ok($$ delete from auth.users where id = '60000000-0000-0000-0000-000000000010' $$, 'an account without clients is deleted as before'); select * from finish(); rollback;