011-auditor-db-sync.rst (9750B)
1 DD 11: Auditor-Exchange Database Synchronization 2 ################################################ 3 4 :Design status: Accepted 5 :Implementation status: Implemented 6 :DD shepherd: TBD 7 :Historical contributors: Christian Grothoff, Thien-Thi Nguyen 8 :First published: 2021-01-01 9 :Last substantive change: 2021-01-06 10 :Implementation evidence: exchange (2021-01-11, 2026-08-01) 11 :Normative references: :doc:`../taler-auditor-manual` 12 13 Summary 14 ======= 15 16 .. note:: 17 18 This document records the design rationale. The auditor manual and the 19 current database schema are authoritative for operational details and the 20 set of synchronized tables. 21 22 Ways for the auditor to obtain a current copy of the exchange database (to 23 verify that the exchange is operating correctly) are discussed. 24 25 26 Motivation 27 ========== 28 29 The Taler auditor is expected to check that the exchange is operating 30 correctly. For this, it needs (read-only) access to the exchange database and 31 to the bank account of the exchange. Bank account access is a matter of 32 setting up an additional user with limited rights with the bank and is out of 33 the scope of this document. For database access, the auditor should not trust 34 the exchange. In particular, the auditor must assume that the exchange may 35 violate basic database constraints (like foreign keys) or delete/alter records 36 maliciously. However, we also do not want to complicate the auditor logic to 37 cope with violations of well-formedness constraints (like foreign keys or 38 non-NULL values or size constraints on fields). Finally, the mechanism by 39 which the auditor obtains the database must provide a reasonably current 40 database and the process must perform reasonably well. 41 42 43 Requirements 44 ============ 45 46 * The solution must allow data to be copied incrementally. 47 * The solution must tolerate network outages and recover after connectivity 48 between exchange and auditor is restored. 49 * The solution must enable the auditor database to serve as a full backup 50 of the exchange's database (even if possibly slightly outdated due to 51 asynchronous replication or network outages). 52 * The solution must scale, in particular if the exchange shards the database, 53 the auditor must be also able to use the same kind of sharding and the 54 synchronization should be possible per shard. 55 * The synchronization mechanism must not allow an attacker controlling the 56 exchange database to delete or modify arbitrary data from the auditor's copy 57 via the synchronization mechanism (in other words, some tables are 58 append-only and unalterable). 59 * The solution must support database schema updates. Those may require some 60 downtime and closely coordinated work between exchange and auditor. 61 * The solution must enable eventual garbage collection at the exchange to 62 be permitted and replicated at the auditor (e.g. DELETE on usually append-only 63 tables due to a CASCADE from expired denomination keys). 64 * The synchronization mechanism should raise an alert if the exchange violates basic 65 constraints (unexpected schema changes, deletion/motification on append-only 66 tables) and then NOT replicate those changes. The auditor's internal asynchronous 67 helper may then soft-fail (log and exit) until the exchange has rectified the 68 problem (by manual, human intervention resulting in an exchange master database 69 that again maintains the required invariants). 70 After the corrected master database has been again synchronized with the 71 primary copy of the auditor, the auditor's helper is resumed and can continue to 72 copy the (now valid) database records into the auditor's internal version. 73 * A good solution would work independently of the specific database used. 74 75 76 Proposed Solution 77 ================= 78 79 * Use "common" incremental database replication (whichever is 80 appropriate for the exchange database setup, synchronous 81 or asynchronous) to make a 1:1 copy of the exchange database 82 at the auditor. This should work for any full-featured 83 modern database. This "ingress" copy cannot be trusted, as constraint 84 violations or deletions would also be replicated. 85 * Use helper process to SELECT against the local "ingress" copy (by 86 SERIAL ID => make sure all append-only tables have one!) 87 to copy append-only tables to a second, "trusted" and fully 88 auditor-controlled copy of the database. 89 Order (or transactionally group) SELECT 90 statements to ensure foreign key constraints are maintained. 91 For mutable tables (basically, only current reserve balance) 92 do not make another copy, but do have logic to recompute mutable 93 tables from other data *if* we need to recover from backup. 94 * On schema migration, halt exchange, once auditor DB has 95 synchronized, update all DB schema (the "ingress" DB schema 96 may be updated automatically when the exchange DB schema is 97 migrated, but the "trusted" DB of the auditor must most likely 98 be manually migrated), then finally resume "ingress" to "trusted" 99 helper-based DB synchronization and restart the exchange. 100 * For GC, simply run GC logic also on auditor's "trusted" copy. 101 (The synchronization mechanism will take care of the primary copy, 102 and the helper to copy should not be disturbed by the DELETE operations 103 anyway.) 104 * The auditor's "ingress" database should be well isolated from 105 the rest of the auditor's system and database 106 (different user accounts). The reason is that we should not 107 assume that the PostgreSQL replication code is battle-tested with 108 malicious parties in mind. 109 * The canonical PostgreSQL synchronization between exchange and the 110 auditor's "ingress" database must use transport security. 111 112 The above solution does not gracefully handle mutable tables on which 113 the exchange performs UPDATE statements, as such updates will not bump 114 the BIGSERIAL and thus would not be replicated by the helper. Thus, we 115 need to consider all tables that the exchange ever performs UPDATE on. 116 Those are: 117 118 * /reserves/ --- the exchange updates the remaining reserve balance; 119 here the auditor currently performs a sanity check against 120 its own reserve balance calculation. The proposed way to address 121 this is to make this sanity check optional and to be only used if 122 the auditor auditor runs against the "primary" exchange database 123 (like an internal audit). This is acceptable, as an inaccurate 124 reserve balance is mostly used to raise an early warning and not 125 indicative of any actualized financial gains or losses from the 126 exchange. 127 * /deposits/ --- the exchange updates the /tiny/ and /done/ bit 128 fields. /tiny/ can be trivially established by the auditor, and 129 we can simply avoid the auditor considering that bit. /done/ 130 was so far only used to enrich the reporting. The proposed way 131 to address the uses of both fields is thus to only use them in 132 internal audits (against the primary exchange database). Both 133 can be safely ignored by the external audit. 134 * /prewire/ --- the exchange updates the /finished/ and /failed/ 135 bits. The entire table is not used by the auditor and its 136 main values cannot be validated by the auditor anyway. 137 * /auditors/ --- the exchange updates the /is_active/ and /last_change/ 138 fields. The entire table is of no concern to the auditor. 139 140 A good order for replicating the tables should be: 141 142 * exchange_sign_keys 143 * signkey_revocations 144 * auditors 145 * denominations 146 * denomination_revocations 147 * auditor_denom_sigs 148 * reserves 149 * reserves_out 150 * reserves_in 151 * reserves_close 152 * known_coins 153 * deposits 154 * refunds 155 * refresh_commitments 156 * refresh_transfer_keys 157 * refresh_revealed_coins 158 * recoup_refresh 159 * recoup 160 161 162 163 Alternatives 164 ============ 165 166 * Copy the PostgreSQL WAL, filter it for "illegal" operations 167 and then apply it at the auditor end. Disadvantages: WAL 168 filtering is not a common operation (format documented?), 169 this would be highly PostgreSQL-specific, and would require 170 complex work to write the filter. Also unsure how one 171 could later recover gracefully from transient errors 172 (say where the exchange recified a bogus DELETE). 173 * Directly SELECT against the (remote) exchange DB and then 174 INSERT/UPDATE at the auditor's local copy. Disadvantages: 175 remote SELECT likely very expensive due to high latency. 176 Diagnostics more difficult. May expose exchange to additional 177 risks from auditor, such as attacks exhausting DB resources 178 by running expensive SELECTs. 179 180 181 182 183 Drawbacks 184 ========= 185 186 * SERIAL IDs required in all tables that are "append-only" / immutable. 187 * Additional custom logic required to recompute mutable tables 188 on-demand. 189 * Limited ability to cope with mutable tables, imposes restrictions 190 on future exchange database evolution. 191 * Helper logic to SELECT data in batches that will certainly 192 maintain invariants may be a bit tricky, but in principle 193 the foreign key constraints should form a DAG, simply dictating 194 the order in which new entries are to be copied. It may also 195 be that simply running "big" transactions across all tables 196 is the answer, to be investigated what performs better. 197 * A malicious exchange could theoretically send expensive transactions 198 to the auditor via the replication mechanism (possibly ones that 199 it did not even execute locally itself) to DoS the "ingress" 200 database. This would be noticed primarily by load 201 monitoring or even the auditor lagging unusually far behind the 202 exchange's transaction history. We believe this is acceptable, 203 as it would imply highly visible malicious exchange behavior for 204 virtually no significant gain. 205 * The proposed solution does not create a transactional, synchronous 206 write-only log as suggested by CodeBlau (see audit report, Section 9.4). 207 We believe doing so would be overly costly, both in terms of 208 complexity and performance, for limited gains. 209 210 211 Discussion / Q&A 212 ================