Re: DROP OWNED BY fails to clean out pg_init_privs grants

On Sat, May 25, 2024 at 4:48 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Hannu Krosing <hannuk(at)google(dot)com> writes:
> > Having an pg_init_privs entry referencing a non-existing user is
> > certainly of no practical use.
>
> Sure, that's not up for debate. What I think we're discussing
> right now is
>
> 1. What other cases are badly handled by the pg_init_privs
> mechanisms.
>
> 2. How much of that is practical to fix in v17, seeing that
> it's all long-standing bugs and we're already past beta1.
>
> I kind of doubt that the answer to #2 is "all of it".
> But perhaps we can do better than "none of it".

Putting the fix either in pg_dump or making REVOKE tolerate
non-existing users would definitely be most practical / useful fixes,
as these would actually allow pg_upgrade to v17 to work without
changing anything in older versions.

Currently one already can revoke a privilege that is not there in the
first place, with the end state being that the privilege (still) does
not exist.
This does not even generate a warning.

Extending this to revoking from users that do not exist does not seem
any different on conceptual level, though I understand that
implementation would be very different as it needs catching the user
lookup error from a very different part of the code.

That said, it would be better if we can have something that would be
easy to backport something that would make pg_upgrade work for all
supported versions.
Making REVOKE silently ignore revoking from non-existing users would
improve general robustness but could conceivably change behaviour if
somebody relies on it in their workflows.

Regards,
Hannu