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
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
Greetings,
* Aleš Zelený (zeleny.ales@gmail.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
