-first pass at new KYC DB API

3 files changed, 502 insertions, 4 deletions

diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql
index 7ec6ce6a9..ae5d452a2 100644
--- a/src/exchangedb/common-0001.sql
+++ b/src/exchangedb/common-0001.sql
@@ -56,8 +56,8 @@ BEGIN
       '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
       ',wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)'
       ',payto_uri VARCHAR NOT NULL'
-      ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)'
-      ',external_id VARCHAR'
+      ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)' -- FIXME: REMOVE!
+      ',external_id VARCHAR' -- FIXME: REMOVE!
     ') %s ;'
     ,'wire_targets'
     ,'PARTITION BY HASH (wire_target_h_payto)'
@@ -85,6 +85,65 @@ BEGIN
 END
 $$;
 
+
+----------------------- legitimizations ---------------------------
+
+CREATE OR REPLACE FUNCTION create_table_legitimizations(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(legitimization_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=64)'
+      ',expiration_time INT8 NOT NULL DEFAULT (0)'
+      ',provider_section VARCHAR NOT NULL'
+      ',provider_user_id VARCHAR DEFAULT NULL'
+      ',provider_legitimization_id VARCHAR DEFAULT NULL'
+    ') %s ;'
+    ,'legitimizations'
+    ,'PARTITION BY HASH (h_payto)'
+    ,shard_suffix
+  );
+
+END
+$$;
+
+-- We need a separate function for this, as we call create_table only once but need to add
+-- those constraints to each partition which gets created
+CREATE OR REPLACE FUNCTION add_constraints_to_legitimizations_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  partition_name VARCHAR;
+BEGIN
+
+  partition_name = concat_ws('_', 'legitimizations', partition_suffix);
+
+  EXECUTE FORMAT (
+    'ALTER TABLE ' || partition_name
+    || ' '
+      'ADD CONSTRAINT ' || partition_name || '_legitimization_serial_id_key '
+        'UNIQUE (legitimization_serial_id)');
+  EXECUTE FORMAT (
+    'CREATE INDEX IF NOT EXISTS ' || partition_name || '_by_provider_and_legi_index '
+        'ON '|| partition_name || ' '
+        '(provider_section,provider_legitimization_id)'
+  );
+  EXECUTE FORMAT (
+    'COMMENT ON INDEX ' || partition_name || '_by_provider_and_legi_index '
+    'IS ' || quote_literal('used (rarely) in kyc_provider_account_lookup') || ';'
+  );
+END
+$$;
+
 ------------------------ reserves -------------------------------
 
 CREATE OR REPLACE FUNCTION create_table_reserves(
diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql
index 97f5829e6..efff3874c 100644
--- a/src/exchangedb/exchange-0001-part.sql
+++ b/src/exchangedb/exchange-0001-part.sql
@@ -104,8 +104,10 @@ COMMENT ON COLUMN wire_targets.payto_uri
   IS 'Can be a regular bank account, or also be a URI identifying a reserve-account (for P2P payments)';
 COMMENT ON COLUMN wire_targets.wire_target_h_payto
   IS 'Unsalted hash of payto_uri';
+-- FIXME: remove:
 COMMENT ON COLUMN wire_targets.kyc_ok
   IS 'true if the KYC check was passed successfully';
+-- FIXME: remove:
 COMMENT ON COLUMN wire_targets.external_id
   IS 'Name of the user that was used for OAuth 2.0-based legitimization';
 
@@ -115,6 +117,34 @@ CREATE TABLE IF NOT EXISTS wire_targets_default
 
 SELECT add_constraints_to_wire_targets_partition('default');
 
+
+-- ------------------------------ legitimizations ----------------------------------------
+
+SELECT create_table_legitimizations();
+
+COMMENT ON TABLE legitimizations
+  IS 'List of legitimizations (required and completed) by account and provider';
+COMMENT ON COLUMN legitimizations.legitimization_serial_id
+  IS 'unique ID for this legitimization process at the exchange';
+COMMENT ON COLUMN legitimizations.h_payto
+  IS 'foreign key linking the entry to the wire_targets table, NOT a primary key (multiple legitimizations are possible per wire target)';
+COMMENT ON COLUMN legitimizations.expiration_time
+  IS 'in the future if the respective KYC check was passed successfully';
+COMMENT ON COLUMN legitimizations.provider_section
+  IS 'Configuration file section with details about this provider';
+COMMENT ON COLUMN legitimizations.provider_user_id
+  IS 'Identifier for the user at the provider that was used for the legitimization. NULL if provider is unaware.';
+COMMENT ON COLUMN legitimizations.provider_legitimization_id
+  IS 'Identifier for the specific legitimization process at the provider. NULL if legitimization was not started.';
+
+CREATE TABLE IF NOT EXISTS legitimizations_default
+  PARTITION OF legitimizations
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_legitimizations_partition('default');
+
+
+
 -- ------------------------------ reserves ----------------------------------------
 
 SELECT create_table_reserves();
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c
index f08c1184a..6ada10ca3 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -4515,7 +4515,70 @@ prepare_statements (struct PostgresClosure *pg)
       " FROM exchange_do_close_request"
       "  ($1, $2, $3)",
       3),
