libeufin

export-nexus-withdrawals.sh (4774B)

---

 #!/bin/bash
 # This file is part of TALER
 # Copyright (C) 2026 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/>
 
 set -euo pipefail
 
 usage() {
     cat <<'EOF'
 Usage: export-nexus-withdrawals.sh YYYY-MM-dd OUTPUT.csv [PSQL-CONNECTION]
 
 Export Nexus incoming transfers of type 'reserve', from midnight UTC on the
 given date (inclusive). Requires psql, OpenSSL, and PostgreSQL 12 or later.
 PSQL-CONNECTION is a database name or libpq connection string; if omitted,
 psql uses its usual PGDATABASE, PGHOST, PGUSER, etc. environment variables.
 
 Columns: incoming_transaction_id, execution_time_utc, amount, sender_iban_hash.
 Amounts use the currency configured for this Nexus instance (not stored in SQL).
 The hash is SHA-256(random 32-byte salt || uppercase IBAN), truncated to
 128 bits (32 hexadecimal characters). One fresh salt is used for the entire
 export, so senders can be grouped within a file but hashes change between runs.
 The salt is not included in the CSV or saved by the script.
 Names, payment subjects, and raw payto URIs are not exported.
 Unsupported/missing IBANs cause an error instead of silently dropping transfers.
 The output file is replaced only after a successful export, with mode 0600.
 
 Example:
   export-nexus-withdrawals.sh 2026-01-01 withdrawals.csv 'dbname=libeufin-nexus'
 EOF
 }
 
 if [[ ${1:-} == --help || ${1:-} == -h ]]; then
     usage
     exit 0
 fi
 if (( $# < 2 || $# > 3 )); then
     usage >&2
     exit 1
 fi
 if [[ ! $1 =~ ^[0-9]{4}-[0-9]{2}-[0-9]{2}$ ]]; then
     echo 'Start date must have the format YYYY-MM-dd.' >&2
     exit 1
 fi
 
 since=$1
 output=$2
 connection=()
 if (( $# == 3 )); then
     connection=(--dbname="$3")
 fi
 
 umask 077
 salt=$(openssl rand -hex 32)
 temporary=$(mktemp -- "${output}.tmp.XXXXXXXX")
 trap 'rm -f -- "$temporary"' EXIT
 
 # Feed the salt through stdin, keeping it out of the psql process arguments.
 # A repeatable-read snapshot keeps validation and export consistent.
 {
     printf '\\set export_salt %s\n' "$salt"
     cat <<'SQL'
 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;
 SET LOCAL TIME ZONE 'UTC';
 SET LOCAL DateStyle = 'ISO, YMD';
 
 -- Timestamps in Nexus are microseconds since the Unix epoch.
 SELECT (extract(epoch FROM :'since'::date::timestamptz) * 1000000)::bigint AS cutoff
 \gset
 
 -- Match both payto://iban/IBAN and payto://iban/BIC/IBAN, ignoring
 -- query parameters (including the sender's name). Fail closed on other forms.
 \set iban_pattern '^payto://iban/(?:[A-Z0-9]+/)?([A-Z]{2}[0-9]{2}[A-Z0-9]{11,30})(?:[?].*)?$'
 SELECT NOT EXISTS (
     SELECT 1
     FROM libeufin_nexus.incoming_transactions AS i
     JOIN libeufin_nexus.talerable_incoming_transactions AS t
       USING (incoming_transaction_id)
     WHERE t.type = 'reserve' AND i.execution_time >= :cutoff
       AND (i.debit_payto IS NULL OR i.debit_payto !~* :'iban_pattern')
 ) AS valid_ibans
 \gset
 \if :valid_ibans
 \else
     \echo 'Export failed: a withdrawal has a missing or unsupported IBAN payto URI.'
     -- Make ON_ERROR_STOP terminate with a nonzero exit status.
     DO $$ BEGIN RAISE EXCEPTION 'Cannot export all sender IBANs'; END $$;
 \endif
 
 COPY (
     SELECT i.incoming_transaction_id,
            to_char(
                timestamptz 'epoch'
                    + (i.execution_time / 1000000) * interval '1 second'
                    + (i.execution_time % 1000000) * interval '1 microsecond',
                'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'
            ) AS execution_time_utc,
            (i.amount).val::text || '.' || lpad((i.amount).frac::text, 8, '0') AS amount,
            left(encode(sha256(
                decode(:'export_salt', 'hex')
                || convert_to(upper(substring(i.debit_payto FROM ('(?i)' || :'iban_pattern'))), 'UTF8')
            ), 'hex'), 32) AS sender_iban_hash
     FROM libeufin_nexus.incoming_transactions AS i
     JOIN libeufin_nexus.talerable_incoming_transactions AS t
       USING (incoming_transaction_id)
     WHERE t.type = 'reserve' AND i.execution_time >= :cutoff
     ORDER BY i.execution_time, i.incoming_transaction_id
 ) TO STDOUT WITH (FORMAT CSV, HEADER);
 COMMIT;
 SQL
 } | psql -X --quiet --set=ON_ERROR_STOP=1 --set=since="$since" "${connection[@]}" > "$temporary"
 
 mv -f -- "$temporary" "$output"