Hi all,
https://www.postgresql.org/message-id/flat/[email protected]
is about pg_dump problems after dropped roles, which may
cause postgresql major version update failing.
I witnessed this on our PostgreSQL AWS RDS installation (version 15.12,
upgrading to 17.4) and I had to ask AWS Support to fix the issue - I
could not do it since RDS users do not have full access to the cluster.
I was wondering what caused it since it was not in all our databases
with PostGIS extension installed. Playing around revealed a possible
route to the situation where the major version upgrade fails and needs
fixing by the support.
I have PostGIS extension in a database (apparently it is 3.3.3 although
SELECT postgis_full_version(); shows 3.5.1) and it has probably not been
upgraded. Issuing
select objoid, initprivs from pg_init_privs where privtype='e';
shows {rdsadmin=arwdDxtm/rdsadmin,=r/rdsadmin} and objoids point to
postgis installed views and table.
When I now issue select postgis_extensions_upgrade(); and it says
Updating extension postgis 3.3.3
Upgrade to version 3.5.1 completed
And now the above query on pg_init_privs shows long lists on initprivs
with all users on it.
My guess is that if I now drop any of those users, the result is that
the cluster may become not upgradeable.
This may be only related to pre-17 PostgreSQL as the drop user behavior
was fixed somehow regarding pg_init_privs:
https://www.postgresql.org/message-id/1484313.1764115685%40sss.pgh.pa.us
This may be just an inconvenience for self-managed PostgreSQL
installations but it's a main PITA for AWS RDS which is a "managed service".
Best regards,
Ari
