Message-ID: <06371cc5-1117-459f-88d6-a96ffbc6ac40@dunslane.net>
Date: Sun, 10 May 2026 08:53:17 -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>,
 =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <af4YmulGI3pq2Rm0@alvherre.pgsql>
 <4B28CBF6-7470-456A-A635-62FE28067AEE@gmail.com>
 <560b391a-2b3a-4009-a90b-b5e5863aa89d@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: <560b391a-2b3a-4009-a90b-b5e5863aa89d@uni-muenster.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/06371cc5-1117-459f-88d6-a96ffbc6ac40%40dunslane.net>
Precedence: bulk


On 2026-05-10 Su 7:10 AM, Jim Jones wrote:
> Hi Chao
>
> On 09/05/2026 04:01, Chao Li wrote:
>> Á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.
>
>
> Yeah, this is a good point. I don't have a strong opinion about it, 
> but I'd be inclined to simply deny access to the DDL if the user does 
> not have enough privileges -- at least I wouldn't mind seeing an error 
> message in my logs :)
>
>
>> 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.
>
>
> I'm not sure that telling unprivileged users to grant themselves 
> access to pg_tablespace is an improvement -- IMO, a HINT here is 
> supposed to be actionable. Perhaps a DETAIL would be a better fit, 
> e.g. "DETAIL:  The function requires SELECT privilege on catalog 
> "pg_tablespace"."
>
> On top of that, I'm also not sure that replacing the aclcheck_error 
> with an ereport just for the hint/detail is an option, since 
> aclcheck_error is supposed to provide "Standardized reporting of 
> aclcheck permissions failures." (from the aclcheck_error header comment)
>
>

I keep coming back to this point: if the user can access pg_tablespace 
they can see the information anyway. This is an informational function, 
and there is no implied guarantee that the user is going to be able to 
run the supplied DDL. I don't think there's anything to do here.


cheers


andrew


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