merchant-0052.sql (12023B)
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-0052.sql 17 -- @brief repair settlement flags when reconciliation preceded deposit checking 18 19 BEGIN; 20 21 SELECT _v.register_patch('merchant-0052', NULL, NULL); 22 23 SET search_path TO merchant; 24 25 CREATE PROCEDURE merchant.merchant_0052_init(s TEXT) 26 LANGUAGE plpgsql 27 AS $OUTER$ 28 BEGIN 29 30 EXECUTE format('SET LOCAL search_path TO %I', s); 31 32 -- Reconciliation used to require a successful per-deposit check even 33 -- when the aggregate's signed transfer details already covered the coin. 34 -- Repair the resulting stale flags from those stored associations. Do not 35 -- require another visit to the order detail endpoint (issue 11813). 36 -- The predicate below must stay in sync with the one in 37 -- merchant_do_insert_transfer_details(), so that the rows repaired here 38 -- are exactly those the current code would have settled itself. 39 -- 40 -- How to read that predicate. There are two independent proofs that a 41 -- deposit was paid out, and either one on its own suffices: 42 -- 43 -- CLEAN taler-merchant-depositcheck asked the exchange about this 44 -- very deposit and got a wire transfer identifier back. 45 -- merchant_insert_deposit_to_transfer() records that as 46 -- NOT settlement_retry_needed 47 -- AND settlement_last_ec = 0 48 -- AND settlement_wtid IS NOT NULL. 49 -- 50 -- COVERED taler-merchant-reconciliation verified the exchange's 51 -- signature over an aggregate transfer whose coin list names 52 -- this deposit: a merchant_expected_transfer_to_coin row for 53 -- it, on a transfer to this deposit confirmation's own 54 -- exchange and account, that also carries a 55 -- merchant_transfer_signatures row. This is the evidence 56 -- that migration 0045's predicate did not yet accept. 57 -- 58 -- One state overrides both. When the exchange answered authoritatively 59 -- that the money went somewhere we cannot accept -- EC 2558, target 60 -- account unknown -- there is nothing to settle however well the coin is 61 -- covered, so a permanent failure keeps blocking: 62 -- 63 -- PERM_FAIL NOT settlement_retry_needed 64 -- AND settlement_last_ec <> 0 65 -- 66 -- A single deposit therefore blocks its deposit confirmation iff 67 -- 68 -- BLOCKED = PERM_FAIL OR (NOT COVERED AND NOT CLEAN) 69 -- 70 -- and a confirmation is wired once it has at least one deposit and no 71 -- BLOCKED one -- which is the EXISTS / NOT EXISTS pair below. Note that 72 -- PERM_FAIL implies NOT CLEAN, so the two disjuncts are not disjoint; 73 -- that is fine, BLOCKED only has to be TRUE often enough. 74 -- 75 -- By De Morgan on CLEAN, 76 -- 77 -- NOT CLEAN = settlement_retry_needed 78 -- OR settlement_last_ec <> 0 79 -- OR settlement_wtid IS NULL 80 -- 81 -- which is the inner three-way OR. Read it as the single concept "the 82 -- per-deposit check has not come back clean" rather than as three 83 -- separate conditions, and keep it in one piece when editing: factoring 84 -- settlement_last_ec out of it, say, still computes BLOCKED but no 85 -- longer reads as the rule it implements. 86 -- 87 -- Two tempting simplifications are both wrong: 88 -- 89 -- * NOT COVERED alone must not block. A deposit settled through the 90 -- CLEAN route never gets a merchant_expected_transfer_to_coin row, 91 -- since merchant_do_insert_transfer_details() is the only writer of 92 -- that table, so NOT COVERED is the normal case, not an anomaly. 93 -- It is in particular the case for the sibling coins of an order 94 -- whose aggregate has not been reconciled yet. 95 -- 96 -- * NOT CLEAN alone must not block either -- that was exactly the 97 -- pre-fix predicate this migration repairs after. 98 -- 99 -- The COALESCE calls are load-bearing: settlement_retry_needed and 100 -- settlement_last_ec are both nullable, NULL meaning "never inquired". 101 -- Without them a NULL would make the OR chain evaluate to NULL instead 102 -- of TRUE, and the row would silently fail to block. 103 -- 104 -- Why a migration is needed at all, rather than just the fixed 105 -- predicate: wire_pending is cleared in exactly one place in the running 106 -- code, inside merchant_do_insert_transfer_details(), and that function 107 -- returns out_duplicate early -- before its per-coin loop -- once the 108 -- transfer's signature is stored. update_to_expected_transfer_finalized() 109 -- additionally clears retry_needed on the expected transfer, so 110 -- merchant.select_open_transfers() never offers that wtid again, and the 111 -- per-deposit route never writes wire_pending itself. A confirmation 112 -- left pending by the old predicate is therefore stranded for good: 113 -- its order stays paid-but-not-wired no matter how many times 114 -- depositcheck and reconciliation run afterwards. 115 UPDATE merchant_deposit_confirmations mdc 116 SET wire_pending=FALSE 117 WHERE mdc.wire_pending 118 AND EXISTS 119 (SELECT 1 FROM merchant_deposits md 120 WHERE md.deposit_confirmation_serial=mdc.deposit_confirmation_serial) 121 AND NOT EXISTS 122 (SELECT 1 FROM merchant_deposits md 123 WHERE md.deposit_confirmation_serial=mdc.deposit_confirmation_serial 124 AND ((NOT COALESCE(md.settlement_retry_needed, TRUE) 125 AND COALESCE(md.settlement_last_ec, 0) <> 0) 126 OR ((COALESCE(md.settlement_retry_needed, TRUE) 127 OR md.settlement_wtid IS NULL 128 OR COALESCE(md.settlement_last_ec, 0) <> 0) 129 AND NOT EXISTS 130 (SELECT 1 131 FROM merchant_expected_transfer_to_coin tc 132 JOIN merchant_expected_transfers et 133 USING (expected_credit_serial) 134 JOIN merchant_transfer_signatures ts 135 USING (expected_credit_serial) 136 WHERE tc.deposit_serial=md.deposit_serial 137 AND et.exchange_url=mdc.exchange_url 138 AND et.account_serial=mdc.account_serial)))); 139 140 -- A paid contract is wired once all of its deposit confirmations are done. 141 -- Require a confirmation so externally marked payments are not changed. 142 -- This migration deliberately does not generate historical notifications 143 -- or order-settled webhooks. 144 UPDATE merchant_contract_terms mct 145 SET wired=TRUE 146 WHERE mct.paid 147 AND NOT mct.wired 148 AND EXISTS 149 (SELECT 1 150 FROM merchant_deposit_confirmations mdc 151 WHERE mdc.order_serial=mct.order_serial) 152 AND NOT EXISTS 153 (SELECT 1 154 FROM merchant_deposit_confirmations mdc 155 WHERE mdc.order_serial=mct.order_serial 156 AND mdc.wire_pending); 157 158 SET LOCAL search_path TO merchant; 159 160 END 161 $OUTER$; 162 163 COMMENT ON PROCEDURE merchant.merchant_0052_init(TEXT) 164 IS 'Clears stale wire_pending and wired flags of one instance schema for transfers that the stored signed transfer details already cover.'; 165 166 INSERT INTO merchant.instance_fixups 167 (migration_name 168 ,version) 169 VALUES 170 ('merchant_0052_init' 171 ,52); 172 -- Apply new fix-up to existing instances 173 CALL merchant.fixup_instance_schema (52::INT8); 174 175 176 -- Since commit 9a4407f7a in the exchange, the REST input that writes an 177 -- instance website goes through TALER_JSON_spec_web_url(), i.e. through 178 -- TALER_is_web_url(), which demands 179 -- 180 -- * a case insensitive 'http://' or 'https://' prefix, and 181 -- * every character drawn from the ASCII set 182 -- 'A..Za..z0..9' plus '/' ':' ';' '&' '?' '-' '.' ',' '=' '_' '~' '%' '+' '#'. 183 -- 184 -- An older backend stored whatever a JSON string could hold, so legacy rows 185 -- may carry a bare host ('www.example.com'), a foreign scheme, or bytes 186 -- outside that set. 187 -- 188 -- We force the scheme to be 'https://'. That's a best-case guess and 189 -- could be wrong, just like the user could have specified something 190 -- other than a domain name under 'website'. Tough luck, best-effort 191 -- migration. 192 -- 193 -- If we detect a value that names some *other* scheme, we just 194 -- drop it. Instance owner can easily set their website later, better than 195 -- us silently building nonsense like 'https://ftp://example.com'. 196 -- Warnings are logged when we set a website to NULL. 197 198 SET search_path TO merchant; 199 200 DO $$ 201 DECLARE 202 -- These two are the Web URL predicate, and must stay in sync with 203 -- TALER_is_web_url() in the exchange (src/util/url.c). They are defined 204 -- once: both the repair below and the CHECK constraint at the end are 205 -- built from these variables, so the two cannot drift apart. Keep the 206 -- constraint no stricter than the C check, or the backend would accept 207 -- input that the database then refuses with a 500. 208 my_scheme_re TEXT DEFAULT '^https?://'; 209 my_charset_re TEXT DEFAULT '^[A-Za-z0-9/:;&?.,=_~%+#-]+$'; 210 my_new_scheme TEXT DEFAULT 'https://'; 211 my_conname TEXT DEFAULT 'merchant_instances_website_web_url_check'; 212 rec RECORD; 213 my_trimmed TEXT; 214 my_new TEXT; 215 BEGIN 216 FOR rec IN 217 SELECT merchant_serial 218 ,merchant_id 219 ,website 220 FROM merchant.merchant_instances 221 WHERE website IS NOT NULL 222 AND NOT ( (website ~* my_scheme_re) 223 AND (website ~ my_charset_re) ) 224 FOR UPDATE 225 LOOP 226 -- Surrounding whitespace is not in the allowed set and would otherwise 227 -- cost us the whole URL, while the intent is unambiguous. 228 my_trimmed := btrim(rec.website, E' \t\n\r\f\v'); 229 230 IF my_trimmed = '' 231 THEN 232 my_new := NULL; 233 ELSIF my_trimmed ~* my_scheme_re 234 THEN 235 -- Scheme is already right, so only the character set can be at 236 -- fault; the final guard below rejects it. 237 my_new := my_trimmed; 238 ELSIF (my_trimmed ~ '^[A-Za-z][A-Za-z0-9+.-]*:') 239 AND (my_trimmed !~ '^[A-Za-z0-9.-]+:[0-9]') 240 THEN 241 -- Bad character set 242 my_new := NULL; 243 ELSE 244 my_new := my_new_scheme || my_trimmed; 245 END IF; 246 247 -- Extra guard: satisfy exactly the predicate the constraint enforces 248 IF (my_new IS NOT NULL) 249 AND NOT ( (my_new ~* my_scheme_re) 250 AND (my_new ~ my_charset_re) ) 251 THEN 252 my_new := NULL; 253 END IF; 254 255 IF my_new IS DISTINCT FROM rec.website 256 THEN 257 UPDATE merchant.merchant_instances 258 SET website=my_new 259 WHERE merchant_serial=rec.merchant_serial; 260 261 IF my_new IS NULL 262 THEN 263 RAISE WARNING 264 'instance %: website dropped, it cannot be repaired automatically', 265 rec.merchant_id; 266 ELSE 267 RAISE NOTICE 268 'instance %: website normalized to %', 269 rec.merchant_id, my_new; 270 END IF; 271 END IF; 272 END LOOP; 273 274 -- Enforce the invariant from here on. A NULL website stays legal: the 275 -- column is optional, and CHECK accepts NULL. 276 PERFORM 277 FROM pg_constraint c 278 JOIN pg_namespace n 279 ON (n.oid = c.connamespace) 280 WHERE n.nspname = 'merchant' 281 AND c.conname = my_conname; 282 IF NOT FOUND 283 THEN 284 EXECUTE format('ALTER TABLE merchant.merchant_instances' 285 ' ADD CONSTRAINT %I' 286 ' CHECK ( (website ~* %L)' 287 ' AND (website ~ %L) )', 288 my_conname, 289 my_scheme_re, 290 my_charset_re); 291 END IF; 292 END $$; 293 294 COMMIT;