On 11/13/25 8:28 PM, Álvaro Herrera wrote:
>> But what about the following scenario? If there is no permission to access
>> pg_database. Shouldn't the DDL be returned?
>>
>> postgres=> SELECT * FROM pg_database;
>> ERROR: permission denied for table pg_database
>> postgres=> SELECT pg_get_database_ddl('testdb');
>
> Hmm, what scenario is this? Did you purposefully REVOKE the SELECT
> privileges from pg_database somehow?
>
Yes. I revoked the access permission using the REVOKE command.
The pg_get_xxx_ddl function is actually revealing system information to
the users. This is equivalent to accessing the corresponding system
table. So I think we should consider this issue.
The access permission to the system tables has been revoked. This user
is unable to directly view the contents of the system tables from the
client side via SQL. However, it is still possible to obtain the object
definitions (which was previously inaccessible) through pg_get_xxx_ddl.
This is more like a security flaw.
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
And after having this function. However, users can view these in another
format.
postgres=> SELECT pg_get_database_ddl('testdb');
------------- ...
CREATE DATABASE testdb WITH OWNER = quanzl ENCODING = "UTF8" ...
Perhaps it's just that I'm overthinking things. What do you think about it?
