Horton

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

Jim Jones <jim.jones@uni-muenster.de>

View thread
From: Jim Jones <jim.jones@uni-muenster.de>
To: Chao Li <li.evan.chao@gmail.com>, Manni Wood <manni.wood@enterprisedb.com>
Cc: Álvaro Herrera <alvherre@kurilemu.de>, Nishant Sharma <nishant.sharma@enterprisedb.com>, pgsql-hackers@postgresql.org
Date: 2025-11-19T07:52:49Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add infrastructure for pg_get_*_ddl functions

  2. Add pg_get_tablespace_ddl() function

  3. Split out innards of pg_tablespace_location()

  4. Remove spclocation field from pg_tablespace

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