From: Chao Li <li.evan.chao@gmail.com>
Message-Id: <4B28CBF6-7470-456A-A635-62FE28067AEE@gmail.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_B4765229-9A64-47A4-BDE3-53E368293F8D"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: Fix wrong error message from pg_get_tablespace_ddl()
Date: Sat, 9 May 2026 10:01:08 +0800
In-Reply-To: <af4YmulGI3pq2Rm0@alvherre.pgsql>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
To: =?utf-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>,
 Andrew Dunstan <andrew@dunslane.net>,
 Jim Jones <jim.jones@uni-muenster.de>
References: <af4YmulGI3pq2Rm0@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/4B28CBF6-7470-456A-A635-62FE28067AEE%40gmail.com>
Precedence: bulk


--Apple-Mail=_B4765229-9A64-47A4-BDE3-53E368293F8D
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8


> On May 9, 2026, at 01:20, =C3=81lvaro Herrera <alvherre@kurilemu.de> =
wrote:
>=20
> On 2026-May-08, Jim Jones wrote:
>=20
>> 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.
>=20
> 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?
>=20
> Although tablespaces may be special in that only superusers can "own"
> them anyway.
>=20
> 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 ...?
>=20
> --=20
> =C3=81lvaro Herrera         PostgreSQL Developer  =E2=80=94  =
https://www.EnterpriseDB.com/
> "This is a foot just waiting to be shot"                (Andrew =
Dunstan)

Thank you Jim, Andrew, and =C3=81lvaro for your feedback.

=46rom Andrew=E2=80=99s comment, I think I was too much driven by the =
root cause of the problem. =46rom a user=E2=80=99s 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.
```

=C3=81lvaro 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=E2=80=99s comment about avoiding hardcoding "pg_tablespace=E2=80=9D. =
And I also added a hint in pg_get_role_ddl_internal. With v2, the =
messages are like:
```
evantest=3D> 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=3D> 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/


--Apple-Mail=_B4765229-9A64-47A4-BDE3-53E368293F8D
Content-Disposition: attachment;
	filename=v2-0001-ddlutils-add-hints-for-catalog-privilege-failures.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v2-0001-ddlutils-add-hints-for-catalog-privilege-failures.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=201515cc70ba858c8991dbf2fca9b3b835bff25d78=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20<lic@highgo.com>=0A=
Date:=20Fri,=208=20May=202026=2015:53:23=20+0800=0ASubject:=20[PATCH=20=
v2]=20ddlutils:=20add=20hints=20for=20catalog=20privilege=20failures=0A=
MIME-Version:=201.0=0AContent-Type:=20text/plain;=20charset=3DUTF-8=0A=
Content-Transfer-Encoding:=208bit=0A=0Apg_get_role_ddl_internal()=20and=20=
pg_get_tablespace_ddl_internal()=20require=0ASELECT=20privilege=20on=20=
their=20underlying=20catalogs=20before=20reading=20object=0Aproperties.=20=
=20When=20this=20privilege=20check=20fails,=20the=20error=20message=20=
reports=0Athe=20user-facing=20object,=20such=20as=20the=20role=20or=20=
tablespace=20whose=20DDL=20was=0Arequested,=20but=20it=20does=20not=20=
tell=20the=20user=20which=20privilege=20is=20actually=0Amissing.=0A=0A=
Add=20errhint()=20messages=20to=20explain=20that=20SELECT=20on=20the=20=
relevant=20catalog=20is=0Aneeded=20to=20read=20the=20object=20=
properties.=20=20This=20keeps=20the=20primary=20error=0Amessage=20=
focused=20on=20the=20requested=20object,=20while=20giving=20users=20a=20=
concrete=0Away=20to=20resolve=20the=20failure.=0A=0AAuthor:=20Chao=20Li=20=
<lic@highgo.com>=0AReviewed-by:=20Jim=20Jones=20=
<jim.jones@uni-muenster.de>=0AReviewed-by:=20Andrew=20Dunstan=20=
<andrew@dunslane.net>=0AReviewed-by:=20=C3=81lvaro=20Herrera=20=
<alvherre@kurilemu.de>=0AReviewed-by:=20Zhenwei=20Shang=20=
<a934172442@gmail.com>=0ADiscussion:=20=
https://postgr.es/m/53D05145-CE87-424F-A492-BB22A09BBE11@gmail.com=0A---=0A=
=20src/backend/utils/adt/ddlutils.c=20|=2013=20++++++++++---=0A=201=20=
file=20changed,=2010=20insertions(+),=203=20deletions(-)=0A=0Adiff=20=
--git=20a/src/backend/utils/adt/ddlutils.c=20=
b/src/backend/utils/adt/ddlutils.c=0Aindex=20d6f55c48f37..db72854b1c5=20=
100644=0A---=20a/src/backend/utils/adt/ddlutils.c=0A+++=20=
b/src/backend/utils/adt/ddlutils.c=0A@@=20-342,8=20+342,10=20@@=20=
pg_get_role_ddl_internal(Oid=20roleid,=20bool=20pretty,=20bool=20=
memberships)=0A=20=09{=0A=20=09=09ReleaseSysCache(tuple);=0A=20=09=09=
ereport(ERROR,=0A-=09=09=09=09(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),=0A=
-=09=09=09=09=20errmsg("permission=20denied=20for=20role=20%s",=20=
rolname)));=0A+=09=09=09=09errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),=0A+=09=
=09=09=09errmsg("permission=20denied=20for=20role=20\"%s\"",=20rolname),=0A=
+=09=09=09=09errhint("Grant=20SELECT=20on=20catalog=20\"%s\"=20to=20read=20=
role=20properties.",=0A+=09=09=09=09=09=09=
get_rel_name(AuthIdRelationId)));=0A=20=09}=0A=20=0A=20=09/*=0A@@=20=
-681,7=20+683,12=20@@=20pg_get_tablespace_ddl_internal(Oid=20tsid,=20=
bool=20pretty,=20bool=20no_owner)=0A=20=09if=20=
(pg_class_aclcheck(TableSpaceRelationId,=20GetUserId(),=20ACL_SELECT)=20=
!=3D=20ACLCHECK_OK)=0A=20=09{=0A=20=09=09ReleaseSysCache(tuple);=0A-=09=09=
aclcheck_error(ACLCHECK_NO_PRIV,=20OBJECT_TABLESPACE,=20spcname);=0A+=09=09=
ereport(ERROR,=0A+=09=09=09=09errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),=0A=
+=09=09=09=09errmsg("permission=20denied=20for=20tablespace=20\"%s\"",=0A=
+=09=09=09=09=09=20=20=20spcname),=0A+=09=09=09=09errhint("Grant=20=
SELECT=20on=20catalog=20\"%s\"=20to=20read=20tablespace=20properties.",=0A=
+=09=09=09=09=09=09get_rel_name(TableSpaceRelationId)));=0A=20=09}=0A=20=0A=
=20=09/*=0A--=20=0A2.50.1=20(Apple=20Git-155)=0A=0A=

--Apple-Mail=_B4765229-9A64-47A4-BDE3-53E368293F8D--