[google-cloud-sql-discuss] Re: Upgrade GCP hosted PostgreSQL while keeping existing user passwords

Did any of you manage to resolve this? I am in the same situation now and it doesn't look like a good solution exists. I am particularly concerned about the part about passwords. Some of the user accounts in the source database I need to migrate belonged to users who have left the company now. I don't know what their passwords were. Others are "machine" users, used by other parts of the company that I do not have access to. Getting hold of those passwords will require someone to sign off on my permission to do so, i.e. hoops to jump through. It feels a bit crazy that moving from postgres vX to vY on Google Cloud should be this complicated.

Just out of curiosity, do you know what will happen if I recreate the user accounts with the same name and permissions, but with different passwords? Will that work, or block the migration?

On Monday, 4 October 2021 at 10:43:49 UTC+2 davis...@gmail.com wrote:

My database for Lil Peep's rare merch suffers from the same problem. I have read all documentation, but cannot resolve the issue.

On Wednesday, 1 September 2021 at 12:37:09 UTC+5 jo...@vendohm.com wrote:

I have been tasked with upgrading our existing GCP hosted PostgreSQL databases from version 9.6 to version 13.

The instructions I found (https://cloud.google.com/sql/docs/postgres/upgrade-db)

say that we need to create a new database and then

"Make sure the target instance has... The same user accounts, with the same PostgreSQL privileges and passwords"

We have a number of databases with dozens of users and roles, we allow users to connect directly and set their own passwords.

We would like to perform the database upgrade without changing all the passwords.

I've looked at using pg_dumpall to copy the users to a new database, but it fails as I don't have permission to read pg_authid.

It seems that we need a superuser account to be able to read pg_authid, and GCP does not permit superuser accounts.

Is there any way to upgrade the databases and keep the existing passwords?

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/5b550a6b-6cb3-4829-bfbd-b643b84861c8n%40googlegroups.com.