postgis_extensions_upgrade() issue with pg_init_privs

Ari Jolma <[email protected]> Wed, 14 Jan 2026 08:12:05 +0200
Newsgroups gmane.comp.gis.postgis
Message-ID <[email protected]> 
This is a multi-part message in MIME format.
--------------5T0mbafPZ5gqkhtTpBTk8gf0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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


--------------5T0mbafPZ5gqkhtTpBTk8gf0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!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/[email protected]">https://www.postgresql.org/message-id/flat/[email protected]</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>

--------------5T0mbafPZ5gqkhtTpBTk8gf0--