Re: DROP OWNED BY fails to clean out pg_init_privs grants

On Fri, Apr 05, 2024 at 07:10:59PM -0400, Tom Lane wrote:
> I wondered why buildfarm member copperhead has started to fail
> xversion-upgrade-HEAD-HEAD tests. I soon reproduced the problem here:
>
> pg_restore: creating ACL "regress_pg_dump_schema.TYPE "test_type""
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 4355; 0 0 ACL TYPE "test_type" buildfarm
> pg_restore: error: could not execute query: ERROR: role "74603" does not exist
> Command was: SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);
> GRANT ALL ON TYPE "regress_pg_dump_schema"."test_type" TO "74603";
> SELECT pg_catalog.binary_upgrade_set_record_init_privs(false);
> REVOKE ALL ON TYPE "regress_pg_dump_schema"."test_type" FROM "74603";
>
> (So now I'm wondering why *only* copperhead has shown this so far.
> Are our other cross-version-upgrade testing animals AWOL?)
>
> I believe this is a longstanding problem that was exposed by accident
> by commit 936e3fa37. If you run "make installcheck" in HEAD's
> src/test/modules/test_pg_dump, and then poke around in the leftover
> contrib_regression database, you can find dangling grants in
> pg_init_privs:
>
> contrib_regression=# table pg_init_privs;
> objoid | classoid | objsubid | privtype | initprivs
>
> --------+----------+----------+----------+--------------------------------------
> ---------------------------
> ...
> es}
> 43134 | 1259 | 0 | e | {postgres=rwU/postgres,43125=U/postgr
> es}
> 43128 | 1259 | 0 | e | {postgres=arwdDxtm/postgres,43125=r/p
> ostgres}
> ...
>
> 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