commit d4a179ec064367e0fffc3c688abdade29a81be27 parent a3bed4aa0c40934e67e51448ae037db9a601d056 Author: Christian Grothoff <christian@grothoff.org> Date: Thu, 6 Nov 2025 16:39:58 +0100 implement protocol v31: #10413 Diffstat:
42 files changed, 1116 insertions(+), 60 deletions(-)
diff --git a/src/exchange/Makefile.am b/src/exchange/Makefile.am @@ -145,6 +145,7 @@ taler_exchange_wirewatch_LDADD = \ taler_exchange_httpd_SOURCES = \ taler-exchange-httpd.c taler-exchange-httpd.h \ + taler-exchange-httpd_aml-accounts-get.c taler-exchange-httpd_aml-accounts-get.h \ taler-exchange-httpd_aml-attributes-get.c taler-exchange-httpd_aml-attributes-get.h \ taler-exchange-httpd_aml-decision.c taler-exchange-httpd_aml-decision.h \ taler-exchange-httpd_aml-decisions-get.c \ diff --git a/src/exchange/taler-exchange-httpd.c b/src/exchange/taler-exchange-httpd.c @@ -32,6 +32,7 @@ #include "taler/taler_templating_lib.h" #include "taler/taler_mhd_lib.h" #include "taler-exchange-httpd_withdraw.h" +#include "taler-exchange-httpd_aml-accounts-get.h" #include "taler-exchange-httpd_aml-attributes-get.h" #include "taler-exchange-httpd_aml-decision.h" #include "taler-exchange-httpd_aml-legitimization-measures-get.h" @@ -591,6 +592,10 @@ handle_get_aml (struct TEH_RequestContext *rc, } h[] = { { + .op = "accounts", + .handler = &TEH_handler_aml_accounts_get + }, + { .op = "attributes", .handler = &TEH_handler_aml_attributes_get }, diff --git a/src/exchange/taler-exchange-httpd_aml-accounts-get.c b/src/exchange/taler-exchange-httpd_aml-accounts-get.c @@ -0,0 +1,514 @@ +/* + This file is part of TALER + Copyright (C) 2024, 2025 Taler Systems SA + + TALER is free software; you can redistribute it and/or modify it under the + terms of the GNU Affero 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 Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License along with + TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +*/ +/** + * @file taler-exchange-httpd_aml-accounts-get.c + * @brief Return summary information about accounts + * @author Christian Grothoff + */ +#include "taler/platform.h" +#include <gnunet/gnunet_util_lib.h> +#include <jansson.h> +#include <microhttpd.h> +#include <pthread.h> +#include "taler/taler_json_lib.h" +#include "taler/taler_mhd_lib.h" +#include "taler/taler_signatures.h" +#include "taler-exchange-httpd.h" +#include "taler/taler_exchangedb_plugin.h" +#include "taler-exchange-httpd_aml-accounts-get.h" + + +/** + * Maximum number of records we return in one go. Must be + * small enough to ensure that XML/CSV encoded results stay + * below the 16MB limit of GNUNET_malloc(). + */ +#define MAX_RECORDS (1024 * 64) + +#define CSV_HEADER \ + "File number,Customer,Comments,Risky,Acquisition date,Exit date\r\n" +#define CSV_FOOTER "\r\n" + +#define XML_HEADER "<?xml version=\"1.0\"?>" \ + "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"" \ + "xmlns:o=\"urn:schemas-microsoft-com:office:office\"" \ + "xmlns:x=\"urn:schemas-microsoft-com:office:excel\"" \ + "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">" \ + "<Worksheet ss:Name=\"Sheet1\">" \ + "<Table>" \ + "<Row>" \ + "<Cell ss:StyleID=\"Header\"><Data ss:Type=\"String\">File number</Data></Cell>" \ + "<Cell ss:StyleID=\"Header\"><Data ss:Type=\"String\">Customer</Data></Cell>" \ + "<Cell ss:StyleID=\"Header\"><Data ss:Type=\"String\">Comments</Data></Cell>" \ + "<Cell ss:StyleID=\"Header\"><Data ss:Type=\"String\">Increased risk business relationship</Data></Cell>" \ + "<Cell ss:StyleID=\"Header\"><Data ss:Type=\"String\">Acquisition date</Data></Cell>" \ + "<Cell ss:StyleID=\"Header\"><Data ss:Type=\"String\">Exit date</Data></Cell>" \ + "</Row>\n" +#define XML_FOOTER "</Table></Worksheet></Workbook>" + +/** + * Closure for the record_cb(). + */ +struct ResponseContext +{ + /** + * Format of the response we are to generate. + */ + enum + { + RCF_JSON, + RCF_XML, + RCF_CSV + } format; + + /** + * Where we store the response data. + */ + union + { + /** + * If @e format is #RCF_JSON. + */ + json_t *json; + + /** + * If @e format is #RCF_XML. + */ + struct GNUNET_Buffer xml; + + /** + * If @e format is #RCF_CSV. + */ + struct GNUNET_Buffer csv; + + } details; +}; + + +/** + * Free resources from @a rc + * + * @param[in] rc context to clean up + */ +static void +free_rc (struct ResponseContext *rc) +{ + switch (rc->format) + { + case RCF_JSON: + json_decref (rc->details.json); + break; + case RCF_XML: + GNUNET_buffer_clear (&rc->details.xml); + break; + case RCF_CSV: + GNUNET_buffer_clear (&rc->details.csv); + break; + } +} + + +/** + * Escape @a str for encoding in XML. + * + * @param str string to escape + * @return XML-encoded @a str + */ +static char * +escape_xml (const char *str) +{ + struct GNUNET_Buffer out = { 0 }; + const char *p = str; + + while (*p) + { + const char *esc = NULL; + + switch (*p) + { + case '&': + esc = "&"; + break; + case '<': + esc = "<"; + break; + case '>': + esc = ">"; + break; + case '"': + esc = """; + break; + case '\'': + esc = "'"; + break; + } + + if (NULL != esc) + { + GNUNET_buffer_write_str (&out, + esc); + } + else + { + GNUNET_buffer_write (&out, + p, + 1); + } + p++; + } + return GNUNET_buffer_reap_str (&out); +} + + +/** + * Return account summary information. + * + * @param cls closure + * @param row_id current row in AML status table + * @param h_payto account for which the attribute data is stored + * @param open_time when was the account opened formally, + * GNUNET_TIME_UNIT_FOREVER_TS if it was never opened + * @param close_time when was the account formally closed, + * GNUNET_TIME_UNIT_ZERO_TS if it was never closed + * @param comments comments on the account + * @param high_risk is this a high-risk business relationship + * @param to_investigate TRUE if this account should be investigated + * @param payto the payto URI of the account + */ +static void +record_cb ( + void *cls, + uint64_t row_id, + const struct TALER_NormalizedPaytoHashP *h_payto, + struct GNUNET_TIME_Timestamp open_time, + struct GNUNET_TIME_Timestamp close_time, + const char *comments, + bool high_risk, + bool to_investigate, + struct TALER_FullPayto payto) +{ + struct ResponseContext *rc = cls; + + if ( (NULL == comments) && + (GNUNET_TIME_absolute_is_never (open_time.abs_time)) ) + comments = "transacted amounts below limits that trigger account opening"; + switch (rc->format) + { + case RCF_JSON: + GNUNET_assert ( + 0 == + json_array_append_new ( + rc->details.json, + GNUNET_JSON_PACK ( + GNUNET_JSON_pack_data_auto ("h_payto", + h_payto), + TALER_JSON_pack_full_payto ("full_payto", + payto), + GNUNET_JSON_pack_bool ("high_risk", + high_risk), + GNUNET_JSON_pack_allow_null ( + GNUNET_JSON_pack_string ("comments", + comments)), + GNUNET_JSON_pack_int64 ("rowid", + row_id), + GNUNET_JSON_pack_timestamp ("open_time", + open_time), + GNUNET_JSON_pack_timestamp ("close_time", + close_time), + GNUNET_JSON_pack_bool ("to_investigate", + to_investigate) + ))); + return; + case RCF_XML: + { + char *ecomments = NULL; + + if ( (NULL == comments) && + (GNUNET_TIME_absolute_is_never (open_time.abs_time)) ) + comments = + "transacted amounts below limits that trigger account opening"; + ecomments = escape_xml (comments); + GNUNET_buffer_write_fstr (&rc->details.xml, + "<Row>" + "<Cell><Data ss:Type=\"Number\">%llu</Data></Cell>" + "<Cell><Data ss:Type=\"String\">%s</Data></Cell>" + "<Cell><Data ss:Type=\"String\">%s</Data></Cell>" + "<Cell><Data ss:Type=\"Boolean\">%s</Data></Cell>" + "<Cell><Data ss:Type=\"DateTime\">%s</Data></Cell>" + "<Cell><Data ss:Type=\"DateTime\">%s</Data></Cell>" + "</Row>\n", + (unsigned long long) row_id, + payto.full_payto, + NULL == ecomments + ? "" + : ecomments, + high_risk ? "1" : "0", + GNUNET_TIME_absolute_is_never (open_time. + abs_time) + ? "-" + : GNUNET_TIME_timestamp2s (open_time), + GNUNET_TIME_absolute_is_zero (close_time. + abs_time) + ? "-" + : GNUNET_TIME_timestamp2s (close_time)); + GNUNET_free (ecomments); + break; + } /* end case RCF_XML */ + case RCF_CSV: + { + char *ecomments = NULL; + char otbuf[64]; + char ctbuf[64]; + + GNUNET_snprintf (otbuf, + sizeof (otbuf), + "%s", + GNUNET_TIME_timestamp2s (open_time)); + GNUNET_snprintf (ctbuf, + sizeof (ctbuf), + "%s", + GNUNET_TIME_timestamp2s (close_time)); + if (NULL != comments) + { + size_t len = strlen (comments); + size_t wpos = 0; + + /* Escape 'comments' to double '"' as per RFC 4180, 2.7. */ + ecomments = GNUNET_malloc (2 * len + 1); + for (size_t off = 0; off<len; off++) + { + if ('"' == comments[off]) + ecomments[wpos++] = '"'; + ecomments[wpos++] = comments[off++]; + } + + GNUNET_buffer_write_fstr (&rc->details.xml, + "%llu,%s,\"%s\",%s,%s,%s\r\n", + (unsigned long long) row_id, + payto.full_payto, + NULL == ecomments + ? "-" + : ecomments, + high_risk ? "X":" ", + GNUNET_TIME_absolute_is_never (open_time. + abs_time) + ? "-" + : otbuf, + GNUNET_TIME_absolute_is_zero (close_time. + abs_time) + ? "-" + : ctbuf); + GNUNET_free (ecomments); + break; + } + } /* end case RCF_CSV */ + } /* end switch */ +} + + +MHD_RESULT +TEH_handler_aml_accounts_get ( + struct TEH_RequestContext *rc, + const struct TALER_AmlOfficerPublicKeyP *officer_pub, + const char *const args[]) +{ + struct ResponseContext rctx; + int64_t limit = -20; + uint64_t offset; + enum TALER_EXCHANGE_YesNoAll open_filter; + enum TALER_EXCHANGE_YesNoAll high_risk_filter; + enum TALER_EXCHANGE_YesNoAll investigation_filter; + + memset (&rctx, + 0, + sizeof (rctx)); + if (NULL != args[0]) + { + GNUNET_break_op (0); + return TALER_MHD_reply_with_error ( + rc->connection, + MHD_HTTP_NOT_FOUND, + TALER_EC_GENERIC_ENDPOINT_UNKNOWN, + args[0]); + } + TALER_MHD_parse_request_snumber (rc->connection, + "limit", + &limit); + if (limit > 0) + offset = 0; + else + offset = INT64_MAX; + TALER_MHD_parse_request_number (rc->connection, + "offset", + &offset); + if (offset > INT64_MAX) + { + GNUNET_break_op (0); /* broken client */ + offset = INT64_MAX; + } + TALER_MHD_parse_request_yna (rc->connection, + "open", + TALER_EXCHANGE_YNA_ALL, + &open_filter); + TALER_MHD_parse_request_yna (rc->connection, + "investigation", + TALER_EXCHANGE_YNA_ALL, + &investigation_filter); + TALER_MHD_parse_request_yna (rc->connection, + "high_risk", + TALER_EXCHANGE_YNA_ALL, + &high_risk_filter); + { + const char *mime; + + mime = MHD_lookup_connection_value (rc->connection, + MHD_HEADER_KIND, + MHD_HTTP_HEADER_ACCEPT); + if (NULL == mime) + mime = "application/json"; + if (0 == strcmp (mime, + "application/json")) + { + rctx.format = RCF_JSON; + rctx.details.json = json_array (); + GNUNET_assert (NULL != rctx.details.json); + } + else if (0 == strcmp (mime, + "application/vnd.ms-excel")) + { + rctx.format = RCF_XML; + GNUNET_buffer_write_str (&rctx.details.xml, + XML_HEADER); + } + else if (0 == strcmp (mime, + "text/csv")) + { + rctx.format = RCF_CSV; + GNUNET_buffer_write_str (&rctx.details.csv, + CSV_HEADER); + } + else + { + GNUNET_break_op (0); + return TALER_MHD_REPLY_JSON_PACK ( + rc->connection, + MHD_HTTP_NOT_ACCEPTABLE, + GNUNET_JSON_pack_string ("hint", + mime)); + } + } + + { + enum GNUNET_DB_QueryStatus qs; + + if (limit > MAX_RECORDS) + limit = MAX_RECORDS; + if (limit < -MAX_RECORDS) + limit = -MAX_RECORDS; + qs = TEH_plugin->select_kyc_accounts ( + TEH_plugin->cls, + investigation_filter, + open_filter, + high_risk_filter, + offset, + limit, + &record_cb, + &rctx); + switch (qs) + { + case GNUNET_DB_STATUS_HARD_ERROR: + case GNUNET_DB_STATUS_SOFT_ERROR: + free_rc (&rctx); + GNUNET_break (0); + return TALER_MHD_reply_with_error ( + rc->connection, + MHD_HTTP_INTERNAL_SERVER_ERROR, + TALER_EC_GENERIC_DB_FETCH_FAILED, + "select_kyx_accounts"); + case GNUNET_DB_STATUS_SUCCESS_NO_RESULTS: + free_rc (&rctx); + return TALER_MHD_reply_static ( + rc->connection, + MHD_HTTP_NO_CONTENT, + NULL, + NULL, + 0); + case GNUNET_DB_STATUS_SUCCESS_ONE_RESULT: + break; + } /* end switch (qs) */ + + switch (rctx.format) + { + case RCF_JSON: + return TALER_MHD_REPLY_JSON_PACK ( + rc->connection, + MHD_HTTP_OK, + GNUNET_JSON_pack_array_steal ("accounts", + rctx.details.json)); + case RCF_XML: + { + struct MHD_Response *resp; + MHD_RESULT mret; + + GNUNET_buffer_write_str (&rctx.details.xml, + XML_FOOTER); + /* FIXME: add support for compression */ + resp = MHD_create_response_from_buffer (rctx.details.xml.position, + rctx.details.xml.mem, + MHD_RESPMEM_MUST_FREE); + TALER_MHD_add_global_headers (resp, + false); + GNUNET_break (MHD_YES == + MHD_add_response_header (resp, + MHD_HTTP_HEADER_CONTENT_TYPE, + "application/vnd.ms-excel")); + mret = MHD_queue_response (rc->connection, + MHD_HTTP_OK, + resp); + MHD_destroy_response (resp); + return mret; + } + case RCF_CSV: + { + struct MHD_Response *resp; + MHD_RESULT mret; + + GNUNET_buffer_write_str (&rctx.details.csv, + CSV_FOOTER); + /* FIXME: add support for compression */ + resp = MHD_create_response_from_buffer (rctx.details.xml.position, + rctx.details.xml.mem, + MHD_RESPMEM_MUST_FREE); + TALER_MHD_add_global_headers (resp, + false); + GNUNET_break (MHD_YES == + MHD_add_response_header (resp, + MHD_HTTP_HEADER_CONTENT_TYPE, + "text/csv")); + mret = MHD_queue_response (rc->connection, + MHD_HTTP_OK, + resp); + MHD_destroy_response (resp); + return mret; + } + } /* end switch (rctx.format) */ + } + GNUNET_break (0); + return MHD_NO; +} + + +/* end of taler-exchange-httpd_aml-accounts_get.c */ diff --git a/src/exchange/taler-exchange-httpd_aml-accounts-get.h b/src/exchange/taler-exchange-httpd_aml-accounts-get.h @@ -0,0 +1,44 @@ +/* + This file is part of TALER + Copyright (C) 2025 Taler Systems SA + + TALER is free software; you can redistribute it and/or modify it under the + terms of the GNU Affero 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 Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License along with + TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +*/ +/** + * @file taler-exchange-httpd_aml-accounts-get.h + * @brief Handle GET /aml/$OFFICER_PUB/accounts requests + * @author Christian Grothoff + */ +#ifndef TALER_EXCHANGE_HTTPD_AML_ACCOUNTS_GET_H +#define TALER_EXCHANGE_HTTPD_AML_ACCOUNTS_GET_H + +#include <microhttpd.h> +#include "taler-exchange-httpd.h" + + +/** + * Handle a GET "/aml/$OFFICER_PUB/accounts" request. Parses the request + * details, checks the signatures and if appropriately authorized returns + * the matching decisions. + * + * @param rc request context + * @param officer_pub public key of the AML officer who made the request + * @param args GET arguments (should be the state) + * @return MHD result code + */ +MHD_RESULT +TEH_handler_aml_accounts_get ( + struct TEH_RequestContext *rc, + const struct TALER_AmlOfficerPublicKeyP *officer_pub, + const char *const args[]); + +#endif diff --git a/src/exchange/taler-exchange-httpd_config.h b/src/exchange/taler-exchange-httpd_config.h @@ -41,7 +41,7 @@ * * Returned via both /config and /keys endpoints. */ -#define EXCHANGE_PROTOCOL_VERSION "30:3:8" +#define EXCHANGE_PROTOCOL_VERSION "31:0:9" /** diff --git a/src/exchangedb/.gitignore b/src/exchangedb/.gitignore @@ -14,9 +14,3 @@ test-exchangedb-populate-link-data-postgres test-exchangedb-populate-ready-deposit-postgres test-exchangedb-populate-select-refunds-by-coin-postgres exchange-0004.sql -exchange-0005.sql -exchange-0006.sql -exchange-0007.sql -exchange-0008.sql -exchange-0009.sql -exchange-0010.sql diff --git a/src/exchangedb/0003-wire_targets.sql b/src/exchangedb/0003-wire_targets.sql @@ -14,9 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE FUNCTION alter_table_wire_targets3( - IN partition_suffix TEXT DEFAULT NULL -) +CREATE FUNCTION alter_table_wire_targets3() RETURNS VOID LANGUAGE plpgsql AS $$ diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am @@ -39,6 +39,7 @@ sql_DATA = \ exchange-0002.sql \ exchange-0003.sql \ exchange-0004.sql \ + exchange-0005.sql \ drop.sql \ procedures.sql \ tops-0001.sql @@ -259,6 +260,7 @@ libtaler_plugin_exchangedb_postgres_la_SOURCES = \ pg_select_exchange_credit_transfers.h pg_select_exchange_credit_transfers.c \ pg_select_exchange_kycauth_transfers.h pg_select_exchange_kycauth_transfers.c \ pg_select_exchange_debit_transfers.h pg_select_exchange_debit_transfers.c \ + pg_select_kyc_accounts.h pg_select_kyc_accounts.c \ pg_select_kyc_attributes.h pg_select_kyc_attributes.c \ pg_select_merge_amounts_for_kyc_check.h pg_select_merge_amounts_for_kyc_check.c \ pg_select_purse.h pg_select_purse.c \ diff --git a/src/exchangedb/exchange-0005.sql b/src/exchangedb/exchange-0005.sql @@ -0,0 +1,133 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2025 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 <http://www.gnu.org/licenses/> +-- + +BEGIN; + +SELECT _v.register_patch('exchange-0005', NULL, NULL); + +SET search_path TO exchange; + +-- convert all JSON-valued fields from TEXT to JSONB + +CREATE FUNCTION alter_table_wire_accounts5() +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + ALTER TABLE wire_accounts + ALTER COLUMN debit_restrictions + TYPE JSONB + USING debit_restrictions::JSONB, + ALTER COLUMN credit_restrictions + TYPE JSONB + USING credit_restrictions::JSONB; +END +$$; + + +CREATE FUNCTION alter_table_legitimization_outcomes5() +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + ALTER TABLE legitimization_outcomes + ALTER COLUMN jproperties + TYPE JSONB + USING jproperties::JSONB, + ALTER COLUMN jnew_rules + TYPE JSONB + USING jnew_rules::JSONB; +END +$$; + + +CREATE FUNCTION alter_table_legitimization_measures5() +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + ALTER TABLE legitimization_measures + ALTER COLUMN jmeasures + TYPE JSONB + USING jmeasures::JSONB; +END +$$; + + +CREATE FUNCTION alter_table_policy_fulfillments5() +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + ALTER TABLE policy_fulfillments + ALTER COLUMN fulfillment_proof + TYPE JSONB + USING fulfillment_proof::JSONB; +END +$$; + + +CREATE FUNCTION alter_table_kyc_targets5() +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'kyc_targets'; +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD COLUMN open_time INT8 DEFAULT(NULL)' + ',ADD COLUMN close_time INT8 DEFAULT(NULL);' + ); +END +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('wire_accounts5' + ,'exchange-0005' + ,'alter' + ,TRUE + ,FALSE), + ('legitimization_outcomes5' + ,'exchange-0005' + ,'alter' + ,TRUE + ,FALSE), + ('legitimization_measures5' + ,'exchange-0005' + ,'alter' + ,TRUE + ,FALSE), + ('policy_fulfillments5' + ,'exchange-0005' + ,'alter' + ,TRUE + ,FALSE), + ('kyc_targets5' + ,'exchange-0005' + ,'alter' + ,TRUE + ,FALSE); + + +COMMIT; diff --git a/src/exchangedb/exchange_do_get_kyc_rules.sql b/src/exchangedb/exchange_do_get_kyc_rules.sql @@ -21,7 +21,7 @@ CREATE FUNCTION exchange_do_get_kyc_rules( IN in_merchant_pub BYTEA, -- possibly NULL OUT out_target_pub BYTEA, -- possibly NULL OUT out_reserve_pub BYTEA, -- possibly NULL - OUT out_jnew_rules TEXT -- possibly NULL + OUT out_jnew_rules JSONB -- possibly NULL ) LANGUAGE plpgsql AS $$ diff --git a/src/exchangedb/exchange_do_insert_active_legitimization_measure.sql b/src/exchangedb/exchange_do_insert_active_legitimization_measure.sql @@ -18,7 +18,7 @@ DROP FUNCTION IF EXISTS exchange_do_insert_active_legitimization_measure; CREATE FUNCTION exchange_do_insert_active_legitimization_measure( IN in_access_token BYTEA, IN in_start_time INT8, - IN in_jmeasures TEXT, + IN in_jmeasures JSONB, OUT out_legitimization_measure_serial_id INT8) LANGUAGE plpgsql AS $$ @@ -47,5 +47,5 @@ INSERT INTO legitimization_measures END $$; -COMMENT ON FUNCTION exchange_do_insert_active_legitimization_measure(BYTEA, INT8, TEXT) +COMMENT ON FUNCTION exchange_do_insert_active_legitimization_measure(BYTEA, INT8, JSONB) IS 'Inserts legitimization measure for an account and marks all existing such measures as inactive'; diff --git a/src/exchangedb/exchange_do_insert_aml_decision.sql b/src/exchangedb/exchange_do_insert_aml_decision.sql @@ -21,14 +21,14 @@ CREATE FUNCTION exchange_do_insert_aml_decision( IN in_h_full_payto BYTEA, -- can be NULL! IN in_decision_time INT8, IN in_expiration_time INT8, - IN in_properties TEXT, -- can be NULL + IN in_properties JSONB, -- can be NULL IN in_kyc_attributes_enc BYTEA, -- can be NULL IN in_kyc_attributes_hash BYTEA, -- can be NULL IN in_kyc_attributes_expiration INT8, -- can be NULL - IN in_new_rules TEXT, + IN in_new_rules JSONB, IN in_to_investigate BOOLEAN, IN in_new_measure_name TEXT, -- can be NULL - IN in_jmeasures TEXT, -- can be NULL + IN in_jmeasures JSONB, -- can be NULL IN in_justification TEXT, -- can be NULL IN in_decider_pub BYTEA, -- can be NULL IN in_decider_sig BYTEA, -- can be NULL @@ -300,6 +300,19 @@ LOOP ) VALUES ( in_decision_time ,ini_event); + IF (ini-event = 'ACCOUNT_OPEN') + THEN + UPDATE kyc_targets + SET open_time=in_decision_time + ,close_time=NULL + WHERE h_normalized_payto=in_h_normalized_payto; + END IF; + IF (ini-event = 'ACCOUNT_IDLE') + THEN + UPDATE kyc_targets + SET close_time=in_decision_time + WHERE h_normalized_payto=in_h_normalized_payto; + END IF; END LOOP; -- wake up taler-exchange-aggregator @@ -320,5 +333,5 @@ EXECUTE FORMAT ( END $$; -COMMENT ON FUNCTION exchange_do_insert_aml_decision(TEXT, BYTEA, BYTEA, INT8, INT8, TEXT, BYTEA, BYTEA, INT8, TEXT, BOOLEAN, TEXT, TEXT, TEXT, BYTEA, BYTEA, TEXT, TEXT[], TEXT) +COMMENT ON FUNCTION exchange_do_insert_aml_decision(TEXT, BYTEA, BYTEA, INT8, INT8, JSONB, BYTEA, BYTEA, INT8, JSONB, BOOLEAN, TEXT, JSONB, TEXT, BYTEA, BYTEA, TEXT, TEXT[], TEXT) IS 'Checks whether the AML officer is eligible to make AML decisions and if so inserts the decision into the table'; diff --git a/src/exchangedb/exchange_do_insert_sanction_list_hit.sql b/src/exchangedb/exchange_do_insert_sanction_list_hit.sql @@ -19,8 +19,8 @@ CREATE FUNCTION exchange_do_insert_sanction_list_hit( IN in_h_normalized_payto BYTEA, IN in_decision_time INT8, IN in_expiration_time INT8, - IN in_properties TEXT, -- can be NULL - IN in_new_rules TEXT, -- can be NULL + IN in_properties JSONB, -- can be NULL + IN in_new_rules JSONB, -- can be NULL IN in_to_investigate BOOLEAN, IN in_notify_s TEXT, IN ina_events TEXT[], @@ -78,5 +78,5 @@ EXECUTE FORMAT ( END $$; -COMMENT ON FUNCTION exchange_do_insert_sanction_list_hit(BYTEA, INT8, INT8, TEXT, TEXT, BOOLEAN, TEXT, TEXT[]) +COMMENT ON FUNCTION exchange_do_insert_sanction_list_hit(BYTEA, INT8, INT8, JSONB, JSONB, BOOLEAN, TEXT, TEXT[]) IS 'Insert result from sanction list check into the table'; diff --git a/src/exchangedb/exchange_do_insert_successor_measure.sql b/src/exchangedb/exchange_do_insert_successor_measure.sql @@ -20,7 +20,7 @@ CREATE FUNCTION exchange_do_insert_successor_measure( IN in_decision_time INT8, IN in_expiration_time INT8, IN in_new_measure_name TEXT, -- can be NULL - IN in_jmeasures TEXT, -- can be NULL + IN in_jmeasures JSONB, -- can be NULL OUT out_last_date INT8, OUT out_account_unknown BOOLEAN, OUT out_legitimization_measure_serial_id INT8 @@ -153,7 +153,7 @@ INSERT INTO legitimization_outcomes (in_h_normalized_payto ,in_decision_time ,in_expiration_time - ,'{}' + ,'{}'::JSONB ,in_new_measure_name ,FALSE ,NULL @@ -166,5 +166,5 @@ INSERT INTO legitimization_outcomes END $$; -COMMENT ON FUNCTION exchange_do_insert_successor_measure(BYTEA, INT8, INT8, TEXT, TEXT) +COMMENT ON FUNCTION exchange_do_insert_successor_measure(BYTEA, INT8, INT8, TEXT, JSONB) IS 'Checks whether the AML officer is eligible to make AML decisions and if so inserts the decision into the table'; diff --git a/src/exchangedb/exchange_do_lookup_kyc_requirement_by_row.sql b/src/exchangedb/exchange_do_lookup_kyc_requirement_by_row.sql @@ -23,7 +23,7 @@ CREATE FUNCTION exchange_do_lookup_kyc_requirement_by_row( OUT out_account_pub BYTEA, -- NULL allowed OUT out_reserve_pub BYTEA, -- NULL allowed OUT out_access_token BYTEA, -- NULL if 'out_not_found' - OUT out_jrules TEXT, -- NULL allowed + OUT out_jrules JSONB, -- NULL allowed OUT out_is_wallet BOOLEAN, -- NULL allowed OUT out_not_found BOOLEAN, OUT out_rule_gen INT8, -- NULL allowed diff --git a/src/exchangedb/exchange_do_trigger_kyc_rule_for_account.sql b/src/exchangedb/exchange_do_trigger_kyc_rule_for_account.sql @@ -23,7 +23,7 @@ CREATE FUNCTION exchange_do_trigger_kyc_rule_for_account( IN in_payto_uri TEXT, -- can be NULL IN in_h_full_payto BYTEA, IN in_now INT8, - IN in_jmeasures TEXT, + IN in_jmeasures JSONB, IN in_display_priority INT4, IN in_notify_s TEXT, OUT out_legitimization_measure_serial_id INT8, diff --git a/src/exchangedb/pg_add_policy_fulfillment_proof.c b/src/exchangedb/pg_add_policy_fulfillment_proof.c @@ -101,7 +101,7 @@ TEH_PG_add_policy_fulfillment_proof ( ",fulfillment_proof" ",h_fulfillment_proof" ",policy_hash_codes" - ") VALUES ($1, $2, $3, $4)" + ") VALUES ($1, $2::TEXT::JSON, $3, $4)" " ON CONFLICT DO NOTHING;"); qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, "insert_proof_into_policy_fulfillments", diff --git a/src/exchangedb/pg_get_kyc_rules.c b/src/exchangedb/pg_get_kyc_rules.c @@ -77,7 +77,7 @@ TEH_PG_get_kyc_rules ( "get_kyc_rules", "SELECT" " out_target_pub AS target_pub" - " ,out_jnew_rules AS jnew_rules" + " ,out_jnew_rules::TEXT AS jnew_rules" " ,out_reserve_pub AS reserve_pub" " FROM exchange_do_get_kyc_rules ($1,$2,$3);"); return GNUNET_PQ_eval_prepared_singleton_select ( @@ -114,7 +114,7 @@ TEH_PG_get_kyc_rules2 ( PREPARE (pg, "get_kyc_rules2", "SELECT" - " jnew_rules" + " jnew_rules::TEXT" " FROM legitimization_outcomes" " WHERE h_payto=$1" " AND expiration_time >= $2" diff --git a/src/exchangedb/pg_get_wire_accounts.c b/src/exchangedb/pg_get_wire_accounts.c @@ -151,8 +151,8 @@ TEH_PG_get_wire_accounts (void *cls, "SELECT" " payto_uri" ",conversion_url" - ",debit_restrictions" - ",credit_restrictions" + ",debit_restrictions::TEXT" + ",credit_restrictions::TEXT" ",master_sig" ",bank_label" ",priority" diff --git a/src/exchangedb/pg_insert_active_legitimization_measure.c b/src/exchangedb/pg_insert_active_legitimization_measure.c @@ -53,7 +53,7 @@ TEH_PG_insert_active_legitimization_measure ( "SELECT" " out_legitimization_measure_serial_id" " FROM exchange_do_insert_active_legitimization_measure" - "($1, $2, $3);"); + "($1, $2, $3::TEXT::JSONB);"); return GNUNET_PQ_eval_prepared_singleton_select ( pg->conn, "do_insert_active_legitimization_measure", diff --git a/src/exchangedb/pg_insert_aml_decision.c b/src/exchangedb/pg_insert_aml_decision.c @@ -167,7 +167,8 @@ TEH_PG_insert_aml_decision ( ",out_legitimization_measure_serial_id" ",out_is_wallet" " FROM exchange_do_insert_aml_decision" - "($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19);"); + "($1,$2,$3,$4,$5,$6::TEXT::JSONB,$7,$8,$9,$10::TEXT::JSONB" + ",$11,$12,$13::TEXT::JSONB,$14,$15,$16,$17,$18,$19);"); qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, "do_insert_aml_decision", params, diff --git a/src/exchangedb/pg_insert_records_by_table.c b/src/exchangedb/pg_insert_records_by_table.c @@ -275,7 +275,7 @@ irbt_cb_table_legitimization_measures ( ",jmeasures" ",display_priority" ") VALUES " - "($1, $2, $3, $4, $5);"); + "($1, $2, $3, $4::TEXT::JSONB, $5);"); return GNUNET_PQ_eval_prepared_non_select (pg->conn, "insert_into_table_legitimization_measures", params); @@ -321,7 +321,7 @@ irbt_cb_table_legitimization_outcomes ( ",to_investigate" ",jnew_rules" ") VALUES " - "($1, $2, $3, $4, $5, $6, $7);"); + "($1, $2, $3, $4, $5::TEXT::JSONB, $6, $7::TEXT::JSONB);"); return GNUNET_PQ_eval_prepared_non_select (pg->conn, "insert_into_table_legitimization_outcomes", params); @@ -1441,7 +1441,7 @@ irbt_cb_table_policy_fulfillments (struct PostgresClosure *pg, ",h_fulfillment_proof" ",policy_hash_codes" ") VALUES " - "($1, $2, $3, $4, $5);"); + "($1, $2, $3::TEXT::JSONB, $4, $5);"); return GNUNET_PQ_eval_prepared_non_select (pg->conn, "insert_into_table_policy_fulfillments", params); diff --git a/src/exchangedb/pg_insert_successor_measure.c b/src/exchangedb/pg_insert_successor_measure.c @@ -77,7 +77,7 @@ TEH_PG_insert_successor_measure ( ",out_last_date" ",out_legitimization_measure_serial_id" " FROM exchange_do_insert_successor_measure" - "($1, $2, $3, $4, $5);"); + "($1, $2, $3, $4, $5::TEXT::JSONB);"); qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, "do_insert_successor_measure", params, diff --git a/src/exchangedb/pg_insert_wire.c b/src/exchangedb/pg_insert_wire.c @@ -68,7 +68,7 @@ TEH_PG_insert_wire ( ",bank_label" ",priority" ") VALUES " - "($1, $2, $3, $4, $5, true, $6, $7, $8);"); + "($1,$2,$3::TEXT::JSONB,$4::TEXT::JSONB,$5,true,$6,$7,$8);"); return GNUNET_PQ_eval_prepared_non_select (pg->conn, "insert_wire", params); diff --git a/src/exchangedb/pg_lookup_active_legitimization.c b/src/exchangedb/pg_lookup_active_legitimization.c @@ -51,7 +51,7 @@ TEH_PG_lookup_active_legitimization ( PREPARE (pg, "lookup_active_legitimization", "SELECT " - " lm.jmeasures" + " lm.jmeasures::TEXT" ",lp.measure_index" " FROM legitimization_processes lp" " JOIN legitimization_measures lm" diff --git a/src/exchangedb/pg_lookup_aml_history.c b/src/exchangedb/pg_lookup_aml_history.c @@ -146,8 +146,8 @@ TEH_PG_lookup_aml_history ( " lo.decision_time" ",ah.justification" ",ah.decider_pub" - ",lo.jproperties" - ",lo.jnew_rules" + ",lo.jproperties::TEXT" + ",lo.jnew_rules::TEXT" ",lo.to_investigate" ",lo.is_active" " FROM aml_history ah" diff --git a/src/exchangedb/pg_lookup_completed_legitimization.c b/src/exchangedb/pg_lookup_completed_legitimization.c @@ -76,7 +76,7 @@ TEH_PG_lookup_completed_legitimization ( PREPARE (pg, "lookup_completed_legitimization", "SELECT " - " lm.jmeasures" + " lm.jmeasures::TEXT" ",kt.h_normalized_payto" ",kt.is_wallet" ",lm.access_token" diff --git a/src/exchangedb/pg_lookup_kyc_requirement_by_row.c b/src/exchangedb/pg_lookup_kyc_requirement_by_row.c @@ -109,7 +109,7 @@ TEH_PG_lookup_kyc_requirement_by_row ( " out_account_pub AS account_pub" ",out_reserve_pub AS reserve_pub" ",out_access_token AS access_token" - ",out_jrules AS jrules" + ",out_jrules::TEXT AS jrules" ",out_is_wallet AS is_wallet" ",out_not_found AS not_found" ",out_aml_review AS aml_review" diff --git a/src/exchangedb/pg_lookup_kyc_status_by_token.c b/src/exchangedb/pg_lookup_kyc_status_by_token.c @@ -52,7 +52,7 @@ TEH_PG_lookup_kyc_status_by_token ( "lookup_kyc_status_by_token", "SELECT" " legitimization_measure_serial_id" - ",jmeasures" + ",jmeasures::TEXT" " FROM legitimization_measures" " WHERE access_token=$1" " AND NOT is_finished" diff --git a/src/exchangedb/pg_lookup_pending_legitimization.c b/src/exchangedb/pg_lookup_pending_legitimization.c @@ -63,7 +63,7 @@ TEH_PG_lookup_pending_legitimization ( PREPARE (pg, "lookup_pending_legitimization", "SELECT " - " lm.jmeasures" + " lm.jmeasures::TEXT" ",kt.h_normalized_payto" ",kt.is_wallet" ",lm.access_token" diff --git a/src/exchangedb/pg_lookup_records_by_table.c b/src/exchangedb/pg_lookup_records_by_table.c @@ -3440,14 +3440,17 @@ TEH_PG_lookup_records_by_table (void *cls, rh = &lrbt_cb_table_recoup_refresh; break; case TALER_EXCHANGEDB_RT_EXTENSIONS: + // FIXME: this seems broken! -- where is the SQL!? statement = "select_above_serial_by_table_extensions"; rh = &lrbt_cb_table_extensions; break; case TALER_EXCHANGEDB_RT_POLICY_DETAILS: + // FIXME: this seems broken! -- where is the SQL!? statement = "select_above_serial_by_table_policy_details"; rh = &lrbt_cb_table_policy_details; break; case TALER_EXCHANGEDB_RT_POLICY_FULFILLMENTS: + // FIXME: this seems broken! -- where is the SQL!? statement = "select_above_serial_by_table_policy_fulfillments"; rh = &lrbt_cb_table_policy_fulfillments; break; @@ -3683,7 +3686,7 @@ TEH_PG_lookup_records_by_table (void *cls, " legitimization_measure_serial_id AS serial" ",access_token" ",start_time" - ",jmeasures" + ",jmeasures::TEXT" ",display_priority" " FROM legitimization_measures" " WHERE legitimization_measure_serial_id > $1" @@ -3697,9 +3700,9 @@ TEH_PG_lookup_records_by_table (void *cls, ",h_payto" ",decision_time" ",expiration_time" - ",jproperties" + ",jproperties::TEXT" ",to_investigate" - ",jnew_rules" + ",jnew_rules::TEXT" " FROM legitimization_outcomes" " WHERE outcome_serial_id > $1" " ORDER BY outcome_serial_id ASC;"); diff --git a/src/exchangedb/pg_lookup_rules_by_access_token.c b/src/exchangedb/pg_lookup_rules_by_access_token.c @@ -54,7 +54,7 @@ TEH_PG_lookup_rules_by_access_token ( PREPARE (pg, "lookup_rules_by_access_token", "SELECT" - " jnew_rules" + " jnew_rules::TEXT" ",outcome_serial_id AS row_id" " FROM legitimization_outcomes" " WHERE h_payto=$1" diff --git a/src/exchangedb/pg_select_all_kyc_attributes.c b/src/exchangedb/pg_select_all_kyc_attributes.c @@ -146,7 +146,7 @@ TEH_PG_select_all_kyc_attributes ( " lp.provider_name" ",ka.h_payto" ",ka.kyc_attributes_serial_id" - ",lo.jproperties" + ",lo.jproperties::TEXT" ",ka.collection_time" ",ka.expiration_time" ",ka.encrypted_attributes" diff --git a/src/exchangedb/pg_select_aml_decisions.c b/src/exchangedb/pg_select_aml_decisions.c @@ -195,10 +195,10 @@ TEH_PG_select_aml_decisions ( ",ah.justification" ",lo.decision_time" ",lo.expiration_time" - ",lo.jproperties" + ",lo.jproperties::TEXT" ",lo.to_investigate" ",lo.is_active" - ",lo.jnew_rules" + ",lo.jnew_rules::TEXT" ",kt.is_wallet" ",wt.payto_uri" " FROM legitimization_outcomes lo" @@ -222,10 +222,10 @@ TEH_PG_select_aml_decisions ( ",ah.justification" ",lo.decision_time" ",lo.expiration_time" - ",lo.jproperties" + ",lo.jproperties::TEXT" ",lo.to_investigate" ",lo.is_active" - ",lo.jnew_rules" + ",lo.jnew_rules::TEXT" ",kt.is_wallet" ",wt.payto_uri" " FROM legitimization_outcomes lo" diff --git a/src/exchangedb/pg_select_aml_measures.c b/src/exchangedb/pg_select_aml_measures.c @@ -151,7 +151,7 @@ TEH_PG_select_aml_measures ( "SELECT" " lm.legitimization_measure_serial_id" ",kt.h_normalized_payto" - ",lm.jmeasures" + ",lm.jmeasures::TEXT" ",lm.start_time" ",lm.is_finished" " FROM kyc_targets kt" @@ -167,7 +167,7 @@ TEH_PG_select_aml_measures ( "SELECT" " lm.legitimization_measure_serial_id" ",kt.h_normalized_payto" - ",lm.jmeasures" + ",lm.jmeasures::TEXT" ",lm.start_time" ",lm.is_finished" " FROM kyc_targets kt" diff --git a/src/exchangedb/pg_select_kyc_accounts.c b/src/exchangedb/pg_select_kyc_accounts.c @@ -0,0 +1,234 @@ +/* + This file is part of TALER + Copyright (C) 2025 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 <http://www.gnu.org/licenses/> + */ +/** + * @file exchangedb/pg_select_kyc_accounts.c + * @brief Implementation of the select_kyc_accounts function for Postgres + * @author Christian Grothoff + */ +#include "taler/platform.h" +#include "taler/taler_error_codes.h" +#include "taler/taler_dbevents.h" +#include "taler/taler_pq_lib.h" +#include "pg_select_kyc_accounts.h" +#include "pg_helper.h" + + +/** + * Closure for #handle_aml_result. + */ +struct KycAccountResultContext +{ + /** + * Function to call on each result. + */ + TALER_EXCHANGEDB_AmlAccountListCallback cb; + + /** + * Closure for @e cb. + */ + void *cb_cls; + + /** + * Plugin context. + */ + struct PostgresClosure *pg; + + /** + * Set to #GNUNET_SYSERR on serious errors. + */ + enum GNUNET_GenericReturnValue status; +}; + + +/** + * Function to be called with the results of a SELECT statement + * that has returned @a num_results results. Helper function + * for #TEH_PG_select_kyc_accounts(). + * + * @param cls closure of type `struct KycAccountResultContext *` + * @param result the postgres result + * @param num_results the number of results in @a result + */ +static void +handle_kyc_account_result (void *cls, + PGresult *result, + unsigned int num_results) +{ + struct KycAccountResultContext *ctx = cls; + + for (unsigned int i = 0; i<num_results; i++) + { + uint64_t rowid; + struct TALER_NormalizedPaytoHashP h_payto; + char *comments = NULL; + struct GNUNET_TIME_Timestamp open_time + = GNUNET_TIME_UNIT_FOREVER_TS; + struct GNUNET_TIME_Timestamp close_time + = GNUNET_TIME_UNIT_ZERO_TS; + bool to_investigate; + bool high_risk; + struct TALER_FullPayto payto; + struct GNUNET_PQ_ResultSpec rs[] = { + GNUNET_PQ_result_spec_uint64 ("kyc_target_serial_id", + &rowid), + GNUNET_PQ_result_spec_auto_from_type ("h_payto", + &h_payto), + GNUNET_PQ_result_spec_allow_null ( + GNUNET_PQ_result_spec_string ("comments", + &comments), + NULL), + GNUNET_PQ_result_spec_allow_null ( + GNUNET_PQ_result_spec_timestamp ("open_time", + &open_time), + NULL), + GNUNET_PQ_result_spec_allow_null ( + GNUNET_PQ_result_spec_timestamp ("close_time", + &close_time), + NULL), + GNUNET_PQ_result_spec_bool ("to_investigate", + &to_investigate), + GNUNET_PQ_result_spec_bool ("high_risk", + &high_risk), + GNUNET_PQ_result_spec_string ("payto_uri", + &payto.full_payto), + GNUNET_PQ_result_spec_end + }; + + if (GNUNET_OK != + GNUNET_PQ_extract_result (result, + rs, + i)) + { + GNUNET_break (0); + ctx->status = GNUNET_SYSERR; + return; + } + ctx->cb (ctx->cb_cls, + rowid, + &h_payto, + open_time, + close_time, + comments, + high_risk, + to_investigate, + payto); + GNUNET_PQ_cleanup_result (rs); + } +} + + +enum GNUNET_DB_QueryStatus +TEH_PG_select_kyc_accounts ( + void *cls, + enum TALER_EXCHANGE_YesNoAll investigation_only, + enum TALER_EXCHANGE_YesNoAll open_only, + enum TALER_EXCHANGE_YesNoAll high_risk_only, + uint64_t offset, + int64_t limit, + TALER_EXCHANGEDB_AmlAccountListCallback cb, + void *cb_cls) +{ + struct PostgresClosure *pg = cls; + uint64_t ulimit = (limit > 0) ? limit : -limit; + struct GNUNET_PQ_QueryParam params[] = { + GNUNET_PQ_query_param_uint64 (&offset), + GNUNET_PQ_query_param_uint64 (&ulimit), + GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_ALL == + investigation_only)), + GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_YES == + investigation_only)), + GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_ALL == + open_only)), + GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_YES == + open_only)), + GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_ALL == + high_risk_only)), + GNUNET_PQ_query_param_bool ((TALER_EXCHANGE_YNA_YES == + high_risk_only)), + GNUNET_PQ_query_param_end + }; + struct KycAccountResultContext ctx = { + .cb = cb, + .cb_cls = cb_cls, + .pg = pg, + .status = GNUNET_OK + }; + enum GNUNET_DB_QueryStatus qs; + const char *stmt = (limit > 0) + ? "select_kyc_accounts_inc" + : "select_kyc_accounts_dec"; + + PREPARE (pg, + "select_kyc_accounts_inc", + "SELECT" + " kt.kyc_target_serial_id" + ",kt.h_normalized_payto AS h_payto" + ",kt.open_time" + ",kt.close_time" + ",lo.jproperties ->> 'FILE_NOTE' AS comments" + ",lo.jproperties ->> 'open_date' AS open_time" + ",COALESCE(lo.to_investigate,FALSE) AS to_investigate" + ",COALESCE(lo.jproperties ->> 'HIGH_RISK_CUSTOMER',FALSE) AS high_risk" + ",wt.payto_uri" + " FROM kyc_targets kt" + " JOIN wire_targets wt" + " ON (lo.h_payto = kt.h_normalized_payto)" + " LEFT JOIN legitimication_outcomes lo" + " USING (lo.h_payto = kt.h_normalized_payto)" + " WHERE (kyc_target_serial_id > $1)" + // select most recent outcomes only + " AND COALESCE (lo.is_active, TRUE)" + " AND ($3 OR (COALESCE(lo.to_investigate,FALSE) == $4))" + // Account is open if we had an AML outcome + " AND ($5 OR ((lo.outcome_serial_id IS NULL) == $6))" + " AND ($7 OR ((COALESCE(lo.jproperties ->>'high_risk',FALSE) == $8))" + " ORDER BY lo.kyc_target_serial_id ASC" + " LIMIT $2"); + PREPARE (pg, + "select_kyc_accounts_dec", + "SELECT" + " kt.kyc_target_serial_id" + ",kt.h_normalized_payto AS h_payto" + ",kt.open_time" + ",kt.close_time" + ",lo.jproperties ->> 'FILE_NOTE' AS comments" + ",lo.jproperties ->> 'open_date' AS open_time" + ",COALESCE(lo.to_investigate,FALSE) AS to_investigate" + ",COALESCE(lo.jproperties ->> 'HIGH_RISK_CUSTOMER',FALSE) AS high_risk" + ",wt.payto_uri" + " FROM kyc_targets kt" + " JOIN wire_targets wt" + " ON (lo.h_payto = kt.h_normalized_payto)" + " LEFT JOIN legitimication_outcomes lo" + " USING (lo.h_payto = kt.h_normalized_payto)" + " WHERE (kyc_target_serial_id < $1)" + // select most recent outcomes only + " AND COALESCE (lo.is_active, TRUE)" + " AND ($3 OR (COALESCE(lo.to_investigate,FALSE) == $4))" + // Account is open if we had an AML outcome + " AND ($5 OR ((lo.outcome_serial_id IS NULL) == $6))" + " AND ($7 OR ((COALESCE(lo.jproperties ->>'high_risk',FALSE) == $8))" + " ORDER BY lo.kyc_target_serial_id DESC" + " LIMIT $2"); + qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn, + stmt, + params, + &handle_kyc_account_result, + &ctx); + if (GNUNET_OK != ctx.status) + return GNUNET_DB_STATUS_HARD_ERROR; + return qs; +} diff --git a/src/exchangedb/pg_select_kyc_accounts.h b/src/exchangedb/pg_select_kyc_accounts.h @@ -0,0 +1,55 @@ +/* + This file is part of TALER + Copyright (C) 2025 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 <http://www.gnu.org/licenses/> + */ +/** + * @file exchangedb/pg_select_kyc_accounts.h + * @brief implementation of the select_kyc_accounts function for Postgres + * @author Christian Grothoff + */ +#ifndef PG_SELECT_KYC_ACCOUNTS_H +#define PG_SELECT_KYC_ACCOUNTS_H + +#include "taler/taler_util.h" +#include "taler/taler_json_lib.h" +#include "taler/taler_exchangedb_plugin.h" + + +/** + * List accounts managed by the exchange (for AML/KYC). + * + * @param cls closure + * @param investigation_only filter by investigation state + * @param open_only filter for only open accounts + * @param high_risk_only filter for only high-risk accounts + * @param offset row to start from + * @param limit how many records to return (negative + * to go back in time, positive to go forward) + * @param cb callback to invoke on each match + * @param cb_cls closure for @a cb + * @return database transaction status + */ +enum GNUNET_DB_QueryStatus +TEH_PG_select_kyc_accounts ( + void *cls, + enum TALER_EXCHANGE_YesNoAll investigation_only, + enum TALER_EXCHANGE_YesNoAll open_only, + enum TALER_EXCHANGE_YesNoAll high_risk_only, + uint64_t offset, + int64_t limit, + TALER_EXCHANGEDB_AmlAccountListCallback cb, + void *cb_cls); + + +#endif diff --git a/src/exchangedb/pg_trigger_kyc_rule_for_account.c b/src/exchangedb/pg_trigger_kyc_rule_for_account.c @@ -88,7 +88,7 @@ TEH_PG_trigger_kyc_rule_for_account ( " ,out_bad_kyc_auth" " AS bad_kyc_auth" " FROM exchange_do_trigger_kyc_rule_for_account" - "($1, $2, $3, $4, $5, $6, $7, $8, $9);"); + "($1, $2, $3, $4, $5, $6, $7::TEXT::JSONB, $8, $9);"); if (NULL != payto_uri.full_payto) TALER_full_payto_hash (payto_uri, &h_full_payto); diff --git a/src/exchangedb/pg_update_wire.c b/src/exchangedb/pg_update_wire.c @@ -69,8 +69,8 @@ TEH_PG_update_wire ( " SET" " is_active=$2" " ,conversion_url=$3" - " ,debit_restrictions=$4" - " ,credit_restrictions=$5" + " ,debit_restrictions=$4::TEXT::JSONB" + " ,credit_restrictions=$5::TEXT::JSONB" " ,last_change=$6" " ,master_sig=$7" " ,bank_label=$8" diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c @@ -189,6 +189,7 @@ #include "pg_select_exchange_credit_transfers.h" #include "pg_select_exchange_debit_transfers.h" #include "pg_select_exchange_kycauth_transfers.h" +#include "pg_select_kyc_accounts.h" #include "pg_select_kyc_attributes.h" #include "pg_select_merge_amounts_for_kyc_check.h" #include "pg_select_purse.h" @@ -708,6 +709,8 @@ libtaler_plugin_exchangedb_postgres_init (void *cls) = &TEH_PG_insert_auditor_denom_sig; plugin->select_auditor_denom_sig = &TEH_PG_select_auditor_denom_sig; + plugin->select_kyc_accounts + = &TEH_PG_select_kyc_accounts; plugin->add_denomination_key = &TEH_PG_add_denomination_key; plugin->lookup_signing_key diff --git a/src/include/taler/taler_exchangedb_plugin.h b/src/include/taler/taler_exchangedb_plugin.h @@ -276,6 +276,8 @@ struct TALER_EXCHANGEDB_SignkeyMetaData /** * Enumeration of all of the tables replicated by exchange-auditor * database replication. + * + * Note: wire_accounts is not replicated. So far not needed by the auditor. */ enum TALER_EXCHANGEDB_ReplicatedTable { @@ -3867,6 +3869,34 @@ typedef void /** + * Return account summary information. + * + * @param cls closure + * @param row_id current row in AML status table + * @param h_payto account for which the attribute data is stored + * @param open_time when was the account opened formally, + * GNUNET_TIME_UNIT_FOREVER_TS if it was never opened + * @param close_time when was the account formally closed, + * GNUNET_TIME_UNIT_ZERO_TS if it was never closed + * @param comments comments on the account + * @param high_risk is this a high-risk business relationship + * @param to_investigate TRUE if this account should be investigated + * @param payto the payto URI of the account + */ +typedef void +(*TALER_EXCHANGEDB_AmlAccountListCallback)( + void *cls, + uint64_t row_id, + const struct TALER_NormalizedPaytoHashP *h_payto, + struct GNUNET_TIME_Timestamp open_time, + struct GNUNET_TIME_Timestamp close_time, + const char *comments, + bool high_risk, + bool to_investigate, + struct TALER_FullPayto payto); + + +/** * Return AML attribute information. * * @param cls closure @@ -7681,6 +7711,32 @@ struct TALER_EXCHANGEDB_Plugin /** + * List accounts managed by the exchange (for AML/KYC). + * + * @param cls closure + * @param investigation_only filter by investigation state + * @param open_only filter for only open accounts + * @param high_risk_only filter for only high-risk accounts + * @param offset row to start from + * @param limit how many records to return (negative + * to go back in time, positive to go forward) + * @param cb callback to invoke on each match + * @param cb_cls closure for @a cb + * @return database transaction status + */ + enum GNUNET_DB_QueryStatus + (*select_kyc_accounts)( + void *cls, + enum TALER_EXCHANGE_YesNoAll investigation_only, + enum TALER_EXCHANGE_YesNoAll open_only, + enum TALER_EXCHANGE_YesNoAll high_risk_only, + uint64_t offset, + int64_t limit, + TALER_EXCHANGEDB_AmlAccountListCallback cb, + void *cb_cls); + + + /** * Lookup AML attributes of a particular account. * * @param cls closure diff --git a/src/lib/exchange_api_handle.c b/src/lib/exchange_api_handle.c @@ -40,12 +40,12 @@ * Which version of the Taler protocol is implemented * by this library? Used to determine compatibility. */ -#define EXCHANGE_PROTOCOL_CURRENT 30 +#define EXCHANGE_PROTOCOL_CURRENT 31 /** * How many versions are we backwards compatible with? */ -#define EXCHANGE_PROTOCOL_AGE 4 +#define EXCHANGE_PROTOCOL_AGE 5 /** * Set to 1 for extra debug logging.