Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vsvqA-009KX3-1a for pgpool-hackers@arkaria.postgresql.org; Thu, 19 Feb 2026 04:40:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vsvq9-001s8B-18 for pgpool-hackers@arkaria.postgresql.org; Thu, 19 Feb 2026 04:40:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vsvq9-001s83-03 for pgpool-hackers@lists.postgresql.org; Thu, 19 Feb 2026 04:40:37 +0000 Received: from mail-yx1-xb131.google.com ([2607:f8b0:4864:20::b131]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vsvq4-000000004zw-2HiC for pgpool-hackers@lists.postgresql.org; Thu, 19 Feb 2026 04:40:35 +0000 Received: by mail-yx1-xb131.google.com with SMTP id 956f58d0204a3-649e97f1e99so420892d50.2 for ; Wed, 18 Feb 2026 20:40:33 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1771476033; cv=none; d=google.com; s=arc-20240605; b=SohTqvlg42/F+/A+R9XGu2cysDCQpe/ObgOCsRALhXpPOWn9vhL4HEljpK/FNvp9xZ gqRGZMCr/DR/04RZ+N8QOC4cIkJoXrcRyarbj8I5ZDnrbG1ELaDLrgs7oUIOpklP2QHz ba2tmhvZ1Ay54QOI7PR/s8vTfV2bhI9+YKzZthrCCZGZFEs5rt57CNw8uKRCEX0hfIpk dionnY2bPkYLsAOOKo4ngc9oPIyysVdZNVoP58QR1Cs6IkJ7aU8BiJ/kzQMD8Az6tSTF 2jQyvHW5Id7fFwzSAJREF8kPomH85eDMDsaK3ObE2flaZgwQlspPcWQzQPGgGAnqVuym UqFg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=OKTQISml/MC74cFbdaC1KDyzBNS+uF1FwTHBeyYywj4=; fh=m3oiE62fRN5J1XaE+5vpHZRIgCyFefxVI0mFw3d8yxA=; b=lvFMldXrQR5fzFbQhVFqlfwbWrUW4KhRu8OAyEicNdTL+CXm9eIKEL7ah0geYFAtPn HJ/dOUQ7riSL44ApZKw+uatJmDerPd1AKm0zEXHwU8YfcACRXFE94nmda+tE/43IfMkM 2uadXquUlptQ2piQKC8fMQ9ja09nYgGe12b0TUAWJTogu89qfW7oRUW7q2rWsM3J/99H HQ5VFiSOLogdXylCiekSbOWWLUTw6flw22W90TS4PjV1Dtu88Tgmnbv2Oa3O35VshLSf FTgA70BhBtjRmF+d9rRAP+Xcz5rJ8CyddG4NIAN/jmR8rsjuLooX5tkh+yb3MC/A/+Sm SWZw==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tailorbrands.com; s=google; t=1771476033; x=1772080833; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=OKTQISml/MC74cFbdaC1KDyzBNS+uF1FwTHBeyYywj4=; b=Z681pS0sklTP2XEveAbFMcZJVGSIaE4x50FOt6WtCn8pvPNcxracfZRfTuOASsyBIJ IWSzijh6LJ6EH3U/ggfvjmGFXrHidCUjyThtH+Q3H+RJkjlY6R7sj8LudQu9wUP2w0qp HYrZ3qSGHxpFMtgJsG9rZDWVNlhYT+P9DkuhlWpEGNL3TQmqDkEsdPNCfqUVdUYE8nL+ 8eSVIAPIuW062BP9Ii7QEK6nB/lzkqCQuy1EqChLYNMgugbqsgiMBdq+xTYk1NfmoFeA qypUBayP7gCV5+6aNTe92ZPfIjCmzWVgmfyRrwmoQE0k7u+/KGeDHcqY225y8R2TqXA8 2FyQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1771476033; x=1772080833; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=OKTQISml/MC74cFbdaC1KDyzBNS+uF1FwTHBeyYywj4=; b=ZiKfBYVsPu/ef4kNEgBwq4fkaO+3TaLC3fNiqgr18U+EHReO7HnKWlOhDGpi1eMRLy IhYGFgoL+wWt7CaaiP0VlTM9mnlkffGL/wjgfnTyRntREC+qbGZq7JSOAwXUhBc9nCqI prwcP5DuVmBPhDPMPO4HIexejnxrQ6CrAOE28HBww0ZQ94dssDEgbPfftAK8bLklp1A8 w2aXFr8/VvGZScBMbOT1llYjWQI+pIG6H0jKCignH+A9JNiz9gvuwdvp6y4RW647a7C/ 505c3ifbPXaCa7NeL1+CCAJ8ph65J/uqYha50ouKCu5zJZFvWFJJvmAvYcWqYRKcDlaU BDbg== X-Gm-Message-State: AOJu0YzLbq2YnA8IBgPJAEGUIc6Iz4GTNbndpH64NxeMQFftc4LWJ84S s455W/EL0o9W1yPWxigerXwbxJvMkBd6+1W906slYEz3EJWQEOMkdhe2z7QWnFUhmptUey8PXWI Wx7A7vOEuTAsrXBxvlwyeQjvqSyauz7lAP3r9JUYIMzCloWX/545KIXNqMw== X-Gm-Gg: AZuq6aI9xNarUyLiLAkizLiIMUz2M4eIebfK2FQRpLWdx4kk55FZRLvLHolEI46GHQq YdV0ineahdOydeCRcGbtf8Z96S6s8wI+o5q/O6Ngi9tMk3W+5dLgroFr5Hsq/fFCoUxVupfEi43 lWlTxZfhEH1/6CVePgdCdn0SNcEYmV9nZmBZWKl6Xh8bNO5n9X01WiREBeI5WRAWOEq5ckPp8P+ IGMhwdUcr6krgKkiEyH3WFrwvOk7HbWiijNEI7u0UX8O2O+Q7MmLp8EK7P/5aOfhiZnxUxrbEGp aZXVjKy3vblMAFSkiQuGFLvLl/eBy002ciOqV4BbQiFf3pXNPlR1thCDlHn9LuECAXMaS7pGMpk AappTHgQSM1oTBKIXsWOKX6YWIQw0Ocukio6zWnqCX3xGRQmZ8NzlGbzdDM+xgYXCd/gile0PX/ 6s+qCmHMc= X-Received: by 2002:a53:b1d2:0:b0:64c:20f7:b01a with SMTP id 956f58d0204a3-64c614889e8mr949974d50.63.1771476032871; Wed, 18 Feb 2026 20:40:32 -0800 (PST) MIME-Version: 1.0 References: <20260211.192845.1888610184173239311.ishii@postgresql.org> <20260219.085121.1424536813757196179.ishii@postgresql.org> In-Reply-To: <20260219.085121.1424536813757196179.ishii@postgresql.org> From: Nadav Shatz Date: Thu, 19 Feb 2026 06:40:21 +0200 X-Gm-Features: AaiRm53zngmjIlsehy5UzuMt_fRFt-hZgitk_9ZYqH2zcjTB-7UXkbsYVnTDX2k Message-ID: Subject: Re: Proposal: Recent mutated table tracking in memory To: Tatsuo Ishii Cc: pgpool-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000ab0a4f064b25e75a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ab0a4f064b25e75a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks! I=E2=80=99ll look into it and share an updated patch Nadav Shatz Tailor Brands | CTO On Thu, Feb 19, 2026 at 1:51=E2=80=AFAM Tatsuo Ishii = wrote: > > Hi Tatsuo, > > > > Thank you for the careful review. You raised an important concern. I'= ve > > addressed it in the updated patch =E2=80=95 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-memo= ry > > 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 =3D 1 WHERE FALSE; > > ROLLBACK; > > > > has zero effect on the shared-memory table map. The dml_adaptive_glob= al > > mode piggybacks on the existing dml_adaptive per-transaction write list > > (transaction_temp_write_list). On COMMIT, the accumulated table names a= re > > resolved to OIDs and flushed to shared memory. On ROLLBACK, > > the list is simply discarded (the existing dml_adaptive behavior). > > > > For autocommit statements (outside explicit transactions), tables are > > marked immediately =E2=80=95 but in that case the write is committed, s= o this is > > correct. > > > > Regression test included. Test 042 now includes: > > - Test 10: verifies that BEGIN; INSERT; ROLLBACK; SELECT does NOT rou= te > > 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 =3D > > 'dml_adaptive_global' =E2=80=95 it is opt-in, not enabled by default. O= perators > who > > enable it accept documented trade-offs (additional shared memory, > TTL-based > > staleness window). > > 2. An attacker who can connect and execute SQL against pgpool already > has > > the ability to cause far more damage (DROP TABLE, mass DELETEs, resourc= e > > exhaustion via expensive queries, connection flooding, etc.). The > > table-marking via committed writes is a minor concern compared to > > those vectors. Authentication, connection limits, and network securit= y > > 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 =E2=80=95 it = doesn't > > cause data loss or correctness issues. > > 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 =E2=80= =95 > > dml_adaptive_global extends this across sessions with a TTL. > > > > Thanks! > > Thank you for the patch. While I am looking into it, I noticed a > regression test failure. > > t-ishii$ ./regress.sh 04[12] > creating pgpool-II temporary installation ... > : > : > testing 041.external_replication_delay...ok. > testing 042.track_table_mutation...failed. > out of 2 ok:1 failed:1 timeout:0 > > However if I run 042 only, it succeeds. > > t-ishii$ ./regress.sh 042 > : > : > testing 042.track_table_mutation...ok. > out of 1 ok:1 failed:0 timeout:0 > > Can you please take a look at this? log/042.track_table_mutation > attached. > > Best regards, > -- > Tatsuo Ishii > SRA OSS K.K. > English: http://www.sraoss.co.jp/index_en/ > Japanese:http://www.sraoss.co.jp > --000000000000ab0a4f064b25e75a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks! I=E2=80=99ll look into it and share an updated pa= tch

Nadav Shatz
Tailor Brands=C2=A0| CTO
=


On Thu, Feb 19, 2026 at 1:51=E2= =80=AFAM Tatsuo Ishii <ishii@pos= tgresql.org> wrote:
>=C2=A0 Hi Tatsuo,<= br> >
>=C2=A0 =C2=A0Thank you for the careful review. You raised an important = concern. I've
> addressed it in the updated patch =E2=80=95 here's the explanation= :
>
>=C2=A0 =C2=A0The attack scenario you describe is now handled. In the up= dated patch,
> writes inside explicit transactions are only flushed to the shared-mem= ory
> table map at COMMIT time. If the transaction is rolled back, the table= is
> never marked as stale. So the attack pattern:
>
>=C2=A0 =C2=A0BEGIN;
>=C2=A0 =C2=A0UPDATE t1 SET i =3D 1 WHERE FALSE;
>=C2=A0 =C2=A0ROLLBACK;
>
>=C2=A0 =C2=A0has zero effect on the shared-memory table map. The dml_ad= aptive_global
> mode piggybacks on the existing dml_adaptive per-transaction write lis= t
> (transaction_temp_write_list). On COMMIT, the accumulated table names = are
> resolved to OIDs and flushed to shared memory. On ROLLBACK,
>=C2=A0 =C2=A0the list is simply discarded (the existing dml_adaptive be= havior).
>
>=C2=A0 =C2=A0For autocommit statements (outside explicit transactions),= tables are
> marked immediately =E2=80=95 but in that case the write is committed, = so this is
> correct.
>
>=C2=A0 =C2=A0Regression test included. Test 042 now includes:
>=C2=A0 =C2=A0- Test 10: verifies that BEGIN; INSERT; ROLLBACK; SELECT d= oes NOT route
> the SELECT to primary
>=C2=A0 =C2=A0- Test 11: verifies that BEGIN; INSERT; COMMIT; SELECT DOE= S route the
> SELECT to primary
>
>=C2=A0 =C2=A0Additional context on the threat model:
>
>=C2=A0 =C2=A01. This feature requires disable_load_balance_on_write =3D=
> 'dml_adaptive_global' =E2=80=95 it is opt-in, not enabled by d= efault. Operators who
> enable it accept documented trade-offs (additional shared memory, TTL-= based
> staleness window).
>=C2=A0 =C2=A02. An attacker who can connect and execute SQL against pgp= ool already has
> the ability to cause far more damage (DROP TABLE, mass DELETEs, resour= ce
> exhaustion via expensive queries, connection flooding, etc.). The
> table-marking via committed writes is a minor concern compared to
>=C2=A0 =C2=A0those vectors. Authentication, connection limits, and netw= ork security
> are the appropriate defenses at that layer.
>=C2=A0 =C2=A03. Even in the worst case (an attacker commits real writes= in a loop),
> the impact is bounded: the stale marking is temporary (TTL-based, typi= cally
> a few seconds), and only affects load-balancing decisions =E2=80=95 it= doesn't
> cause data loss or correctness issues.
>=C2=A0 =C2=A04. 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 =E2=80= =95
> dml_adaptive_global extends this across sessions with a TTL.
>
> Thanks!

Thank you for the patch. While I am looking into it, I noticed a
regression test failure.

t-ishii$ ./regress.sh 04[12]
creating pgpool-II temporary installation ...
:
:
testing 041.external_replication_delay...ok.
testing 042.track_table_mutation...failed.
out of 2 ok:1 failed:1 timeout:0

However if I run 042 only, it succeeds.

t-ishii$ ./regress.sh 042
:
:
testing 042.track_table_mutation...ok.
out of 1 ok:1 failed:0 timeout:0

Can you please take a look at this? log/042.track_table_mutation
attached.

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