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.
> 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)
