From 6e5092d83473dc1b0200d82744cf0f0056b0c110 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Thu, 14 Sep 2023 17:37:53 +0200 Subject: more work towards auditor support for AML/KYC --- src/auditor/taler-helper-auditor-wire.c | 451 +++++++++++++++++---- src/auditordb/Makefile.am | 3 + src/auditordb/auditor-0001.sql | 26 +- src/auditordb/pg_delete_pending_deposit.c | 51 +++ src/auditordb/pg_delete_pending_deposit.h | 46 +++ src/auditordb/pg_get_wire_auditor_progress.c | 13 +- src/auditordb/pg_insert_pending_deposit.c | 61 +++ src/auditordb/pg_insert_pending_deposit.h | 50 +++ src/auditordb/pg_insert_wire_auditor_progress.c | 10 +- src/auditordb/pg_select_pending_deposits.c | 152 +++++++ src/auditordb/pg_select_pending_deposits.h | 46 +++ src/auditordb/pg_template.c | 2 +- src/auditordb/pg_template.h | 2 +- src/auditordb/pg_update_wire_auditor_progress.c | 12 +- src/auditordb/plugin_auditordb_postgres.c | 10 + src/exchange/taler-exchange-httpd_common_kyc.c | 10 + src/exchangedb/0002-kyc_attributes.sql | 1 + src/exchangedb/Makefile.am | 2 + src/exchangedb/exchange-0002.sql.in | 12 +- .../exchange_do_insert_kyc_attributes.sql | 8 +- .../exchange_do_select_deposits_missing_wire.sql | 171 +------- ...ge_do_select_justification_for_missing_wire.sql | 104 +++++ src/exchangedb/pg_insert_kyc_attributes.c | 10 +- src/exchangedb/pg_insert_kyc_attributes.h | 6 +- .../pg_select_aggregations_above_serial.c | 137 +++++++ .../pg_select_aggregations_above_serial.h | 47 +++ .../pg_select_batch_deposits_missing_wire.c | 65 +-- .../pg_select_batch_deposits_missing_wire.h | 16 +- .../pg_select_justification_for_missing_wire.c | 89 ++++ .../pg_select_justification_for_missing_wire.h | 49 +++ src/exchangedb/pg_template.c | 2 +- src/exchangedb/pg_template.h | 2 +- src/exchangedb/plugin_exchangedb_postgres.c | 8 +- src/exchangedb/procedures.sql.in | 1 + src/exchangedb/test_exchangedb.c | 49 +-- src/include/taler_auditordb_plugin.h | 92 ++++- src/include/taler_exchangedb_plugin.h | 92 ++++- src/include/taler_kyclogic_lib.h | 14 + src/kyclogic/kyclogic_api.c | 11 + 39 files changed, 1554 insertions(+), 379 deletions(-) create mode 100644 src/auditordb/pg_delete_pending_deposit.c create mode 100644 src/auditordb/pg_delete_pending_deposit.h create mode 100644 src/auditordb/pg_insert_pending_deposit.c create mode 100644 src/auditordb/pg_insert_pending_deposit.h create mode 100644 src/auditordb/pg_select_pending_deposits.c create mode 100644 src/auditordb/pg_select_pending_deposits.h create mode 100644 src/exchangedb/exchange_do_select_justification_for_missing_wire.sql create mode 100644 src/exchangedb/pg_select_aggregations_above_serial.c create mode 100644 src/exchangedb/pg_select_aggregations_above_serial.h create mode 100644 src/exchangedb/pg_select_justification_for_missing_wire.c create mode 100644 src/exchangedb/pg_select_justification_for_missing_wire.h (limited to 'src') diff --git a/src/auditor/taler-helper-auditor-wire.c b/src/auditor/taler-helper-auditor-wire.c index 71775bd4b..2d2bdbcc2 100644 --- a/src/auditor/taler-helper-auditor-wire.c +++ b/src/auditor/taler-helper-auditor-wire.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2017-2022 Taler Systems SA + Copyright (C) 2017-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 @@ -548,14 +548,18 @@ do_shutdown (void *cls) start_time), TALER_JSON_pack_time_abs_human ("wire_auditor_end_time", GNUNET_TIME_absolute_get ()), - GNUNET_JSON_pack_uint64 ("start_pp_reserve_close_uuid", + GNUNET_JSON_pack_uint64 ("start_pp_reserve_close_id", start_pp.last_reserve_close_uuid), - GNUNET_JSON_pack_uint64 ("end_pp_reserve_close_uuid", + GNUNET_JSON_pack_uint64 ("end_pp_reserve_close_id", pp.last_reserve_close_uuid), - TALER_JSON_pack_time_abs_human ("start_pp_last_timestamp", - start_pp.last_timestamp.abs_time), - TALER_JSON_pack_time_abs_human ("end_pp_last_timestamp", - pp.last_timestamp.abs_time), + GNUNET_JSON_pack_uint64 ("start_pp_last_batch_deposit_id", + start_pp.last_batch_deposit_uuid), + GNUNET_JSON_pack_uint64 ("end_pp_last_batch_deposit_id", + pp.last_batch_deposit_uuid), + GNUNET_JSON_pack_uint64 ("start_pp_last_aggregation_serial_id", + start_pp.last_aggregation_serial), + GNUNET_JSON_pack_uint64 ("end_pp_last_aggregation_serial_id", + pp.last_aggregation_serial), GNUNET_JSON_pack_array_steal ("account_progress", report_account_progress))); report_wire_out_inconsistencies = NULL; @@ -773,8 +777,7 @@ commit (enum GNUNET_DB_QueryStatus qs) GNUNET_JSON_pack_uint64 ("end_reserve_in", wa->pp.last_reserve_in_serial_id), GNUNET_JSON_pack_uint64 ("start_wire_out", - wa->start_pp. - last_wire_out_serial_id), + wa->start_pp.last_wire_out_serial_id), GNUNET_JSON_pack_uint64 ("end_wire_out", wa->pp.last_wire_out_serial_id)))); if (GNUNET_DB_STATUS_SUCCESS_ONE_RESULT == wa->qsx) @@ -818,8 +821,9 @@ commit (enum GNUNET_DB_QueryStatus qs) return qs; } GNUNET_log (GNUNET_ERROR_TYPE_INFO, - "Concluded audit step at %s\n", - GNUNET_TIME_timestamp2s (pp.last_timestamp)); + "Concluded audit step at %llu/%llu\n", + (unsigned long long) pp.last_aggregation_serial, + (unsigned long long) pp.last_batch_deposit_uuid); if (GNUNET_DB_STATUS_SUCCESS_ONE_RESULT == qs) { @@ -856,60 +860,207 @@ commit (enum GNUNET_DB_QueryStatus qs) /* ***************************** Analyze required transfers ************************ */ /** - * Function called on deposits that are past their due date - * and have not yet seen a wire transfer. + * Closure for import_wire_missing_cb(). + */ +struct ImportMissingWireContext +{ + /** + * Set to maximum row ID encountered. + */ + uint64_t max_batch_deposit_uuid; + + /** + * Set to database errors in callback. + */ + enum GNUNET_DB_QueryStatus err; +}; + + +/** + * Function called on deposits that need to be checked for their + * wire transfer. * - * @param cls closure, points to a `struct GNUNET_TIME_Timestamp` + * @param cls closure, points to a `struct ImportMissingWireContext` + * @param batch_deposit_serial_id serial of the entry in the batch deposits table * @param total_amount value of the missing deposits, including fee - * @param payto_uri where should the funds be wired + * @param wire_target_h_payto where should the funds be wired * @param deadline what was the earliest requested wire transfer deadline - * @param kyc_pending NULL if no KYC requirement is pending, otherwise text describing the missing KYC requirement - * @param aml_status status of AML possibly blocking the transfer - * @param aml_limit current monthly AML limit */ static void -wire_missing_cb (void *cls, - const struct TALER_Amount *total_amount, - const char *payto_uri, - struct GNUNET_TIME_Timestamp deadline, - const char *kyc_pending, - enum TALER_AmlDecisionState status, - const struct TALER_Amount *aml_limit) +import_wire_missing_cb (void *cls, + uint64_t batch_deposit_serial_id, + const struct TALER_Amount *total_amount, + const struct TALER_PaytoHashP *wire_target_h_payto, + struct GNUNET_TIME_Timestamp deadline) { - struct GNUNET_TIME_Timestamp *nt = cls; - json_t *rep; + struct ImportMissingWireContext *wc = cls; + enum GNUNET_DB_QueryStatus qs; + + if (wc->err < 0) + return; /* already failed */ + GNUNET_assert (batch_deposit_serial_id > wc->max_batch_deposit_uuid); + wc->max_batch_deposit_uuid = batch_deposit_serial_id; + qs = TALER_ARL_adb->insert_pending_deposit ( + TALER_ARL_adb->cls, + &TALER_ARL_master_pub, + batch_deposit_serial_id, + wire_target_h_payto, + total_amount, + deadline); + if (qs < 0) + wc->err = qs; +} + + +/** + * Information about a delayed wire transfer and the possible + * reasons for the delay. + */ +struct ReasonDetail +{ + /** + * Total amount that should have been transferred. + */ + struct TALER_Amount total_amount; + + /** + * Earliest deadline for an expected transfer to the account. + */ + struct GNUNET_TIME_Timestamp deadline; + + /** + * Target account, NULL if even that is not known (due to + * exchange lacking required entry in wire_targets table). + */ + char *payto_uri; + + /** + * Reasons due to pending KYC requests. + */ + char *kyc_pending; + + /** + * AML decision state for the target account. + */ + enum TALER_AmlDecisionState status; + + /** + * Current AML threshold for the account, may be an invalid account if the + * default threshold applies. + */ + struct TALER_Amount aml_limit; +}; + +/** + * Closure for report_wire_missing_cb(). + */ +struct ReportMissingWireContext +{ + /** + * Map from wire_target_h_payto to `struct ReasonDetail`. + */ + struct GNUNET_CONTAINER_MultiShortmap *map; + + /** + * Set to database errors in callback. + */ + enum GNUNET_DB_QueryStatus err; +}; + + +/** + * Closure for #clear_finished_transfer_cb(). + */ +struct AggregationContext +{ + /** + * Set to maximum row ID encountered. + */ + uint64_t max_aggregation_serial; + + /** + * Set to database errors in callback. + */ + enum GNUNET_DB_QueryStatus err; +}; + + +/** + * Free memory allocated in @a value. + * + * @param cls unused + * @param key unused + * @param value must be a `struct ReasonDetail` + * @return #GNUNET_YES if we should continue to + * iterate, + * #GNUNET_NO if not. + */ +static enum GNUNET_GenericReturnValue +free_report_entry (void *cls, + const struct GNUNET_ShortHashCode *key, + void *value) +{ + struct ReasonDetail *rd = value; + + GNUNET_free (rd->kyc_pending); + GNUNET_free (rd->payto_uri); + GNUNET_free (rd); + return GNUNET_YES; +} + + +/** + * We had an entry in our map of wire transfers that + * should have been performed. Generate report. + * + * @param cls unused + * @param key unused + * @param value must be a `struct ReasonDetail` + * @return #GNUNET_YES if we should continue to + * iterate, + * #GNUNET_NO if not. + */ +static enum GNUNET_GenericReturnValue +generate_report (void *cls, + const struct GNUNET_ShortHashCode *key, + void *value) +{ + struct ReasonDetail *rd = value; - *nt = GNUNET_TIME_timestamp_min (deadline, - *nt); - (void) cls; - // TODO: maybe split up by category? - TALER_ARL_amount_add (&total_amount_lag, - &total_amount_lag, - total_amount); /* For now, we simplify and only check that the amount was tiny */ - if (0 > TALER_amount_cmp (total_amount, + if (0 > TALER_amount_cmp (&rd->total_amount, &tiny_amount)) - return; /* acceptable, amount was tiny */ - if (NULL != kyc_pending) + return free_report_entry (cls, + key, + value); /* acceptable, amount was tiny */ + // TODO: maybe split total_amount_lag up by category below? + TALER_ARL_amount_add (&total_amount_lag, + &total_amount_lag, + &rd->total_amount); + if (NULL != rd->kyc_pending) { + json_t *rep; + rep = GNUNET_JSON_PACK ( TALER_JSON_pack_amount ("total_amount", - total_amount), + &rd->total_amount), TALER_JSON_pack_time_abs_human ("deadline", - deadline.abs_time), + rd->deadline.abs_time), GNUNET_JSON_pack_string ("kyc_pending", - kyc_pending), - GNUNET_JSON_pack_string ("account", - payto_uri)); + rd->kyc_pending), + GNUNET_JSON_pack_allow_null ( + GNUNET_JSON_pack_string ("account", + rd->payto_uri))); TALER_ARL_report (report_kyc_lags, rep); } - else if (TALER_AML_NORMAL != status) + else if (TALER_AML_NORMAL != rd->status) { const char *sstatus; + json_t *rep; - switch (status) + switch (rd->status) { case TALER_AML_NORMAL: GNUNET_assert (0); @@ -923,30 +1074,128 @@ wire_missing_cb (void *cls, } rep = GNUNET_JSON_PACK ( TALER_JSON_pack_amount ("total_amount", - total_amount), - TALER_JSON_pack_amount ("aml_limit", - aml_limit), + &rd->total_amount), + GNUNET_JSON_pack_allow_null ( + TALER_JSON_pack_amount ("aml_limit", + TALER_amount_is_valid (&rd->aml_limit) + ? &rd->aml_limit + : NULL)), TALER_JSON_pack_time_abs_human ("deadline", - deadline.abs_time), + rd->deadline.abs_time), GNUNET_JSON_pack_string ("aml_status", sstatus), - GNUNET_JSON_pack_string ("account", - payto_uri)); + GNUNET_JSON_pack_allow_null ( + GNUNET_JSON_pack_string ("account", + rd->payto_uri))); TALER_ARL_report (report_aml_lags, rep); } else { + json_t *rep; + rep = GNUNET_JSON_PACK ( TALER_JSON_pack_amount ("total_amount", - total_amount), + &rd->total_amount), TALER_JSON_pack_time_abs_human ("deadline", - deadline.abs_time), - GNUNET_JSON_pack_string ("account", - payto_uri)); + rd->deadline.abs_time), + GNUNET_JSON_pack_allow_null ( + GNUNET_JSON_pack_string ("account", + rd->payto_uri))); TALER_ARL_report (report_lags, rep); } + + return free_report_entry (cls, + key, + value); +} + + +/** + * Function called on deposits that are past their due date + * and have not yet seen a wire transfer. + * + * @param cls closure, points to a `struct ReportMissingWireContext` + * @param total_amount value of the missing deposits, including fee + * @param payto_uri where should the funds be wired + * @param deadline what was the earliest requested wire transfer deadline + */ +static void +report_wire_missing_cb (void *cls, + uint64_t batch_deposit_serial_id, + const struct TALER_Amount *total_amount, + const struct TALER_PaytoHashP *wire_target_h_payto, + struct GNUNET_TIME_Timestamp deadline) +{ + struct ReportMissingWireContext *rc = cls; + struct ReasonDetail *rd; + + rd = GNUNET_CONTAINER_multishortmap_get (rc->map, + &wire_target_h_payto->hash); + if (NULL == rd) + { + rd = GNUNET_new (struct ReasonDetail); + GNUNET_assert (GNUNET_YES == + GNUNET_CONTAINER_multishortmap_put ( + rc->map, + &wire_target_h_payto->hash, + rd, + GNUNET_CONTAINER_MULTIHASHMAPOPTION_UNIQUE_ONLY)); + rc->err = TALER_ARL_edb->select_justification_for_missing_wire ( + TALER_ARL_edb->cls, + wire_target_h_payto, + &rd->payto_uri, + &rd->kyc_pending, + &rd->status, + &rd->aml_limit); + rd->total_amount = *total_amount; + rd->deadline = deadline; + } + else + { + TALER_ARL_amount_add (&rd->total_amount, + &rd->total_amount, + total_amount); + rd->deadline = GNUNET_TIME_timestamp_min (rd->deadline, + deadline); + } +} + + +/** + * Function called on aggregations that were done for + * a (batch) deposit. + * + * @param cls closure + * @param tracking_serial_id where in the table are we + * @param batch_deposit_serial_id which batch deposit was aggregated + */ +static void +clear_finished_transfer_cb ( + void *cls, + uint64_t tracking_serial_id, + uint64_t batch_deposit_serial_id) +{ + struct AggregationContext *ac = cls; + enum GNUNET_DB_QueryStatus qs; + + if (0 > ac->err) + return; /* already failed */ + GNUNET_assert (ac->max_aggregation_serial < tracking_serial_id); + ac->max_aggregation_serial = tracking_serial_id; + qs = TALER_ARL_adb->delete_pending_deposit ( + TALER_ARL_adb->cls, + &TALER_ARL_master_pub, + batch_deposit_serial_id); + if (0 == qs) + { + /* Aggregated something twice or other error, report! */ + GNUNET_break (0); + // FIXME: report more nicely! + } + if (0 > qs) + ac->err = qs; } @@ -957,31 +1206,79 @@ wire_missing_cb (void *cls, static void check_for_required_transfers (void) { - struct GNUNET_TIME_Timestamp next_timestamp; + struct ImportMissingWireContext wc = { + .max_batch_deposit_uuid = pp.last_batch_deposit_uuid, + .err = GNUNET_DB_STATUS_SUCCESS_ONE_RESULT + }; + struct GNUNET_TIME_Absolute deadline; enum GNUNET_DB_QueryStatus qs; + struct ReportMissingWireContext rc = { + .err = GNUNET_DB_STATUS_SUCCESS_ONE_RESULT + }; + struct AggregationContext ac = { + .max_aggregation_serial = pp.last_aggregation_serial, + .err = GNUNET_DB_STATUS_SUCCESS_ONE_RESULT + }; + qs = TALER_ARL_edb->select_batch_deposits_missing_wire ( + TALER_ARL_edb->cls, + pp.last_batch_deposit_uuid, + &import_wire_missing_cb, + &wc); + if ( (0 > qs) || (0 > wc.err) ) + { + GNUNET_break (0); + GNUNET_break ( (GNUNET_DB_STATUS_SOFT_ERROR == qs) || + (GNUNET_DB_STATUS_SOFT_ERROR == wc.err) ); + global_ret = EXIT_FAILURE; + GNUNET_SCHEDULER_shutdown (); + return; + } + pp.last_batch_deposit_uuid = wc.max_batch_deposit_uuid; + qs = TALER_ARL_edb->select_aggregations_above_serial ( + TALER_ARL_edb->cls, + pp.last_aggregation_serial, + &clear_finished_transfer_cb, + &ac); + if ( (0 > qs) || (0 > ac.err) ) + { + GNUNET_break (0); + GNUNET_break ( (GNUNET_DB_STATUS_SOFT_ERROR == qs) || + (GNUNET_DB_STATUS_SOFT_ERROR == ac.err) ); + global_ret = EXIT_FAILURE; + GNUNET_SCHEDULER_shutdown (); + return; + } + pp.last_aggregation_serial = ac.max_aggregation_serial; /* Subtract #GRACE_PERIOD, so we can be a bit behind in processing without immediately raising undue concern */ - next_timestamp = GNUNET_TIME_absolute_to_timestamp ( - GNUNET_TIME_absolute_subtract (GNUNET_TIME_absolute_get (), - GRACE_PERIOD)); - GNUNET_log (GNUNET_ERROR_TYPE_INFO, - "Analyzing exchange's unfinished deposits (deadline: %s)\n", - GNUNET_TIME_timestamp2s (next_timestamp)); - qs = TALER_ARL_edb->select_batch_deposits_missing_wire (TALER_ARL_edb->cls, - pp.last_timestamp, - next_timestamp, - &wire_missing_cb, - &next_timestamp); - if (0 > qs) + deadline = GNUNET_TIME_absolute_subtract (GNUNET_TIME_absolute_get (), + GRACE_PERIOD); + rc.map = GNUNET_CONTAINER_multishortmap_create (1024, + GNUNET_NO); + qs = TALER_ARL_adb->select_pending_deposits ( + TALER_ARL_adb->cls, + &TALER_ARL_master_pub, + deadline, + &report_wire_missing_cb, + &rc); + if ( (0 > qs) || (0 > rc.err) ) { GNUNET_break (0); - GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs); + GNUNET_break ( (GNUNET_DB_STATUS_SOFT_ERROR == qs) || + (GNUNET_DB_STATUS_SOFT_ERROR == rc.err) ); + GNUNET_CONTAINER_multishortmap_iterate (rc.map, + &free_report_entry, + NULL); + GNUNET_CONTAINER_multishortmap_destroy (rc.map); global_ret = EXIT_FAILURE; GNUNET_SCHEDULER_shutdown (); return; } - pp.last_timestamp = next_timestamp; + GNUNET_CONTAINER_multishortmap_iterate (rc.map, + &generate_report, + NULL); + GNUNET_CONTAINER_multishortmap_destroy (rc.map); /* conclude with success */ commit (GNUNET_DB_STATUS_SUCCESS_ONE_RESULT); GNUNET_SCHEDULER_shutdown (); @@ -2275,10 +2572,11 @@ begin_transaction (void) GNUNET_assert (GNUNET_OK == TALER_amount_set_zero (TALER_ARL_currency, &total_wire_out)); - qs = TALER_ARL_adb->get_predicted_balance (TALER_ARL_adb->cls, - &TALER_ARL_master_pub, - &start_balance, - &total_drained); + qs = TALER_ARL_adb->get_predicted_balance ( + TALER_ARL_adb->cls, + &TALER_ARL_master_pub, + &start_balance, + &total_drained); switch (qs) { case GNUNET_DB_STATUS_HARD_ERROR: @@ -2328,9 +2626,10 @@ begin_transaction (void) { start_pp = pp; GNUNET_log (GNUNET_ERROR_TYPE_INFO, - "Resuming wire audit at %s / %llu\n", - GNUNET_TIME_timestamp2s (pp.last_timestamp), - (unsigned long long) pp.last_reserve_close_uuid); + "Resuming wire audit at %llu / %llu / %llu\n", + (unsigned long long) pp.last_reserve_close_uuid, + (unsigned long long) pp.last_batch_deposit_uuid, + (unsigned long long) pp.last_aggregation_serial); } { diff --git a/src/auditordb/Makefile.am b/src/auditordb/Makefile.am index 98b19f894..abdba5c2b 100644 --- a/src/auditordb/Makefile.am +++ b/src/auditordb/Makefile.am @@ -62,6 +62,9 @@ libtaler_plugin_auditordb_postgres_la_SOURCES = \ pg_get_reserve_info.h pg_get_reserve_info.c \ pg_insert_reserve_summary.h pg_insert_reserve_summary.c \ pg_update_reserve_summary.h pg_update_reserve_summary.c \ + pg_select_pending_deposits.h pg_select_pending_deposits.c \ + pg_delete_pending_deposit.h pg_delete_pending_deposit.c \ + pg_insert_pending_deposit.h pg_insert_pending_deposit.c \ pg_get_reserve_summary.h pg_get_reserve_summary.c \ pg_insert_wire_fee_summary.h pg_insert_wire_fee_summary.c \ pg_update_wire_fee_summary.h pg_update_wire_fee_summary.c \ diff --git a/src/auditordb/auditor-0001.sql b/src/auditordb/auditor-0001.sql index c5c037c31..2fe322b1f 100644 --- a/src/auditordb/auditor-0001.sql +++ b/src/auditordb/auditor-0001.sql @@ -139,8 +139,9 @@ COMMENT ON TABLE wire_auditor_account_progress CREATE TABLE IF NOT EXISTS wire_auditor_progress (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,last_timestamp INT8 NOT NULL ,last_reserve_close_uuid INT8 NOT NULL + ,last_batch_deposit_uuid INT8 NOT NULL + ,last_aggregation_serial INT8 NOT NULL ,PRIMARY KEY (master_pub) ); @@ -309,5 +310,28 @@ COMMENT ON TABLE auditor_predicted_result IS 'Table with the sum of the ledger, auditor_historic_revenue and the auditor_reserve_balance and the drained profits. This is the final amount that the exchange should have in its bank account right now (and the total amount drained as profits to non-escrow accounts).'; +CREATE TABLE IF NOT EXISTS auditor_pending_deposits + (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE + ,total_amount taler_amount NOT NULL + ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=64) + ,batch_deposit_serial_id INT8 NOT NULL + ,deadline INT8 NOT NULL + ,PRIMARY KEY(master_pub, batch_deposit_serial_id) + ); +COMMENT ON TABLE auditor_pending_deposits + IS 'Table with the sum of the (batch) deposits we have seen but not yet checked that they have been aggregated and wired for a particular target bank account'; +COMMENT ON COLUMN auditor_pending_deposits.total_amount + IS 'Amount we expect to be wired in total for the batch. Includes deposit fees, not the actual expected net wire transfer amount.'; +COMMENT ON COLUMN auditor_pending_deposits.wire_target_h_payto + IS 'Hash of the payto URI of the bank account to be credited by the deadline'; +COMMENT ON COLUMN auditor_pending_deposits.batch_deposit_serial_id + IS 'Entry in the batch_deposits table of the exchange this entry is for'; +COMMENT ON COLUMN auditor_pending_deposits.deadline + IS 'Deadline by which funds should be wired (may be in the future)'; +CREATE INDEX IF NOT EXISTS auditor_pending_deposits_by_deadline + ON auditor_pending_deposits + (master_pub + ,deadline ASC); + -- Finally, commit everything COMMIT; diff --git a/src/auditordb/pg_delete_pending_deposit.c b/src/auditordb/pg_delete_pending_deposit.c new file mode 100644 index 000000000..3dfd467c5 --- /dev/null +++ b/src/auditordb/pg_delete_pending_deposit.c @@ -0,0 +1,51 @@ +/* + 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 + */ +/** + * @file auditordb/pg_delete_pending_deposit.c + * @brief Implementation of the delete_pending_deposit function for Postgres + * @author Christian Grothoff + */ +#include "platform.h" +#include "taler_error_codes.h" +#include "taler_dbevents.h" +#include "taler_pq_lib.h" +#include "pg_delete_pending_deposit.h" +#include "pg_helper.h" + + +enum GNUNET_DB_QueryStatus +TAH_PG_delete_pending_deposit ( + void *cls, + const struct TALER_MasterPublicKeyP *master_pub, + uint64_t batch_deposit_serial_id) +{ + struct PostgresClosure *pg = cls; + struct GNUNET_PQ_QueryParam params[] = { + GNUNET_PQ_query_param_auto_from_type (master_pub), + GNUNET_PQ_query_param_uint64 (&batch_deposit_serial_id), + GNUNET_PQ_query_param_end + }; + + PREPARE (pg, + "auditor_delete_pending_deposit", + "DELETE" + " FROM auditor_pending_deposits" + " WHERE master_pub=$1" + " AND batch_deposit_serial_id=$2;"); + return GNUNET_PQ_eval_prepared_non_select (pg->conn, + "auditor_delete_pending_deposit", + params); +} diff --git a/src/auditordb/pg_delete_pending_deposit.h b/src/auditordb/pg_delete_pending_deposit.h new file mode 100644 index 000000000..fb39ef199 --- /dev/null +++ b/src/auditordb/pg_delete_pending_deposit.h @@ -0,0 +1,46 @@ +/* + 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 + */ +/** + * @file auditordb/pg_delete_pending_deposit.h + * @brief implementation of the delete_pending_deposit function for Postgres + * @author Christian Grothoff + */ +#ifndef PG_DELETE_PENDING_DEPOSIT_H +#define PG_DELETE_PENDING_DEPOSIT_H + +#include "taler_util.h" +#include "taler_json_lib.h" +#include "taler_auditordb_plugin.h" + + +/** + * Delete a row from the pending deposit table. + * Usually done when the respective wire transfer + * was finally detected. + * + * @param cls the @e cls of this struct with the plugin-specific state + * @param master_pub master key of the exchange + * @param batch_deposit_serial_id which entry to delete + * @return transaction status code + */ +enum GNUNET_DB_QueryStatus +TAH_PG_delete_pending_deposit ( + void *cls, + const struct TALER_MasterPublicKeyP *master_pub, + uint64_t batch_deposit_serial_id); + + +#endif diff --git a/src/auditordb/pg_get_wire_auditor_progress.c b/src/auditordb/pg_get_wire_auditor_progress.c index c5caf3f01..a0eaefc65 100644 --- a/src/auditordb/pg_get_wire_auditor_progress.c +++ b/src/auditordb/pg_get_wire_auditor_progress.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + Copyright (C) 2022-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 @@ -38,18 +38,21 @@ TAH_PG_get_wire_auditor_progress ( GNUNET_PQ_query_param_end }; struct GNUNET_PQ_ResultSpec rs[] = { - GNUNET_PQ_result_spec_timestamp ("last_timestamp", - &pp->last_timestamp), GNUNET_PQ_result_spec_uint64 ("last_reserve_close_uuid", &pp->last_reserve_close_uuid), + GNUNET_PQ_result_spec_uint64 ("last_batch_deposit_uuid", + &pp->last_batch_deposit_uuid), + GNUNET_PQ_result_spec_uint64 ("last_aggregation_serial", + &pp->last_aggregation_serial), GNUNET_PQ_result_spec_end }; PREPARE (pg, "wire_auditor_progress_select", "SELECT" - " last_timestamp" - ",last_reserve_close_uuid" + " last_reserve_close_uuid" + ",last_batch_deposit_uuid" + ",last_aggregation_serial" " FROM wire_auditor_progress" " WHERE master_pub=$1;"); return GNUNET_PQ_eval_prepared_singleton_select (pg->conn, diff --git a/src/auditordb/pg_insert_pending_deposit.c b/src/auditordb/pg_insert_pending_deposit.c new file mode 100644 index 000000000..11b3e48e1 --- /dev/null +++ b/src/auditordb/pg_insert_pending_deposit.c @@ -0,0 +1,61 @@ +/* + 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 + */ +/** + * @file auditordb/pg_insert_pending_deposit.c + * @brief Implementation of the insert_pending_deposit function for Postgres + * @author Christian Grothoff + */ +#include "platform.h" +#include "taler_error_codes.h" +#include "taler_dbevents.h" +#include "taler_pq_lib.h" +#include "pg_insert_pending_deposit.h" +#include "pg_helper.h" + + +enum GNUNET_DB_QueryStatus +TAH_PG_insert_pending_deposit ( + void *cls, + const struct TALER_MasterPublicKeyP *master_pub, + uint64_t batch_deposit_serial_id, + const struct TALER_PaytoHashP *wire_target_h_payto, + const struct TALER_Amount *total_amount, + struct GNUNET_TIME_Timestamp deadline) +{ + struct PostgresClosure *pg = cls; + struct GNUNET_PQ_QueryParam params[] = { + GNUNET_PQ_query_param_auto_from_type (master_pub), + TALER_PQ_query_param_amount (pg->conn, + total_amount), + GNUNET_PQ_query_param_auto_from_type (wire_target_h_payto), + GNUNET_PQ_query_param_uint64 (&batch_deposit_serial_id), + GNUNET_PQ_query_param_timestamp (&deadline), + GNUNET_PQ_query_param_end + }; + + PREPARE (pg, + "auditor_insert_pending_deposit", + "INSERT INTO auditor_pending_deposits " + "(master_pub" + ",total_amount" + ",wire_target_h_payto" + ",batch_deposit_serial_id" + ",deadline" + ") VALUES ($1,$2,$3,$4,$5);"); + return GNUNET_PQ_eval_prepared_non_select (pg->conn, + "auditor_insert_pending_deposit", + params); +} diff --git a/src/auditordb/pg_insert_pending_deposit.h b/src/auditordb/pg_insert_pending_deposit.h new file mode 100644 index 000000000..2f867e87e --- /dev/null +++ b/src/auditordb/pg_insert_pending_deposit.h @@ -0,0 +1,50 @@ +/* + 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 + */ +/** + * @file auditordb/pg_insert_pending_deposit.h + * @brief implementation of the insert_pending_deposit function for Postgres + * @author Christian Grothoff + */ +#ifndef PG_INSERT_PENDING_DEPOSIT_H +#define PG_INSERT_PENDING_DEPOSIT_H + +#include "taler_util.h" +#include "taler_json_lib.h" +#include "taler_auditordb_plugin.h" + + +/** + * Insert new row into the pending deposits table. + * + * @param cls the @e cls of this struct with the plugin-specific state + * @param master_pub master key of the exchange + * @param batch_deposit_serial_id where in the table are we + * @param total_amount value of all missing deposits, including fees + * @param wire_target_h_payto hash of the recipient account's payto URI + * @param deadline what was the requested wire transfer deadline + * @return transaction status code + */ +enum GNUNET_DB_QueryStatus +TAH_PG_insert_pending_deposit ( + void *cls, + const struct TALER_MasterPublicKeyP *master_pub, + uint64_t batch_deposit_serial_id, + const struct TALER_PaytoHashP *wire_target_h_payto, + const struct TALER_Amount *total_amount, + struct GNUNET_TIME_Timestamp deadline); + + +#endif diff --git a/src/auditordb/pg_insert_wire_auditor_progress.c b/src/auditordb/pg_insert_wire_auditor_progress.c index 7853d3ff6..c9e302773 100644 --- a/src/auditordb/pg_insert_wire_auditor_progress.c +++ b/src/auditordb/pg_insert_wire_auditor_progress.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + Copyright (C) 2022-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 @@ -35,8 +35,9 @@ TAH_PG_insert_wire_auditor_progress ( struct PostgresClosure *pg = cls; struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_auto_from_type (master_pub), - GNUNET_PQ_query_param_timestamp (&pp->last_timestamp), GNUNET_PQ_query_param_uint64 (&pp->last_reserve_close_uuid), + GNUNET_PQ_query_param_uint64 (&pp->last_batch_deposit_uuid), + GNUNET_PQ_query_param_uint64 (&pp->last_aggregation_serial), GNUNET_PQ_query_param_end }; @@ -44,9 +45,10 @@ TAH_PG_insert_wire_auditor_progress ( "wire_auditor_progress_insert", "INSERT INTO wire_auditor_progress " "(master_pub" - ",last_timestamp" ",last_reserve_close_uuid" - ") VALUES ($1,$2,$3);"); + ",last_batch_deposit_uuid" + ",last_aggregation_serial" + ") VALUES ($1,$2,$3,$4);"); return GNUNET_PQ_eval_prepared_non_select (pg->conn, "wire_auditor_progress_insert", params); diff --git a/src/auditordb/pg_select_pending_deposits.c b/src/auditordb/pg_select_pending_deposits.c new file mode 100644 index 000000000..a5d1c6ae8 --- /dev/null +++ b/src/auditordb/pg_select_pending_deposits.c @@ -0,0 +1,152 @@ +/* + 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 + */ +/** + * @file auditordb/pg_select_pending_deposits.c + * @brief Implementation of the select_pending_deposits function for Postgres + * @author Christian Grothoff + */ +#include "platform.h" +#include "taler_error_codes.h" +#include "taler_dbevents.h" +#include "taler_pq_lib.h" +#include "pg_select_pending_deposits.h" +#include "pg_helper.h" + + +/** + * Closure for #wire_missing_cb(). + */ +struct WireMissingContext +{ + + /** + * Function to call for each pending deposit. + */ + TALER_AUDITORDB_WireMissingCallback cb; + + /** + * Closure for @e cb + */ + void *cb_cls; + + /** + * Plugin context. + */ + struct PostgresClosure *pg; + + /** + * Query status to return. + */ + enum GNUNET_DB_QueryStatus qs; +}; + + +/** + * Helper function for #TAH_PG_select_purse_expired(). + * To be called with the results of a SELECT statement + * that has returned @a num_results results. + * + * @param cls closure of type `struct WireMissingContext *` + * @param result the postgres result + * @param num_results the number of results in @a result + */ +static void +wire_missing_cb (void *cls, + PGresult *result, + unsigned int num_results) +{ + struct WireMissingContext *eic = cls; + struct PostgresClosure *pg = eic->pg; + + for (unsigned int i = 0; i < num_results; i++) + { + uint64_t batch_deposit_serial_id; + struct TALER_Amount total_amount; + struct TALER_PaytoHashP wire_target_h_payto; + struct GNUNET_TIME_Timestamp deadline; + struct GNUNET_PQ_ResultSpec rs[] = { + GNUNET_PQ_result_spec_uint64 ("batch_deposit_serial_id", + &batch_deposit_serial_id), + TALER_PQ_RESULT_SPEC_AMOUNT ("total_amount", + &total_amount), + GNUNET_PQ_result_spec_auto_from_type ("wire_target_h_payto", + &wire_target_h_payto), + GNUNET_PQ_result_spec_timestamp ("deadline", + &deadline), + GNUNET_PQ_result_spec_end + }; + + if (GNUNET_OK != + GNUNET_PQ_extract_result (result, + rs, + i)) + { + GNUNET_break (0); + eic->qs = GNUNET_DB_STATUS_HARD_ERROR; + return; + } + eic->cb (eic->cb_cls, + batch_deposit_serial_id, + &total_amount, + &wire_target_h_payto, + deadline); + } + eic->qs = num_results; +} + + +enum GNUNET_DB_QueryStatus +TAH_PG_select_pending_deposits ( + void *cls, + const struct TALER_MasterPublicKeyP *master_pub, + struct GNUNET_TIME_Absolute deadline, + TALER_AUDITORDB_WireMissingCallback cb, + void *cb_cls) +{ + struct PostgresClosure *pg = cls; + struct GNUNET_PQ_QueryParam params[] = { + GNUNET_PQ_query_param_auto_from_type (master_pub), + GNUNET_PQ_query_param_absolute_time (&deadline), + GNUNET_PQ_query_param_end + }; + struct WireMissingContext eic = { + .cb = cb, + .cb_cls = cb_cls, + .pg = pg + }; + enum GNUNET_DB_QueryStatus qs; + + PREPARE (pg, + "auditor_select_pending_deposits", + "SELECT" + " batch_deposit_serial_id" + ",total_amount" + ",wire_target_h_payto" + ",deadline" + " FROM auditor_pending_deposits" + " WHERE master_pub=$1" + " AND deadline<$2;"); + qs = GNUNET_PQ_eval_prepared_multi_select ( + pg->conn, + "auditor_select_pending_deposits", + params, + &wire_missing_cb, + &eic); + if (0 > qs) + return qs; + GNUNET_break (GNUNET_DB_STATUS_HARD_ERROR != eic.qs); + return eic.qs; +} diff --git a/src/auditordb/pg_select_pending_deposits.h b/src/auditordb/pg_select_pending_deposits.h new file mode 100644 index 000000000..bbf3e843a --- /dev/null +++ b/src/auditordb/pg_select_pending_deposits.h @@ -0,0 +1,46 @@ +/* + 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 + */ +/** + * @file auditordb/pg_select_pending_deposits.h + * @brief implementation of the select_pending_deposits function for Postgres + * @author Christian Grothoff + */ +#ifndef PG_SELECT_PENDING_DEPOSITS_H +#define PG_SELECT_PENDING_DEPOSITS_H + +#include "taler_util.h" +#include "taler_json_lib.h" +#include "taler_auditordb_plugin.h" + + +/** + * Return (batch) deposits for which we have not yet + * seen the required wire transfer. + * + * @param deadline only return up to this deadline + * @param cb function to call on each entry + * @param cb_cls closure for @a cb + * @return transaction status code + */ +enum GNUNET_DB_QueryStatus +TAH_PG_select_pending_deposits ( + void *cls, + const struct TALER_MasterPublicKeyP *master_pub, + struct GNUNET_TIME_Absolute deadline, + TALER_AUDITORDB_WireMissingCallback cb, + void *cb_cls); + +#endif diff --git a/src/auditordb/pg_template.c b/src/auditordb/pg_template.c index 3e9cb642e..ab1f968e6 100644 --- a/src/auditordb/pg_template.c +++ b/src/auditordb/pg_template.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + 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 diff --git a/src/auditordb/pg_template.h b/src/auditordb/pg_template.h index acada6059..3910115b1 100644 --- a/src/auditordb/pg_template.h +++ b/src/auditordb/pg_template.h @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + 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 diff --git a/src/auditordb/pg_update_wire_auditor_progress.c b/src/auditordb/pg_update_wire_auditor_progress.c index 5fb0ff31a..a4b991543 100644 --- a/src/auditordb/pg_update_wire_auditor_progress.c +++ b/src/auditordb/pg_update_wire_auditor_progress.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + Copyright (C) 2022-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 @@ -34,8 +34,9 @@ TAH_PG_update_wire_auditor_progress ( { struct PostgresClosure *pg = cls; struct GNUNET_PQ_QueryParam params[] = { - GNUNET_PQ_query_param_timestamp (&pp->last_timestamp), GNUNET_PQ_query_param_uint64 (&pp->last_reserve_close_uuid), + GNUNET_PQ_query_param_uint64 (&pp->last_batch_deposit_uuid), + GNUNET_PQ_query_param_uint64 (&pp->last_aggregation_serial), GNUNET_PQ_query_param_auto_from_type (master_pub), GNUNET_PQ_query_param_end }; @@ -43,9 +44,10 @@ TAH_PG_update_wire_auditor_progress ( PREPARE (pg, "wire_auditor_progress_update", "UPDATE wire_auditor_progress SET " - " last_timestamp=$1" - ",last_reserve_close_uuid=$2" - " WHERE master_pub=$3"); + " last_reserve_close_uuid=$1" + ",last_batch_deposit_uuid=$2" + ",last_aggregation_serial=$3" + " WHERE master_pub=$4"); return GNUNET_PQ_eval_prepared_non_select (pg->conn, "wire_auditor_progress_update", params); diff --git a/src/auditordb/plugin_auditordb_postgres.c b/src/auditordb/plugin_auditordb_postgres.c index 5ed01e5d2..24d1768bf 100644 --- a/src/auditordb/plugin_auditordb_postgres.c +++ b/src/auditordb/plugin_auditordb_postgres.c @@ -36,6 +36,9 @@ #include "pg_insert_auditor_progress_deposit_confirmation.h" #include "pg_update_auditor_progress_deposit_confirmation.h" #include "pg_get_auditor_progress_deposit_confirmation.h" +#include "pg_select_pending_deposits.h" +#include "pg_delete_pending_deposit.h" +#include "pg_insert_pending_deposit.h" #include "pg_insert_auditor_progress_coin.h" #include "pg_update_auditor_progress_coin.h" #include "pg_get_auditor_progress_coin.h" @@ -510,6 +513,13 @@ libtaler_plugin_auditordb_postgres_init (void *cls) plugin->insert_historic_reserve_revenue = &TAH_PG_insert_historic_reserve_revenue; + plugin->select_pending_deposits + = &TAH_PG_select_pending_deposits; + plugin->delete_pending_deposit + = &TAH_PG_delete_pending_deposit; + plugin->insert_pending_deposit + = &TAH_PG_insert_pending_deposit; + plugin->get_predicted_balance = &TAH_PG_get_predicted_balance; plugin->update_predicted_result diff --git a/src/exchange/taler-exchange-httpd_common_kyc.c b/src/exchange/taler-exchange-httpd_common_kyc.c index e32a92a80..dfd9b710d 100644 --- a/src/exchange/taler-exchange-httpd_common_kyc.c +++ b/src/exchange/taler-exchange-httpd_common_kyc.c @@ -120,6 +120,8 @@ kyc_aml_finished (void *cls, unsigned int birthday = 0; struct GNUNET_ShortHashCode kyc_prox; struct GNUNET_AsyncScopeSave old_scope; + unsigned int num_checks; + char **provided_checks; kat->kyc_aml = NULL; GNUNET_async_scope_enter (&kat->scope, @@ -155,12 +157,17 @@ kyc_aml_finished (void *cls, kat->attributes, &ea, &eas); + TALER_KYCLOGIC_lookup_checks (kat->provider_section, + &num_checks, + &provided_checks); qs = TEH_plugin->insert_kyc_attributes ( TEH_plugin->cls, kat->process_row, &kat->account_id, &kyc_prox, kat->provider_section, + num_checks, + provided_checks, birthday, GNUNET_TIME_timestamp_get (), kat->provider_user_id, @@ -169,6 +176,9 @@ kyc_aml_finished (void *cls, eas, ea, 0 != code); + for (unsigned int i = 0; i= in_start_date - AND bdep.wire_deadline < in_end_date - AND NOT EXISTS (SELECT 1 - FROM aggregation_tracking atr - WHERE (atr.batch_deposit_serial_id = bdep.batch_deposit_serial_id)); -DECLARE - my_earliest_deadline INT8; -- earliest deadline that was missed + batch_deposit_serial_id + ,wire_target_h_payto + ,wire_deadline + FROM batch_deposits + WHERE batch_deposit_serial_id > in_min_serial_id; DECLARE my_total_val INT8; -- all deposits without wire DECLARE my_total_frac INT8; -- all deposits without wire (fraction, not normalized) -DECLARE - my_refund_val INT8; -- all refunds without wire -DECLARE - my_refund_frac INT8; -- all refunds without wire (fraction, not normalized) -DECLARE - my_wire_target_h_payto BYTEA; -- hash of the target account -DECLARE - my_payto_uri TEXT; -- the target account -DECLARE - my_kyc_pending TEXT; -- pending KYC operations -DECLARE - my_required_checks TEXT[]; -DECLARE - my_aml_status INT4; -- AML status (0: normal) DECLARE my_total taler_amount; -- amount that was originally deposited DECLARE my_batch_record RECORD; -DECLARE - my_aml_data RECORD; -DECLARE - my_aml_threshold taler_amount; -- threshold above which AML is triggered DECLARE i RECORD; BEGIN @@ -83,133 +46,25 @@ LOOP FETCH NEXT FROM missing INTO i; EXIT WHEN NOT FOUND; - IF ( (my_earliest_deadline IS NULL) OR - (my_earliest_deadline > i.wire_deadline) ) - THEN - my_earliest_deadline = i.wire_deadline; - END IF; SELECT SUM((cdep.amount_with_fee).val) AS total_val ,SUM((cdep.amount_with_fee).frac::INT8) AS total_frac - ,SUM((r.amount_with_fee).val) AS refund_val - ,SUM((r.amount_with_fee).frac::INT8) AS refund_frac INTO my_batch_record FROM coin_deposits cdep - LEFT JOIN refunds r - ON ( (r.coin_pub = cdep.coin_pub) AND - (r.batch_deposit_serial_id = cdep.batch_deposit_serial_id) ) WHERE cdep.batch_deposit_serial_id = i.batch_deposit_serial_id; --- GROUP BY bdep.wire_target_h_payto; -- maybe use temporary table intead of cursor, or accumulate C-side? my_total_val=my_batch_record.total_val; my_total_frac=my_batch_record.total_frac; - my_refund_val=my_batch_record.refund_val; - my_refund_frac=my_batch_record.refund_frac; - RAISE WARNING 'tval: %', my_total_val; - RAISE WARNING 'tfrac: %', my_total_frac; - RAISE WARNING 'rval: %', my_refund_val; - RAISE WARNING 'rfrac: %', my_refund_frac; - - IF my_refund_val IS NOT NULL - THEN - -- subtract refunds from total - my_total_val = my_total_val - my_refund_val; - -- note: frac could go negative here, that's OK - my_total_frac = my_total_frac - my_refund_frac; - END IF; -- Normalize total amount - IF my_total_frac < 0 - THEN - my_total.val = my_total_val - 1 + my_total_frac / 100000000; - my_total.frac = 100000000 + my_total_frac % 100000000; - ELSE - my_total.val = my_total_val + my_total_frac / 100000000; - my_total.frac = my_total_frac % 100000000; - END IF; - RAISE WARNING 'val: %', my_total.val; - RAISE WARNING 'frac: %', my_total.frac; - ASSERT my_total.frac >= 0, 'Normalized amount fraction must be non-negative'; - ASSERT my_total.frac < 100000000, 'Normalized amount fraction must be below 100000000'; - - IF (my_total.val < 0) - THEN - -- Refunds above deposits. That's a problem, but not one for this auditor pass. - CONTINUE; - END IF; - - -- Note: total amount here is NOT the exact amount due for the - -- wire transfer, as we did not consider deposit, refund and wire fees. - -- The amount given in the report is thus ONLY indicative of the non-refunded - -- gross amount, not the net transfer amount. - - IF 0 = my_total_val + my_total_frac - THEN - -- full refund, skip report entirely - CONTINUE; - END IF; - - -- Fetch payto URI - -- NOTE: we want to group by my_wire_target_h_payto and not do this repeatedly per batch deposit! - my_payto_uri = NULL; - SELECT payto_uri - INTO my_payto_uri - FROM wire_targets - WHERE wire_target_h_payto=my_wire_target_h_payto; - - -- Get last AML decision - SELECT - new_threshold - ,kyc_requirements - ,new_status - INTO - my_aml_data - FROM aml_history - WHERE h_payto=my_wire_target_h_payto - ORDER BY aml_history_serial_id -- get last decision - DESC LIMIT 1; - IF FOUND - THEN - my_aml_threshold=my_aml_data.new_threshold; - my_kyc_pending=my_aml_data.kyc_requirements; - my_aml_status=my_aml_data.kyc_status; - ELSE - my_aml_threshold=NULL; - my_kyc_pending=NULL; - my_aml_status=0; - END IF; - IF 0 != my_aml_status - THEN - RETURN NEXT ( - my_total - ,my_payto_uri - ,my_kyc_pending - ,my_earliest_deadline - ,my_aml_status - ,NULL); - END IF; - - -- Check KYC status - SELECT string_to_array (required_checks, ' ') - INTO my_required_checks - FROM legitimization_requirements - WHERE h_payto=my_wire_target_h_payto; - - --- PERFORM -- provider --- FROM kyc_attributes --- WHERE legitimization_serial=my_legitimization_serial; - -- FIXME: can't tell if providers cover all required checks from DB!!! - -- Idea: expand kyc_attributes table with list of satisfied checks!??! - - RETURN NEXT ( - my_total - ,my_payto_uri - ,my_kyc_pending - ,my_earliest_deadline - ,my_aml_status - ,NULL::taler_amount); + my_total.val = my_total_val + my_total_frac / 100000000; + my_total.frac = my_total_frac % 100000000; + RETURN NEXT ( + i.batch_deposit_serial_id + ,my_total + ,i.wire_target_h_payto + ,i.wire_deadline); END LOOP; CLOSE missing; diff --git a/src/exchangedb/exchange_do_select_justification_for_missing_wire.sql b/src/exchangedb/exchange_do_select_justification_for_missing_wire.sql new file mode 100644 index 000000000..5c53e6286 --- /dev/null +++ b/src/exchangedb/exchange_do_select_justification_for_missing_wire.sql @@ -0,0 +1,104 @@ +-- +-- 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: Christian Grothoff + +-- FIXME: this function is not working as intended at all yet, work in progress! + +CREATE OR REPLACE FUNCTION exchange_do_select_justification_missing_wire( + IN in_wire_target_h_payto BYTEA, + IN in_current_time INT8, + OUT out_payto_uri TEXT, -- NULL allowed + OUT out_kyc_pending TEXT, -- NULL allowed + OUT out_aml_status INT4, -- NULL allowed + OUT out_aml_limit taler_amount) -- NULL allowed! +LANGUAGE plpgsql +AS $$ +DECLARE + my_required_checks TEXT[]; +DECLARE + my_aml_data RECORD; +DECLARE + satisfied CURSOR FOR + SELECT satisfied_checks + FROM kyc_attributes + WHERE h_payto=in_wire_target_h_payto + AND expiration_time < in_current_time; +DECLARE + i RECORD; +BEGIN + + -- Fetch payto URI + out_payto_uri = NULL; + SELECT payto_uri + INTO out_payto_uri + FROM wire_targets + WHERE wire_target_h_payto=my_wire_target_h_payto; + + -- Check KYC status + my_required_checks = NULL; + SELECT string_to_array (required_checks, ' ') + INTO my_required_checks + FROM legitimization_requirements + WHERE h_payto=my_wire_target_h_payto; + + -- Get last AML decision + SELECT + new_threshold + ,kyc_requirements + ,new_status + INTO + my_aml_data + FROM aml_history + WHERE h_payto=in_wire_target_h_payto + ORDER BY aml_history_serial_id -- get last decision + DESC LIMIT 1; + IF FOUND + THEN + out_aml_limit=my_aml_data.new_threshold; + out_aml_status=my_aml_data.kyc_status; + -- Combine KYC requirements + my_required_checks + = array_cat (my_required_checks, + my_aml_data.kyc_requirements); + ELSE + out_aml_limit=NULL; + out_aml_status=0; -- or NULL? Style question! + END IF; + + OPEN satisfied; + LOOP + FETCH NEXT FROM satisfied INTO i; + EXIT WHEN NOT FOUND; + + -- remove all satisfied checks from the list + FOR i in 1..array_length(i.satisfied_checks) + LOOP + my_required_checks + = array_remove (my_required_checks, + i.satisfied_checks[i]); + END LOOP; + END LOOP; + + -- Return remaining required checks as one string + IF ( (my_required_checks IS NOT NULL) AND + (0 < array_length(my_satisfied_checks)) ) + THEN + out_kyc_pending + = array_to_string (my_required_checks, ' '); + END IF; + + RETURN; +END $$; diff --git a/src/exchangedb/pg_insert_kyc_attributes.c b/src/exchangedb/pg_insert_kyc_attributes.c index e3c246e5c..bdddd020c 100644 --- a/src/exchangedb/pg_insert_kyc_attributes.c +++ b/src/exchangedb/pg_insert_kyc_attributes.c @@ -25,9 +25,6 @@ #include "pg_insert_kyc_attributes.h" #include "pg_helper.h" -void -event_do_poll (struct GNUNET_PQ_Context *db); - enum GNUNET_DB_QueryStatus TEH_PG_insert_kyc_attributes ( @@ -36,6 +33,8 @@ TEH_PG_insert_kyc_attributes ( const struct TALER_PaytoHashP *h_payto, const struct GNUNET_ShortHashCode *kyc_prox, const char *provider_section, + unsigned int num_checks, + const char *satisfied_checks[static num_checks], uint32_t birthday, struct GNUNET_TIME_Timestamp collection_time, const char *provider_account_id, @@ -60,6 +59,9 @@ TEH_PG_insert_kyc_attributes ( GNUNET_PQ_query_param_auto_from_type (h_payto), GNUNET_PQ_query_param_auto_from_type (kyc_prox), GNUNET_PQ_query_param_string (provider_section), + GNUNET_PQ_query_param_array_ptrs_string (num_checks, + satisfied_checks, + pg->conn), GNUNET_PQ_query_param_uint32 (&birthday), (NULL == provider_account_id) ? GNUNET_PQ_query_param_null () @@ -92,7 +94,7 @@ TEH_PG_insert_kyc_attributes ( "SELECT " " out_ok" " FROM exchange_do_insert_kyc_attributes " - "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13);"); + "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14);"); qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, "insert_kyc_attributes", params, diff --git a/src/exchangedb/pg_insert_kyc_attributes.h b/src/exchangedb/pg_insert_kyc_attributes.h index c1aad0eb5..35b25bdc8 100644 --- a/src/exchangedb/pg_insert_kyc_attributes.h +++ b/src/exchangedb/pg_insert_kyc_attributes.h @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + Copyright (C) 2022, 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 @@ -35,6 +35,8 @@ * @param h_payto account for which the attribute data is stored * @param kyc_prox key for similarity search * @param provider_section provider that must be checked + * @param num_checks how many checks do these attributes satisfy + * @param satisfied_checks array of checks satisfied by these attributes * @param provider_account_id provider account ID * @param provider_legitimization_id provider legitimization ID * @param birthday birthdate of user, in days after 1990, or 0 if unknown or definitively adult @@ -52,6 +54,8 @@ TEH_PG_insert_kyc_attributes ( const struct TALER_PaytoHashP *h_payto, const struct GNUNET_ShortHashCode *kyc_prox, const char *provider_section, + unsigned int num_checks, + const char *satisfied_checks[static num_checks], uint32_t birthday, struct GNUNET_TIME_Timestamp collection_time, const char *provider_account_id, diff --git a/src/exchangedb/pg_select_aggregations_above_serial.c b/src/exchangedb/pg_select_aggregations_above_serial.c new file mode 100644 index 000000000..52d202702 --- /dev/null +++ b/src/exchangedb/pg_select_aggregations_above_serial.c @@ -0,0 +1,137 @@ +/* + 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 + */ +/** + * @file exchangedb/pg_select_aggregations_above_serial.c + * @brief Implementation of the select_aggregations_above_serial function for Postgres + * @author Christian Grothoff + */ +#include "platform.h" +#include "taler_error_codes.h" +#include "taler_dbevents.h" +#include "taler_pq_lib.h" +#include "pg_select_aggregations_above_serial.h" +#include "pg_helper.h" + +/** + * Closure for #aggregation_serial_helper_cb(). + */ +struct AggregationSerialContext +{ + + /** + * Callback to call. + */ + TALER_EXCHANGEDB_AggregationCallback cb; + + /** + * Closure for @e cb. + */ + void *cb_cls; + + /** + * Plugin context. + */ + struct PostgresClosure *pg; + + /** + * Status code, set to #GNUNET_SYSERR on hard errors. + */ + enum GNUNET_GenericReturnValue status; +}; + + +/** + * Helper function to be called with the results of a SELECT statement + * that has returned @a num_results results. + * + * @param cls closure of type `struct AggregationSerialContext` + * @param result the postgres result + * @param num_results the number of results in @a result + */ +static void +aggregation_serial_helper_cb (void *cls, + PGresult *result, + unsigned int num_results) +{ + struct AggregationSerialContext *dsc = cls; + + for (unsigned int i = 0; istatus = GNUNET_SYSERR; + return; + } + dsc->cb (dsc->cb_cls, + tracking_rowid, + batch_deposit_serial_id); + GNUNET_PQ_cleanup_result (rs); + } +} + + +enum GNUNET_DB_QueryStatus +TEH_PG_select_aggregations_above_serial ( + void *cls, + uint64_t min_tracking_serial_id, + TALER_EXCHANGEDB_AggregationCallback cb, + void *cb_cls) +{ + struct PostgresClosure *pg = cls; + struct GNUNET_PQ_QueryParam params[] = { + GNUNET_PQ_query_param_uint64 (&min_tracking_serial_id), + GNUNET_PQ_query_param_end + }; + struct AggregationSerialContext asc = { + .cb = cb, + .cb_cls = cb_cls, + .pg = pg, + .status = GNUNET_OK + }; + enum GNUNET_DB_QueryStatus qs; + + /* Fetch aggregations with rowid '\geq' the given parameter */ + PREPARE (pg, + "select_aggregations_above_serial", + "SELECT" + " aggregation_serial_id" + ",batch_deposit_serial_id" + " FROM aggregation_tracking" + " WHERE aggregation_serial_id>=$1" + " ORDER BY aggregation_serial_id ASC;"); + qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn, + "select_aggregations_above_serial", + params, + &aggregation_serial_helper_cb, + &asc); + if (GNUNET_OK != asc.status) + return GNUNET_DB_STATUS_HARD_ERROR; + return qs; +} diff --git a/src/exchangedb/pg_select_aggregations_above_serial.h b/src/exchangedb/pg_select_aggregations_above_serial.h new file mode 100644 index 000000000..3950d11fb --- /dev/null +++ b/src/exchangedb/pg_select_aggregations_above_serial.h @@ -0,0 +1,47 @@ +/* + 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 + */ +/** + * @file exchangedb/pg_select_aggregations_above_serial.h + * @brief implementation of the select_aggregations_above_serial function for Postgres + * @author Christian Grothoff + */ +#ifndef PG_SELECT_AGGREGATIONS_ABOVE_SERIAL_H +#define PG_SELECT_AGGREGATIONS_ABOVE_SERIAL_H + +#include "taler_util.h" +#include "taler_json_lib.h" +#include "taler_exchangedb_plugin.h" + + +/** + * Select all aggregation tracking IDs in the database + * above a given @a min_tracking_serial_id. + * + * @param cls closure + * @param min_tracking_serial_id only return entries stricly above this row (and in order) + * @param cb function to call on all such aggregations + * @param cb_cls closure for @a cb + * @return transaction status code + */ +enum GNUNET_DB_QueryStatus +TEH_PG_select_aggregations_above_serial ( + void *cls, + uint64_t min_tracking_serial_id, + TALER_EXCHANGEDB_AggregationCallback cb, + void *cb_cls); + + +#endif diff --git a/src/exchangedb/pg_select_batch_deposits_missing_wire.c b/src/exchangedb/pg_select_batch_deposits_missing_wire.c index 25b532ad6..1ab0a4b3b 100644 --- a/src/exchangedb/pg_select_batch_deposits_missing_wire.c +++ b/src/exchangedb/pg_select_batch_deposits_missing_wire.c @@ -69,34 +69,19 @@ missing_wire_cb (void *cls, while (0 < num_results) { - struct TALER_Amount total_amount; - struct TALER_Amount aml_limit; - char *payto_uri = NULL; - char *kyc_pending = NULL; - uint32_t aml_status32 = TALER_AML_NORMAL; + uint64_t batch_deposit_serial_id; struct GNUNET_TIME_Timestamp deadline; - bool no_aml_limit; + struct TALER_PaytoHashP wire_target_h_payto; + struct TALER_Amount total_amount; struct GNUNET_PQ_ResultSpec rs[] = { - TALER_PQ_RESULT_SPEC_AMOUNT ("total_amount_with_fee", - &total_amount), - GNUNET_PQ_result_spec_allow_null ( - GNUNET_PQ_result_spec_string ("payto_uri", - &payto_uri), - NULL), - GNUNET_PQ_result_spec_allow_null ( - GNUNET_PQ_result_spec_string ("kyc_pending", - &kyc_pending), - NULL), - GNUNET_PQ_result_spec_timestamp ("wire_deadline", + GNUNET_PQ_result_spec_uint64 ("batch_deposit_serial_id", + &batch_deposit_serial_id), + GNUNET_PQ_result_spec_auto_from_type ("wire_target_h_payto", + &wire_target_h_payto), + GNUNET_PQ_result_spec_timestamp ("deadline", &deadline), - GNUNET_PQ_result_spec_allow_null ( - GNUNET_PQ_result_spec_uint32 ("aml_status", - &aml_status32), - NULL), - GNUNET_PQ_result_spec_allow_null ( - TALER_PQ_RESULT_SPEC_AMOUNT ("aml_limit", - &aml_limit), - &no_aml_limit), + TALER_PQ_RESULT_SPEC_AMOUNT ("total_amount", + &total_amount), GNUNET_PQ_result_spec_end }; @@ -110,12 +95,10 @@ missing_wire_cb (void *cls, return; } mwc->cb (mwc->cb_cls, + batch_deposit_serial_id, &total_amount, - payto_uri, - deadline, - kyc_pending, - (enum TALER_AmlDecisionState) aml_status32, - no_aml_limit ? NULL : &aml_limit); + &wire_target_h_payto, + deadline); GNUNET_PQ_cleanup_result (rs); } } @@ -124,15 +107,13 @@ missing_wire_cb (void *cls, enum GNUNET_DB_QueryStatus TEH_PG_select_batch_deposits_missing_wire ( void *cls, - struct GNUNET_TIME_Timestamp start_date, - struct GNUNET_TIME_Timestamp end_date, + uint64_t min_batch_deposit_serial_id, TALER_EXCHANGEDB_WireMissingCallback cb, void *cb_cls) { struct PostgresClosure *pg = cls; struct GNUNET_PQ_QueryParam params[] = { - GNUNET_PQ_query_param_timestamp (&start_date), - GNUNET_PQ_query_param_timestamp (&end_date), + GNUNET_PQ_query_param_uint64 (&min_batch_deposit_serial_id), GNUNET_PQ_query_param_end }; struct MissingWireContext mwc = { @@ -144,18 +125,16 @@ TEH_PG_select_batch_deposits_missing_wire ( enum GNUNET_DB_QueryStatus qs; PREPARE (pg, - "deposits_get_overdue", + "deposits_get_deposits_missing_wire", "SELECT" - " total_amount_with_fee" - ",payto_uri" - ",kyc_pending" - ",wire_deadline" - ",aml_status" - ",aml_limit" + " batch_deposit_serial_id" + ",wire_target_h_payto" + ",deadline" + ",total_amount" " FROM exchange_do_select_deposits_missing_wire" - " ($1,$2);"); + " ($1);"); qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn, - "deposits_get_overdue", + "deposits_get_deposits_missing_wire", params, &missing_wire_cb, &mwc); diff --git a/src/exchangedb/pg_select_batch_deposits_missing_wire.h b/src/exchangedb/pg_select_batch_deposits_missing_wire.h index 27e6b0850..16f1d0cb3 100644 --- a/src/exchangedb/pg_select_batch_deposits_missing_wire.h +++ b/src/exchangedb/pg_select_batch_deposits_missing_wire.h @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + Copyright (C) 2022-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 @@ -25,13 +25,11 @@ #include "taler_json_lib.h" #include "taler_exchangedb_plugin.h" /** - * Select all of those deposits in the database for which we do - * not have a wire transfer (or a refund) and which should have - * been deposited between @a start_date and @a end_date. + * Select all of those batch deposits in the database + * above the given serial ID. * * @param cls closure - * @param start_date lower bound on the requested wire execution date - * @param end_date upper bound on the requested wire execution date + * @param min_batch_deposit_serial_id select all batch deposits above this ID * @param cb function to call on all such deposits * @param cb_cls closure for @a cb * @return transaction status code @@ -39,10 +37,8 @@ enum GNUNET_DB_QueryStatus TEH_PG_select_batch_deposits_missing_wire ( void *cls, - struct GNUNET_TIME_Timestamp start_date, - struct GNUNET_TIME_Timestamp end_date, - TALER_EXCHANGEDB_WireMissingCallback - cb, + uint64_t min_batch_deposit_serial_id, + TALER_EXCHANGEDB_WireMissingCallback cb, void *cb_cls); #endif diff --git a/src/exchangedb/pg_select_justification_for_missing_wire.c b/src/exchangedb/pg_select_justification_for_missing_wire.c new file mode 100644 index 000000000..77d5b4de7 --- /dev/null +++ b/src/exchangedb/pg_select_justification_for_missing_wire.c @@ -0,0 +1,89 @@ +/* + This file is part of TALER + Copyright (C) 2022-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 + */ +/** + * @file exchangedb/pg_select_batch_deposits_missing_wire.c + * @brief Implementation of the select_batch_deposits_missing_wire function for Postgres + * @author Christian Grothoff + */ +#include "platform.h" +#include "taler_error_codes.h" +#include "taler_dbevents.h" +#include "taler_pq_lib.h" +#include "pg_select_batch_deposits_missing_wire.h" +#include "pg_helper.h" + + +enum GNUNET_DB_QueryStatus +TEH_PG_select_justification_for_missing_wire ( + void *cls, + const struct TALER_PaytoHashP *wire_target_h_payto, + char **payto_uri, + char **kyc_pending, + enum TALER_AmlDecisionState *status, + struct TALER_Amount *aml_limit) +{ + struct PostgresClosure *pg = cls; + struct GNUNET_TIME_Absolute now + = GNUNET_TIME_absolute_get (); + struct GNUNET_PQ_QueryParam params[] = { + GNUNET_PQ_query_param_auto_from_type (wire_target_h_payto), + GNUNET_PQ_query_param_absolute_time (&now), + GNUNET_PQ_query_param_end + }; + uint32_t aml_status32; + struct GNUNET_PQ_ResultSpec rs[] = { + GNUNET_PQ_result_spec_allow_null ( + GNUNET_PQ_result_spec_string ("payto_uri", + payto_uri), + NULL), + GNUNET_PQ_result_spec_allow_null ( + GNUNET_PQ_result_spec_string ("kyc_pending", + kyc_pending), + NULL), + GNUNET_PQ_result_spec_allow_null ( + GNUNET_PQ_result_spec_uint32 ("aml_status", + &aml_status32), + NULL), + GNUNET_PQ_result_spec_allow_null ( + TALER_PQ_RESULT_SPEC_AMOUNT ("aml_limit", + aml_limit), + NULL), + GNUNET_PQ_result_spec_end + }; + enum GNUNET_DB_QueryStatus qs; + + PREPARE (pg, + "deposits_get_overdue", + "SELECT" + " out_payto_uri AS payto_uri" + ",out_kyc_pending AS kyc_pending" + ",out_deadline AS deadline" + ",out_aml_status AS aml_status" + ",out_aml_limit AS aml_limit" + " FROM exchange_do_select_justification_missing_wire" + " ($1, $2);"); + memset (aml_limit, + 0, + sizeof (*aml_limit)); + qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, + "", + params, + rs); + if (qs <= 0) + return qs; + *status = (enum TALER_AmlDecisionState) aml_status32; + return qs; +} diff --git a/src/exchangedb/pg_select_justification_for_missing_wire.h b/src/exchangedb/pg_select_justification_for_missing_wire.h new file mode 100644 index 000000000..7f73eb511 --- /dev/null +++ b/src/exchangedb/pg_select_justification_for_missing_wire.h @@ -0,0 +1,49 @@ +/* + This file is part of TALER + Copyright (C) 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 + */ +/** + * @file exchangedb/pg_select_justification_for_missing_wire.h + * @brief implementation of the select_justification_for_missing_wire function for Postgres + * @author Christian Grothoff + */ +#ifndef PG_SELECT_JUSTIFICATION_FOR_MISSING_WIRE_H +#define PG_SELECT_JUSTIFICATION_FOR_MISSING_WIRE_H + +#include "taler_util.h" +#include "taler_json_lib.h" +#include "taler_exchangedb_plugin.h" + +/** + * Select all of those justifications for why we might not have + * done a wire transfer from in the database for a particular target account. + * + * @param cls closure + * @param wire_target_h_payto effected target account + * @param[out] payto_uri target account URI, set to NULL if unknown + * @param[out] kyc_pending set to string describing missing KYC data + * @param[out] status set to AML status + * @param[out] aml_limit set to AML limit, or invalid amount for none + * @return transaction status code + */ +enum GNUNET_DB_QueryStatus +TEH_PG_select_justification_for_missing_wire ( + void *cls, + const struct TALER_PaytoHashP *wire_target_h_payto, + char **payto_uri, + char **kyc_pending, + enum TALER_AmlDecisionState *status, + struct TALER_Amount *aml_limit); + +#endif diff --git a/src/exchangedb/pg_template.c b/src/exchangedb/pg_template.c index 095d89615..be54970f0 100644 --- a/src/exchangedb/pg_template.c +++ b/src/exchangedb/pg_template.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + 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 diff --git a/src/exchangedb/pg_template.h b/src/exchangedb/pg_template.h index 88bb930d3..7387b4883 100644 --- a/src/exchangedb/pg_template.h +++ b/src/exchangedb/pg_template.h @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + 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 diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index 067e859b8..c6e55d017 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2014--2022 Taler Systems SA + Copyright (C) 2014--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 @@ -177,6 +177,8 @@ #include "pg_insert_denomination_revocation.h" #include "pg_get_denomination_revocation.h" #include "pg_select_batch_deposits_missing_wire.h" +#include "pg_select_justification_for_missing_wire.h" +#include "pg_select_aggregations_above_serial.h" #include "pg_lookup_auditor_timestamp.h" #include "pg_lookup_auditor_status.h" #include "pg_insert_auditor.h" @@ -699,6 +701,10 @@ libtaler_plugin_exchangedb_postgres_init (void *cls) = &TEH_PG_get_denomination_revocation; plugin->select_batch_deposits_missing_wire = &TEH_PG_select_batch_deposits_missing_wire; + plugin->select_justification_for_missing_wire + = &TEH_PG_select_justification_for_missing_wire; + plugin->select_aggregations_above_serial + = &TEH_PG_select_aggregations_above_serial; plugin->lookup_auditor_timestamp = &TEH_PG_lookup_auditor_timestamp; plugin->lookup_auditor_status diff --git a/src/exchangedb/procedures.sql.in b/src/exchangedb/procedures.sql.in index 4a23ec742..b963900c9 100644 --- a/src/exchangedb/procedures.sql.in +++ b/src/exchangedb/procedures.sql.in @@ -27,6 +27,7 @@ SET search_path TO exchange; #include "exchange_do_deposit.sql" #include "exchange_do_melt.sql" #include "exchange_do_select_deposits_missing_wire.sql" +#include "exchange_do_select_justification_for_missing_wire.sql" #include "exchange_do_refund.sql" #include "exchange_do_recoup_to_reserve.sql" #include "exchange_do_recoup_to_coin.sql" diff --git a/src/exchangedb/test_exchangedb.c b/src/exchangedb/test_exchangedb.c index 081126637..90c539a98 100644 --- a/src/exchangedb/test_exchangedb.c +++ b/src/exchangedb/test_exchangedb.c @@ -1113,40 +1113,32 @@ drop: /** - * Function called on deposits that are past their due date - * and have not yet seen a wire transfer. + * Function called on batch deposits that may require a + * wire transfer. * * @param cls closure a `struct TALER_EXCHANGEDB_Deposit *` + * @param batch_deposit_serial_id where in the table are we * @param total_amount value of all missing deposits, including fees - * @param payto_uri where should the funds be wired; URI in payto://-format + * @param wire_target_h_payto hash of the recipient account's payto URI * @param deadline what was the earliest requested wire transfer deadline - * @param kyc_pending NULL if no KYC requirement is pending, otherwise text describing the missing KYC requirement - * @param aml_status status of AML possibly blocking the transfer - * @param aml_limit current monthly AML limit */ static void wire_missing_cb ( void *cls, + uint64_t batch_deposit_serial_id, const struct TALER_Amount *total_amount, - const char *payto_uri, - struct GNUNET_TIME_Timestamp deadline, - const char *kyc_pending, - enum TALER_AmlDecisionState status, - const struct TALER_Amount *aml_limit) + const struct TALER_PaytoHashP *wire_target_h_payto, + struct GNUNET_TIME_Timestamp deadline) { const struct TALER_EXCHANGEDB_CoinDepositInformation *deposit = cls; - (void) payto_uri; + (void) batch_deposit_serial_id; (void) deadline; - (void) kyc_pending; - (void) status; - (void) aml_limit; - if (0 != TALER_amount_cmp (total_amount, - &deposit->amount_with_fee)) - { - GNUNET_break (0); - result = 66; - } + (void) wire_target_h_payto; + if (0 == + TALER_amount_cmp (total_amount, + &deposit->amount_with_fee)) + result = 8; } @@ -2162,19 +2154,10 @@ run (void *cls) r)); } { - struct GNUNET_TIME_Timestamp start_range; - struct GNUNET_TIME_Timestamp end_range; - - start_range = GNUNET_TIME_absolute_to_timestamp ( - GNUNET_TIME_absolute_subtract (deadline.abs_time, - GNUNET_TIME_UNIT_SECONDS)); - end_range = GNUNET_TIME_absolute_to_timestamp ( - GNUNET_TIME_absolute_add (deadline.abs_time, - GNUNET_TIME_UNIT_SECONDS)); - FAILIF (GNUNET_DB_STATUS_SUCCESS_ONE_RESULT != + result = 66; + FAILIF (0 >= plugin->select_batch_deposits_missing_wire (plugin->cls, - start_range, - end_range, + 0, &wire_missing_cb, &deposit)); FAILIF (8 != result); diff --git a/src/include/taler_auditordb_plugin.h b/src/include/taler_auditordb_plugin.h index 99b5e7f3f..eebb31e06 100644 --- a/src/include/taler_auditordb_plugin.h +++ b/src/include/taler_auditordb_plugin.h @@ -97,17 +97,23 @@ typedef enum GNUNET_GenericReturnValue struct TALER_AUDITORDB_WireProgressPoint { - /** - * Time until which we have confirmed that all wire transactions - * that the exchange should do, have indeed been done. - */ - struct GNUNET_TIME_Timestamp last_timestamp; - /** * reserves_close uuid until which we have checked * reserve closures. */ uint64_t last_reserve_close_uuid; + + /** + * batch_deposits serial until which we have imported + * batch deposits into the auditor_pending_deposits table. + */ + uint64_t last_batch_deposit_uuid; + + /** + * Maximum number in the aggregations table that + * we have processed so far. + */ + uint64_t last_aggregation_serial; }; @@ -605,6 +611,25 @@ typedef enum GNUNET_GenericReturnValue const struct TALER_AUDITORDB_DepositConfirmation *dc); +/** + * Function called on deposits that are past their due date + * and have not yet seen a wire transfer. + * + * @param cls closure + * @param batch_deposit_serial_id where in the table are we + * @param total_amount value of all missing deposits, including fees + * @param wire_target_h_payto hash of the recipient account's payto URI + * @param earliest_deadline what was the earliest requested wire transfer deadline + */ +typedef void +(*TALER_AUDITORDB_WireMissingCallback)( + void *cls, + uint64_t batch_deposit_serial_id, + const struct TALER_Amount *total_amount, + const struct TALER_PaytoHashP *wire_target_h_payto, + struct GNUNET_TIME_Timestamp deadline); + + /** * Function called on expired purses. * @@ -1704,6 +1729,61 @@ struct TALER_AUDITORDB_Plugin struct TALER_Amount *balance, struct TALER_Amount *drained_profits); + /** + * Insert new row into the pending deposits table. + * + * @param cls the @e cls of this struct with the plugin-specific state + * @param master_pub master key of the exchange + * @param batch_deposit_serial_id where in the table are we + * @param total_amount value of all missing deposits, including fees + * @param wire_target_h_payto hash of the recipient account's payto URI + * @param deadline what was the requested wire transfer deadline + * @return transaction status code + */ + enum GNUNET_DB_QueryStatus + (*insert_pending_deposit)( + void *cls, + const struct TALER_MasterPublicKeyP *master_pub, + uint64_t batch_deposit_serial_id, + const struct TALER_PaytoHashP *wire_target_h_payto, + const struct TALER_Amount *total_amount, + struct GNUNET_TIME_Timestamp deadline); + + + /** + * Delete a row from the pending deposit table. + * Usually done when the respective wire transfer + * was finally detected. + * + * @param cls the @e cls of this struct with the plugin-specific state + * @param master_pub master key of the exchange + * @param batch_deposit_serial_id which entry to delete + * @return transaction status code + */ + enum GNUNET_DB_QueryStatus + (*delete_pending_deposit)( + void *cls, + const struct TALER_MasterPublicKeyP *master_pub, + uint64_t batch_deposit_serial_id); + + + /** + * Return (batch) deposits for which we have not yet + * seen the required wire transfer. + * + * @param deadline only return up to this deadline + * @param cb function to call on each entry + * @param cb_cls closure for @a cb + * @return transaction status code + */ + enum GNUNET_DB_QueryStatus + (*select_pending_deposits)( + void *cls, + const struct TALER_MasterPublicKeyP *master_pub, + struct GNUNET_TIME_Absolute deadline, + TALER_AUDITORDB_WireMissingCallback cb, + void *cb_cls); + }; diff --git a/src/include/taler_exchangedb_plugin.h b/src/include/taler_exchangedb_plugin.h index 9f1060601..ee382ebe8 100644 --- a/src/include/taler_exchangedb_plugin.h +++ b/src/include/taler_exchangedb_plugin.h @@ -3417,26 +3417,37 @@ typedef void /** - * Function called on deposits that are past their due date - * and have not yet seen a wire transfer. + * Function called on (batch) deposits will need a wire + * transfer. * * @param cls closure + * @param batch_deposit_serial_id where in the table are we * @param total_amount value of all missing deposits, including fees - * @param payto_uri where should the funds be wired; URI in payto://-format - * @param deadline what was the earliest requested wire transfer deadline - * @param kyc_pending NULL if no KYC requirement is pending, otherwise text describing the missing KYC requirement - * @param aml_status status of AML possibly blocking the transfer - * @param aml_limit current monthly AML limit + * @param wire_target_h_payto hash of the recipient account's payto URI + * @param earliest_deadline what was the earliest requested wire transfer deadline */ typedef void (*TALER_EXCHANGEDB_WireMissingCallback)( void *cls, + uint64_t batch_deposit_serial_id, const struct TALER_Amount *total_amount, - const char *payto_uri, - struct GNUNET_TIME_Timestamp deadline, - const char *kyc_pending, - enum TALER_AmlDecisionState status, - const struct TALER_Amount *aml_limit); + const struct TALER_PaytoHashP *wire_target_h_payto, + struct GNUNET_TIME_Timestamp deadline); + + +/** + * Function called on aggregations that were done for + * a (batch) deposit. + * + * @param cls closure + * @param tracking_serial_id where in the table are we + * @param batch_deposit_serial_id which batch deposit was aggregated + */ +typedef void +(*TALER_EXCHANGEDB_AggregationCallback)( + void *cls, + uint64_t tracking_serial_id, + uint64_t batch_deposit_serial_id); /** @@ -5576,13 +5587,11 @@ struct TALER_EXCHANGEDB_Plugin /** - * Select all of those deposits in the database for which we do - * not have a wire transfer (or a refund) and which should have - * been deposited between @a start_date and @a end_date. + * Select all (batch) deposits in the database + * above a given @a min_batch_deposit_serial_id. * * @param cls closure - * @param start_date lower bound on the requested wire execution date - * @param end_date upper bound on the requested wire execution date + * @param min_batch_deposit_serial_id only return entries stricly above this row (and in order) * @param cb function to call on all such deposits * @param cb_cls closure for @a cb * @return transaction status code @@ -5590,12 +5599,53 @@ struct TALER_EXCHANGEDB_Plugin enum GNUNET_DB_QueryStatus (*select_batch_deposits_missing_wire)( void *cls, - struct GNUNET_TIME_Timestamp start_date, - struct GNUNET_TIME_Timestamp end_date, + uint64_t min_batch_deposit_serial_id, TALER_EXCHANGEDB_WireMissingCallback cb, void *cb_cls); + /** + * Select all aggregation tracking IDs in the database + * above a given @a min_tracking_serial_id. + * + * @param cls closure + * @param min_tracking_serial_id only return entries stricly above this row (and in order) + * @param cb function to call on all such aggregations + * @param cb_cls closure for @a cb + * @return transaction status code + */ + enum GNUNET_DB_QueryStatus + (*select_aggregations_above_serial)( + void *cls, + uint64_t min_tracking_serial_id, + TALER_EXCHANGEDB_AggregationCallback cb, + void *cb_cls); + + + /** + * Return any applicable justification as to why + * a wire transfer might have been held. Used + * by the auditor to determine if a wire transfer + * is legitimately stalled. + * + * @param cls closure + * @param wire_target_h_payto effected target account + * @param[out] payto_uri target account URI, set to NULL if unknown + * @param[out] kyc_pending set to string describing missing KYC data + * @param[out] status set to AML status + * @param[out] aml_limit set to AML limit, or invalid amount for none + * @return transaction status code + */ + enum GNUNET_DB_QueryStatus + (*select_justification_for_missing_wire)( + void *cls, + const struct TALER_PaytoHashP *wire_target_h_payto, + char **payto_uri, + char **kyc_pending, + enum TALER_AmlDecisionState *status, + struct TALER_Amount *aml_limit); + + /** * Check the last date an auditor was modified. * @@ -6897,6 +6947,8 @@ struct TALER_EXCHANGEDB_Plugin * @param h_payto account for which the attribute data is stored * @param kyc_prox key for similarity search * @param provider_section provider that must be checked + * @param num_checks how many checks do these attributes satisfy + * @param satisfied_checks array of checks satisfied by these attributes * @param provider_account_id provider account ID * @param provider_legitimization_id provider legitimization ID * @param birthday birthdate of user, in days after 1990, or 0 if unknown or definitively adult @@ -6914,6 +6966,8 @@ struct TALER_EXCHANGEDB_Plugin const struct TALER_PaytoHashP *h_payto, const struct GNUNET_ShortHashCode *kyc_prox, const char *provider_section, + unsigned int num_checks, + const char *satisfied_checks[static num_checks], uint32_t birthday, struct GNUNET_TIME_Timestamp collection_time, const char *provider_account_id, diff --git a/src/include/taler_kyclogic_lib.h b/src/include/taler_kyclogic_lib.h index 44cc16e5b..dfa4c58ac 100644 --- a/src/include/taler_kyclogic_lib.h +++ b/src/include/taler_kyclogic_lib.h @@ -357,4 +357,18 @@ TALER_KYCLOGIC_lookup_logic (const char *name, const char **configuration_section); +/** + * Obtain array of KYC checks provided by the provider + * configured in @a section_name. + * + * @param section_name configuration section name + * @param[out] num_checks set to the length of the array + * @param[out] provided_checks set to an array with the + * names of the checks provided by this KYC provider + */ +void +TALER_KYCLOGIC_lookup_checks (const char *section_name, + unsigned int *num_checks, + char ***provided_checks); + #endif diff --git a/src/kyclogic/kyclogic_api.c b/src/kyclogic/kyclogic_api.c index 65f3f3ba3..f583fd225 100644 --- a/src/kyclogic/kyclogic_api.c +++ b/src/kyclogic/kyclogic_api.c @@ -1480,4 +1480,15 @@ TALER_KYCLOGIC_kyc_iterate_thresholds ( } +void +TALER_KYCLOGIC_lookup_checks (const char *section_name, + unsigned int *num_checks, + char ***provided_checks) +{ + *num_checks = 0; + *provided_checks = NULL; + GNUNET_break (0); // FIXME: NOT implemented! +} + + /* end of taler-exchange-httpd_kyc.c */ -- cgit v1.2.3