Message-ID: <91b25e9b-5761-4046-84ef-cb73b5db17e6@dunslane.net>
Date: Fri, 8 May 2026 08:07:38 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Fix wrong error message from pg_get_tablespace_ddl()
To: Jim Jones <jim.jones@uni-muenster.de>, Chao Li <li.evan.chao@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <53D05145-CE87-424F-A492-BB22A09BBE11@gmail.com>
 <9cc5e7a5-1cbc-4e07-ace4-ad04a8e1a6fe@uni-muenster.de>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAlwQTAQgAQQIbAwIX
 gAIZAQULCQgHAwUVCgkICwUWAgMBAAIeBRYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNGd
 BQkdEO8nAAoJEJn6f8tZ/DuBq74H/jkTR4Zi3stbw+xC7v2u3QozssK7MYPL2AsVfh7OealS
 h182fiWXpfvmmAB7WUHbhk9GC2RAOnHI/2d2jgKaMLAHsGYOT0YopTVIwRY43fCw/mK67yxc
 wmDcX+zyKfLaivNbf5A7QPLNwda98bEAMSJ8Sn652Uc6cA8t3uKGsVzbRBQOoYzjgvBCfSrE
 9ql3PDNg0l4BfAqabd2f70ZUm9VAMEPrgv/v2xI7M2XiL4g5BVmqLCOwxLM8RMCotCuoweUr
 VO43DeBCIDwLxotMJKvGWDjBzQYlU1NPUAtNcz/gN9ITUe1VUGjyvGj4u1lxBOcQQUw7l1+T
 5moZ4iZxXzvOwE0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oKWaqf+wmW4tpDe/VV
 cxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7llV0xSSoFiieQBsr
 GvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8BoVrRFcdzlIY/NFN
 hFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C4R+M6w7o2PLGOjdZ
 +kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaYL++LAEOqrlvm2Tvq
 QqZRtnEAEQEAAcLAfAQYAQgAJgIbDBYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNI2BQkd
 EODdAAoJEJn6f8tZ/DuBfw0IAKTsfD40teP/pp+bsLLMSxPXUYrrprTj7WFB5v61p6dkpSr/
 qXmMlyahdxQFaPmfVgVirB1Vk/kHiWNnnGjfUV9nB2Zg9LI0Xb9/ts3LsUiRWXzG3tkMY6XL
 vsVOxW4XFRND9l2q+WW93aZ1DZl+fqWfYgMvsusFRhmGFOKTRfKPta2Pkv+AhA24N4+PrR5p
 bU4k2MO8PAGiK8eaYKGFG1bHKuAvoDoF7WXJ3FHxuWqLnKEt4dfOLm5pAe3zq1Lt6q8azT9i
 QWGpSAK5vQUWQHBHpiDjdPeqKZ6HiAXIIKfSmb+jrvXBqoP+D6/K7rUjG2aXiRtTIAXms9sm
 VRu7cmw=
In-Reply-To: <9cc5e7a5-1cbc-4e07-ace4-ad04a8e1a6fe@uni-muenster.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/91b25e9b-5761-4046-84ef-cb73b5db17e6%40dunslane.net>
Precedence: bulk


On 2026-05-08 Fr 5:20 AM, Jim Jones wrote:
> Hi Chao
>
> On 08/05/2026 10:14, Chao Li wrote:
>> This is easy to reproduce:
>> ```
>> evantest=# set allow_in_place_tablespaces = true;
>> SET
>> evantest=# create role r1;
>> CREATE ROLE
>> evantest=# create tablespace ts1 location '';
>> CREATE TABLESPACE
>> evantest=# revoke select on pg_tablespace from r1;
>> REVOKE
>> evantest=# set role r1;
>> SET
>> evantest=> select * from pg_get_tablespace_ddl('ts1');
>> ERROR:  permission denied for tablespace ts1
>> ```
>>
>> Attached is a simple one-line fix. Attached is a simple one-line fix. 
>> I did not add a new test, as we usually try to avoid extending the 
>> test time for such a small fix. With the fix, the error message now 
>> looks like:
>> ```
>> evantest=> select * from pg_get_tablespace_ddl('ts1');
>> ERROR:  permission denied for table pg_tablespace
>> ```
>
> A few comments:
>
> == hardcoded table name ==
>
> Hardcoding "pg_tablespace" looks IMO a bit fragile. What about
> get_rel_name(TableSpaceRelationId) instead?
>
> See get_database_name(dbid) in pg_get_database_ddl_internal().
>
> == similar issue in pg_get_role_ddl_internal ==
>
> If we do this change, we should also address pg_authid to keep the 
> code consistent:
>
> /* User must have SELECT privilege on pg_authid. */
> if (pg_class_aclcheck(AuthIdRelationId, GetUserId(), ACL_SELECT) != 
> ACLCHECK_OK)
> {
>   ReleaseSysCache(tuple);
>   ereport(ERROR,
>   (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
>     errmsg("permission denied for role %s", rolname)));
> }
>
> Perhaps something like this instead of the ereport:
>
> aclcheck_error(ACLCHECK_NO_PRIV, OBJECT_TABLE, 
> get_rel_name(AuthIdRelationId));
>
>

I'm not 100% convinced these are in fact wrong. The user asks for the 
DDL for a named role or tablespace and we tell them that they don't have 
the privilege to get the information for that object. If we tell them 
that the problem is that they don't have privilege on the underlying 
catalog table, they might think "Well, I didn't ask for that".


cheers


andrew


>
>
--
Andrew Dunstan
EDB: https://www.enterprisedb.com