public inbox for [email protected]  

help / color / mirror / Atom feed

From: Quan Zongliang <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
Date: Fri, 14 Nov 2025 09:13:30 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>



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?

view thread (38+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox