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

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
>