Unable to import a Database to new Cloud SQL instance

Question

I am trying to import a cloud sql postgres db instance to a newer instance with the same users created but i am getting this error:

.....ALTER DEFAULT PRIVILEGES ALTER DEFAULT PRIVILEGES stderr: ERROR: must be member of role "readonly"

Btw readonly is a user which use to connect our read only apps, but the problem is that to my expectation I should be able to export an instance and import it without any problem what am I missing here.

My exact steps

1. Export DB from cloud sql interface
2. Create a new db with a user named "proxyuser" (old convention in the company)
3. Import through the cloud sql, and select the user to "proxyuser"

After this the import fails at 2 hour mark with the above error message.

Side note: The import is from a replica DB in the another instance (i dont think so it matters, but let me know if it does)

Answer 1

This is a permission issue and to fix this as per the suggestions provided in the GCP documentation and the tips from this thread, Google Postgres team here.
The process while creating a new database and importing the user must be a member of the role that will be the owner of the database. Additionally, as stated in the documentation, before importing a SQL dump, all the database users who own objects or were granted permissions on objects in the dumped database must exist.
You can find more information on CloudSQL PostgreSQL users and roles here 4. As per the PostgreSQL documentation, "you can change default privileges only for objects that will be created by yourself or by roles that you are a member of." You may also try to run the import itself by the user who is the owner of the database that you want to import data to. You may have a look at the following examples for similar example:

• Error must be member of role when creating schema
• Must be member of role error