0003-kyc_targets.sql (4781B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2025 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 CREATE FUNCTION create_table_kyc_targets( 18 IN partition_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 my_rec RECORD; 25 my_payto TEXT; 26 my_is_wallet BOOL; 27 wtc CURSOR FOR 28 SELECT 29 access_token 30 ,target_pub 31 ,h_normalized_payto 32 ,aml_program_lock_timeout 33 ,payto_uri 34 FROM exchange.wire_targets; 35 BEGIN 36 PERFORM create_partitioned_table( 37 'CREATE TABLE %I' 38 '(kyc_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' 39 ',h_normalized_payto BYTEA PRIMARY KEY CHECK(LENGTH(h_normalized_payto)=32)' 40 ',access_token BYTEA CHECK(LENGTH(access_token)=32)' 41 ' DEFAULT random_bytea(32)' 42 ',target_pub BYTEA CHECK(LENGTH(target_pub)=32) DEFAULT NULL' 43 ',aml_program_lock_timeout INT8 DEFAULT NULL' 44 ',is_wallet BOOL' 45 ') %s ;' 46 ,'kyc_targets' 47 ,'PARTITION BY HASH (h_normalized_payto)' 48 ,partition_suffix 49 ); 50 PERFORM comment_partitioned_table( 51 'All identities for KYC purposes based on normalized payto URIs' 52 ,'kyc_targets' 53 ,partition_suffix 54 ); 55 PERFORM comment_partitioned_column( 56 'high-entropy random value that is used as a bearer token used to authenticate access to the KYC SPA and its state (without requiring a signature)' 57 ,'access_token' 58 ,'kyc_targets' 59 ,NULL 60 ); 61 PERFORM comment_partitioned_column( 62 'Public key of a merchant instance or reserve to authenticate access; NULL if KYC is not allowed for the account (if there was no incoming KYC wire transfer yet); updated, thus NOT available to the auditor' 63 ,'target_pub' 64 ,'kyc_targets' 65 ,NULL 66 ); 67 PERFORM comment_partitioned_column( 68 'hash over the normalized payto URI for this account; used for KYC operations' 69 ,'h_normalized_payto' 70 ,'kyc_targets' 71 ,NULL 72 ); 73 PERFORM comment_partitioned_column( 74 'If non-NULL, an AML program should be running and it holds a lock on this account, thus other AML programs should not be started concurrently. Given the possibility of crashes, the lock automatically expires at the time value given in this column. At that time, the lock can be considered stale.' 75 ,'aml_program_lock_timeout' 76 ,'kyc_targets' 77 ,NULL 78 ); 79 PERFORM comment_partitioned_column( 80 'True if this KYC account is for a wallet, false if it is for a bank account' 81 ,'is_wallet' 82 ,'kyc_targets' 83 ,NULL 84 ); 85 86 -- Migrate existing entries. We may have multiple for 87 -- the same account, which is a historic bug (#10003) 88 -- we are implicitly fixing here via "ON CONFLICT 89 -- DO NOTHING" which ensures that moving forward we 90 -- have a unique access token per KYC account. 91 FOR my_rec IN wtc 92 LOOP 93 my_payto = my_rec.payto_uri; 94 my_is_wallet 95 = (LOWER (SUBSTRING (my_payto, 0, 23)) = 96 'payto://taler-reserve/') OR 97 (LOWER (SUBSTRING (my_payto, 0, 28)) = 98 'payto://taler-reserve-http/'); 99 INSERT INTO kyc_targets 100 (h_normalized_payto 101 ,access_token 102 ,target_pub 103 ,aml_program_lock_timeout 104 ,is_wallet 105 ) VALUES ( 106 my_rec.h_normalized_payto 107 ,my_rec.access_token 108 ,my_rec.target_pub 109 ,my_rec.aml_program_lock_timeout 110 ,my_is_wallet 111 ) 112 ON CONFLICT DO NOTHING; 113 END LOOP; 114 END $$; 115 116 117 CREATE FUNCTION constrain_table_kyc_targets( 118 IN partition_suffix TEXT 119 ) 120 RETURNS void 121 LANGUAGE plpgsql 122 AS $$ 123 DECLARE 124 table_name TEXT DEFAULT 'kyc_targets'; 125 BEGIN 126 table_name = concat_ws('_', table_name, partition_suffix); 127 EXECUTE FORMAT ( 128 'ALTER TABLE ' || table_name || 129 ' ADD CONSTRAINT ' || table_name || '_kyc_target_serial_id_key' 130 ' UNIQUE (kyc_target_serial_id)' 131 ); 132 EXECUTE FORMAT ( 133 'ALTER TABLE ' || table_name || 134 ' ADD CONSTRAINT ' || table_name || '_kyc_target_access_token_unique' 135 ' UNIQUE (access_token)' 136 ); 137 END 138 $$; 139 140 141 INSERT INTO exchange_tables 142 (name 143 ,version 144 ,action 145 ,partitioned 146 ,by_range) 147 VALUES 148 ('kyc_targets' 149 ,'exchange-0003' 150 ,'create' 151 ,TRUE 152 ,FALSE), 153 ('kyc_targets' 154 ,'exchange-0003' 155 ,'constrain' 156 ,TRUE 157 ,FALSE);