Message-ID: <001adbbd-8a33-4992-a5db-f2623ca4d8ca@yeah.net>
Date: Fri, 14 Nov 2025 09:13:30 +0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
From: Quan Zongliang <quanzongliang@yeah.net>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE
 DATABASE statement
To: pgsql-hackers <pgsql-hackers@postgresql.org>
References: <202511131220.vxws67smjbvn@alvherre.pgsql>
Content-Language: en-US
In-Reply-To: <202511131220.vxws67smjbvn@alvherre.pgsql>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/001adbbd-8a33-4992-a5db-f2623ca4d8ca%40yeah.net>
Precedence: bulk


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?