Post-Migration Tasks in PostgreSQL: Migrating Grants and Reassigning Owners

Published in

Google Cloud - Community

6 min readNov 10, 2022

It is part of series on migrating user, roles and grants as part of homogenous migration from PostgreSQL to Google Cloud PostgreSQL databases. Check out part 1 on migrating user with credentials to CloudSQL or AlloyDB.

Google Cloud Data Migration services(DMS) ease overall migration with minimal downtime to Google Cloud database portfolio primarily to PostgreSQL managed service. For PostgreSQL as source, it internally leverages pglogical extension and automates the whole process of migrating schema, code, initial backfill data and continuous changes for all databases within an instance.

Check out video release by DMS product team.

Getting Started with Database Migration Service

High level task perform by DMS on migrating to PostgreSQL in Google Cloud.

Taking AlloyDB as target for reference, below is high level tasks perform by DMS.

1. Create database and migrate schema-only components with alloydbexternalsync as owner.
2. Create pglogical extension with node and subscriber.
3. Initiate backfill per database in parallel in an instance.
4. Start ongoing data capture once backfill is completed for table.
5. Monitor Replication delay, once it is 0, and primary is taken down, initiate Promote.
6. if any Synchronize Sequence using pglogical and drop pglogical node and subscription.
7. Promote AlloyDB instance to be primary instance.

Post DMS migration jobs are completed; we are good with overall Schema and data components, but additional tasks in terms of user and roles are pending. Listed as below

Migrate Users\Roles from source with same or new credentials
Migrate privilege including grants and revoke
Alter database object owner from alloydbexternalsync to user\role as per source.

I have already published blog on how to Migrate users with credentials from CloudSQL to AlloyDB or another CloudSQL instance. It can also be leverage to migrate users from PostgreSQL compatible instance to either CloudSQL or AlloyDB.

In this blog we will find feasible approach and solution as automated script on migrating privilege and perform necessary Alter command to change owner as per Source.

Migrate privilege including grants and revoke from Source.

We will use PostgreSQL client utility pg_dumpall to export necessary grant and revoke from source PostgreSQL including CloudSQL if migrating to AlloyDB.

We will use key pg_dumpall option as listed below for migrating user\roles related grant and revoke command.

— exclude-database : exclude internal databases as per managed services like rdsadmin on migrating from AWS or cloudsqladmin on migrating cloudsql to alloydb.
— no-role-passwords : Avoid exception to export password only in case of managed instance.
— schema-only : Dump only Schema related component.

pg_dumpall doesn’t have any specific option on migrating only grants, hence we will use OS level command like sed and grep to get desired output.

Sample command to export grants from CloudSQL as source using pg_dumpall.

pg_dumpall -h <<instance-endpoints>> -U postgres --exclude-database="alloydbadmin|cloudsqladmin|rdsadmin" --schema-only --no-role-passwords | sed '/cloudsqladmin/d;/cloudsqlagent/d;/cloudsqliamserviceaccount/d;/cloudsqliamuser/d;/cloudsqlimportexport/d;/cloudsqlreplica/d;/cloudsqlsuperuser/d;s/NOSUPERUSER//g' | grep -E '^(GRANT|REVOKE|\\connect)'

We can use similar command with necessary changes to generate sql script to be executed at migrated database post DMS migration job is completed.

Alter database object owner from alloydbexternalsync(applicable for AlloyDB as target) to user\role as per source.

Once DMS migration job is completed, you will find all the migrated schema objects like table, functions , indexes including schema with alloydbexternalsync as owner.

Below is script build to find all objects in a migrated database with owner as alloydbexternalsync. If we have migrated to CloudSQL for PostgreSQL same script can be used to check all objects owned by cloudsqlexternalsync.

It will connect to all database within migrated instance and display database objects own by DMS created migration user (alloydbexternalsync)

dbobj_ownerlist.sh

