Re: DROP OWNED BY fails to clean out pg_init_privs grants

Daniel Gustafsson <daniel(at)yesql(dot)se> writes:
> On 24 May 2024, at 16:20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

Thinking about this some more: the point of pg_init_privs is to record
an object's privileges as they stood at the end of CREATE EXTENSION
(or extension update), with the goal that pg_dump should be able to
compute the delta between that and the object's current privileges
and emit GRANT/REVOKE commands to restore those current privileges
after a fresh extension install. (We slide gently past the question
of whether the fresh extension install is certain to create privileges
matching the previous pg_init_privs entry.) So this goal seems to
mean that neither ALTER OWNER nor REASSIGN OWNED should touch
pg_init_privs at all, as that would break its function of recording
a historical state. Only DROP OWNED should get rid of pg_init_privs
grants, and that only because there's no choice -- if the role is
about to go away, we can't hang on to a reference to its OID.

However ... then what are the implications of doing ALTER OWNER on
an extension-owned object? Is pg_dump supposed to recognize that
that's happened and replay it too? If not, is it sane at all to
try to restore the current privileges, which are surely dependent
on the current owner? I kind of doubt that that's possible at all,
and even if it is it might result in security issues. It seems
like pg_init_privs has missed a critical thing, which is to record
the original owner not only the original privileges.

(Alternatively, maybe we should forbid ALTER OWNER on extension-owned
objects? Or at least on those having pg_init_privs entries?)

I'm wondering too about this scenario:

1. CREATE EXTENSION installs an object and sets some initial privileges.

2. DBA manually modifies the object's privileges.

3. ALTER EXTENSION UPDATE further modifies the object's privileges.

I think what will happen is that at the end of ALTER EXTENSION,
we'll store the object's current ACL verbatim in pg_init_privs,
therefore including the effects of step 2. This seems undesirable,
but I'm not sure how to get around it.

Anyway, this is starting to look like the sort of can of worms
best not opened post-beta1. v17 has made some things better in this
area, and I don't think it's made anything worse; so maybe we should
declare victory for the moment and hope to address these additional
concerns later. I've added an open item though.

regards, tom lane