From 4c8aef9841ac52c5cb0421fe01ce0b84cb0009c3 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Tue, 5 Jan 2021 21:21:38 +0100 Subject: schema update in preparation of exchange-auditor database replication logic --- src/exchangedb/exchange-0002.sql | 80 ++++++++++++++++++++++++++-- src/exchangedb/plugin_exchangedb_postgres.c | 81 ++++++++++++++++++++--------- 2 files changed, 133 insertions(+), 28 deletions(-) diff --git a/src/exchangedb/exchange-0002.sql b/src/exchangedb/exchange-0002.sql index da01151bc..f0e191a3b 100644 --- a/src/exchangedb/exchange-0002.sql +++ b/src/exchangedb/exchange-0002.sql @@ -42,8 +42,76 @@ COMMENT ON INDEX prepare_get_index IS 'for wire_prepare_data_get'; +-- need serial IDs on various tables for exchange-auditor replication +ALTER TABLE denominations + ADD COLUMN denominations_serial BIGSERIAL UNIQUE; +COMMENT ON COLUMN denominations.denominations_serial + IS 'needed for exchange-auditor replication logic'; +ALTER TABLE refresh_revealed_coins + ADD COLUMN rrc_serial BIGSERIAL UNIQUE; +COMMENT ON COLUMN refresh_revealed_coins.rrc_serial + IS 'needed for exchange-auditor replication logic'; +ALTER TABLE refresh_transfer_keys + ADD COLUMN rtc_serial BIGSERIAL UNIQUE; +COMMENT ON COLUMN refresh_transfer_keys.rtc_serial + IS 'needed for exchange-auditor replication logic'; +ALTER TABLE wire_fee + ADD COLUMN wire_fee_serial BIGSERIAL UNIQUE; +COMMENT ON COLUMN wire_fee.wire_fee_serial + IS 'needed for exchange-auditor replication logic'; + +-- for the reserves, we add the new reserve_uuid, and also +-- change the foreign keys to use the new BIGSERIAL instead +-- of the public key to reference the entry +ALTER TABLE reserves + ADD COLUMN reserve_uuid BIGSERIAL UNIQUE; +ALTER TABLE reserves_in + ADD COLUMN reserve_uuid INT8 REFERENCES reserves (reserve_uuid) ON DELETE CASCADE; +UPDATE reserves_in + SET reserve_uuid=r.reserve_uuid + FROM reserves_in rin + INNER JOIN reserves r USING(reserve_pub); +ALTER TABLE reserves_in + ALTER COLUMN reserve_uuid SET NOT NULL; +ALTER TABLE reserves_out + ADD COLUMN reserve_uuid INT8 REFERENCES reserves (reserve_uuid) ON DELETE CASCADE; +UPDATE reserves_out + SET reserve_uuid=r.reserve_uuid + FROM reserves_out rout + INNER JOIN reserves r USING(reserve_pub); +ALTER TABLE reserves_out + ALTER COLUMN reserve_uuid SET NOT NULL; +ALTER TABLE reserves_close + ADD COLUMN reserve_uuid INT8 REFERENCES reserves (reserve_uuid) ON DELETE CASCADE; +UPDATE reserves_close + SET reserve_uuid=r.reserve_uuid + FROM reserves_close rclose + INNER JOIN reserves r USING(reserve_pub); +ALTER TABLE reserves_close + ALTER COLUMN reserve_uuid SET NOT NULL; + +ALTER TABLE reserves_in + DROP COLUMN reserve_pub; +ALTER TABLE reserves_out + DROP COLUMN reserve_pub; +ALTER TABLE reserves_close + DROP COLUMN reserve_pub; + + +-- "reserves" has no BIGSERIAL because it is a 'mutable' table +-- the auditor recomputes these balances itself +-- => verify_reserve_balance check only done for 'internal' auditor +-- "deposits" is updated with 'tiny' and 'done' bits +-- => those SHALL NOT to be used by the (external) auditor! +-- "prewire" is updated with 'finished' and 'failed' bits, but +-- those are of no concern for the auditor (prewire is not auditable!) +-- "auditors" is updated with 'is_active' and 'last_change', but +-- those are of no concern for the auditor + + CREATE TABLE IF NOT EXISTS auditors - (auditor_pub BYTEA PRIMARY KEY CHECK (LENGTH(auditor_pub)=32) + (auditor_uuid BIGSERIAL UNIQUE + ,auditor_pub BYTEA PRIMARY KEY CHECK (LENGTH(auditor_pub)=32) ,auditor_name VARCHAR NOT NULL ,auditor_url VARCHAR NOT NULL ,is_active BOOLEAN NOT NULL @@ -59,10 +127,13 @@ COMMENT ON COLUMN auditors.is_active IS 'true if we are currently supporting the use of this auditor.'; COMMENT ON COLUMN auditors.last_change IS 'Latest time when active status changed. Used to detect replays of old messages.'; +-- "auditors" has no BIGSERIAL because it is a 'mutable' table +-- and is of no concern to the auditor CREATE TABLE IF NOT EXISTS auditor_denom_sigs - (auditor_pub BYTEA NOT NULL REFERENCES auditors (auditor_pub) ON DELETE CASCADE + (auditor_denom_serial BIGSERIAL UNIQUE + ,auditor_pub BYTEA NOT NULL REFERENCES auditors (auditor_pub) ON DELETE CASCADE ,denom_pub_hash BYTEA NOT NULL REFERENCES denominations (denom_pub_hash) ON DELETE CASCADE ,auditor_sig BYTEA CHECK (LENGTH(auditor_sig)=64) ,PRIMARY KEY (denom_pub_hash, auditor_pub) @@ -78,7 +149,8 @@ COMMENT ON COLUMN auditor_denom_sigs.auditor_sig CREATE TABLE IF NOT EXISTS exchange_sign_keys - (exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32) + (esk_serial BIGSERIAL UNIQUE + ,exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32) ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) ,valid_from INT8 NOT NULL ,expire_sign INT8 NOT NULL @@ -114,6 +186,8 @@ COMMENT ON COLUMN wire_accounts.is_active IS 'true if we are currently supporting the use of this account.'; COMMENT ON COLUMN wire_accounts.last_change IS 'Latest time when active status changed. Used to detect replays of old messages.'; +-- "wire_accounts" has no BIGSERIAL because it is a 'mutable' table +-- and is of no concern to the auditor CREATE TABLE IF NOT EXISTS signkey_revocations diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index c55eb48bc..3df1af74b 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -438,7 +438,7 @@ postgres_get_session (void *cls) /* Used in #postgres_insert_reserve_closed() */ GNUNET_PQ_make_prepare ("reserves_close_insert", "INSERT INTO reserves_close " - "(reserve_pub" + "(reserve_uuid" ",execution_date" ",wtid" ",receiver_account" @@ -446,8 +446,9 @@ postgres_get_session (void *cls) ",amount_frac" ",closing_fee_val" ",closing_fee_frac" - ") VALUES " - "($1, $2, $3, $4, $5, $6, $7, $8);", + ") SELECT reserve_uuid, $2, $3, $4, $5, $6, $7, $8" + " FROM reserves" + " WHERE reserve_pub=$1;", 8), /* Used in #reserves_update() when the reserve is updated */ GNUNET_PQ_make_prepare ("reserve_update", @@ -457,22 +458,22 @@ postgres_get_session (void *cls) ",gc_date=$2" ",current_balance_val=$3" ",current_balance_frac=$4" - " WHERE" - " reserve_pub=$5;", + " WHERE reserve_pub=$5;", 5), /* Used in #postgres_reserves_in_insert() to store transaction details */ GNUNET_PQ_make_prepare ("reserves_in_add_transaction", "INSERT INTO reserves_in " - "(reserve_pub" + "(reserve_uuid" ",wire_reference" ",credit_val" ",credit_frac" ",exchange_account_section" ",sender_account_details" ",execution_date" - ") VALUES " - "($1, $2, $3, $4, $5, $6, $7) " - "ON CONFLICT DO NOTHING;", + ") SELECT reserve_uuid, $2, $3, $4, $5, $6, $7" + " FROM reserves" + " WHERE reserve_pub=$1" + " ON CONFLICT DO NOTHING;", 7), /* Used in postgres_select_reserves_in_above_serial_id() to obtain inbound transactions for reserves with serial id '\geq' the given parameter */ @@ -488,7 +489,7 @@ postgres_get_session (void *cls) transactions for reserves with serial id '\geq' the given parameter */ GNUNET_PQ_make_prepare ("audit_reserves_in_get_transactions_incr", "SELECT" - " reserve_pub" + " reserves.reserve_pub" ",wire_reference" ",credit_val" ",credit_frac" @@ -496,6 +497,8 @@ postgres_get_session (void *cls) ",sender_account_details" ",reserve_in_serial_id" " FROM reserves_in" + " JOIN reserves" + " USING (reserve_uuid)" " WHERE reserve_in_serial_id>=$1" " ORDER BY reserve_in_serial_id;", 1), @@ -504,7 +507,7 @@ postgres_get_session (void *cls) GNUNET_PQ_make_prepare ( "audit_reserves_in_get_transactions_incr_by_account", "SELECT" - " reserve_pub" + " reserves.reserve_pub" ",wire_reference" ",credit_val" ",credit_frac" @@ -512,6 +515,8 @@ postgres_get_session (void *cls) ",sender_account_details" ",reserve_in_serial_id" " FROM reserves_in" + " JOIN reserves " + " USING (reserve_uuid)" " WHERE reserve_in_serial_id>=$1 AND exchange_account_section=$2" " ORDER BY reserve_in_serial_id;", 2), @@ -525,7 +530,10 @@ postgres_get_session (void *cls) ",execution_date" ",sender_account_details" " FROM reserves_in" - " WHERE reserve_pub=$1;", + " WHERE reserve_uuid=" + " (SELECT reserve_uuid " + " FROM reserves" + " WHERE reserve_pub=$1);", 1), /* Lock withdraw table; NOTE: we may want to eventually shard the deposit table to avoid this lock being the main point of @@ -539,18 +547,20 @@ postgres_get_session (void *cls) the coin's denomination information (public key, signature) and the blinded message as well as the reserve that the coin is being withdrawn from and the signature of the message - authorizing the withdrawal. */GNUNET_PQ_make_prepare ("insert_withdraw_info", + authorizing the withdrawal. */ + GNUNET_PQ_make_prepare ("insert_withdraw_info", "INSERT INTO reserves_out " "(h_blind_ev" ",denom_pub_hash" ",denom_sig" - ",reserve_pub" + ",reserve_uuid" ",reserve_sig" ",execution_date" ",amount_with_fee_val" ",amount_with_fee_frac" - ") VALUES " - "($1, $2, $3, $4, $5, $6, $7, $8);", + ") SELECT $1, $2, $3, reserve_uuid, $5, $6, $7, $8" + " FROM reserves" + " WHERE reserve_pub=$4;", 8), /* Used in #postgres_get_withdraw_info() to locate the response for a /reserve/withdraw request @@ -561,13 +571,15 @@ postgres_get_session (void *cls) " denom_pub_hash" ",denom_sig" ",reserve_sig" - ",reserve_pub" + ",reserves.reserve_pub" ",execution_date" ",amount_with_fee_val" ",amount_with_fee_frac" ",denom.fee_withdraw_val" ",denom.fee_withdraw_frac" " FROM reserves_out" + " JOIN reserves" + " USING (reserve_uuid)" " JOIN denominations denom" " USING (denom_pub_hash)" " WHERE h_blind_ev=$1;", @@ -590,7 +602,10 @@ postgres_get_session (void *cls) " FROM reserves_out" " JOIN denominations denom" " USING (denom_pub_hash)" - " WHERE reserve_pub=$1;", + " WHERE reserve_uuid=" + " (SELECT reserve_uuid" + " FROM reserves" + " WHERE reserve_pub=$1);", 1), /* Used in #postgres_select_withdrawals_above_serial_id() */ GNUNET_PQ_make_prepare ("audit_get_reserves_out_incr", @@ -598,12 +613,14 @@ postgres_get_session (void *cls) " h_blind_ev" ",denom.denom_pub" ",reserve_sig" - ",reserve_pub" + ",reserves.reserve_pub" ",execution_date" ",amount_with_fee_val" ",amount_with_fee_frac" ",reserve_out_serial_id" " FROM reserves_out" + " JOIN reserves" + " USING (reserve_uuid)" " JOIN denominations denom" " USING (denom_pub_hash)" " WHERE reserve_out_serial_id>=$1" @@ -1268,7 +1285,7 @@ postgres_get_session (void *cls) "SELECT" " recoup_uuid" ",timestamp" - ",ro.reserve_pub" + ",reserves.reserve_pub" ",coin_pub" ",coin_sig" ",coin_blind" @@ -1283,6 +1300,8 @@ postgres_get_session (void *cls) " USING (coin_pub)" " JOIN reserves_out ro" " USING (h_blind_ev)" + " JOIN reserves" + " USING (reserve_uuid)" " JOIN denominations denoms" " ON (coins.denom_pub_hash = denoms.denom_pub_hash)" " WHERE recoup_uuid>=$1" @@ -1324,7 +1343,7 @@ postgres_get_session (void *cls) GNUNET_PQ_make_prepare ("reserves_close_get_incr", "SELECT" " close_uuid" - ",reserve_pub" + ",reserves.reserve_pub" ",execution_date" ",wtid" ",receiver_account" @@ -1333,6 +1352,8 @@ postgres_get_session (void *cls) ",closing_fee_val" ",closing_fee_frac" " FROM reserves_close" + " JOIN reserves" + " USING (reserve_uuid)" " WHERE close_uuid>=$1" " ORDER BY close_uuid ASC;", 1), @@ -1353,7 +1374,10 @@ postgres_get_session (void *cls) " USING (coin_pub)" " JOIN reserves_out ro" " USING (h_blind_ev)" - " WHERE ro.reserve_pub=$1;", + " WHERE ro.reserve_uuid=" + " (SELECT reserve_uuid" + " FROM reserves" + " WHERE reserve_pub=$1);", 1), /* Used in #postgres_get_coin_transactions() to obtain recoup transactions affecting old coins of refreshed coins */ @@ -1389,7 +1413,10 @@ postgres_get_session (void *cls) ",receiver_account" ",wtid" " FROM reserves_close" - " WHERE reserve_pub=$1;", + " WHERE reserve_uuid=" + " (SELECT reserve_uuid" + " FROM reserves" + " WHERE reserve_pub=$1);", 1), /* Used in #postgres_get_expired_reserves() */ GNUNET_PQ_make_prepare ("get_expired_reserves", @@ -1410,7 +1437,7 @@ postgres_get_session (void *cls) for a coin */ GNUNET_PQ_make_prepare ("recoup_by_coin", "SELECT" - " ro.reserve_pub" + " reserves.reserve_pub" ",coins.denom_pub_hash" ",coin_sig" ",coin_blind" @@ -1421,6 +1448,8 @@ postgres_get_session (void *cls) " FROM recoup" " JOIN reserves_out ro" " USING (h_blind_ev)" + " JOIN reserves" + " USING (reserve_uuid)" " JOIN known_coins coins" " USING (coin_pub)" " WHERE recoup.coin_pub=$1;", @@ -1450,8 +1479,10 @@ postgres_get_session (void *cls) /* Used in #postgres_get_reserve_by_h_blind() */ GNUNET_PQ_make_prepare ("reserve_by_h_blind", "SELECT" - " reserve_pub" + " reserves.reserve_pub" " FROM reserves_out" + " JOIN reserves" + " USING (reserve_uuid)" " WHERE h_blind_ev=$1" " LIMIT 1;", 1), -- cgit v1.2.3