Re: DROP OWNED BY fails to clean out pg_init_privs grants

Noah Misch <noah(at)leadboat(dot)com> writes:
> On Fri, Apr 05, 2024 at 07:10:59PM -0400, Tom Lane wrote:
>> The fact that the DROP ROLE added by 936e3fa37 succeeded indicates
>> that these role references weren't captured in pg_shdepend.
>> I imagine that we also lack code that would allow DROP OWNED BY to
>> follow up on such entries if they existed, but I've not checked that
>> for sure. In any case, there's probably a nontrivial amount of code
>> to be written to make this work.
>>
>> Given the lack of field complaints, I suspect that extension scripts
>> simply don't grant privileges to random roles that aren't the
>> extension's owner. So I wonder a little bit if this is even worth
>> fixing, as opposed to blocking off somehow. But probably we should
>> first try to fix it.

> This sounds closely-related to the following thread:
> https://www.postgresql.org/message-id/flat/1573808483712.96817%40Optiver.com

Oh, interesting, I'd forgotten that thread completely.

So Stephen was pushing back against dealing with the case because
he thought that the SQL commands issued in that example should not
have produced pg_init_privs entries in the first place. Which nobody
else wanted to opine on, so the thread stalled. However, in the case
of the test_pg_dump extension, the test_pg_dump--1.0.sql script
absolutely did grant those privileges so it's very hard for me to
think that they shouldn't be listed in pg_init_privs. Hence, I think
we've accidentally stumbled across a case where we do need all that
mechanism --- unless somebody wants to argue that what
test_pg_dump--1.0.sql is doing should be disallowed.

regards, tom lane