#!/bin/bash#Argument input hostname , user and password of target PostgreSQL instance on GCP.
#sh dbobj_ownerlist.sh <<host-endpoint-Ip>> <<user>> <<password>>databaseList=$(cat << EOD
SELECT datname FROM pg_database WHERE datname not in ('cloudsqladmin','template0','template1','alloydbadmin' , 'rdsadmin') ORDER BY datname
EOD
)echo ${databaseList} | PGPASSWORD=$3 psql -h $1 \
-d postgres \
-U $2 \
--no-align \
-t \
--field-separator '-' \
-q \
| while IFS='-' read databasename ; dosqlCommand=$(cat <<EOD
SELECT '${databasename}' as DatabaseName,
nsp.nspname as SchemaName
,cls.relname as ObjectName
,rol.rolname as ObjectOwner
,CASE cls.relkind
WHEN 'r' THEN 'TABLE'
WHEN 'm' THEN 'MATERIALIZED_VIEW'
WHEN 'i' THEN 'INDEX'
WHEN 'S' THEN 'SEQUENCE'
WHEN 'v' THEN 'VIEW'
WHEN 'c' THEN 'TYPE'
WHEN 'm' THEN 'MVIEW'
WHEN 'p' THEN 'PARTITION TABLE'
WHEN 'I' THEN 'PARTITION INDEX'
WHEN 'f' THEN 'FOREIGN TABLE'
ELSE cls.relkind::text
END as ObjectType
FROM pg_class cls
JOIN pg_roles rol
ON rol.oid = cls.relowner
JOIN pg_namespace nsp
ON nsp.oid = cls.relnamespace
WHERE nsp.nspname NOT IN ('information_schema', 'pg_catalog')
AND nsp.nspname NOT LIKE 'pg_toast%'
AND rol.rolname IN ('alloydbexternalsync','cloudsqlexternalsync')
UNION
SELECT '${databasename}' , nsp.nspname , nsp.nspname , rol.rolname , 'SCHEMA'
FROM  pg_namespace nsp INNER JOIN pg_roles rol
ON nsp.nspowner = rol.oid
WHERE rol.rolname IN ('alloydbexternalsync','cloudsqlexternalsync')
UNION
SELECT  '${databasename}' ,  N.nspname  ,  quote_ident(p.proname) ,  rol.rolname , CASE p.prokind
WHEN 'f' THEN 'FUNCTION'
WHEN 'p' THEN 'PROCUDURE'
WHEN 'a' THEN 'AGGREGATE FUNCTION'
WHEN 'w' THEN 'WINDOW FUNCTION'
ELSE p.prokind::text
END as ObjectType
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
JOIN pg_roles rol
ON   rol.oid = p.proowner
WHERE  n.nspname not like 'pg_catalog%'
AND rol.rolname IN ('alloydbexternalsync','cloudsqlexternalsync')
order by 1,4 ,2
EOD
)echo  ${sqlCommand} | PGPASSWORD=$3 psql -h $1 -U $2 -d ${databasename}done

sh dbobj_ownerlist.sh <<host-endpoint-Ip>> <<user>> <<password>>

As next steps, we will generate necessary ALTER command to change owner as per source database. On applying necessary ALTER to change in object owner, we will use same approach as migrating grants i.e. pg_dumpall.

pg_dumpall -h <<instance-endpoints>>  --exclude-database="alloydbadmin|cloudsqladmin|rdsadmin" --schema-only --no-role-passwords  | sed '/cloudsqladmin/d;/cloudsqlagent/d;/cloudsqliamserviceaccount/d;/cloudsqliamuser/d;/cloudsqlimportexport/d;/cloudsqlreplica/d;/cloudsqlsuperuser/d;s/NOSUPERUSER//g' | grep -E '^(GRANT|REVOKE|\\connect|ALTER.*OWNER.*)'

It will generate Alter as per owner along with grants. check out sample output generated as below.

So now we can apply above generated pg_dumpall output to migrated target instance, post DMS migration jobs is completed.

With pg_dumpall , we can combine all command on overall user\roles migration including creation, privilege and re-assign owner as per Source Database.

pg_dumpall -h <<instance-endpoints>> -U postgres  --exclude-database="alloydbadmin|cloudsqladmin|rdsadmin" --schema-only --no-role-passwords  | sed '/cloudsqladmin/d;/cloudsqlagent/d;/cloudsqliamserviceaccount/d;/cloudsqliamuser/d;/cloudsqlimportexport/d;/cloudsqlreplica/d;/cloudsqlsuperuser/d;s/NOSUPERUSER//g' | grep -E '^(GRANT|REVOKE|\\connect|ALTER.*OWNER.*|CREATE ROLE|ALTER ROLE)'

Additional Notes

By default whenever a database is created in managed instance like CloudSQL or AlloyDB, public schema is owned by cloudsqlsuperuser or alloydbsuperuser. If we are migrating from a Managed instance to AlloyDB we need to explicitly take care of assigning owner of public schema to respective user.

Conclusion

Post DMS migration job is completed, we need to take care of user\roles related tasks listed as below.