Data Sync Privilege Planning

When using Tacnode DataSync for data migration and synchronization, permissions for newly migrated data are bound to the task creator. If fine-grained data privilege management is required, you can plan the privilege management model before migration.

Simplified Privilege Model

Simple read-only and write-only privilege management for data.

Creating Read-Only Accounts

Create an account with read-only privileges for all data in the current database:

-- Create a read-only account named reader
CREATE USER reader WITH PASSWORD 'XXXX';
 
-- pg_read_all_data is a predefined role
GRANT pg_read_all_data TO reader;

pg_read_all_data is a predefined role introduced in PostgreSQL 14+ that grants read access to all tables, views, sequences, and other objects in the database.

Creating Write-Only Accounts

Create an account with write privileges for all data in the current database (note: cannot create tables, views, or other operations):

-- Create a write-only account named writer
CREATE USER writer WITH PASSWORD 'XXXX';
 
-- pg_write_all_data is a predefined role
GRANT pg_write_all_data TO writer;

The pg_write_all_data role can only perform insert, update, and delete operations on existing tables, but cannot create new tables, views, or other database objects.

Fine-Grained Privilege Management

When read-only and write-only privileges don't meet business requirements, more precise privilege management is needed. You can refer to privilege management best practices to create custom roles and manage related privileges.

Creating Administrator-Level Accounts

Create an account with privileges essentially equivalent to the data owner:

-- Create an administrator account
CREATE USER owner WITH PASSWORD 'XXXX';
 
-- Grant all privileges on database d1 to owner
GRANT ALL ON DATABASE d1 TO owner WITH GRANT OPTION;
 
-- Grant all privileges on public schema to owner
GRANT ALL ON SCHEMA public TO owner;
 
-- Grant privileges on existing objects
GRANT ALL ON ALL TABLES IN SCHEMA public TO owner;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO owner;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO owner;

Configuring Default Privileges

Modify the default privileges for objects created by the current user in the future. When objects are created, they will be assigned privileges based on the current user's default configuration:

-- The following statements modify the user's default privilege configuration,
-- so that when corresponding objects are created, they will be granted owner privileges by default.
-- Note: This does not affect already created objects.
 
-- Schema privileges
ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO owner;
 
-- Table privileges
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO owner;
 
-- Sequence privileges
ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO owner;
 
-- Function privileges
ALTER DEFAULT PRIVILEGES GRANT ALL ON FUNCTIONS TO owner;

ALTER DEFAULT PRIVILEGES only affects newly created objects and does not apply to existing objects. To apply privileges to existing objects, use GRANT ALL ON ALL ... statements.

DataSync-Specific Privilege Configuration

Data Source Connection Privileges

Minimum privilege requirements for different types of data sources in DataSync:

MySQL Data Sources

-- Create dedicated sync account
CREATE USER 'datasync_user'@'%' IDENTIFIED BY 'password';
 
-- Grant necessary privileges
GRANT SELECT ON database_name.* TO 'datasync_user'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'datasync_user'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'datasync_user'@'%';
 
-- For CDC requirements, also need binlog-related privileges
GRANT SHOW VIEW ON database_name.* TO 'datasync_user'@'%';

PostgreSQL Data Sources

-- Create dedicated sync account
CREATE USER datasync_user WITH PASSWORD 'password';
 
-- Grant basic privileges
GRANT CONNECT ON DATABASE database_name TO datasync_user;
GRANT USAGE ON SCHEMA schema_name TO datasync_user;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO datasync_user;
 
-- For CDC requirements, grant logical replication privileges
ALTER USER datasync_user REPLICATION;

Oracle Data Sources

-- Create dedicated sync account
CREATE USER datasync_user IDENTIFIED BY password;
 
-- Grant necessary privileges
GRANT CONNECT TO datasync_user;
GRANT SELECT_CATALOG_ROLE TO datasync_user;
GRANT SELECT ANY TABLE TO datasync_user;
 
-- For CDC requirements, grant LogMiner privileges
GRANT EXECUTE_CATALOG_ROLE TO datasync_user;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO datasync_user;

Target Database Privilege Configuration

In the Tacnode target database, DataSync requires the following privileges:

-- Create DataSync dedicated account
CREATE USER datasync_target WITH PASSWORD 'password';
 
-- Grant necessary privileges
GRANT CONNECT ON DATABASE target_database TO datasync_target;
GRANT CREATE ON SCHEMA target_schema TO datasync_target;
GRANT USAGE ON SCHEMA target_schema TO datasync_target;
 
-- Grant table operation privileges
GRANT CREATE ON SCHEMA target_schema TO datasync_target;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA target_schema TO datasync_target;
 
-- Configure default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA target_schema 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO datasync_target;

Privilege Best Practices

1. Principle of Least Privilege

Only grant the minimum set of privileges required for DataSync to complete synchronization tasks:

Read Privileges: Source database only needs SELECT privileges and necessary metadata access privileges
Write Privileges: Target database only needs INSERT, UPDATE, DELETE privileges
DDL Privileges: Only grant CREATE, ALTER privileges when table structure changes need to be synchronized

2. Role Management

Use roles to manage privileges for easier batch authorization and privilege revocation:

-- Create DataSync role
CREATE ROLE datasync_role;
 
-- Configure role privileges
GRANT SELECT ON ALL TABLES IN SCHEMA public TO datasync_role;
GRANT USAGE ON SCHEMA public TO datasync_role;
 
-- Grant role to user
GRANT datasync_role TO datasync_user;

3. Network Security

Combine with network access control to restrict the connection sources of DataSync accounts:

-- Configure in pg_hba.conf
host database_name datasync_user datasync_ip_range md5

4. Privilege Monitoring

Regularly check and audit the privilege usage of DataSync-related accounts:

-- Check user privileges
SELECT 
    grantee, 
    table_schema, 
    table_name, 
    privilege_type 
FROM information_schema.table_privileges 
WHERE grantee = 'datasync_user';
 
-- Check role membership
SELECT 
    r.rolname AS role_name,
    m.rolname AS member_name
FROM pg_roles r 
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
WHERE r.rolname LIKE '%datasync%';

Troubleshooting

Insufficient Privilege Errors

When encountering insufficient privilege errors, you can troubleshoot using the following methods:

Check Connection Privileges:

-- Check database connection privileges
SELECT datname FROM pg_database WHERE datname = 'your_database'
AND has_database_privilege('datasync_user', datname, 'CONNECT');

Check Table Privileges:

-- Check table-level privileges
SELECT schemaname, tablename, 
       has_table_privilege('datasync_user', schemaname||'.'||tablename, 'SELECT') as can_select
FROM pg_tables 
WHERE schemaname = 'your_schema';

Check Sequence Privileges:

-- Check sequence privileges (for auto-increment fields)
SELECT schemaname, sequencename,
       has_sequence_privilege('datasync_user', schemaname||'.'||sequencename, 'USAGE') as can_use
FROM pg_sequences 
WHERE schemaname = 'your_schema';

Before using DataSync in production environments, it's recommended to validate privilege configuration in test environments to ensure all synchronization operations can execute normally.

Through proper privilege planning, you can ensure DataSync runs efficiently in a secure environment while meeting enterprise security compliance requirements.

Data Sync Privilege Planning

On this page