postgis_extensions_upgrade() issue with pg_init_privs

[Ari Jolma]

Hi all,

https://www.postgresql.org/message-id/flat/1573808483712.96817@Optiver.com 
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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20260114/26ba9d66/attachment.htm>