MIME-Version: 1.0
In-Reply-To: <4B28CBF6-7470-456A-A635-62FE28067AEE@gmail.com>
References: <af4YmulGI3pq2Rm0@alvherre.pgsql>
 <4B28CBF6-7470-456A-A635-62FE28067AEE@gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Fri, 8 May 2026 19:52:25 -0700
Message-ID: 
 <CAKFQuwbtJopaWEPj=YM96Nu7qn-K1XofRGpU9j660kUEYyPtzg@mail.gmail.com>
Subject: Re: Fix wrong error message from pg_get_tablespace_ddl()
To: Chao Li <li.evan.chao@gmail.com>
Cc: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>,
	Andrew Dunstan <andrew@dunslane.net>, Jim Jones <jim.jones@uni-muenster.de>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000079beb20651599a8d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwbtJopaWEPj%3DYM96Nu7qn-K1XofRGpU9j660kUEYyPtzg%40mail.gmail.com>
Precedence: bulk

--00000000000079beb20651599a8d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Friday, May 8, 2026, Chao Li <li.evan.chao@gmail.com> wrote:

>
>
> > On May 9, 2026, at 01:20, =C3=81lvaro Herrera <alvherre@kurilemu.de> wr=
ote:
> >
> > 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 the=
m
> > 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=E2=80=99s comment, I think I was too much driven by the root =
cause of
> the problem. From a user=E2=80=99s perspective, if they are trying to vie=
w the DDL
> of "ts1", but the command fails with an error against "pg_tablespace", th=
at
> 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 b=
e
> 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=
.
> ```
>
> =C3=81lvaro seems to bring the question to a deeper level, and I feel tha=
t
> 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 th=
e
> user to create objects within that tablespace, but it does not necessaril=
y
> mean the user is allowed to inspect the definition of the tablespace itse=
lf.
>


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=E2=80=99t see the benefit to jumping through hoops making this=
 feature
work in a world where that isn=E2=80=99t true.  If you cannot read the cata=
log 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=E2=80=99d 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=E2=80=99m fine gating the object-based output behind an RLS policies on c=
atalogs
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.

--00000000000079beb20651599a8d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Friday, May 8, 2026, Chao Li &lt;<a href=3D"mailto:li.evan.chao@gmail.co=
m">li.evan.chao@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gmail_quot=
e" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">=
<br>
<br>
&gt; On May 9, 2026, at 01:20, =C3=81lvaro Herrera &lt;<a href=3D"mailto:al=
vherre@kurilemu.de">alvherre@kurilemu.de</a>&gt; wrote:<br>
&gt; <br>
&gt; On 2026-May-08, Jim Jones wrote:<br>
&gt; <br>
&gt;&gt; It depends on what we expect from the error message. If its purpos=
e is<br>
&gt;&gt; simply to tell the user &quot;you can&#39;t access this object,&qu=
ot; the current message<br>
&gt;&gt; is totally fine. If, however, the goal is to show the error&#39;s =
root cause, it<br>
&gt;&gt; could be a bit misleading.<br>
&gt; <br>
&gt; Hmm, the idea in my mind was that if SELECT from the catalog is<br>
&gt; revoked, but the user does have a grant on the tablespace that lets th=
em<br>
&gt; read the DDL, then they should be able to obtain the CREATE statement<=
br>
&gt; for it even though they cannot read the properties from the catalog<br=
>
&gt; directly.=C2=A0 The current coding does not seem to do that, but inste=
ad<br>
&gt; it refuses to produce the DDL.=C2=A0 Is this really what we want?<br>
&gt; <br><br>