insert_aml_decision.sql (9449B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2023, 2024 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 17 DROP FUNCTION IF EXISTS exchange_do_insert_aml_decision; 18 CREATE FUNCTION exchange_do_insert_aml_decision( 19 IN in_payto_uri TEXT, -- can be NULL! 20 IN in_h_normalized_payto BYTEA, 21 IN in_h_full_payto BYTEA, -- can be NULL! 22 IN in_decision_time INT8, 23 IN in_expiration_time INT8, 24 IN in_properties JSONB, -- can be NULL 25 IN in_kyc_attributes_enc BYTEA, -- can be NULL 26 IN in_kyc_attributes_hash BYTEA, -- can be NULL 27 IN in_kyc_attributes_expiration INT8, -- can be NULL 28 IN in_new_rules JSONB, 29 IN in_to_investigate BOOLEAN, 30 IN in_new_measure_name TEXT, -- can be NULL 31 IN in_jmeasures JSONB, -- can be NULL 32 IN in_justification TEXT, -- can be NULL 33 IN in_decider_pub BYTEA, -- can be NULL 34 IN in_decider_sig BYTEA, -- can be NULL 35 IN in_notify_s TEXT, 36 IN ina_events TEXT[], 37 IN in_form_name TEXT, -- can be NULL 38 OUT out_invalid_officer BOOLEAN, 39 OUT out_account_unknown BOOLEAN, 40 OUT out_last_date INT8, 41 OUT out_legitimization_measure_serial_id INT8, 42 OUT out_is_wallet BOOL) -- can be (left at) NULL 43 LANGUAGE plpgsql 44 AS $$ 45 DECLARE 46 my_outcome_serial_id INT8; 47 my_legitimization_process_serial_id INT8; 48 my_kyc_attributes_serial_id INT8; 49 my_rec RECORD; 50 my_access_token BYTEA; 51 my_i INT4; 52 ini_event TEXT; 53 BEGIN 54 55 out_account_unknown=FALSE; 56 out_legitimization_measure_serial_id=0; 57 58 IF in_decider_pub IS NOT NULL 59 THEN 60 IF in_justification IS NULL OR in_decider_sig IS NULL 61 THEN 62 RAISE EXCEPTION 'Got in_decider_sig without justification or signature.'; 63 END IF; 64 -- Check officer is eligible to make decisions. aml_staff is append-only, 65 -- so this must look at the officer's *latest* status and only then at the 66 -- flags: filtering on the flags first would let any read-write status the 67 -- officer ever held answer for one that has since been revoked. 68 PERFORM 69 FROM (SELECT is_active 70 ,read_only 71 FROM aml_staff 72 WHERE decider_pub=in_decider_pub 73 ORDER BY last_change DESC 74 LIMIT 1) latest 75 WHERE is_active 76 AND NOT read_only; 77 IF NOT FOUND 78 THEN 79 out_invalid_officer=TRUE; 80 out_last_date=0; 81 RETURN; 82 END IF; 83 END IF; 84 85 out_invalid_officer=FALSE; 86 87 -- Check no more recent decision exists. 88 SELECT decision_time 89 INTO out_last_date 90 FROM legitimization_outcomes 91 WHERE h_payto=in_h_normalized_payto 92 AND is_active 93 ORDER BY decision_time DESC, outcome_serial_id DESC; 94 95 IF FOUND 96 THEN 97 IF in_decider_pub IS NOT NULL AND out_last_date > in_decision_time 98 THEN 99 -- Refuse to insert older decision for officer decisions. 100 RETURN; 101 END IF; 102 UPDATE legitimization_outcomes 103 SET is_active=FALSE 104 WHERE h_payto=in_h_normalized_payto 105 AND is_active; 106 ELSE 107 out_last_date = 0; 108 END IF; 109 110 SELECT access_token 111 ,is_wallet 112 INTO my_rec 113 FROM kyc_targets 114 WHERE h_normalized_payto=in_h_normalized_payto; 115 116 IF NOT FOUND 117 THEN 118 -- AML decision for previously unknown account; better includes 119 -- all required details about the account ... 120 IF in_payto_uri IS NULL 121 THEN 122 -- AML decision on an unknown account without payto_uri => fail. 123 out_account_unknown=TRUE; 124 RETURN; 125 END IF; 126 -- Well, fine, setup the account 127 out_is_wallet 128 = (LOWER (SUBSTRING (in_payto_uri, 0, 23)) = 129 'payto://taler-reserve/') OR 130 (LOWER (SUBSTRING (in_payto_uri, 0, 28)) = 131 'payto://taler-reserve-http/'); 132 INSERT INTO kyc_targets 133 (h_normalized_payto 134 ,is_wallet 135 ) VALUES ( 136 in_h_normalized_payto 137 ,out_is_wallet 138 ) 139 RETURNING access_token 140 INTO my_access_token; 141 INSERT INTO wire_targets 142 (wire_target_h_payto 143 ,h_normalized_payto 144 ,payto_uri 145 ) VALUES ( 146 in_h_full_payto 147 ,in_h_normalized_payto 148 ,in_payto_uri 149 ) 150 ON CONFLICT DO NOTHING; 151 ELSE 152 my_access_token = my_rec.access_token; 153 out_is_wallet = my_rec.is_wallet; 154 END IF; 155 156 -- Did KYC measures get prescribed? 157 IF in_jmeasures IS NOT NULL 158 THEN 159 -- First check if a perfectly equivalent legi measure 160 -- already exists, to avoid creating tons of duplicates. 161 SELECT legitimization_measure_serial_id 162 INTO out_legitimization_measure_serial_id 163 FROM legitimization_measures 164 WHERE access_token=my_access_token 165 AND jmeasures=in_jmeasures 166 AND NOT is_finished; 167 168 IF NOT FOUND 169 THEN 170 -- Enable new legitimization measure 171 INSERT INTO legitimization_measures 172 (access_token 173 ,start_time 174 ,jmeasures 175 ,display_priority 176 ) VALUES ( 177 my_access_token 178 ,in_decision_time 179 ,in_jmeasures 180 ,1) 181 RETURNING 182 legitimization_measure_serial_id 183 INTO 184 out_legitimization_measure_serial_id; 185 END IF; 186 -- end if for where we had in_jmeasures 187 END IF; 188 189 RAISE NOTICE 'marking legi measures of % as finished except for %', my_access_token, out_legitimization_measure_serial_id; 190 191 -- AML decision: mark all other active measures finished! 192 UPDATE legitimization_measures 193 SET is_finished=TRUE 194 WHERE access_token=my_access_token 195 AND NOT is_finished 196 AND legitimization_measure_serial_id != out_legitimization_measure_serial_id; 197 198 UPDATE legitimization_outcomes 199 SET is_active=FALSE 200 WHERE h_payto=in_h_normalized_payto 201 -- this clause is a minor optimization to avoid 202 -- updating outcomes that have long expired. 203 AND expiration_time >= in_decision_time; 204 205 INSERT INTO legitimization_outcomes 206 (h_payto 207 ,decision_time 208 ,expiration_time 209 ,jproperties 210 ,new_measure_name 211 ,to_investigate 212 ,jnew_rules 213 ) VALUES ( 214 in_h_normalized_payto 215 ,in_decision_time 216 ,in_expiration_time 217 ,in_properties 218 ,in_new_measure_name 219 ,in_to_investigate 220 ,in_new_rules 221 ) 222 RETURNING outcome_serial_id 223 INTO my_outcome_serial_id; 224 225 IF in_kyc_attributes_enc IS NOT NULL 226 THEN 227 IF in_kyc_attributes_hash IS NULL OR in_kyc_attributes_expiration IS NULL 228 THEN 229 RAISE EXCEPTION 'Got in_kyc_attributes_hash without hash or expiration.'; 230 END IF; 231 IF in_decider_pub IS NULL 232 THEN 233 RAISE EXCEPTION 'Got in_kyc_attributes_hash without in_decider_pub.'; 234 END IF; 235 -- Simulate a legi process for attribute insertion by AML Officer 236 INSERT INTO legitimization_processes 237 (h_payto 238 ,start_time 239 ,expiration_time 240 ,provider_name 241 ,provider_user_id 242 ,finished 243 ) VALUES ( 244 in_h_normalized_payto 245 ,in_decision_time 246 -- Process starts and finishes instantly 247 ,in_decision_time 248 ,'aml-officer' 249 ,ENCODE(in_decider_pub, 'base64') 250 ,TRUE 251 ) 252 RETURNING legitimization_process_serial_id 253 INTO my_legitimization_process_serial_id; 254 -- Now we can insert the attribute! 255 INSERT INTO kyc_attributes 256 (h_payto 257 ,collection_time 258 ,expiration_time 259 ,form_name 260 ,by_aml_officer 261 ,encrypted_attributes 262 ,legitimization_serial 263 ) VALUES ( 264 in_h_normalized_payto 265 ,in_decision_time 266 ,in_kyc_attributes_expiration 267 ,in_form_name 268 ,TRUE 269 ,in_kyc_attributes_enc 270 ,my_legitimization_process_serial_id 271 ) 272 RETURNING kyc_attributes_serial_id 273 INTO my_kyc_attributes_serial_id; 274 -- Wake up taler-exchange-sanctionscheck to check new attributes 275 -- This is value for TALER_DBEVENT_EXCHANGE_NEW_KYC_ATTRIBUTES. 276 NOTIFY XSX9Z5XGWWYFKXTAYCES63B62527JKNX9XD0131Z08THVV8YW5BZG; 277 END IF; 278 279 IF in_decider_pub IS NOT NULL 280 THEN 281 INSERT INTO aml_history 282 (h_payto 283 ,outcome_serial_id 284 ,justification 285 ,decider_pub 286 ,decider_sig 287 ,kyc_attributes_hash 288 ,kyc_attributes_serial_id 289 ) VALUES ( 290 in_h_normalized_payto 291 ,my_outcome_serial_id 292 ,in_justification 293 ,in_decider_pub 294 ,in_decider_sig 295 ,in_kyc_attributes_hash 296 ,my_kyc_attributes_serial_id 297 ); 298 END IF; 299 300 -- Trigger events 301 FOR i IN 1..COALESCE(array_length(ina_events,1),0) 302 LOOP 303 ini_event = ina_events[i]; 304 INSERT INTO kyc_events 305 (event_timestamp 306 ,event_type 307 ) VALUES ( 308 in_decision_time 309 ,ini_event); 310 IF (ini_event = 'ACCOUNT_OPEN') 311 THEN 312 UPDATE kyc_targets 313 SET open_time=in_decision_time 314 ,close_time=NULL 315 WHERE h_normalized_payto=in_h_normalized_payto; 316 END IF; 317 IF (ini_event = 'ACCOUNT_IDLE') 318 THEN 319 UPDATE kyc_targets 320 SET close_time=in_decision_time 321 WHERE h_normalized_payto=in_h_normalized_payto; 322 END IF; 323 END LOOP; 324 325 -- wake up taler-exchange-aggregator 326 INSERT INTO kyc_alerts 327 (h_payto 328 ,trigger_type 329 ) VALUES ( 330 in_h_normalized_payto 331 ,1 332 ) 333 ON CONFLICT DO NOTHING; 334 335 EXECUTE FORMAT ( 336 'NOTIFY %s' 337 ,in_notify_s); 338 339 340 END $$; 341 342 343 COMMENT ON FUNCTION exchange_do_insert_aml_decision(TEXT, BYTEA, BYTEA, INT8, INT8, JSONB, BYTEA, BYTEA, INT8, JSONB, BOOLEAN, TEXT, JSONB, TEXT, BYTEA, BYTEA, TEXT, TEXT[], TEXT) 344 IS 'Checks whether the AML officer is eligible to make AML decisions and if so inserts the decision into the table';