Re: DROP OWNED BY fails to clean out pg_init_privs grants

> On 24 May 2024, at 16:20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I've tentatively concluded that I shouldn't have modeled
> SHARED_DEPENDENCY_INITACL so closely on SHARED_DEPENDENCY_ACL,
> in particular the decision that we don't need such an entry if
> there's also SHARED_DEPENDENCY_OWNER.

+1, in light of this report I think we need to go back on that.

> I can see two routes to a solution:
>
> 1. Create SHARED_DEPENDENCY_INITACL, if applicable, whether the
> role is the object's owner or not. Then, clearing out the
> pg_shdepend entry cues us to go delete the pg_init_privs entry.
>
> 2. Just always search pg_init_privs for relevant entries
> when dropping an object.
>
> I don't especially like #2 on performance grounds, but it has
> a lot fewer moving parts than #1.

#1 is more elegant, but admittedly also more complicated. An unscientific
guess is that a majority of objects dropped won't have init privs, making the
extra scan in #2 quite possibly more than academic. #2 could however be
backported and solve the issue in existing clusters.

> Another point: shdepReassignOwned explicitly does not touch grants
> or default ACLs. It feels like the same should be true of
> pg_init_privs entries,

Agreed, I can't see why pg_init_privs should be treated differently.

> Another thing I'm wondering about right now is privileges on global
> objects (roles, databases, tablespaces). The fine manual says
> "Although an extension script is not prohibited from creating such
> objects, if it does so they will not be tracked as part of the
> extension". Presumably, that also means that no pg_init_privs
> entries are made; but do we do that correctly?

I'm away from a tree to check, but that does warrant investigation. If we
don't have a test for it already then it might be worth constructing something
to catch that.

--
Daniel Gustafsson