View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0011446Talerdeployment and operationspublic2026-05-27 19:342026-05-29 20:27
Reportervecirex Assigned Tovecirex  
PriorityurgentSeverityblockReproducibilityhave not tried
Status feedbackResolutionopen 
Summary0011446: Test KYC Auth reset via SQL
DescriptionDirectly related to 0011428, trying on TOPS/Stage if this works, i.e., doesn't create additional issues or even ends up w/ dysfunctional instances.

Three types of merchants shall be considered:

* kycauth_1_pending: merchant which entered an IBAN, but did not yet pay
* kycauth_2_kyc_ok: as above, but transfer fulfilled; exchange acceptance gotten
* kycauth_3_kyc_ok_and_withdraw: dito as w/ kycauth_2*, but additionally the merchant also acts as wallet user and withdrew money from the exchange he verified against as merchant, and doing so to the *very same IBAN* which was used as merchant (for IBAN settlements from paid orders)
TagsNo tags attached.

Activities

vecirex

2026-05-29 19:08

manager   ~0028733

Last edited: 2026-05-29 19:09

 Following situation was created on TOPS/CHF stage (cf. https://docs.taler.net/deployments/tops-stage-devtesting.html):

sudo -u taler-merchant-httpd psql taler-merchant -c "
SET search_path TO merchant;
SELECT 
    mi.merchant_id,
    mi.merchant_name AS business_name,
    k.kyc_ok,
    to_timestamp(k.kyc_timestamp / 1000000) AT TIME ZONE 'Europe/Zurich' AS timestamp_cest,                                      
    k.access_token                                  
FROM merchant_kyc k                                              
JOIN merchant_accounts ma ON k.account_serial = ma.account_serial
JOIN merchant_instances mi USING (merchant_serial)
WHERE mi.merchant_id ILIKE 'kyc%'
ORDER BY mi.merchant_id;
" 2>&1 | cat
SET
          merchant_id           |   business_name    | kyc_ok |   timestamp_cest    |                            access_token                            
--------------------------------+--------------------+--------+---------------------+--------------------------------------------------------------------
 kycauth_1_pending              | KYC Auth 1 Pending | f      | 2026-05-29 18:43:58 | 
 kycauth_1_pending              | KYC Auth 1 Pending | f      | 2026-05-29 18:43:58 | 
 kycauth_2_kyc_ok               | KYC Auth 2 KYC OK  | t      | 2026-05-29 18:47:22 | \xb0e861480d1aac501c2dccee4a8e4a24961434230017bc133f2ee30100b1fa87
 kycauth_2_kyc_ok               | KYC Auth 2 KYC OK  | f      | 2026-05-29 18:43:58 | 
 kycauth_3_kyc_ok_with_withdraw | Tammy Tester       | t      | 2026-05-29 18:55:00 | \x036cbe036d4937c0f963ce76b98b72d93bf00cd47f9fff7a41b8e695ffd6463f
 kycauth_3_kyc_ok_with_withdraw | Tammy Tester       | f      | 2026-05-29 18:52:28 | 
(6 rows)

vecirex

2026-05-29 19:45

manager   ~0028734

Last edited: 2026-05-29 20:27

 0. Backup DB, even if it's "only" stage

sudo -u postgres pg_dumpall > /root/backups/postgres-backup-$EPOCHSECONDS.sql
sudo -u taler-merchant-httpd pg_dump taler-merchant > /root/backups/taler-merchant-backup-$EPOCHSECONDS.sql


1. Test for kycauth_1_pending

1a. DB situation w/ relevant KYC fields found:

sudo -u taler-merchant-httpd psql taler-merchant -c "
SET search_path TO merchant;
SELECT 
    mi.merchant_id,
    mi.merchant_name AS business_name,
    k.kyc_ok,
    k.kyc_timestamp,
    to_timestamp(k.kyc_timestamp / 1000000) AT TIME ZONE 'Europe/Zurich' AS timestamp_cest, 
    k.access_token,
    k.exchange_url,
    k.exchange_http_status,
    k.exchange_ec_code,
    k.next_kyc_poll,
    k.kyc_backoff
FROM merchant_kyc k
JOIN merchant_accounts ma ON k.account_serial = ma.account_serial
JOIN merchant_instances mi USING (merchant_serial)
WHERE mi.merchant_id = 'kycauth_1_pending';
" 2>&1 | cat
SET
    merchant_id    |   business_name    | kyc_ok |  kyc_timestamp   |   timestamp_cest    | access_token |             exchange_url             | exchange_http_status | exchange_ec_code |  next_kyc_poll   | kyc_backoff 
-------------------+--------------------+--------+------------------+---------------------+--------------+--------------------------------------+----------------------+------------------+------------------+-------------
 kycauth_1_pending | KYC Auth 1 Pending | f      | 1780073038000000 | 2026-05-29 18:43:58 |              | https://exchange.stage.taler-ops.ch/ |                    0 |               10 | 1780074838551502 |  1800000000
 kycauth_1_pending | KYC Auth 1 Pending | f      | 1780073038000000 | 2026-05-29 18:43:58 |              | https://exchange.taler-ops.ch/       |                    0 |               10 | 1780074838550415 |  1800000000
(2 rows)


1b. Tentative KYC Reset

sudo -u taler-merchant-httpd psql taler-merchant -c "
SET search_path TO merchant;
BEGIN;

UPDATE merchant_kyc
SET 
    access_token = NULL,
    kyc_ok = FALSE,
    kyc_timestamp = 0,
    exchange_http_status = 0,
    exchange_ec_code = 0,
    next_kyc_poll = 0,
    kyc_backoff = 0
WHERE account_serial IN (
    SELECT ma.account_serial 
    FROM merchant_accounts ma
    JOIN merchant_instances mi USING (merchant_serial)
    WHERE mi.merchant_id = 'kycauth_1_pending'
);

COMMIT;

SELECT 'KYC reset completed for instance:' AS status, 'kycauth_1_pending' AS instance" 2>&1 | cat
SET
BEGIN
UPDATE 2
COMMIT
              status               |     instance      
-----------------------------------+-------------------
 KYC reset completed for instance: | kycauth_1_pending
(1 row)


1c. Restart of taler-merchant-httpd, and check if it's running:

systemctl restart taler-merchant-httpd
systemctl --failed
  UNIT LOAD ACTIVE SUB DESCRIPTION

0 loaded units listed.


1d. Check DB entries

Looks fine, most notably time is changed, that is, including next KYC poll attempt, shown in Zurich / CEST time:

sudo -u taler-merchant-httpd psql taler-merchant -c "
SET search_path TO merchant;
SELECT 
    mi.merchant_id,
    mi.merchant_name AS business_name,
    k.kyc_ok,
    k.kyc_timestamp,
    to_timestamp(k.kyc_timestamp / 1000000) AT TIME ZONE 'Europe/Zurich' AS timestamp_cest,
    k.access_token,
    k.exchange_url,
    k.exchange_http_status,
    k.exchange_ec_code,
    to_timestamp(k.next_kyc_poll / 1000000) AT TIME ZONE 'Europe/Zurich' AS timestamp_kyc_poll_cest, 
    k.kyc_backoff
FROM merchant_kyc k                                              
JOIN merchant_accounts ma ON k.account_serial = ma.account_serial
JOIN merchant_instances mi USING (merchant_serial)
WHERE mi.merchant_id = 'kycauth_1_pending';
" 2>&1 | cat
SET
    merchant_id    |   business_name    | kyc_ok |  kyc_timestamp   |   timestamp_cest    | access_token |             exchange_url             | exchange_http_status | exchange_ec_code | timestamp_kyc_poll_cest | kyc_backoff 
-------------------+--------------------+--------+------------------+---------------------+--------------+--------------------------------------+----------------------+------------------+-------------------------+-------------
 kycauth_1_pending | KYC Auth 1 Pending | f      | 1780076639000000 | 2026-05-29 19:43:59 |              | https://exchange.stage.taler-ops.ch/ |                    0 |               10 | 2026-05-29 19:46:23     |  1800000000
 kycauth_1_pending | KYC Auth 1 Pending | f      | 1780076639000000 | 2026-05-29 19:43:59 |              | https://exchange.taler-ops.ch/       |                    0 |               10 | 2026-05-29 19:46:23     |  1800000000
(2 rows)


1e. Check merchant portal

Nothing specially noted, KYC still shown as pending, also KYC:* string w/ public key for wire transfer remains stable.

vecirex

2026-05-29 20:00

manager   ~0028735

Last edited: 2026-05-29 20:27

 2. Test for kycauth_2_kyc_ok

2a. DB situation w/ relevant KYC fields found

Note that for the exchange case where KYC is done, there's no real next KYC poll time in the near future; both timestamps are equal:

sudo -u taler-merchant-httpd psql taler-merchant -c "
SET search_path TO merchant;
SELECT 
    mi.merchant_id,
    mi.merchant_name AS business_name,
    k.kyc_ok,
    k.kyc_timestamp,
    to_timestamp(k.kyc_timestamp / 1000000) AT TIME ZONE 'Europe/Zurich' AS timestamp_cest,
    k.access_token,
    k.exchange_url,
    k.exchange_http_status,
    k.exchange_ec_code,
    to_timestamp(k.next_kyc_poll / 1000000) AT TIME ZONE 'Europe/Zurich' AS timestamp_kyc_poll_cest,
    k.kyc_backoff
FROM merchant_kyc k                                              
JOIN merchant_accounts ma ON k.account_serial = ma.account_serial
JOIN merchant_instances mi USING (merchant_serial)
WHERE mi.merchant_id = 'kycauth_2_kyc_ok';
" 2>&1 | cat
SET
   merchant_id    |   business_name   | kyc_ok |  kyc_timestamp   |   timestamp_cest    |                            access_token                            |             exchange_url             | exchange_http_status | exchange_ec_code | timestamp_kyc_poll_cest | kyc_backoff 
------------------+-------------------+--------+------------------+---------------------+--------------------------------------------------------------------+--------------------------------------+----------------------+------------------+-------------------------+-------------
 kycauth_2_kyc_ok | KYC Auth 2 KYC OK | t      | 1780076785000000 | 2026-05-29 19:46:25 | \xb0e861480d1aac501c2dccee4a8e4a24961434230017bc133f2ee30100b1fa87 | https://exchange.stage.taler-ops.ch/ |                  200 |                0 | 2026-05-29 19:46:25     |    60000000
 kycauth_2_kyc_ok | KYC Auth 2 KYC OK | f      | 1780076639000000 | 2026-05-29 19:43:59 |                                                                    | https://exchange.taler-ops.ch/       |                    0 |               10 | 2026-05-29 19:49:48     |   348885239
(2 rows)


2b. Tentative KYC Reset

sudo -u taler-merchant-httpd psql taler-merchant -c "
SET search_path TO merchant;
BEGIN;

UPDATE merchant_kyc
SET 
    access_token = NULL,
    kyc_ok = FALSE,
    kyc_timestamp = 0,
    exchange_http_status = 0,
    exchange_ec_code = 0,
    next_kyc_poll = 0,
    kyc_backoff = 0
WHERE account_serial IN (
    SELECT ma.account_serial 
    FROM merchant_accounts ma
    JOIN merchant_instances mi USING (merchant_serial)
    WHERE mi.merchant_id = 'kycauth_2_kyc_ok'
);

COMMIT;

SELECT 'KYC reset completed for instance:' AS status, 'kycauth_2_kyc_ok' AS instance" 2>&1 | cat
SET
BEGIN
UPDATE 2
COMMIT
              status               |     instance     
-----------------------------------+------------------
 KYC reset completed for instance: | kycauth_2_kyc_ok
(1 row)


2c. Restart of taler-merchant-httpd, and check if it's running

Looks fine:

systemctl restart taler-merchant-httpd
systemctl --failed
  UNIT LOAD ACTIVE SUB DESCRIPTION

0 loaded units listed.


2d. Check DB entries

At first, doesn't look very promising, w/ timestamps equally everywhere at UNIX time zero:

sudo -u taler-merchant-httpd psql taler-merchant -c "
SET search_path TO merchant;
SELECT 
    mi.merchant_id,
    mi.merchant_name AS business_name,
    k.kyc_ok,
    k.kyc_timestamp,
    to_timestamp(k.kyc_timestamp / 1000000) AT TIME ZONE 'Europe/Zurich' AS timestamp_cest,
    k.access_token,
    k.exchange_url,
    k.exchange_http_status,
    k.exchange_ec_code,
    to_timestamp(k.next_kyc_poll / 1000000) AT TIME ZONE 'Europe/Zurich' AS timestamp_kyc_poll_cest,
    k.kyc_backoff
FROM merchant_kyc k
JOIN merchant_accounts ma ON k.account_serial = ma.account_serial
JOIN merchant_instances mi USING (merchant_serial)
WHERE mi.merchant_id = 'kycauth_2_kyc_ok';
" 2>&1 | cat
SET
   merchant_id    |   business_name   | kyc_ok | kyc_timestamp |   timestamp_cest    | access_token |             exchange_url             | exchange_http_status | exchange_ec_code | timestamp_kyc_poll_cest | kyc_backoff 
------------------+-------------------+--------+---------------+---------------------+--------------+--------------------------------------+----------------------+------------------+-------------------------+-------------
 kycauth_2_kyc_ok | KYC Auth 2 KYC OK | f      |             0 | 1970-01-01 01:00:00 |              | https://exchange.stage.taler-ops.ch/ |                    0 |                0 | 1970-01-01 01:00:00     |           0
 kycauth_2_kyc_ok | KYC Auth 2 KYC OK | f      |             0 | 1970-01-01 01:00:00 |              | https://exchange.taler-ops.ch/       |                    0 |                0 | 1970-01-01 01:00:00     |           0
(2 rows)


In about a minute after, however, situation stabilizes and KYC OK status is indeed reflected again:

sudo -u taler-merchant-httpd psql taler-merchant -c "
SET search_path TO merchant;
SELECT 
    mi.merchant_id,
    mi.merchant_name AS business_name,
    k.kyc_ok,
    k.kyc_timestamp,
    to_timestamp(k.kyc_timestamp / 1000000) AT TIME ZONE 'Europe/Zurich' AS timestamp_cest,
    k.access_token,
    k.exchange_url,
    k.exchange_http_status,
    k.exchange_ec_code,
    to_timestamp(k.next_kyc_poll / 1000000) AT TIME ZONE 'Europe/Zurich' AS timestamp_kyc_poll_cest,
    k.kyc_backoff
FROM merchant_kyc k
JOIN merchant_accounts ma ON k.account_serial = ma.account_serial
JOIN merchant_instances mi USING (merchant_serial)
WHERE mi.merchant_id = 'kycauth_2_kyc_ok';
" 2>&1 | cat
SET
   merchant_id    |   business_name   | kyc_ok |  kyc_timestamp   |   timestamp_cest    |                            access_token                            |             exchange_url             | exchange_http_status | exchange_ec_code | timestamp_kyc_poll_cest | kyc_backoff 
------------------+-------------------+--------+------------------+---------------------+--------------------------------------------------------------------+--------------------------------------+----------------------+------------------+-------------------------+-------------
 kycauth_2_kyc_ok | KYC Auth 2 KYC OK | t      | 1780078443000000 | 2026-05-29 20:14:03 | \xb0e861480d1aac501c2dccee4a8e4a24961434230017bc133f2ee30100b1fa87 | https://exchange.stage.taler-ops.ch/ |                  200 |                0 | 2026-05-29 20:16:24     |    60000000
 kycauth_2_kyc_ok | KYC Auth 2 KYC OK | f      |                0 | 1970-01-01 01:00:00 |                                                                    | https://exchange.taler-ops.ch/       |                    0 |                0 | 2026-05-29 20:16:24     |           0
(2 rows)


From a cosmetic perspective or to keep more up w/ reality, first timestamp could be set to current time instead of going towards "year zero":

2e. Check merchant portal

KYC situation looks fine / the same as before.

Issue History

Date Modified Username Field Change
2026-05-27 19:34 vecirex New Issue
2026-05-27 19:34 vecirex Description Updated
2026-05-27 19:35 vecirex Assigned To => vecirex
2026-05-27 19:35 vecirex Status new => assigned
2026-05-27 19:38 vecirex Description Updated
2026-05-27 19:38 vecirex Description Updated
2026-05-29 19:08 vecirex Note Added: 0028733
2026-05-29 19:09 vecirex Note Edited: 0028733
2026-05-29 19:10 vecirex Steps to Reproduce Updated
2026-05-29 19:10 vecirex Additional Information Updated
2026-05-29 19:45 vecirex Note Added: 0028734
2026-05-29 19:56 vecirex Note Edited: 0028734
2026-05-29 19:57 vecirex Note Edited: 0028734
2026-05-29 19:57 vecirex Status assigned => feedback
2026-05-29 20:00 vecirex Note Added: 0028735
2026-05-29 20:00 vecirex Note Edited: 0028735
2026-05-29 20:00 vecirex Note Edited: 0028734
2026-05-29 20:08 vecirex Note Edited: 0028735
2026-05-29 20:12 vecirex Note Edited: 0028735
2026-05-29 20:22 vecirex Note Edited: 0028735
2026-05-29 20:27 vecirex Note Edited: 0028735
2026-05-29 20:27 vecirex Note Edited: 0028734