How Do I Add Additional Permissions for PostgreSQL and GaussDB Data Sources?

Symptom

The default permissions of the PostgreSQL and GaussDB data sources cannot run real-time migration jobs.

Possible Causes

• PostgreSQL uses the pgoutput plugin to decode the logic of incremental synchronization. This plugin depends on PUBLICATION.
• During the startup of PostgreSQL and GaussDB incremental synchronization start points and the advancement of logical replication slots, the WAL logs of databases need to be queried. This requires the permission to query the pg_ls_waldir() function.

Solution

1. PostgreSQL uses the pgoutput plugin to decode the logic of incremental synchronization. This plugin depends on PUBLICATION.
   1. Query the publication.

      SELECT * FROM pg_publication;

   2. If there is no dbz_publication, a publication will be automatically created using the account configured for the migration job after the job is started. You must assign permissions to the account in advance or use an authorized account to create a publication.

      Assign the permissions to create publications to a user.

      GRANT CREATE ON DATABASE "your_databasename" TO "your_user";

      Create a publication. (If permissions are not assigned, create a publication manually.)

      -- The PostgreSQL version is 13 or later.
      CREATE PUBLICATION dbz_publication FOR ALL TABLES WITH (publish_via_partition_root = true)
      -- The PostgreSQL version is earlier than 13.
      CREATE PUBLICATION dbz_publication FOR ALL TABLES

   3. If dbz_publication is available, add the following parameter. (Skip this step if the PostgreSQL version is earlier than 13.)

      ALTER PUBLICATION dbz_publication SET (publish_via_partition_root = true);

2. During the startup of PostgreSQL incremental synchronization start points and the advancement of logical replication slots, the WAL logs of databases need to be queried. This requires the permission to query the pg_ls_waldir() function.
   1. Check whether the account configured for the job has the permission to execute the pg_ls_waldir() function.

      SELECT has_function_privilege('your_user', 'pg_ls_waldir()', 'EXECUTE');
      SELECT pg_ls_waldir() -- Check whether the function can be executed successfully.

   2. If the account does not have the required permission, grant the permission to the account using the super user (SUPERUSER) or a user (root) with the pg_monitor role.

      GRANT pg_monitor TO your_user;      -- Grant the permission.
      REVOKE pg_monitor from your_user;   -- Revoke the permission.

   3. Log in to the database using the account configured for the job and repeat step 1 to check whether the permission takes effect.

3. During the startup of GaussDB incremental synchronization start points and the advancement of logical replication slots, the WAL logs of databases need to be queried. This requires the permission to query the pg_ls_waldir() function.
   1. Check whether the account configured for the job has the permission to execute the pg_ls_waldir() function.

      SELECT pg_ls_waldir() -- If the account does not have the permission, message "only system/monitor admin can check WAL directory!" is displayed.

   2. If the account does not have the required permission, grant the permission to the account using the super user (SUPERUSER) or a user (root) with the monitor role.

      ALTER USER your_user MONADMIN    -- Grant the permission.
      ALTER USER your_user NOMONADMIN  -- Revoke the permission.

   3. Log in to the database using the account configured for the job and repeat step 1 to check whether the permission takes effect.