The v3 patch adds a check for the CONNECT privilege on the target database for pg_get_database_ddl(). This aligns its security model with functions like pg_database_size(). Note that revoking permissions on the *pg_database* table alone is insufficient to restrict DDL access; users must manually revoke permission on the pg_get_database_ddl() function itself if restriction is desired.
Attached is the v3 patch ready for review. On Fri, Nov 14, 2025 at 4:42 PM Álvaro Herrera <[email protected]> wrote: > On 2025-Nov-13, Quan Zongliang wrote: > > > A more specific example. Originally, it was impossible to obtain the > > definition of "testdb" by accessing pg_database: > > > > postgres=> SELECT * FROM pg_database WHERE datname='testdb'; > > ERROR: permission denied for table pg_database > > Hmm. So I was thinking that running things in this mode (where catalog > access is restricted) has never been supported. But you're right that > we would be opening a hole that we don't have today, because if the > admin closes down permissions on pg_database, then this new function > would be a way to obtain information that the user can't currently > obtain. > > My further point was to be that you still need to obtain a list of > database names or OIDs in order to do anything of value. But it turns > out that this is extremely easy and quick to do, with something like > > SELECT i, pg_describe_object('pg_database'::regclass, i, 0) > FROM generate_series(1, 1_000_000) i > WHERE pg_describe_object('pg_database'::regclass, i, 0) IS NOT NULL; > > ... and with this function, the user could again obtain everything about > the database even when they can't read the catalog directly. > > Maybe checking privs for the database being dumped is enough protection > against this -- the equivalent of has_database_privilege( ..., 'CONNECT') > I suppose. > > -- > Álvaro Herrera PostgreSQL Developer — > https://www.EnterpriseDB.com/ > "¿Qué importan los años? Lo que realmente importa es comprobar que > a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda) >
v3-0001-Add-pg_get_database_ddl-function-to-reconstruct-C.patch
Description: Binary data
