Re: Fix wrong error message from pg_get_tablespace_ddl()

> On May 9, 2026, at 01:20, Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:
>
> On 2026-May-08, Jim Jones wrote:
>
>> It depends on what we expect from the error message. If its purpose is
>> simply to tell the user "you can't access this object," the current message
>> is totally fine. If, however, the goal is to show the error's root cause, it
>> could be a bit misleading.
>
> Hmm, the idea in my mind was that if SELECT from the catalog is
> revoked, but the user does have a grant on the tablespace that lets them
> read the DDL, then they should be able to obtain the CREATE statement
> for it even though they cannot read the properties from the catalog
> directly. The current coding does not seem to do that, but instead
> it refuses to produce the DDL. Is this really what we want?
>
> Although tablespaces may be special in that only superusers can "own"
> them anyway.
>
> TBH I'm undecided about how this should work. If somebody has
> ACL_CREATE on a certain tablespace, should she be able to know what the
> spcoptions are, for instance? What about a database owner whose default
> tablespace is that one? Maybe we'd hide the location unless superuser,
> and show the rest ...?
>
> --
> Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
> "This is a foot just waiting to be shot" (Andrew Dunstan)

Thank you Jim, Andrew, and Álvaro for your feedback.

From Andrew’s comment, I think I was too much driven by the root cause of the problem. From a user’s perspective, if they are trying to view the DDL of "ts1", but the command fails with an error against "pg_tablespace", that could be confusing. So, how about keeping the original error message and adding a hint about how to resolve the error? Otherwise, the user might be misled into granting privileges on "ts1" itself, which would not help resolve the problem. For example:

```
ERROR: permission denied for tablespace ts1
HINT: Grant SELECT on catalog pg_tablespace to read tablespace properties.
```

Álvaro seems to bring the question to a deeper level, and I feel that might be worth a dedicated discussion. For example, I am not sure ACL_CREATE on the tablespace is enough to imply visibility of the tablespace DDL. My understanding is that CREATE on a tablespace allows the user to create objects within that tablespace, but it does not necessarily mean the user is allowed to inspect the definition of the tablespace itself.

How about keeping the scope of this patch narrow, as only adding a hint to guide users on how to fix the error if they really need to view the DDL of the tablespace? I will start a separate thread for the discussion of the access-checking model.

The attached v2 keeps the original error message and adds a hint. I took Jim’s comment about avoiding hardcoding "pg_tablespace”. And I also added a hint in pg_get_role_ddl_internal. With v2, the messages are like:
```
evantest=> select * from pg_get_tablespace_ddl('ts1');
ERROR: permission denied for tablespace "ts1"
HINT: Grant SELECT on catalog "pg_tablespace" to read tablespace properties.

evantest=> select * from pg_get_role_ddl('r1');
ERROR: permission denied for role "r1"
HINT: Grant SELECT on catalog "pg_authid" to read role properties.
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/