Re: Proposal: Recent mutated table tracking in memory

> Hi Tatsuo,
>
> Thank you for the careful review. You raised an important concern. I've
> addressed it in the updated patch ― here's the explanation:
>
> The attack scenario you describe is now handled. In the updated patch,
> writes inside explicit transactions are only flushed to the shared-memory
> table map at COMMIT time. If the transaction is rolled back, the table is
> never marked as stale. So the attack pattern:
>
> BEGIN;
> UPDATE t1 SET i = 1 WHERE FALSE;
> ROLLBACK;
>
> has zero effect on the shared-memory table map. The dml_adaptive_global
> mode piggybacks on the existing dml_adaptive per-transaction write list
> (transaction_temp_write_list). On COMMIT, the accumulated table names are
> resolved to OIDs and flushed to shared memory. On ROLLBACK,
> the list is simply discarded (the existing dml_adaptive behavior).

Ok.

> For autocommit statements (outside explicit transactions), tables are
> marked immediately ― but in that case the write is committed, so this is
> correct.

Agreed.

> Regression test included. Test 042 now includes:
> - Test 10: verifies that BEGIN; INSERT; ROLLBACK; SELECT does NOT route
> the SELECT to primary
> - Test 11: verifies that BEGIN; INSERT; COMMIT; SELECT DOES route the
> SELECT to primary
>
> Additional context on the threat model:
>
> 1. This feature requires disable_load_balance_on_write =
> 'dml_adaptive_global' ― it is opt-in, not enabled by default. Operators who
> enable it accept documented trade-offs (additional shared memory, TTL-based
> staleness window).

Ok.

> 2. An attacker who can connect and execute SQL against pgpool already has
> the ability to cause far more damage (DROP TABLE, mass DELETEs, resource
> exhaustion via expensive queries, connection flooding, etc.). The
> table-marking via committed writes is a minor concern compared to
> those vectors.

These existing risks are widely understood by PostgreSQL users and
already accepted (with various measures/workarounds). But the concern
is new one. If the concern is left, it will make users to hesitate to
use the new feature. I don't want to add a feature which users might
avoid to use it especially when we know that it is technically
possible to remove the concern.

> Authentication, connection limits, and network security
> are the appropriate defenses at that layer.

> 3. Even in the worst case (an attacker commits real writes in a loop),
> the impact is bounded: the stale marking is temporary (TTL-based, typically
> a few seconds), and only affects load-balancing decisions ― it doesn't
> cause data loss or correctness issues.

But it makes Pgpool's load balance feature less usefull. In my
uderstanding the new feature tries to keep load balance usefully while
it also tries to avoid steal data read.

> 4. The existing dml_adaptive mode has analogous behavior: within a
> transaction, a write to table T causes all reads of T to go to primary for
> the remainder of that transaction. The only difference is scope ―
> dml_adaptive_global extends this across sessions with a TTL.

The scope difference is huge. With existing dml_adaptive mode, it just
hits himself in the foot. Your patch allows him to hit someone else's
foot.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp