Migrate users with credentials to AlloyDB or another CloudSQL instance.

Database users and roles are integral parts of databases and need to be migrated as part of overall database objects. We might have cases on migrating user from PostgreSQL managed instance with same password and privilege to another instance. With AlloyDB being available in preview, we might have a use case to migrate Users from CloudSQL to AlloyDB along with data for proof of concepts.

PostgreSQL provides pg_dumpall command line to migrate complete instances with multiple databases including users and roles. Below is the sample command to export users or roles from PostgreSQL.

pg_dumpall -h <<CLOUD_SQL_INSTANCE_IP>> -U postgres -r — if-exists -c

Let’s assume a source Cloud SQL instance with the below users and roles. Highlighted ones are the user created and we will migrate it using pg_dumpall.

Export User and Roles without password

Now let’s run the pg_dumpall command and check and output.

deepakmahto@db-benchmark:~$ pg_dumpall -h <<CLOUD_SQL_INSTANCE_IP>> -U postgres -r --if-exists -c
Password: 
 — 
 — PostgreSQL database cluster dump
 —
SET default_transaction_read_only = off;
SET client_encoding = ‘UTF8’;
SET standard_conforming_strings = on;
pg_dumpall: error: query failed: ERROR: permission denied for table pg_authid
pg_dumpall: error: query was: SELECT rolname FROM pg_authid WHERE rolname !~ ‘^pg_’ ORDER BY 1

Only superuser have access to pg_authid table and it can’t be access from master user (postgres) as part of managed instance. One approach is to export users and roles but with no password with option “ — no-role-passwords”.

deepakmahto@db-benchmark:~$ pg_dumpall -h <<CLOUD_SQL_INSTANCE_IP>> -U postgres -r --no-role-passwords --if-exists -c
Password:
..
CREATE ROLE cloudsqlsuperuser;
ALTER ROLE cloudsqlsuperuser WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE postgres;
ALTER ROLE postgres WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION NOBYPASSRLS;
CREATE ROLE readuser;
ALTER ROLE readuser WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
..
GRANT pg_read_all_data TO readuser GRANTED BY postgres;
..

It will export all users and roles, including managed users maintained internally by CloudSQL likes cloudsqlsuperuser, cloudsqladmin and more.

We can apply OS level command and delete users and roles native to CloudSQL instance and only roles\users other than managed users. We have added an additional command to replace NOSUPERUSER as part of ALTER ROLE to avoid superuser privilege issue.

PGPASSWORD=******* pg_dumpall -h <<CLOUD_SQL_INSTANCE_IP>> -U postgres -r --no-role-passwords --if-exists -c | sed '/cloudsqladmin/d;/cloudsqlagent/d;/cloudsqliamserviceaccount/d;/cloudsqliamuser/d;/cloudsqlimportexport/d;/cloudsqlreplica/d;/cloudsqlsuperuser/d;s/NOSUPERUSER//g'
...
DROP ROLE IF EXISTS readuser;
DROP ROLE IF EXISTS readwrite;
DROP ROLE IF EXISTS replication_user;
...
CREATE ROLE postgres;
ALTER ROLE postgres WITH  INHERIT CREATEROLE CREATEDB LOGIN REPLICATION NOBYPASSRLS;
CREATE ROLE readuser;
ALTER ROLE readuser WITH  INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE readwrite;
ALTER ROLE readwrite WITH  INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE replication_user;
ALTER ROLE replication_user WITH  INHERIT NOCREATEROLE NOCREATEDB LOGIN REPLICATION NOBYPASSRLS;

Now, though we have exported users and roles but what about passwords, how to export it from one managed instance to another?

Export password using pg_shadow

CloudSQL provides a database flag “cloudsql.pg_shadow_select_role” that enables select roles for a specified user in pg_shadow view. pg_shadow view provides queries properties of users with login privilege from pg_authid.

In the first steps, we will enable “cloudsql.pg_shadow_select_role” on the CloudSQL instance for postgres master user.pg_authid. Change of database flag will not require restart of the instance.

let’s connect using psql and verify the access.

postgres=> show cloudsql.pg_shadow_select_role ;
 cloudsql.pg_shadow_select_role 
 — — — — — — — — — — — — — — — — 
 postgres
(1 row)
postgres=> select usename from pg_shadow where usename not like 'cloudsql%';
     usename      
------------------
 readuser
 readwrite
 replication_user
 postgres
(4 rows)

We can export the password by creating alter statements as part of querying pg_shadow view. The pg_shadow view shows passwords based on password encryption set.

postgres=> show password_encryption;
 password_encryption 
---------------------
 scram-sha-256
(1 row)
postgres=> select 'alter user '|| usename || ' with encrypted password '|| '''' || passwd || ''''||';' from pg_shadow where usename not like 'cloudsql%';

pg_dumpall command line can be used to export all users and roles as intended and post enabling database flags on querying pg_shadow, we can apply alter password command.

Conclusion

Now we understand the overall approach on exporting users and its privileges form one CloudSQL instance to another instance. We will combine overall command and use pipe approach to move users and passwords without using staging file or storage. Assuming we have necessary connectivity to source and target instance.

Step 1 :- Export users and roles without password

Pipe pgdumpall and psql for exporting users and roles without password.

PGPASSWORD=******* pg_dumpall -h <<CLOUD_SQL_INSTANCE_SOURCE_IP>> -U postgres -r — no-role-passwords | sed '/cloudsqladmin/d;/cloudsqlagent/d;/cloudsqliamserviceaccount/d;/cloudsqliamuser/d;/cloudsqlimportexport/d;/cloudsqlreplica/d;/cloudsqlsuperuser/d;s/NOSUPERUSER//g' | PGPASSWORD=********* psql -h <<TARGET_CLOUDSQL_OR_ALLOYDB>> -U postgres

Step 2:- Generate Alter password and apply on target

Pipe psql with sql generating alter command from source having necessary flags enable for SELECT on pg_shadow view and apply it using psql with connection details of target instance.

deepakmahto@db-benchmark:~$ PGPASSWORD=******* psql -h <<CLOUD_SQL_INSTANCE_SOURCE_IP>> -U postgres -t -c "select 'alter user '|| usename || ' with encrypted password '|| ''''|| passwd || ''''||';' from pg_shadow where usename not like 'cloudsql%'" | PGPASSWORD=******* psql -h <<TARGET_CLOUDSQL_OR_ALLOYDB>> -U postgres
ALTER ROLE
ALTER ROLE
ALTER ROLE
ALTER ROLE