-
+    /* Used in #postgres_insert_kyc_requirement_for_account() */
+    GNUNET_PQ_make_prepare (
+      "insert_legitimization_requirement",
+      "INSERT INTO legitimizations"
+      "  (h_payto"
+      "  ,provider_section"
+      "  ) VALUES "
+      "  ($1, $2)"
+      " RETURNING legitimization_serial_id",
+      2),
+    /* Used in #postgres_update_kyc_requirement_by_row() */
+    GNUNET_PQ_make_prepare (
+      "update_legitimization_requirement",
+      "UPDATE legitimizations"
+      " SET provider_user_id=$4"
+      "    ,provider_legitimization_id=$5"
+      "    ,expiration_time=$6"
+      " WHERE"
+      "      h_payto=$3"
+      "  AND legitimization_serial_id=$1"
+      "  AND provider_section=$2;",
+      6),
+    /* Used in #postgres_lookup_kyc_requirement_by_row() */
+    GNUNET_PQ_make_prepare (
+      "lookup_legitimization_by_row",
+      "SELECT "
+      " provider_section"
+      ",h_payto"
+      ",expiration_time"
+      ",provider_user_id"
+      ",provider_legitimization_id"
+      " FROM legitimizations"
+      " WHERE legitimization_serial_id=$1;",
+      1),
+    /* Used in #postgres_lookup_kyc_requirement_by_account() */
+    GNUNET_PQ_make_prepare (
+      "lookup_legitimization_by_account",
+      "SELECT "
+      " legitimization_serial_id"
+      ",expiration_time"
+      ",provider_user_id"
+      ",provider_legitimization_id"
+      " FROM legitimizations"
+      " WHERE h_payto=$1"
+      "   AND provider_section=$2;",
+      2),
+    /* Used in #postgres_kyc_provider_account_lookup() */
+    GNUNET_PQ_make_prepare (
+      "get_wire_target_by_legitimization_id",
+      "SELECT "
+      " h_payto"
+      " FROM legitimizations"
+      " WHERE provider_legitimization_id=$1"
+      "   AND provider_section=$2;",
+      2),
+    /* Used in #postgres_select_satisfied_kyc_processes() */
+    GNUNET_PQ_make_prepare (
+      "get_satisfied_legitimizations",
+      "SELECT "
+      " provider_section"
+      " FROM legitimizations"
+      " WHERE h_payto=$1"
+      "   AND expiration_time>=$2;",
+      2),
     GNUNET_PQ_PREPARED_STATEMENT_END
   };
 
@@ -13511,7 +13574,7 @@ struct GetWireFeesContext
 /**
  * Invoke the callback for each result.
  *
- * @param cls a `struct MissingWireContext *`
+ * @param cls a `struct GetWireFeesContext *`
  * @param result SQL result
  * @param num_results number of rows in @a result
  */
