Re: [PATCH] Add pg_get_tablespace_ddl() function to reconstruct CREATE TABLESPACE statement - Mailing list pgsql-hackers

Hi Chao

On 19/11/2025 04:13, Chao Li wrote:
> 1. Do we need to perform some privilege check? I just did a test:
> ```
> evantest=> \c
> You are now connected to database "evantest" as user "evan".
> evantest=> select pg_get_tablespace_ddl('pg_default');
>            pg_get_tablespace_ddl
> -------------------------------------------
>  CREATE TABLESPACE pg_default OWNER chaol;
> (1 row)
> ```
> 
> Where “evan” is a new user without grant any persuasion to it, but it can view the system default tablespace’s DDL. I
don’tthink that’s expected.
 

It is expected. \db behaves similarly:


CREATE TABLESPACE ts LOCATION '/tmp/ts';
CREATE TABLESPACE

postgres=# CREATE USER foo;
CREATE ROLE

postgres=# SET ROLE foo;
SET

postgres=> \db ts
   List of tablespaces
 Name | Owner | Location
------+-------+----------
 ts   | jim   | /tmp/ts
(1 row)


IIUC the user foo is just reading the catalog entry of the new
tablespace, which is fine. Of course, accessing the tablespace itself is
not allowed. See \db+ (calculates the tablespace's size)


postgres=> \db+ ts
ERROR:  permission denied for tablespace ts

Best, Jim