Re: Fix wrong error message from pg_get_tablespace_ddl()

On Friday, May 8, 2026, Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:

>
>
> > 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?
> >
>
> 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.
>

The system is designed and built with the assumption that knowledge of
catalog contents are not private (aside from a few security-related cases).
I really don’t see the benefit to jumping through hoops making this feature
work in a world where that isn’t true. If you cannot read the catalog in
question your superuser did something outside of our design and us choosing
to refuse to produce any DDL requiring the contents of that catalog is
reasonable. I’d draw the line that if any part of the DDL we would produce
is restricted the entire production is halted, not that we will provide a
best effort result.

IOW, parity with pg_dump seems reasonable.

Reporting which catalogs are restricted is a good message to send.

I’m fine gating the object-based output behind an RLS policies on catalogs
feature so we can at least let people leave select in place and restrict
output to owners/admins of the objects in question.

David J.