database: the PostgreSQL reseed probe needs CONNECT on every database #183

New issue

Closed

opened 2026-10-16 04:42:55 -03:00 by navigator · 0 comments

navigator commented

2026-10-16 04:42:55 -03:00

Owner

Found by the LNPP pair test on hw12-br1, 2026-10-16 (keel 0.23.29).

The reseed of a PostgreSQL replica (keel spec apply --system-only --destroy-local-database) refused:

database.server.role: refused: cannot ask [fd0b:d5b0:6fcd::1]:5432 as repl: ERROR:  permission denied for database keel_check; nothing was dropped and replication was not started

Cause: PROBE_SQL of keel/system/dbpgseed.py sums pg_database_size(oid) over every database as the role repl. pg_database_size() needs CONNECT on the database, or the role pg_read_all_stats. An application database with REVOKE CONNECT ... FROM PUBLIC stops every reseed.

The keel-lnpp check database had that REVOKE. keel-lnpp#2 removes it, so the LNPP images work with keel 0.23.29. Other applications can do the same, so keel must not need CONNECT on every database.

Possible fixes, the least power first:

Sum the size only where has_database_privilege(oid, 'CONNECT') is true, and say in the report that the free space check left those databases out.
Grant pg_read_all_stats to repl. This gives more read access than the reseed needs.

Found by the LNPP pair test on hw12-br1, 2026-10-16 (keel 0.23.29). The reseed of a PostgreSQL replica (`keel spec apply --system-only --destroy-local-database`) refused: ``` database.server.role: refused: cannot ask [fd0b:d5b0:6fcd::1]:5432 as repl: ERROR: permission denied for database keel_check; nothing was dropped and replication was not started ``` Cause: `PROBE_SQL` of `keel/system/dbpgseed.py` sums `pg_database_size(oid)` over every database as the role `repl`. `pg_database_size()` needs CONNECT on the database, or the role `pg_read_all_stats`. An application database with `REVOKE CONNECT ... FROM PUBLIC` stops every reseed. The keel-lnpp check database had that REVOKE. keel-lnpp#2 removes it, so the LNPP images work with keel 0.23.29. Other applications can do the same, so keel must not need CONNECT on every database. Possible fixes, the least power first: 1. Sum the size only where `has_database_privilege(oid, 'CONNECT')` is true, and say in the report that the free space check left those databases out. 2. Grant `pg_read_all_stats` to `repl`. This gives more read access than the reseed needs.