After using dms, new instance of cloudsql for pg, permission denied for schema

[shiqi]

As title, I used dms service between two cloudsql for postgresql instances, they are not in the same project. Now cdc full phase is completed, I can find all schemas  in pgAdmin4 tools， but not having any permissons both postgres user and sync users, how to solve it?

[ms4446]

The "permission denied" error on schema chain1 after a Cloud SQL PostgreSQL migration using Google Cloud DMS between projects is common since permissions aren't automatically transferred.

Concise steps to resolve:

1. Connect to the target Cloud SQL instance

   Use psql (via Cloud SQL Auth Proxy):

   Bash

   psql "host=127.0.0.1 port=5432 sslmode=disable dbname=<db_name> user=<user>"
   

   or pgAdmin.

2. Identify DMS sync user(s)

   Run this query and look for usernames containing dms or cloudsqlsync:

   SELECT usename FROM pg_user;
   

3. Grant permissions

   Execute these commands for both the postgres and DMS sync users (replace <dms_sync_user> with actual usernames):

   GRANT USAGE ON SCHEMA chain1 TO postgres, <dms_sync_user>;
   GRANT SELECT ON ALL TABLES IN SCHEMA chain1 TO postgres, <dms_sync_user>;
   -- Optionally, grant additional permissions:
   -- GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA chain1 TO postgres, <dms_sync_user>;
   

4. Set DEFAULT PRIVILEGES (recommended)

   Automatically grant permissions to future tables:

   ALTER DEFAULT PRIVILEGES IN SCHEMA chain1 GRANT SELECT ON TABLES TO postgres, <dms_sync_user>;