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