@@ -16413,6 +16476,340 @@ postgres_profit_drains_set_finished (
 
 
 /**
+ * Insert KYC requirement for @a h_payto account into table.
+ *
+ * @param cls closure
+ * @param provider_section provider that must be checked
+ * @param h_payto account that must be KYC'ed
+ * @param[out] legi_row set to legitimization row for this check
+ * @return database transaction status
+ */
+static enum GNUNET_DB_QueryStatus
+postgres_insert_kyc_requirement_for_account (
+  void *cls,
+  const char *provider_section,
+  const struct TALER_PaytoHashP *h_payto,
+  uint64_t *legi_row)
+{
+  struct PostgresClosure *pg = cls;
+  struct GNUNET_PQ_QueryParam params[] = {
+    GNUNET_PQ_query_param_auto_from_type (h_payto),
+    GNUNET_PQ_query_param_string (provider_section),
+    GNUNET_PQ_query_param_end
+  };
+  struct GNUNET_PQ_ResultSpec rs[] = {
+    GNUNET_PQ_result_spec_uint64 ("legitimization_serial_id",
+                                  legi_row),
+    GNUNET_PQ_result_spec_end
+  };
+
+  return GNUNET_PQ_eval_prepared_singleton_select (
+    pg->conn,
+    "insert_legitimization_requirement",
+    params,
+    rs);
+}
+
+
+/**
+ * Update KYC requirement check with provider-linkage and/or
+ * expiration data.
+ *
+ * @param cls closure
+ * @param provider_section provider that must be checked
+ * @param h_payto account that must be KYC'ed
+ * @param provider_account_id provider account ID
+ * @param provider_legitimization_id provider legitimization ID
+ * @param expiration how long is this KYC check set to be valid (in the past if invalid)
+ * @return database transaction status
+ */
+static enum GNUNET_DB_QueryStatus
+postgres_update_kyc_requirement_by_row (
+  void *cls,
+  uint64_t legi_row,
+  const char *provider_section,
+  struct TALER_PaytoHashP *h_payto,
+  const char *provider_account_id,
+  const char *provider_legitimization_id,
+  struct GNUNET_TIME_Absolute expiration)
+{
+  struct PostgresClosure *pg = cls;
+  struct GNUNET_PQ_QueryParam params[] = {
+    GNUNET_PQ_query_param_uint64 (&legi_row),
+    GNUNET_PQ_query_param_string (provider_section),
+    GNUNET_PQ_query_param_auto_from_type (h_payto),
+    GNUNET_PQ_query_param_string (provider_account_id),
+    GNUNET_PQ_query_param_string (provider_legitimization_id),
+    GNUNET_PQ_query_param_absolute_time (&expiration),
+    GNUNET_PQ_query_param_end
+  };
+
+  return GNUNET_PQ_eval_prepared_non_select (
+    pg->conn,
+    "update_legitimization_requirement",
+    params);
+}
+
+
+/**
+ * Lookup KYC provider meta data.
+ *
+ * @param cls closure
+ * @param legi_row legitimization row to lookup
+ * @param[out] provider_section provider that must be checked
+ * @param[out] h_payto account that must be KYC'ed
+ * @param[out] expiration how long is this KYC check set to be valid (in the past if invalid)
+ * @param[out] provider_account_id provider account ID
+ * @param[out] provider_legitimization_id provider legitimization ID
+ * @return database transaction status
+ */
+static enum GNUNET_DB_QueryStatus
+postgres_lookup_kyc_requirement_by_row (
+  void *cls,
+  uint64_t legi_row,
+  char **provider_section,
+  struct TALER_PaytoHashP *h_payto,
+  struct GNUNET_TIME_Absolute *expiration,
+  char **provider_account_id,
+  char **provider_legitimization_id)
+{
+  struct PostgresClosure *pg = cls;
+  struct GNUNET_PQ_QueryParam params[] = {
+    GNUNET_PQ_query_param_uint64 (&legi_row),
+    GNUNET_PQ_query_param_end
+  };
+  struct GNUNET_PQ_ResultSpec rs[] = {
+    GNUNET_PQ_result_spec_string ("provider_section",
+                                  provider_section),
+    GNUNET_PQ_result_spec_auto_from_type ("h_payto",
+                                          h_payto),
+    GNUNET_PQ_result_spec_absolute_time ("expiration_time",
+                                         expiration),
+    GNUNET_PQ_result_spec_allow_null (
+      GNUNET_PQ_result_spec_string ("provider_user_id",
+                                    provider_account_id),
+      NULL),
+    GNUNET_PQ_result_spec_allow_null (
+      GNUNET_PQ_result_spec_string ("provider_legitimization_id",
+                                    provider_legitimization_id),
+      NULL),
+    GNUNET_PQ_result_spec_end
+  };
+
+  *provider_account_id = NULL;
+  *provider_legitimization_id = NULL;
+  return GNUNET_PQ_eval_prepared_singleton_select (
+    pg->conn,
+    "lookup_legitimization_by_row",
+    params,
+    rs);
+}
+
+
+/**
+ * Lookup KYC provider meta data.
+ *
+ * @param cls closure
+ * @param provider_section provider that must be checked
+ * @param h_payto account that must be KYC'ed
+ * @param[out] legi_row row with the legitimization data
+ * @param[out] expiration how long is this KYC check set to be valid (in the past if invalid)
+ * @param[out] provider_account_id provider account ID
+ * @param[out] provider_legitimization_id provider legitimization ID
+ * @return database transaction status
+ */
+static enum GNUNET_DB_QueryStatus
+postgres_lookup_kyc_requirement_by_account (
+  void *cls,
+  const char *provider_section,
+  const struct TALER_PaytoHashP *h_payto,
+  uint64_t *legi_row,
+  struct GNUNET_TIME_Absolute *expiration,
+  char **provider_account_id,
+  char **provider_legitimization_id)
+{
+  struct PostgresClosure *pg = cls;
+  struct GNUNET_PQ_QueryParam params[] = {
+    GNUNET_PQ_query_param_auto_from_type (h_payto),
+    GNUNET_PQ_query_param_string (provider_section),
+    GNUNET_PQ_query_param_end
+  };
+  struct GNUNET_PQ_ResultSpec rs[] = {
+    GNUNET_PQ_result_spec_uint64 ("legitimization_serial_id",
+                                  legi_row),
+    GNUNET_PQ_result_spec_absolute_time ("expiration_time",
+                                         expiration),
+    GNUNET_PQ_result_spec_allow_null (
+      GNUNET_PQ_result_spec_string ("provider_user_id",
+                                    provider_account_id),
+      NULL),
+    GNUNET_PQ_result_spec_allow_null (
+      GNUNET_PQ_result_spec_string ("provider_legitimization_id",
+                                    provider_legitimization_id),
+      NULL),
+    GNUNET_PQ_result_spec_end
+  };
+
+  *provider_account_id = NULL;
+  *provider_legitimization_id = NULL;
+  return GNUNET_PQ_eval_prepared_singleton_select (
+    pg->conn,
+    "lookup_legitimization_by_account",
+    params,
+    rs);
+}
+
+
+/**
+ * Lookup an
+ * @a h_payto by @a provider_legitimization_id.
+ *
+ * @param cls closure
+ * @param provider_section
+ * @param provider_legitimization_id legi to look up
+ * @param[out] h_payto where to write the result
+ * @return database transaction status
+ */
+static enum GNUNET_DB_QueryStatus
+postgres_kyc_provider_account_lookup (
+  void *cls,
+  const char *provider_section,
+  const char *provider_legitimization_id,
+  struct TALER_PaytoHashP *h_payto)
+{
+  struct PostgresClosure *pg = cls;
+  struct GNUNET_PQ_QueryParam params[] = {
+    GNUNET_PQ_query_param_string (provider_section),
+    GNUNET_PQ_query_param_string (provider_legitimization_id),
+    GNUNET_PQ_query_param_end
+  };
+  struct GNUNET_PQ_ResultSpec rs[] = {
+    GNUNET_PQ_result_spec_auto_from_type ("h_payto",
+                                          h_payto),
+    GNUNET_PQ_result_spec_end
+  };
+
+  return GNUNET_PQ_eval_prepared_singleton_select (
+    pg->conn,
+    "get_wire_target_by_legitimization_id",
+    params,
+    rs);
+}
+
+
+/**
+ * Closure for #get_wire_fees_cb().
+ */
+struct GetLegitimizationsContext
+{
+  /**
+   * Function to call per result.
+   */
+  TALER_EXCHANGEDB_SatisfiedProviderCallback cb;
+
+  /**
+   * Closure for @e cb.
+   */
+  void *cb_cls;
+
+  /**
+   * Plugin context.
+   */
+  struct PostgresClosure *pg;
+
+  /**
+   * Flag set to #GNUNET_OK as long as everything is fine.
+   */
+  enum GNUNET_GenericReturnValue status;
+
+};
+
+
+/**
+ * Invoke the callback for each result.
+ *
+ * @param cls a `struct GetLegitimizationsContext *`
+ * @param result SQL result
+ * @param num_results number of rows in @a result
+ */
+static void
+get_legitimizations_cb (void *cls,
+                        PGresult *result,
+                        unsigned int num_results)
+{
+  struct GetLegitimizationsContext *ctx = cls;
+
+  for (unsigned int i = 0; i < num_results; i++)
+  {
+    char *provider_section;
+    struct GNUNET_PQ_ResultSpec rs[] = {
+      GNUNET_PQ_result_spec_string ("provider_section",
+                                    &provider_section),
+      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,
+             provider_section);
+    GNUNET_PQ_cleanup_result (rs);
+  }
+}
+
+
+/**
+ * Call us on KYC processes satisfied for the given
+ * account.
+ *
+ * @param cls the @e cls of this struct with the plugin-specific state
+ * @param h_payto account identifier
+ * @param spc function to call for each satisfied KYC process
+ * @param spc_cls closure for @a spc
+ * @return transaction status code
+ */
+static enum GNUNET_DB_QueryStatus
+postgres_select_satisfied_kyc_processes (
+  void *cls,
+  const struct TALER_PaytoHashP *h_payto,
+  TALER_EXCHANGEDB_SatisfiedProviderCallback spc,
+  void *spc_cls)
+{
+  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 (h_payto),
+    GNUNET_PQ_query_param_absolute_time (&now),
+    GNUNET_PQ_query_param_end
+  };
+  struct GetLegitimizationsContext ctx = {
+    .cb = spc,
+    .cb_cls = spc_cls,
+    .pg = pg,
+    .status = GNUNET_OK
+  };
+  enum GNUNET_DB_QueryStatus qs;
+
+  qs = GNUNET_PQ_eval_prepared_multi_select (
+    pg->conn,
+    "get_satisfied_legitimizations",
+    params,
+    &get_legitimizations_cb,
+    &ctx);
+  if (GNUNET_OK != ctx.status)
+    return GNUNET_DB_STATUS_HARD_ERROR;
+  return qs;
+}
+
+
+/**
  * Initialize Postgres database subsystem.
  *
  * @param cls a configuration instance
@@ -16736,6 +17133,18 @@ libtaler_plugin_exchangedb_postgres_init (void *cls)
     = &postgres_get_drain_profit;
   plugin->profit_drains_set_finished
     = &postgres_profit_drains_set_finished;
+  plugin->insert_kyc_requirement_for_account
+    = &postgres_insert_kyc_requirement_for_account;
+  plugin->update_kyc_requirement_by_row
+    = &postgres_update_kyc_requirement_by_row;
+  plugin->lookup_kyc_requirement_by_row
+    = &postgres_lookup_kyc_requirement_by_row;
+  plugin->lookup_kyc_requirement_by_account
+    = &postgres_lookup_kyc_requirement_by_account;
+  plugin->kyc_provider_account_lookup
+    = &postgres_kyc_provider_account_lookup;
+  plugin->select_satisfied_kyc_processes
+    = &postgres_select_satisfied_kyc_processes;
   return plugin;
 }