fix: the reseed probe measures only the databases repl may connect to (keel#183) #185

Merged

navigator merged 3 commits from fix/pg-reseed-probe-connect-183 into main

2026-10-16 07:26:01 -03:00

navigator commented

2026-10-16 06:53:11 -03:00

Owner

Fixes #183.

The reseed probe of a PostgreSQL standby summed pg_database_size() over every database as the role repl. That function needs CONNECT on the database. An application database with REVOKE CONNECT ... FROM PUBLIC (the keel-lnpp check database had it) stopped every reseed: "permission denied for database keel_check".

What changes:

PROBE_SQL sums the size with FILTER (WHERE has_database_privilege(oid, 'CONNECT')) and counts the databases it leaves out.
The free space check uses that size. When it refuses, the message says how many databases the need leaves out.
The base backup is not changed: it copies the whole cluster.
pg_read_all_stats is not granted to repl (more read access than the reseed needs).

Tests:

Unit: the probe filters by CONNECT; a left-out database does not stop the probe; a refusal for space names the left-out count.
netns (postgres / trixie): the shop database revokes CONNECT from PUBLIC before case (d), so the real reseed runs against such a database.
The SQL was also run on a local PostgreSQL 17 as a role without CONNECT on one database: the old query fails with "permission denied", the new one answers f 2 7689907 1.

keel 0.23.33.

Fixes #183. The reseed probe of a PostgreSQL standby summed `pg_database_size()` over every database as the role `repl`. That function needs CONNECT on the database. An application database with `REVOKE CONNECT ... FROM PUBLIC` (the keel-lnpp check database had it) stopped every reseed: "permission denied for database keel_check". What changes: - `PROBE_SQL` sums the size with `FILTER (WHERE has_database_privilege(oid, 'CONNECT'))` and counts the databases it leaves out. - The free space check uses that size. When it refuses, the message says how many databases the need leaves out. - The base backup is not changed: it copies the whole cluster. - `pg_read_all_stats` is not granted to `repl` (more read access than the reseed needs). Tests: - Unit: the probe filters by CONNECT; a left-out database does not stop the probe; a refusal for space names the left-out count. - netns (`postgres / trixie`): the shop database revokes CONNECT from PUBLIC before case (d), so the real reseed runs against such a database. - The SQL was also run on a local PostgreSQL 17 as a role without CONNECT on one database: the old query fails with "permission denied", the new one answers `f 2 7689907 1`. keel 0.23.33.

navigator added 3 commits

2026-10-16 06:53:11 -03:00

test: a reseed works when repl has no CONNECT on a database (keel#183) ecad9eaa5a

The reseed probe sums pg_database_size() over every database as the
role repl. That function needs CONNECT on the database. The keel-lnpp
check database revoked CONNECT from PUBLIC, and every reseed of the
LNPP pair refused: "permission denied for database keel_check".

The netns test of the pair now revokes CONNECT on the shop database
from PUBLIC before case (d), as the check database did. The unit tests
ask that the probe measures only the databases repl may connect to,
and that a refusal for lack of space names the databases it left out.

fix: the reseed probe measures only the databases repl may connect to (keel#183) e33d7f3a5b

pg_database_size() needs CONNECT on the database or the role
pg_read_all_stats. An application database with REVOKE CONNECT FROM
PUBLIC stopped every reseed with "permission denied".

The probe now sums the size with a FILTER on
has_database_privilege(oid, 'CONNECT') and counts the databases it
leaves out. The free space check uses the size it has. When the check
refuses, the message says how many databases the need leaves out. The
base backup itself is not changed: it copies the whole cluster.

pg_read_all_stats is not granted to repl: it gives more read access
than the reseed needs.

chore: keel 0.23.33 (keel#183)

tests / etcd (pull_request) Successful in 6m27s

Details

tests / postgres (pull_request) Successful in 11m17s

Details

tests / vip (pull_request) Successful in 7m14s

Details

tests / mesh (pull_request) Successful in 11m7s

Details

tests / etcd-auth (pull_request) Successful in 5m7s

Details

tests / package (pull_request) Successful in 8s