<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi all,</p>
<p><a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/flat/1573808483712.96817@Optiver.com">https://www.postgresql.org/message-id/flat/1573808483712.96817@Optiver.com</a>
is about pg_dump problems after dropped roles, which may cause postgresql
major version update failing.</p>
<p>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.</p>
<p>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.</p>
<p>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</p>
<p><font face="monospace">select objoid, initprivs from
pg_init_privs where privtype='e';</font></p>
<p>shows {rdsadmin=arwdDxtm/rdsadmin,=r/rdsadmin} and objoids point
to postgis installed views and table.</p>
<p>When I now issue select postgis_extensions_upgrade(); and it
says </p>
<pre><font face="monospace">Updating extension postgis 3.3.3</font></pre>
<pre><font face="monospace">Upgrade to version 3.5.1 completed</font></pre>
<p>And now the above query on pg_init_privs shows long lists on
initprivs with all users on it.</p>
<p>My guess is that if I now drop any of those users, the result is
that the cluster may become not upgradeable. </p>
<p>This may be only related to pre-17 PostgreSQL as the drop user
behavior was fixed somehow regarding pg_init_privs:
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/1484313.1764115685%40sss.pgh.pa.us">https://www.postgresql.org/message-id/1484313.1764115685%40sss.pgh.pa.us</a></p>
<p>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".</p>
<p>Best regards,</p>
<p>Ari</p>
<p><br>
</p>
</body>
</html>