From d0835367865b5a0ae941abcec2c1c2d067f10b25 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Fri, 28 Jul 2023 12:27:46 +0200 Subject: combine v3 and v4 of exchange schema into v2 due to major incompatible tuple change forthcoming --- src/exchangedb/0002-age_withdrawl.sql | 156 +++++++++++++++++++++++++++++++++ src/exchangedb/0002-aml_history.sql | 148 +++++++++++++++++++++++++++++++ src/exchangedb/0002-aml_staff.sql | 40 +++++++++ src/exchangedb/0002-aml_status.sql | 102 +++++++++++++++++++++ src/exchangedb/0002-kyc_attributes.sql | 155 ++++++++++++++++++++++++++++++++ src/exchangedb/0002-purse_actions.sql | 121 +++++++++++++++++++++++++ src/exchangedb/0002-purse_deletion.sql | 110 +++++++++++++++++++++++ src/exchangedb/0002-wire_accounts.sql | 11 +++ src/exchangedb/0003-age_withdraw.sql | 156 --------------------------------- src/exchangedb/0003-aml_history.sql | 148 ------------------------------- src/exchangedb/0003-aml_staff.sql | 40 --------- src/exchangedb/0003-aml_status.sql | 102 --------------------- src/exchangedb/0003-kyc_attributes.sql | 156 --------------------------------- src/exchangedb/0003-purse_actions.sql | 121 ------------------------- src/exchangedb/0003-purse_deletion.sql | 110 ----------------------- src/exchangedb/0004-kyc_attributes.sql | 44 ---------- src/exchangedb/0004-wire_accounts.sql | 26 ------ src/exchangedb/Makefile.am | 22 +---- src/exchangedb/exchange-0002.sql.in | 9 ++ src/exchangedb/exchange-0003.sql.in | 7 -- src/exchangedb/exchange-0004.sql.in | 25 ------ 21 files changed, 854 insertions(+), 955 deletions(-) create mode 100644 src/exchangedb/0002-age_withdrawl.sql create mode 100644 src/exchangedb/0002-aml_history.sql create mode 100644 src/exchangedb/0002-aml_staff.sql create mode 100644 src/exchangedb/0002-aml_status.sql create mode 100644 src/exchangedb/0002-kyc_attributes.sql create mode 100644 src/exchangedb/0002-purse_actions.sql create mode 100644 src/exchangedb/0002-purse_deletion.sql delete mode 100644 src/exchangedb/0003-age_withdraw.sql delete mode 100644 src/exchangedb/0003-aml_history.sql delete mode 100644 src/exchangedb/0003-aml_staff.sql delete mode 100644 src/exchangedb/0003-aml_status.sql delete mode 100644 src/exchangedb/0003-kyc_attributes.sql delete mode 100644 src/exchangedb/0003-purse_actions.sql delete mode 100644 src/exchangedb/0003-purse_deletion.sql delete mode 100644 src/exchangedb/0004-kyc_attributes.sql delete mode 100644 src/exchangedb/0004-wire_accounts.sql delete mode 100644 src/exchangedb/exchange-0004.sql.in diff --git a/src/exchangedb/0002-age_withdrawl.sql b/src/exchangedb/0002-age_withdrawl.sql new file mode 100644 index 000000000..05c0df272 --- /dev/null +++ b/src/exchangedb/0002-age_withdrawl.sql @@ -0,0 +1,156 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2023 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see +-- +-- @author Özgür Kesim + +CREATE FUNCTION create_table_age_withdraw( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'age_withdraw'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE %I' + '(age_withdraw_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',h_commitment BYTEA NOT NULL CONSTRAINT h_commitment_length CHECK(LENGTH(h_commitment)=64)' + ',max_age SMALLINT NOT NULL CONSTRAINT max_age_positive CHECK(max_age>=0)' + ',amount_with_fee taler_amount NOT NULL' + ',reserve_pub BYTEA NOT NULL CONSTRAINT reserve_pub_length CHECK(LENGTH(reserve_pub)=32)' + ',reserve_sig BYTEA NOT NULL CONSTRAINT reserve_sig_length CHECK(LENGTH(reserve_sig)=64)' + ',noreveal_index SMALLINT NOT NULL CONSTRAINT noreveal_index_positive CHECK(noreveal_index>=0)' + ',h_blind_evs BYTEA[] NOT NULL CONSTRAINT h_blind_evs_length CHECK(cardinality(h_blind_evs)=cardinality(denom_serials))' + ',denom_serials INT8[] NOT NULL CONSTRAINT denom_serials_array_length CHECK(cardinality(denom_serials)=cardinality(denom_sigs))' + ',denom_sigs BYTEA[] NOT NULL CONSTRAINT denom_sigs_array_length CHECK(cardinality(denom_sigs)=cardinality(denom_serials))' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'Commitments made when withdrawing coins with age restriction and the gamma value chosen by the exchange. ' + 'It also contains the blindly signed coins, their signatures and denominations.' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'The gamma value chosen by the exchange in the cut-and-choose protocol' + ,'noreveal_index' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'The maximum age (in years) that the client commits to with this request' + ,'max_age' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Commitment made by the client, hash over the various client inputs in the cut-and-choose protocol' + ,'h_commitment' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Reference to the public key of the reserve from which the coins are going to be withdrawn' + ,'reserve_pub' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Signature of the reserve''s private key over the age-withdraw request' + ,'reserve_sig' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Array of references to the denominations' + ,'denom_serials' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Array of the blinded envelopes of the chosen fresh coins, with value as given by the denomination in the corresponding slot in denom_serials' + ,'h_blind_evs' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Array of signatures over each blinded envelope' + ,'denom_sigs' + ,table_name + ,partition_suffix + ); +END +$$; + + +CREATE FUNCTION constrain_table_age_withdraw( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'age_withdraw'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD PRIMARY KEY (h_commitment);' + ); + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_h_commitment_reserve_pub_key' + ' UNIQUE (h_commitment, reserve_pub);' + ); + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_age_withdraw_id_key' + ' UNIQUE (age_withdraw_id);' + ); +END +$$; + + +CREATE FUNCTION foreign_table_age_withdraw() +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'age_withdraw'; +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub' + ' FOREIGN KEY (reserve_pub)' + ' REFERENCES reserves(reserve_pub);' -- ON DELETE CASCADE;' + ); +END +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) +VALUES + ('age_withdraw', 'exchange-0003', 'create', TRUE ,FALSE), + ('age_withdraw', 'exchange-0003', 'constrain',TRUE ,FALSE), + ('age_withdraw', 'exchange-0003', 'foreign', TRUE ,FALSE); diff --git a/src/exchangedb/0002-aml_history.sql b/src/exchangedb/0002-aml_history.sql new file mode 100644 index 000000000..e57a2313f --- /dev/null +++ b/src/exchangedb/0002-aml_history.sql @@ -0,0 +1,148 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see +-- + +CREATE OR REPLACE FUNCTION create_table_aml_history( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aml_history'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(aml_history_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)' + ',new_threshold_val INT8 NOT NULL DEFAULT(0)' + ',new_threshold_frac INT4 NOT NULL DEFAULT(0)' + ',new_status INT4 NOT NULL DEFAULT(0)' + ',decision_time INT8 NOT NULL DEFAULT(0)' + ',justification VARCHAR NOT NULL' + ',kyc_requirements VARCHAR' + ',kyc_req_row INT8 NOT NULL DEFAULT(0)' + ',decider_pub BYTEA CHECK (LENGTH(decider_pub)=32)' + ',decider_sig BYTEA CHECK (LENGTH(decider_sig)=64)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (h_payto)' + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'AML decision history for a particular payment destination' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'hash of the payto://-URI this AML history is about' + ,'h_payto' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'new monthly inbound transaction limit below which we are OK' + ,'new_threshold_val' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + '0 for all OK, 1 for AML decision required, 2 for account is frozen (prevents further transactions)' + ,'new_status' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'when was the status changed' + ,'decision_time' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'human-readable justification for the status change' + ,'justification' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Public key of the staff member who made the AML decision' + ,'decider_pub' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Additional KYC requirements imposed by the AML staff member. Serialized JSON array of strings.' + ,'kyc_requirements' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Row in the KYC table for this KYC requirement, 0 for none.' + ,'kyc_req_row' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Signature key of the staff member affirming the AML decision; of type AML_DECISION' + ,'decider_sig' + ,table_name + ,partition_suffix + ); +END $$; + +COMMENT ON FUNCTION create_table_aml_history + IS 'Creates the aml_history table'; + + +CREATE OR REPLACE FUNCTION constrain_table_aml_history( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aml_history'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_serial_key ' + 'UNIQUE (aml_history_serial_id)' + ); + EXECUTE FORMAT ( + 'CREATE INDEX ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(h_payto, decision_time DESC);' + ); +END $$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('aml_history' + ,'exchange-0003' + ,'create' + ,TRUE + ,FALSE), + ('aml_history' + ,'exchange-0003' + ,'constrain' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0002-aml_staff.sql b/src/exchangedb/0002-aml_staff.sql new file mode 100644 index 000000000..00f60985a --- /dev/null +++ b/src/exchangedb/0002-aml_staff.sql @@ -0,0 +1,40 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see +-- + + +CREATE TABLE aml_staff + (aml_staff_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,decider_pub BYTEA PRIMARY KEY CHECK (LENGTH(decider_pub)=32) + ,master_sig BYTEA CHECK (LENGTH(master_sig)=64) + ,decider_name VARCHAR NOT NULL + ,is_active BOOLEAN NOT NULL + ,read_only BOOLEAN NOT NULL + ,last_change INT8 NOT NULL + ); +COMMENT ON TABLE aml_staff + IS 'Table with AML staff members the exchange uses or has used in the past. Entries never expire as we need to remember the last_change column indefinitely.'; +COMMENT ON COLUMN aml_staff.decider_pub + IS 'Public key of the AML staff member.'; +COMMENT ON COLUMN aml_staff.master_sig + IS 'The master public key signature on the AML staff member status, of type TALER_SIGNATURE_MASTER_AML_KEY.'; +COMMENT ON COLUMN aml_staff.decider_name + IS 'Name of the staff member.'; +COMMENT ON COLUMN aml_staff.is_active + IS 'true if we are currently supporting the use of this AML staff member.'; +COMMENT ON COLUMN aml_staff.is_active + IS 'true if the member has read-only access.'; +COMMENT ON COLUMN aml_staff.last_change + IS 'Latest time when active status changed. Used to detect replays of old messages.'; diff --git a/src/exchangedb/0002-aml_status.sql b/src/exchangedb/0002-aml_status.sql new file mode 100644 index 000000000..ee61cc391 --- /dev/null +++ b/src/exchangedb/0002-aml_status.sql @@ -0,0 +1,102 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see +-- + +CREATE OR REPLACE FUNCTION create_table_aml_status( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aml_status'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(aml_status_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32)' + ',threshold_val INT8 NOT NULL DEFAULT(0)' + ',threshold_frac INT4 NOT NULL DEFAULT(0)' + ',status INT4 NOT NULL DEFAULT(0)' + ',kyc_requirement INT8 NOT NULL DEFAULT(0)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (h_payto)' + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'AML status for a particular payment destination' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'hash of the payto://-URI this AML status is about' + ,'h_payto' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'monthly inbound transaction limit below which we are OK (if status is 1)' + ,'threshold_val' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + '0 for all OK, 1 for AML decision required, 2 for account is frozen (prevents further transactions)' + ,'status' + ,table_name + ,partition_suffix + ); +END $$; + +COMMENT ON FUNCTION create_table_aml_status + IS 'Creates the aml_status table'; + + +CREATE OR REPLACE FUNCTION constrain_table_aml_status( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aml_status'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_serial_key ' + 'UNIQUE (aml_status_serial_id)' + ); +END $$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('aml_status' + ,'exchange-0003' + ,'create' + ,TRUE + ,FALSE), + ('aml_status' + ,'exchange-0003' + ,'constrain' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0002-kyc_attributes.sql b/src/exchangedb/0002-kyc_attributes.sql new file mode 100644 index 000000000..9b18b6c2f --- /dev/null +++ b/src/exchangedb/0002-kyc_attributes.sql @@ -0,0 +1,155 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see +-- + +CREATE OR REPLACE FUNCTION create_table_kyc_attributes( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'kyc_attributes'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(kyc_attributes_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32)' + ',kyc_prox BYTEA NOT NULL CHECK (LENGTH(kyc_prox)=32)' + ',provider VARCHAR NOT NULL' + ',collection_time INT8 NOT NULL' + ',expiration_time INT8 NOT NULL' + ',encrypted_attributes BYTEA NOT NULL' + ',legitimization_serial INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (h_payto)' + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'KYC data about particular payment addresses' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'hash of payto://-URI the attributes are about' + ,'h_payto' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'short hash of normalized full name and birthdate; used to efficiently find likely duplicate users' + ,'kyc_prox' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'birth date of the user, in format YYYY-MM-DD where a value of 0 is used to indicate unknown (in official documents); NULL if the birth date was not collected by the provider; used for KYC-driven age restrictions' + ,'birthdate' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'time when the attributes were collected by the provider' + ,'collection_time' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'time when the attributes should no longer be considered validated' + ,'expiration_time' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'configuration section name of the provider that affirmed the attributes' + ,'provider' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + '(encrypted) JSON object (as string) with the attributes' + ,'encrypted_attributes' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Reference the legitimization process for which theses attributes are gathered for.' + ,'legitimization_serial' + ,table_name + ,partition_suffix + ); +END $$; + +COMMENT ON FUNCTION create_table_kyc_attributes + IS 'Creates the kyc_attributes table'; + + +CREATE OR REPLACE FUNCTION constrain_table_kyc_attributes( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'kyc_attributes'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_serial_key ' + 'UNIQUE (kyc_attributes_serial_id)' + ); + -- The legitimization_serial is a foreign key. + -- TODO: due to partitioning by h_payto, we can not simply reference + -- the serial id of the legitimization_processes + -- EXECUTE FORMAT ( + -- 'ALTER TABLE ' || table_name || + -- ' ADD CONSTRAINT ' || table_name || '_foreign_legitimization_processes' + -- ' FOREIGN KEY (legitimization_serial) ' + -- ' REFERENCES legitimization_processes (legitimization_process_serial_id)' -- ON DELETE CASCADE + -- ); + -- To search similar users (e.g. during AML checks) + EXECUTE FORMAT ( + 'CREATE INDEX ' || table_name || '_similarity_index ' + 'ON ' || table_name || ' ' + '(kyc_prox);' + ); + -- For garbage collection + EXECUTE FORMAT ( + 'CREATE INDEX ' || table_name || '_expiration_time ' + 'ON ' || table_name || ' ' + '(expiration_time ASC);' + ); +END $$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('kyc_attributes' + ,'exchange-0003' + ,'create' + ,TRUE + ,FALSE), + ('kyc_attributes' + ,'exchange-0003' + ,'constrain' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0002-purse_actions.sql b/src/exchangedb/0002-purse_actions.sql new file mode 100644 index 000000000..b4e7e132d --- /dev/null +++ b/src/exchangedb/0002-purse_actions.sql @@ -0,0 +1,121 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see +-- + + +CREATE OR REPLACE FUNCTION create_table_purse_actions( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_actions'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(purse_pub BYTEA NOT NULL PRIMARY KEY CHECK(LENGTH(purse_pub)=32)' + ',action_date INT8 NOT NULL' + ',partner_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'purses awaiting some action by the router' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'public (contract) key of the purse' + ,'purse_pub' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'when is the purse ready for action' + ,'action_date' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'wad target of an outgoing wire transfer, 0 for local, NULL if the purse is unmerged and thus the target is still unknown' + ,'partner_serial_id' + ,table_name + ,partition_suffix + ); +END $$; + + +CREATE OR REPLACE FUNCTION purse_requests_insert_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + INSERT INTO + purse_actions + (purse_pub + ,action_date) + VALUES + (NEW.purse_pub + ,NEW.purse_expiration); + RETURN NEW; +END $$; + +COMMENT ON FUNCTION purse_requests_insert_trigger() + IS 'When a purse is created, insert it into the purse_action table to take action when the purse expires.'; + + +CREATE OR REPLACE FUNCTION master_table_purse_actions() +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_actions'; +BEGIN + -- Create global index + CREATE INDEX IF NOT EXISTS purse_action_by_target + ON purse_actions + (partner_serial_id,action_date); + + -- Setup trigger + CREATE TRIGGER purse_requests_on_insert + AFTER INSERT + ON purse_requests + FOR EACH ROW EXECUTE FUNCTION purse_requests_insert_trigger(); + COMMENT ON TRIGGER purse_requests_on_insert + ON purse_requests + IS 'Here we install an entry for the purse expiration.'; +END $$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('purse_actions' + ,'exchange-0003' + ,'create' + ,TRUE + ,FALSE), + ('purse_actions' + ,'exchange-0003' + ,'master' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0002-purse_deletion.sql b/src/exchangedb/0002-purse_deletion.sql new file mode 100644 index 000000000..69db4293c --- /dev/null +++ b/src/exchangedb/0002-purse_deletion.sql @@ -0,0 +1,110 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see +-- + +CREATE OR REPLACE FUNCTION create_table_purse_deletion( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_deletion'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(purse_deletion_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',purse_sig BYTEA CHECK (LENGTH(purse_sig)=64)' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'signatures affirming explicit purse deletions' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'signature of type WALLET_PURSE_DELETE' + ,'purse_sig' + ,table_name + ,partition_suffix + ); +END $$; + +COMMENT ON FUNCTION create_table_purse_deletion + IS 'Creates the purse_deletion table'; + + +CREATE OR REPLACE FUNCTION constrain_table_purse_deletion( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_deletion'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_delete_serial_key ' + 'UNIQUE (purse_deletion_serial_id)' + ); +END $$; + + +CREATE OR REPLACE FUNCTION master_table_purse_requests_was_deleted ( +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_requests'; +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE exchange.' || table_name || + ' ADD COLUMN' + ' was_deleted BOOLEAN NOT NULL DEFAULT(FALSE)' + ); + COMMENT ON COLUMN purse_requests.was_deleted + IS 'TRUE if the purse was explicitly deleted (purse must have an entry in the purse_deletion table)'; +END $$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('purse_deletion' + ,'exchange-0003' + ,'create' + ,TRUE + ,FALSE), + ('purse_deletion' + ,'exchange-0003' + ,'constrain' + ,TRUE + ,FALSE), + ('purse_requests_was_deleted' + ,'exchange-0003' + ,'master' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0002-wire_accounts.sql b/src/exchangedb/0002-wire_accounts.sql index 628bc599b..0c167dba0 100644 --- a/src/exchangedb/0002-wire_accounts.sql +++ b/src/exchangedb/0002-wire_accounts.sql @@ -19,6 +19,9 @@ CREATE TABLE wire_accounts ,master_sig BYTEA CHECK (LENGTH(master_sig)=64) ,is_active BOOLEAN NOT NULL ,last_change INT8 NOT NULL + ,conversion_url VARCHAR DEFAULT (NULL) + ,debit_restrictions VARCHAR DEFAULT (NULL) + ,credit_restrictions VARCHAR DEFAULT (NULL) ); COMMENT ON TABLE wire_accounts IS 'Table with current and historic bank accounts of the exchange. Entries never expire as we need to remember the last_change column indefinitely.'; @@ -30,5 +33,13 @@ COMMENT ON COLUMN wire_accounts.is_active IS 'true if we are currently supporting the use of this account.'; COMMENT ON COLUMN wire_accounts.last_change IS 'Latest time when active status changed. Used to detect replays of old messages.'; +COMMENT ON COLUMN wire_accounts.conversion_url + IS 'URL of a currency conversion service if conversion is needed when this account is used; NULL if there is no conversion.'; +COMMENT ON COLUMN wire_accounts.debit_restrictions + IS 'JSON array describing restrictions imposed when debiting this account. Empty for no restrictions, NULL if account was migrated from previous database revision or account is disabled.'; +COMMENT ON COLUMN wire_accounts.credit_restrictions + IS 'JSON array describing restrictions imposed when crediting this account. Empty for no restrictions, NULL if account was migrated from previous database revision or account is disabled.'; + + -- "wire_accounts" has no sequence because it is a 'mutable' table -- and is of no concern to the auditor diff --git a/src/exchangedb/0003-age_withdraw.sql b/src/exchangedb/0003-age_withdraw.sql deleted file mode 100644 index 05c0df272..000000000 --- a/src/exchangedb/0003-age_withdraw.sql +++ /dev/null @@ -1,156 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2023 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see --- --- @author Özgür Kesim - -CREATE FUNCTION create_table_age_withdraw( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'age_withdraw'; -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE %I' - '(age_withdraw_id BIGINT GENERATED BY DEFAULT AS IDENTITY' - ',h_commitment BYTEA NOT NULL CONSTRAINT h_commitment_length CHECK(LENGTH(h_commitment)=64)' - ',max_age SMALLINT NOT NULL CONSTRAINT max_age_positive CHECK(max_age>=0)' - ',amount_with_fee taler_amount NOT NULL' - ',reserve_pub BYTEA NOT NULL CONSTRAINT reserve_pub_length CHECK(LENGTH(reserve_pub)=32)' - ',reserve_sig BYTEA NOT NULL CONSTRAINT reserve_sig_length CHECK(LENGTH(reserve_sig)=64)' - ',noreveal_index SMALLINT NOT NULL CONSTRAINT noreveal_index_positive CHECK(noreveal_index>=0)' - ',h_blind_evs BYTEA[] NOT NULL CONSTRAINT h_blind_evs_length CHECK(cardinality(h_blind_evs)=cardinality(denom_serials))' - ',denom_serials INT8[] NOT NULL CONSTRAINT denom_serials_array_length CHECK(cardinality(denom_serials)=cardinality(denom_sigs))' - ',denom_sigs BYTEA[] NOT NULL CONSTRAINT denom_sigs_array_length CHECK(cardinality(denom_sigs)=cardinality(denom_serials))' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (reserve_pub)' - ,partition_suffix - ); - PERFORM comment_partitioned_table( - 'Commitments made when withdrawing coins with age restriction and the gamma value chosen by the exchange. ' - 'It also contains the blindly signed coins, their signatures and denominations.' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'The gamma value chosen by the exchange in the cut-and-choose protocol' - ,'noreveal_index' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'The maximum age (in years) that the client commits to with this request' - ,'max_age' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Commitment made by the client, hash over the various client inputs in the cut-and-choose protocol' - ,'h_commitment' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Reference to the public key of the reserve from which the coins are going to be withdrawn' - ,'reserve_pub' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Signature of the reserve''s private key over the age-withdraw request' - ,'reserve_sig' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Array of references to the denominations' - ,'denom_serials' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Array of the blinded envelopes of the chosen fresh coins, with value as given by the denomination in the corresponding slot in denom_serials' - ,'h_blind_evs' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Array of signatures over each blinded envelope' - ,'denom_sigs' - ,table_name - ,partition_suffix - ); -END -$$; - - -CREATE FUNCTION constrain_table_age_withdraw( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'age_withdraw'; -BEGIN - table_name = concat_ws('_', table_name, partition_suffix); - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD PRIMARY KEY (h_commitment);' - ); - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD CONSTRAINT ' || table_name || '_h_commitment_reserve_pub_key' - ' UNIQUE (h_commitment, reserve_pub);' - ); - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD CONSTRAINT ' || table_name || '_age_withdraw_id_key' - ' UNIQUE (age_withdraw_id);' - ); -END -$$; - - -CREATE FUNCTION foreign_table_age_withdraw() -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'age_withdraw'; -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub' - ' FOREIGN KEY (reserve_pub)' - ' REFERENCES reserves(reserve_pub);' -- ON DELETE CASCADE;' - ); -END -$$; - - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) -VALUES - ('age_withdraw', 'exchange-0003', 'create', TRUE ,FALSE), - ('age_withdraw', 'exchange-0003', 'constrain',TRUE ,FALSE), - ('age_withdraw', 'exchange-0003', 'foreign', TRUE ,FALSE); diff --git a/src/exchangedb/0003-aml_history.sql b/src/exchangedb/0003-aml_history.sql deleted file mode 100644 index e57a2313f..000000000 --- a/src/exchangedb/0003-aml_history.sql +++ /dev/null @@ -1,148 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see --- - -CREATE OR REPLACE FUNCTION create_table_aml_history( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'aml_history'; -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(aml_history_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' - ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)' - ',new_threshold_val INT8 NOT NULL DEFAULT(0)' - ',new_threshold_frac INT4 NOT NULL DEFAULT(0)' - ',new_status INT4 NOT NULL DEFAULT(0)' - ',decision_time INT8 NOT NULL DEFAULT(0)' - ',justification VARCHAR NOT NULL' - ',kyc_requirements VARCHAR' - ',kyc_req_row INT8 NOT NULL DEFAULT(0)' - ',decider_pub BYTEA CHECK (LENGTH(decider_pub)=32)' - ',decider_sig BYTEA CHECK (LENGTH(decider_sig)=64)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (h_payto)' - ,partition_suffix - ); - PERFORM comment_partitioned_table( - 'AML decision history for a particular payment destination' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'hash of the payto://-URI this AML history is about' - ,'h_payto' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'new monthly inbound transaction limit below which we are OK' - ,'new_threshold_val' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - '0 for all OK, 1 for AML decision required, 2 for account is frozen (prevents further transactions)' - ,'new_status' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'when was the status changed' - ,'decision_time' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'human-readable justification for the status change' - ,'justification' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Public key of the staff member who made the AML decision' - ,'decider_pub' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Additional KYC requirements imposed by the AML staff member. Serialized JSON array of strings.' - ,'kyc_requirements' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Row in the KYC table for this KYC requirement, 0 for none.' - ,'kyc_req_row' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Signature key of the staff member affirming the AML decision; of type AML_DECISION' - ,'decider_sig' - ,table_name - ,partition_suffix - ); -END $$; - -COMMENT ON FUNCTION create_table_aml_history - IS 'Creates the aml_history table'; - - -CREATE OR REPLACE FUNCTION constrain_table_aml_history( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'aml_history'; -BEGIN - table_name = concat_ws('_', table_name, partition_suffix); - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD CONSTRAINT ' || table_name || '_serial_key ' - 'UNIQUE (aml_history_serial_id)' - ); - EXECUTE FORMAT ( - 'CREATE INDEX ' || table_name || '_main_index ' - 'ON ' || table_name || ' ' - '(h_payto, decision_time DESC);' - ); -END $$; - - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('aml_history' - ,'exchange-0003' - ,'create' - ,TRUE - ,FALSE), - ('aml_history' - ,'exchange-0003' - ,'constrain' - ,TRUE - ,FALSE); diff --git a/src/exchangedb/0003-aml_staff.sql b/src/exchangedb/0003-aml_staff.sql deleted file mode 100644 index 00f60985a..000000000 --- a/src/exchangedb/0003-aml_staff.sql +++ /dev/null @@ -1,40 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see --- - - -CREATE TABLE aml_staff - (aml_staff_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE - ,decider_pub BYTEA PRIMARY KEY CHECK (LENGTH(decider_pub)=32) - ,master_sig BYTEA CHECK (LENGTH(master_sig)=64) - ,decider_name VARCHAR NOT NULL - ,is_active BOOLEAN NOT NULL - ,read_only BOOLEAN NOT NULL - ,last_change INT8 NOT NULL - ); -COMMENT ON TABLE aml_staff - IS 'Table with AML staff members the exchange uses or has used in the past. Entries never expire as we need to remember the last_change column indefinitely.'; -COMMENT ON COLUMN aml_staff.decider_pub - IS 'Public key of the AML staff member.'; -COMMENT ON COLUMN aml_staff.master_sig - IS 'The master public key signature on the AML staff member status, of type TALER_SIGNATURE_MASTER_AML_KEY.'; -COMMENT ON COLUMN aml_staff.decider_name - IS 'Name of the staff member.'; -COMMENT ON COLUMN aml_staff.is_active - IS 'true if we are currently supporting the use of this AML staff member.'; -COMMENT ON COLUMN aml_staff.is_active - IS 'true if the member has read-only access.'; -COMMENT ON COLUMN aml_staff.last_change - IS 'Latest time when active status changed. Used to detect replays of old messages.'; diff --git a/src/exchangedb/0003-aml_status.sql b/src/exchangedb/0003-aml_status.sql deleted file mode 100644 index ee61cc391..000000000 --- a/src/exchangedb/0003-aml_status.sql +++ /dev/null @@ -1,102 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see --- - -CREATE OR REPLACE FUNCTION create_table_aml_status( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'aml_status'; -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(aml_status_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' - ',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32)' - ',threshold_val INT8 NOT NULL DEFAULT(0)' - ',threshold_frac INT4 NOT NULL DEFAULT(0)' - ',status INT4 NOT NULL DEFAULT(0)' - ',kyc_requirement INT8 NOT NULL DEFAULT(0)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (h_payto)' - ,partition_suffix - ); - PERFORM comment_partitioned_table( - 'AML status for a particular payment destination' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'hash of the payto://-URI this AML status is about' - ,'h_payto' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'monthly inbound transaction limit below which we are OK (if status is 1)' - ,'threshold_val' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - '0 for all OK, 1 for AML decision required, 2 for account is frozen (prevents further transactions)' - ,'status' - ,table_name - ,partition_suffix - ); -END $$; - -COMMENT ON FUNCTION create_table_aml_status - IS 'Creates the aml_status table'; - - -CREATE OR REPLACE FUNCTION constrain_table_aml_status( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'aml_status'; -BEGIN - table_name = concat_ws('_', table_name, partition_suffix); - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD CONSTRAINT ' || table_name || '_serial_key ' - 'UNIQUE (aml_status_serial_id)' - ); -END $$; - - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('aml_status' - ,'exchange-0003' - ,'create' - ,TRUE - ,FALSE), - ('aml_status' - ,'exchange-0003' - ,'constrain' - ,TRUE - ,FALSE); diff --git a/src/exchangedb/0003-kyc_attributes.sql b/src/exchangedb/0003-kyc_attributes.sql deleted file mode 100644 index 56e274a31..000000000 --- a/src/exchangedb/0003-kyc_attributes.sql +++ /dev/null @@ -1,156 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see --- - -CREATE OR REPLACE FUNCTION create_table_kyc_attributes( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'kyc_attributes'; -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(kyc_attributes_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' - ',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32)' - ',kyc_prox BYTEA NOT NULL CHECK (LENGTH(kyc_prox)=32)' - ',provider VARCHAR NOT NULL' - ',birthdate VARCHAR' - ',collection_time INT8 NOT NULL' - ',expiration_time INT8 NOT NULL' - ',encrypted_attributes BYTEA NOT NULL' - ',legitimization_serial INT8 NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (h_payto)' - ,partition_suffix - ); - PERFORM comment_partitioned_table( - 'KYC data about particular payment addresses' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'hash of payto://-URI the attributes are about' - ,'h_payto' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'short hash of normalized full name and birthdate; used to efficiently find likely duplicate users' - ,'kyc_prox' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'birth date of the user, in format YYYY-MM-DD where a value of 0 is used to indicate unknown (in official documents); NULL if the birth date was not collected by the provider; used for KYC-driven age restrictions' - ,'birthdate' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'time when the attributes were collected by the provider' - ,'collection_time' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'time when the attributes should no longer be considered validated' - ,'expiration_time' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'configuration section name of the provider that affirmed the attributes' - ,'provider' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - '(encrypted) JSON object (as string) with the attributes' - ,'encrypted_attributes' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Reference the legitimization process for which theses attributes are gathered for.' - ,'legitimization_serial' - ,table_name - ,partition_suffix - ); -END $$; - -COMMENT ON FUNCTION create_table_kyc_attributes - IS 'Creates the kyc_attributes table'; - - -CREATE OR REPLACE FUNCTION constrain_table_kyc_attributes( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'kyc_attributes'; -BEGIN - table_name = concat_ws('_', table_name, partition_suffix); - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD CONSTRAINT ' || table_name || '_serial_key ' - 'UNIQUE (kyc_attributes_serial_id)' - ); - -- The legitimization_serial is a foreign key. - -- TODO: due to partitioning by h_payto, we can not simply reference - -- the serial id of the legitimization_processes - -- EXECUTE FORMAT ( - -- 'ALTER TABLE ' || table_name || - -- ' ADD CONSTRAINT ' || table_name || '_foreign_legitimization_processes' - -- ' FOREIGN KEY (legitimization_serial) ' - -- ' REFERENCES legitimization_processes (legitimization_process_serial_id)' -- ON DELETE CASCADE - -- ); - -- To search similar users (e.g. during AML checks) - EXECUTE FORMAT ( - 'CREATE INDEX ' || table_name || '_similarity_index ' - 'ON ' || table_name || ' ' - '(kyc_prox);' - ); - -- For garbage collection - EXECUTE FORMAT ( - 'CREATE INDEX ' || table_name || '_expiration_time ' - 'ON ' || table_name || ' ' - '(expiration_time ASC);' - ); -END $$; - - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('kyc_attributes' - ,'exchange-0003' - ,'create' - ,TRUE - ,FALSE), - ('kyc_attributes' - ,'exchange-0003' - ,'constrain' - ,TRUE - ,FALSE); diff --git a/src/exchangedb/0003-purse_actions.sql b/src/exchangedb/0003-purse_actions.sql deleted file mode 100644 index b4e7e132d..000000000 --- a/src/exchangedb/0003-purse_actions.sql +++ /dev/null @@ -1,121 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see --- - - -CREATE OR REPLACE FUNCTION create_table_purse_actions( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'purse_actions'; -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(purse_pub BYTEA NOT NULL PRIMARY KEY CHECK(LENGTH(purse_pub)=32)' - ',action_date INT8 NOT NULL' - ',partner_serial_id INT8' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (purse_pub)' - ,partition_suffix - ); - PERFORM comment_partitioned_table( - 'purses awaiting some action by the router' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'public (contract) key of the purse' - ,'purse_pub' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'when is the purse ready for action' - ,'action_date' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'wad target of an outgoing wire transfer, 0 for local, NULL if the purse is unmerged and thus the target is still unknown' - ,'partner_serial_id' - ,table_name - ,partition_suffix - ); -END $$; - - -CREATE OR REPLACE FUNCTION purse_requests_insert_trigger() - RETURNS trigger - LANGUAGE plpgsql - AS $$ -BEGIN - INSERT INTO - purse_actions - (purse_pub - ,action_date) - VALUES - (NEW.purse_pub - ,NEW.purse_expiration); - RETURN NEW; -END $$; - -COMMENT ON FUNCTION purse_requests_insert_trigger() - IS 'When a purse is created, insert it into the purse_action table to take action when the purse expires.'; - - -CREATE OR REPLACE FUNCTION master_table_purse_actions() -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'purse_actions'; -BEGIN - -- Create global index - CREATE INDEX IF NOT EXISTS purse_action_by_target - ON purse_actions - (partner_serial_id,action_date); - - -- Setup trigger - CREATE TRIGGER purse_requests_on_insert - AFTER INSERT - ON purse_requests - FOR EACH ROW EXECUTE FUNCTION purse_requests_insert_trigger(); - COMMENT ON TRIGGER purse_requests_on_insert - ON purse_requests - IS 'Here we install an entry for the purse expiration.'; -END $$; - - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('purse_actions' - ,'exchange-0003' - ,'create' - ,TRUE - ,FALSE), - ('purse_actions' - ,'exchange-0003' - ,'master' - ,TRUE - ,FALSE); diff --git a/src/exchangedb/0003-purse_deletion.sql b/src/exchangedb/0003-purse_deletion.sql deleted file mode 100644 index 69db4293c..000000000 --- a/src/exchangedb/0003-purse_deletion.sql +++ /dev/null @@ -1,110 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see --- - -CREATE OR REPLACE FUNCTION create_table_purse_deletion( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'purse_deletion'; -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(purse_deletion_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' - ',purse_sig BYTEA CHECK (LENGTH(purse_sig)=64)' - ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (purse_pub)' - ,partition_suffix - ); - PERFORM comment_partitioned_table( - 'signatures affirming explicit purse deletions' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'signature of type WALLET_PURSE_DELETE' - ,'purse_sig' - ,table_name - ,partition_suffix - ); -END $$; - -COMMENT ON FUNCTION create_table_purse_deletion - IS 'Creates the purse_deletion table'; - - -CREATE OR REPLACE FUNCTION constrain_table_purse_deletion( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'purse_deletion'; -BEGIN - table_name = concat_ws('_', table_name, partition_suffix); - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD CONSTRAINT ' || table_name || '_delete_serial_key ' - 'UNIQUE (purse_deletion_serial_id)' - ); -END $$; - - -CREATE OR REPLACE FUNCTION master_table_purse_requests_was_deleted ( -) -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'purse_requests'; -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE exchange.' || table_name || - ' ADD COLUMN' - ' was_deleted BOOLEAN NOT NULL DEFAULT(FALSE)' - ); - COMMENT ON COLUMN purse_requests.was_deleted - IS 'TRUE if the purse was explicitly deleted (purse must have an entry in the purse_deletion table)'; -END $$; - - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('purse_deletion' - ,'exchange-0003' - ,'create' - ,TRUE - ,FALSE), - ('purse_deletion' - ,'exchange-0003' - ,'constrain' - ,TRUE - ,FALSE), - ('purse_requests_was_deleted' - ,'exchange-0003' - ,'master' - ,TRUE - ,FALSE); diff --git a/src/exchangedb/0004-kyc_attributes.sql b/src/exchangedb/0004-kyc_attributes.sql deleted file mode 100644 index e45d46b3b..000000000 --- a/src/exchangedb/0004-kyc_attributes.sql +++ /dev/null @@ -1,44 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2023 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see --- - -CREATE OR REPLACE FUNCTION master_table_kyc_attributes_V2() -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'kyc_attributes'; -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' DROP COLUMN birthdate;' - ); -END $$; - -COMMENT ON FUNCTION master_table_kyc_attributes_V2 - IS 'Removes birthdate column from the kyc_attributes table'; - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('kyc_attributes_V2' - ,'exchange-0004' - ,'master' - ,TRUE - ,FALSE); diff --git a/src/exchangedb/0004-wire_accounts.sql b/src/exchangedb/0004-wire_accounts.sql deleted file mode 100644 index 6114c821a..000000000 --- a/src/exchangedb/0004-wire_accounts.sql +++ /dev/null @@ -1,26 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2023 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see --- - -ALTER TABLE wire_accounts - ADD COLUMN conversion_url VARCHAR DEFAULT (NULL), - ADD COLUMN debit_restrictions VARCHAR DEFAULT (NULL), - ADD COLUMN credit_restrictions VARCHAR DEFAULT (NULL); -COMMENT ON COLUMN wire_accounts.conversion_url - IS 'URL of a currency conversion service if conversion is needed when this account is used; NULL if there is no conversion.'; -COMMENT ON COLUMN wire_accounts.debit_restrictions - IS 'JSON array describing restrictions imposed when debiting this account. Empty for no restrictions, NULL if account was migrated from previous database revision or account is disabled.'; -COMMENT ON COLUMN wire_accounts.credit_restrictions - IS 'JSON array describing restrictions imposed when crediting this account. Empty for no restrictions, NULL if account was migrated from previous database revision or account is disabled.'; diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index 6d89decdc..cbe06ce0b 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -18,19 +18,13 @@ sqlinputs = \ exchange_do_*.sql \ procedures.sql.in \ 0002-*.sql \ - exchange-0002.sql.in \ - 0003-*.sql \ - exchange-0003.sql.in \ - 0004-*.sql \ - exchange-0004.sql.in + exchange-0002.sql.in sql_DATA = \ benchmark-0001.sql \ versioning.sql \ exchange-0001.sql \ exchange-0002.sql \ - exchange-0003.sql \ - exchange-0004.sql \ drop.sql \ procedures.sql @@ -41,9 +35,7 @@ BUILT_SOURCES = \ procedures.sql CLEANFILES = \ - exchange-0002.sql \ - exchange-0003.sql \ - exchange-0004.sql + exchange-0002.sql procedures.sql: procedures.sql.in exchange_do_*.sql chmod +w $@ || true @@ -55,16 +47,6 @@ exchange-0002.sql: exchange-0002.sql.in 0002-*.sql gcc -E -P -undef - < exchange-0002.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ chmod ugo-w $@ -exchange-0003.sql: exchange-0003.sql.in 0003-*.sql - chmod +w $@ || true - gcc -E -P -undef - < exchange-0003.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ - chmod ugo-w $@ - -exchange-0004.sql: exchange-0004.sql.in 0004-*.sql - chmod +w $@ || true - gcc -E -P -undef - < exchange-0004.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ - chmod ugo-w $@ - check_SCRIPTS = \ test_idempotency.sh diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in index f0bf339bc..35113fb5d 100644 --- a/src/exchangedb/exchange-0002.sql.in +++ b/src/exchangedb/exchange-0002.sql.in @@ -86,5 +86,14 @@ COMMENT ON TYPE exchange_do_array_reserve_insert_return_type #include "0002-revolving_work_shards.sql" #include "0002-partners.sql" #include "0002-partner_accounts.sql" +#include "0002-purse_actions.sql" +#include "0002-purse_deletion.sql" +#include "0002-kyc_attributes.sql" +#include "0002-aml_status.sql" +#include "0002-aml_staff.sql" +#include "0002-aml_history.sql" +#include "0002-age_withdraw.sql" + + COMMIT; diff --git a/src/exchangedb/exchange-0003.sql.in b/src/exchangedb/exchange-0003.sql.in index cd16bfbd4..768aa778b 100644 --- a/src/exchangedb/exchange-0003.sql.in +++ b/src/exchangedb/exchange-0003.sql.in @@ -19,13 +19,6 @@ BEGIN; SELECT _v.register_patch('exchange-0003', NULL, NULL); SET search_path TO exchange; -#include "0003-purse_actions.sql" -#include "0003-purse_deletion.sql" -#include "0003-kyc_attributes.sql" -#include "0003-aml_status.sql" -#include "0003-aml_staff.sql" -#include "0003-aml_history.sql" -#include "0003-age_withdraw.sql" COMMIT; diff --git a/src/exchangedb/exchange-0004.sql.in b/src/exchangedb/exchange-0004.sql.in deleted file mode 100644 index 02bdf017a..000000000 --- a/src/exchangedb/exchange-0004.sql.in +++ /dev/null @@ -1,25 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2023 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see --- - -BEGIN; - -SELECT _v.register_patch('exchange-0004', NULL, NULL); -SET search_path TO exchange; - -#include "0004-kyc_attributes.sql" -#include "0004-wire_accounts.sql" - -COMMIT; -- cgit v1.2.3