Thread

Re: [PATCH] Add pg_get_tablespace_ddl() function to reconstruct CREATE TABLESPACE statement

Manni Wood <manni.wood@enterprisedb.com> — 2025-11-19T22:52:12Z

On Wed, Nov 19, 2025 at 1:52 AM Jim Jones <jim.jones@uni-muenster.de> wrote:

>
> 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’t think 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
>

Hello, Chao.

Thanks as always for your ongoing help with improving this feature.

Instead of moving build_tablespace_ddl_string out of pg_tablespace.c, I
made get_reloptions visible outside of ruleutils.c.

Otherwise, I followed your advice on using get_reloptions to DRY up the
code.

Let me know what you think!
-- 
-- Manni Wood EDB: https://www.enterprisedb.com