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