Re: Invalid dump file after drop of role that previously created extension containing a table.

Hello,

during other upgrades, I've faced the same issue (with different
extensions) on 3 out of 9 databases I've been upgrading (of course, it
happened in the production environment).

So far, I've decided to add a check query to the database upgrade workflow:

select initprivs::text::aclitem[] from pg_init_privs;

It fails in case a dropped role OID is persisted in the
pg_catalog.pg_init_privs on databases being upgraded, would it make sense
to extend pg_upgrade check mode by a query like this (probably better
one...)?

Kind regards Ales Zeleny

pá 22. 9. 2023 v 14:56 odesílatel Aleš Zelený <zeleny(dot)ales(at)gmail(dot)com>
napsal:

> Hello,
>
> my personal understanding of the pg_init_privs usage is to achieve some
> level of idempotency (some level -> it perfectly works at least if the role
> that created the extension is the same one that is importing the dump -
> other combinations are producing more or less different results).
>
> So the pg_dump attempts to combine the past time state from pg_init_privs
> with the present state at the dump creation time.
> Revoking all privileges from extension tables (and other objects) might
> solve it partially if all privileges for the extension objects are dumped
> and therefore applied on restore. Sadly, except (at least the following
> one) an issue - the dump file does not contain the extension version so if
> a newer extension version is created during restoration from a dump file
> there might be additional privileges defined by the new extension version
> and thus it is a bad idea to "revoke all" since they will be lost.
>
> While it might take some time to get a sustainable solution, a non-ideal
> workaround I could imagine could be that the dump will check whether the
> role(s) stored in the pg_init_privs still exists at the time of dump
> creation and in case they are no longer the revoke statement will not be
> stored in the dump.
>
> Kind regards Ales Zeleny
>
>
> čt 21. 9. 2023 v 21:30 odesílatel Stephen Frost <sfrost(at)snowman(dot)net>
> napsal:
>
>> Greetings,
>>
>> * Aleš Zelený (zeleny(dot)ales(at)gmail(dot)com) wrote:
>> > Testcase description:
>> > ====================
>> > 1) An extension (I've used pg_cron as an example because it contains a
>> > table) is created by a database user (login role), and the initial
>> > privileges at extension creation are stored for the extension object
>> > (table in my test case) in the pg_catalog.pg_init_privs table.
>> > 2) Change the database user objects ownership from step 1 to another
>> > database user -> this step keeps the pg_catalog.pg_init_privs table
>> > content for the extension table from step 1 untouched.
>> > 3) Drop the database user used in step 1 and as its entry is deleted
>> > from the catalog, all that remains is the OID of the deleted database
>> > user in the pg_catalog.pg_init_privs table, later used by pg_dump.
>>
>> Hrmpf. Yeah, seems like if we're going to allow extensions and
>> extension objects to be impacted by REASSIGN OWNED and such then we need
>> to be sure to update pg_init_privs accordingly. At least that's my
>> first thought seeing this. Would welcome thoughts from others on this
>> though.
>>
>> Thanks,
>>
>> Stephen
>>
>