Cloud SQL - PostgreSQL - Import failed due to the lack of superuser permission

Question

I'm migrating all the role from my PostgreSQL hosted in GCE VM to Cloud SQL by generating dump file

sudo -Hu postgres pg_dumpall -U postgres --globals-only --file=globals.sql

When I import the same(globals.sql) in Cloud SQL I came across below error:

exit status 3 SET SET SET CREATE ROLE ERROR: must be superuser to alter superusers

Note: I used postgres user to import this dump file to the cloud sql database.

I'm curious is there any other way to tackle this since postgres user does not have superuser privileges?

I tried executed one query from globals.sql file using cloud shell, below is the output:

postgres=> CREATE ROLE vipinm;
CREATE ROLE
postgres=> ALTER ROLE vipinm WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
ERROR:  must be superuser to alter superusers

Thanks in advance!

Answer 1

The psql documentation says:

psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g., out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

So don't set ON_ERROR_STOP.

The error means that you cannot execute the following line from your dump:

ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;

That is fine, and you can ignore the error.

Answer 2

This is kind of a bug. As a non-superuser, you can't even reiterate that another role is still not a superuser, as even mentioning anything about superusers even when it would have no effect throws an error. You can get around this by creating the role in its final state, rather than doing the CREATE then ALTER dance that pg_dump likes to do.

 CREATE ROLE vipinm WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;

Alternatively, you could remove from the ALTER statement all the attributes that don't cause any change but merely reiterate the current state of things, leaving:

 ALTER ROLE vipinm WITH LOGIN;