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

From: Álvaro Herrera <alvherre(at)kurilemu(dot)de>
To: Quan Zongliang <quanzongliang(at)yeah(dot)net>
Cc: Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
Date: 2025-11-14 11:12:21
Message-ID: 202511131446.uzn4c25ljmd4@alvherre.pgsql
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2025-11-14 11:12:38 Re: POC: enable logical decoding when wal_level = 'replica' without a server restart
Previous Message Álvaro Herrera 2025-11-14 11:00:46 Re: Reorganize GUC structs