exchange_do_lookup_kyc_requirement_by_row.sql (4839B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 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 -- @author: Christian Grothoff 17 18 DROP FUNCTION IF EXISTS exchange_do_lookup_kyc_requirement_by_row; 19 20 CREATE FUNCTION exchange_do_lookup_kyc_requirement_by_row( 21 IN in_h_normalized_payto BYTEA, 22 IN in_account_pub BYTEA, -- NULL allowed 23 OUT out_account_pub BYTEA, -- NULL allowed 24 OUT out_reserve_pub BYTEA, -- NULL allowed 25 OUT out_access_token BYTEA, -- NULL if 'out_not_found' 26 OUT out_jrules JSONB, -- NULL allowed 27 OUT out_is_wallet BOOLEAN, -- NULL allowed 28 OUT out_not_found BOOLEAN, 29 OUT out_rule_gen INT8, -- NULL allowed 30 OUT out_aml_review BOOLEAN, -- NULL allowed 31 OUT out_kyc_required BOOLEAN) 32 LANGUAGE plpgsql 33 AS $$ 34 DECLARE 35 my_wtrec RECORD; 36 my_lorec RECORD; 37 my_ok BOOL; 38 BEGIN 39 40 -- Find the access token and the current account public key. 41 SELECT access_token 42 ,target_pub 43 ,is_wallet 44 INTO my_wtrec 45 FROM kyc_targets 46 WHERE h_normalized_payto=in_h_normalized_payto; 47 48 IF NOT FOUND 49 THEN 50 -- RAISE WARNING 'kyc_target % not found', in_h_normalized_payto; 51 -- Given that we don't recognize the normalized payto, there is no 52 -- chance that we can match the incoming public key against anything, 53 -- so this is a 404-case. 54 out_not_found = TRUE; 55 out_kyc_required = FALSE; 56 RETURN; 57 END IF; 58 59 -- It is definitively OK if we were given a public key AND it matches 60 my_ok = (in_account_pub IS NOT NULL) AND 61 (my_wtrec.target_pub = in_account_pub); 62 63 IF ( (NOT my_ok) AND 64 (in_account_pub IS NOT NULL) ) 65 THEN 66 -- We were given an in_account_pub, but it did not match the 67 -- target pub. 68 -- Try to see if the in_account_pub appears in ANY reserve_in 69 -- or kyc_auths_in for this account instead. 70 PERFORM 71 FROM reserves_in ri 72 JOIN wire_targets wt 73 ON (ri.wire_source_h_payto = wt.wire_target_h_payto) 74 WHERE ri.reserve_pub=in_account_pub 75 AND wt.h_normalized_payto=in_h_normalized_payto; 76 IF FOUND 77 THEN 78 my_wtrec.target_pub = in_account_pub; 79 my_ok = TRUE; 80 ELSE 81 PERFORM 82 FROM kycauths_in ki 83 JOIN wire_targets wt 84 ON (ki.wire_source_h_payto = wt.wire_target_h_payto) 85 WHERE ki.account_pub=in_account_pub 86 AND wt.h_normalized_payto=in_h_normalized_payto; 87 IF FOUND 88 THEN 89 my_wtrec.target_pub = in_account_pub; 90 my_ok = TRUE; 91 END IF; 92 END IF; 93 END IF; 94 95 IF (NOT my_ok AND 96 ( (in_account_pub IS NOT NULL) OR 97 (my_wtrec.target_pub IS NULL) ) ) 98 THEN 99 -- RAISE WARNING 'No match and target_pub is NOT NULL'; 100 -- We failed to find a matching public key for in_account_pub, and 101 -- either the client provided a specific one to match OR 102 -- we could not return any one that could even work, which means 103 -- we are lacking the KYC auth or any even a triggered requirement. 104 out_not_found = TRUE; 105 out_kyc_required = FALSE; 106 RETURN; 107 END IF; 108 109 -- We have found "something", which may or may not match the input 110 -- public key (if there was one), but at least some KYC requirement 111 -- exists. 112 out_not_found = FALSE; 113 114 out_is_wallet = my_wtrec.is_wallet; 115 out_account_pub = my_wtrec.target_pub; 116 out_access_token = my_wtrec.access_token; 117 118 -- Check if there are active measures for the account. 119 PERFORM 120 FROM legitimization_measures 121 WHERE access_token=out_access_token 122 AND NOT is_finished 123 LIMIT 1; 124 125 out_kyc_required = FOUND; 126 127 -- Get currently applicable rules. 128 -- Only one should ever be active per account. 129 SELECT jnew_rules 130 ,to_investigate 131 ,outcome_serial_id 132 INTO my_lorec 133 FROM legitimization_outcomes 134 WHERE h_payto=in_h_normalized_payto 135 AND is_active; 136 137 IF FOUND 138 THEN 139 out_jrules=my_lorec.jnew_rules; 140 out_aml_review=my_lorec.to_investigate; 141 out_rule_gen=my_lorec.outcome_serial_id; 142 END IF; 143 144 -- Check most recent reserve_in wire transfer, we also 145 -- allow that reserve public key for authentication! 146 -- Only needed for old wallets that don't pass 147 -- in the account pub explicitly. 148 SELECT reserve_pub 149 INTO out_reserve_pub 150 FROM reserves_in 151 WHERE wire_source_h_payto 152 IN (SELECT wt.wire_target_h_payto 153 FROM wire_targets wt 154 WHERE h_normalized_payto=in_h_normalized_payto) 155 ORDER BY execution_date DESC, reserve_in_serial_id DESC 156 LIMIT 1; 157 158 END $$;