Thread

Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement

Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-11-17T14:39:54Z

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 <alvherre@kurilemu.de> 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)
>