merchant-0051.sql (5066B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 16 -- @file merchant-0051.sql 17 -- @brief Constrain the identifier columns to the slug character set 18 19 -- Every column constrained here holds what the protocol calls a slug, and 20 -- TALER_is_slug() has guarded the REST input that writes it only since 21 -- 2026-05-21 (identifiers other than the order ID), 2026-07-05 (instance ID) 22 -- and 2026-08-04 (order ID, product unit). An older backend accepted 23 -- anything a JSON string can hold, among it '@' in an instance ID, which its 24 -- regex allowed on purpose. The read-path parsers are strict now, so such a 25 -- row makes GET /private/orders, GET /management/instances and the payment 26 -- of the affected order fail, and nothing in the schema says which row is at 27 -- fault. 28 -- 29 -- The constraints are therefore added NOT VALID: new and updated rows have 30 -- to be well-formed, while existing rows are left alone so that the upgrade 31 -- cannot fail on data an operator cannot see yet. To find the offending 32 -- rows, validate a constraint by hand; it reports the first row it rejects: 33 -- 34 -- ALTER TABLE merchant_instance_1.merchant_orders 35 -- VALIDATE CONSTRAINT merchant_orders_order_id_slug_check; 36 -- 37 -- A constraint that validates cleanly is marked valid and stays so. 38 -- 39 -- The character set is the ASCII one of TALER_is_slug(): letters, digits, 40 -- '-', '_', '.', ':' and '~', at least one character, and neither '.' nor 41 -- '..' (both are path components). Note that isalnum() is locale-dependent 42 -- in C while the class below is not; the C side has to be pinned to ASCII 43 -- as well for the two to agree. 44 -- 45 -- Deliberately not covered: merchant_statistic_bucket_meta.slug and 46 -- merchant_statistic_interval_meta.slug. Those rows come from SQL an 47 -- operator supplies, not from REST input, and failing their INSERT is not 48 -- this migration's business. 49 50 BEGIN; 51 52 SELECT _v.register_patch('merchant-0051', NULL, NULL); 53 54 SET search_path TO merchant; 55 56 CREATE PROCEDURE merchant.merchant_0051_init(s TEXT) 57 LANGUAGE plpgsql 58 AS $$ 59 DECLARE 60 my_slug_re TEXT DEFAULT '^[A-Za-z0-9._:~-]+$'; 61 my_conname TEXT; 62 rec RECORD; 63 BEGIN 64 FOR rec IN 65 SELECT * 66 FROM (VALUES 67 ('merchant_inventory', 'product_id') 68 ,('merchant_inventory', 'unit') 69 ,('merchant_custom_units', 'unit') 70 ,('merchant_orders', 'order_id') 71 ,('merchant_contract_terms', 'order_id') 72 ,('merchant_template', 'template_id') 73 ,('merchant_webhook', 'webhook_id') 74 ,('merchant_otp_devices', 'otp_id') 75 ,('merchant_token_families', 'slug') 76 ) AS t(tbl, col) 77 LOOP 78 my_conname = format('%s_%s_slug_check', rec.tbl, rec.col); 79 80 -- The procedure also runs from merchant.create_instance_schema() for 81 -- every new instance, so do not add a constraint twice. 82 PERFORM 83 FROM pg_constraint c 84 JOIN pg_namespace n 85 ON (n.oid = c.connamespace) 86 WHERE n.nspname = s 87 AND c.conname = my_conname; 88 IF FOUND 89 THEN 90 CONTINUE; 91 END IF; 92 93 EXECUTE format('ALTER TABLE %I.%I' 94 ' ADD CONSTRAINT %I' 95 ' CHECK ( (%I ~ %L)' 96 ' AND (%I <> %L)' 97 ' AND (%I <> %L) )' 98 ' NOT VALID', 99 s, rec.tbl, 100 my_conname, 101 rec.col, my_slug_re, 102 rec.col, '.', 103 rec.col, '..'); 104 END LOOP; 105 END $$; 106 107 COMMENT ON PROCEDURE merchant.merchant_0051_init(TEXT) 108 IS 'Constrains the slug columns of one instance schema to the slug character set, without validating the rows already there.'; 109 110 111 INSERT INTO merchant.instance_fixups 112 (migration_name 113 ,version) 114 VALUES 115 ('merchant_0051_init' 116 ,51); 117 -- Apply new fix-up to existing instances 118 CALL merchant.fixup_instance_schema (51::INT8); 119 120 -- Also constrain instance IDs. 121 DO $$ 122 BEGIN 123 PERFORM 124 FROM pg_constraint c 125 JOIN pg_namespace n 126 ON (n.oid = c.connamespace) 127 WHERE n.nspname = 'merchant' 128 AND c.conname = 'merchant_instances_merchant_id_slug_check'; 129 IF NOT FOUND 130 THEN 131 ALTER TABLE merchant.merchant_instances 132 ADD CONSTRAINT merchant_instances_merchant_id_slug_check 133 CHECK ( (merchant_id ~ '^[A-Za-z0-9._:~-]+$') 134 AND (merchant_id <> '.') 135 AND (merchant_id <> '..') ) 136 NOT VALID; 137 END IF; 138 END $$; 139 140 COMMIT;