Help Center/ Data Replication Service/ FAQs/ Permissions Management/ Which PostgreSQL Permissions Are Required for DRS?
Updated on 2024-07-11 GMT+08:00
View PDF
Share

Which PostgreSQL Permissions Are Required for DRS?

DRS has certain permission requirements on accounts during data synchronization. This section describes the permission requirements on the PostgreSQL engine.

Permissions

  • You must have the login permission of the source and destination database connection accounts. If you do not have the account, perform the following operations to create one. The ptd user is used as an example.

    Reference statement: CREATE USER ptd PASSWORD 'password';

  • Table 1 lists the required permissions.
    Table 1 Permission requirements and reference statements

    Synchronization Mode

    Source DB

    Destination DB

    Full synchronization

    The CONNECT permission for databases, USAGE permission for schemas, SELECT permission for tables, SELECT permission for sequences, and SELECT permission for system table catalog pg_catalog.pg_authid (used for synchronizing user passwords)

    Reference statements:

    GRANT CONNECT ON DATABASE database_name TO ptd;

    GRANT USAGE ON SCHEMA schema_name TO ptd;

    GRANT SELECT ON TABLE table_name TO ptd;

    GRANT SELECT ON SEQUENCE sequence_name TO ptd;

    GRANT SELECT ON pg_catalog.pg_authid TO ptd;

    Permission requirements for database-level synchronization:

    • If the destination database is not PostgreSQL, the CREATEDB permission is required.

      Reference statement: ALTER ROLE ptd WITH CREATEDB;

    • If the destination database is PostgreSQL, the CONNECT and CREATE permissions on PostgreSQL databases and the USAGE and CREATE permissions on public schemas are required.

      Reference statements:

      GRANT CONNECT, CREATE ON DATABASE postgres TO ptd;

      GRANT USAGE, CREATE ON SCHEMA public TO ptd;

    Permission requirements for table-level synchronization:

    • To synchronize databases, the CREATEDB permission is required.

      Reference statement:

      ALTER ROLE ptd WITH CREATEDB;

    • To synchronize schemas, the CONNECT and CREATE permissions for the database that contains the schemas are required.

      Reference statement:

      GRANT CONNECT, CREATE ON DATABASE database_name TO ptd;

    • To synchronize objects in a schema, the CONNECT permission for the database that contains the schema, and the USAGE and CREATE permissions for the schema that contain the objects are required.

      Reference statement:

      GRANT USAGE, CREATE ON SCHEMA schema_name TO ptd;

    Permission requirements for user synchronization:

    The CREATEROLE permission is required.

    Reference statement:

    ALTER USER ptd CREATEROLE;

    Full+Incremental synchronization
    • The CONNECT permission for databases, USAGE permission for schemas, SELECT permission for tables, SELECT permission for sequences, SELECT permission for system table catalog pg_catalog.pg_authid (used for synchronizing user passwords), and UPDATE, DELETE, and TRUNCATE permissions for tables that do not have primary keys (used to temporarily lock tables to ensure data consistency after the migration)

      Reference statements:

      GRANT CONNECT ON DATABASE database_name TO ptd;

      GRANT USAGE ON SCHEMA schema_name TO ptd;

      GRANT SELECT ON TABLE table_name TO ptd;

      GRANT SELECT ON SEQUENCE sequence_name TO ptd;

      GRANT SELECT ON pg_catalog.pg_authid TO ptd;

      GRANT UPDATE, DELETE, TRUNCATE ON TABLE table_name TO ptd;

    • The permission to create replication connections

      To add the permission, perform the following operations:

      1. Add host replication <src_user_name> <drs_instance_ip>/32 <Authentication mode> before all configurations in the pg_hba.conf file of the source database.

        For details about the authentication mode, see pg_hba.conf in the official document of PostgreSQL. md5 and scram-sha-256 are common authentication modes.

      2. Run select pg_reload_conf(); in the source database as user SUPERUSER, or restart the DB instance to apply the changes.
Parent topic: Permissions Management