| ARTICLE INFO | |||
| Issue ID | NA | Support Ticket | SUPPORT-1128 |
| Environment | YBA xCluster DR | Component | xCluster DDL replication |
| Affected Versions | v2025.2.x | Fixed Version | NA |
Issue
In a Disaster Recovery (DR) deployment utilizing YugabyteDB's xCluster DR in Transactional/Automatic Mode, users may observe that user privileges and grants (such as table-level permissions) are missing on the DR/standby cluster.
This issue typically manifests under two scenarios:
- After executing a globals-only restore (
ysql_dumpall --globals-only) to synchronize roles on the DR replica, table-level permissions remain missing or are not applied. -
Attempts to manually run privilege scripts or
GRANTstatements directly on the DR standby databases fail with the following exception:ERROR: DDL operations are forbidden on a database that is the target of automatic mode xCluster replication
Furthermore, if table-level permissions do not align on the DR side, newly created tables might become "unreplicated," or errors related to the ddl_queue table may appear in YugabyteDB Anywhere (YBA), resulting in a "system error" replication status.
Resolution
Overview
The missing privileges and the subsequent errors are due to expected architectural behaviors under PostgreSQL's design and YugabyteDB's xCluster replication model:
-
Metadata Isolation: Cluster-wide global objects (roles, role memberships, and tablespaces) are shared across the database cluster. Table-level and schema-level privileges (
GRANTstatements), however, are stored as database-specific metadata inside local catalog tables (e.g.,pg_class.relacl). Executingysql_dumpall --globals-only(or-g) only exports cluster-global objects. It intentionally excludes database-specific schema objects and their corresponding privileges. -
Standby Write Restrictions: In xCluster Automatic Mode, the DR standby database acts as a read-only replica. Because any
GRANT,REVOKE, orALTER DEFAULT PRIVILEGESstatement is classified as a schema/DDL modification, direct execution on the DR standby is forbidden and blocked by design. -
Replication Dependencies: While DDL and privilege changes (grants/revokes) replicate automatically from the Primary to the DR standby in Automatic Mode, global roles do not. If a table-level privilege is restored or granted on the primary for a role that does not yet exist on the DR standby, the grant will not attach on the DR standby. Running
ysql_dumpall --globals-onlyafterwards will create the roles, but will not retroactively re-apply the table-level privileges that were missed during database bootstrapping.
Solution Approach
To solve this issue, you must align the roles and default privileges between the Primary and DR clusters using the correct operational sequence:
-
Synchronize roles to the DR cluster first via a roles-only dump. The roles must be restored on a non-replicated database (such as the default
yugabytedatabase) to bypass the standby DDL lock. -
Re-trigger the replication of table-level privileges from the Primary cluster by performing a combined
REVOKEandGRANTtransaction block on the Primary. Since roles now exist on both sides, the new privilege state change will replicate automatically to the DR standby. - Configure Default Privileges on the Primary cluster so that future table-level grants are inherited and replicated seamlessly.
Steps
Follow the steps below to verify, mitigate, and establish best practices for managing privileges in an xCluster DR environment.
Step 1: Verify the Privilege Discrepancy
Before taking actions, verify whether the missing privileges are table-level grants and confirm if target roles exist on both environments.
1.a. Check Table-Level Privileges on the Primary Cluster
Connect to the primary database using ysqlsh and inspect the access privileges of an affected table:
\dp my_schema.my_tableTake note of the list of roles and their assigned permissions (e.g., arwdDxt for Select, Insert, Update, Delete, etc.).
1.b. Check Table-Level Privileges on the DR Standby Cluster
Connect to the DR standby database using ysqlsh and inspect the same table:
\dp my_schema.my_tableIf the access privileges list is blank, it indicates that the table defaults to owner-only access and the replicated privileges are missing.
1.c. Verify Role Existence on the DR Standby
Verify if the target roles (e.g., app_user, read_only_role) are present in the DR cluster:
SELECT rolname FROM pg_roles;
Step 2: Synchronize Global Roles to the DR Standby
Because roles are cluster-wide (global) and are not automatically replicated by xCluster, they must be manually synchronized to the DR standby first.
2.a. Export Roles-Only from the Primary Cluster
Execute the ysql_dumpall command with the --roles-only flag to export only user accounts, passwords, and role memberships:
ysql_dumpall -h <primary-host-ip> -p 5433 -U yugabyte --roles-only -f /tmp/primary_roles.sql2.b. Restore Roles on the DR Standby Cluster
Apply the exported SQL file to the DR standby.
IMPORTANT: You must connect to a database that is NOT a target of xCluster replication (such as the default
yugabytedatabase). This ensures that role-creation statements are permitted on the cluster since roles are global objects.
ysqlsh -h <dr-host-ip> -p 5433 -U yugabyte -d yugabyte -v ON_ERROR_STOP=0 -f /tmp/primary_roles.sql(Using -v ON_ERROR_STOP=0 ensures the script continues even if some pre-existing roles generate "already exists" errors).
Step 3: Re-trigger and Replicate Table-Level Privileges
Now that the global roles exist on both the Primary and DR standby clusters, you can push the missing database-level privileges from the Primary.
NOTE: Simply re-running the exact same
GRANTstatement on the primary might not trigger a replication event if the database engine detects no actual state change. To force a schema change event that replicates to the DR standby, you must wrap aREVOKEandGRANTsequence within a single transaction on the Primary cluster.
3.a. Apply the Revoke-and-Grant Transaction on the Primary Cluster
Run the following block on the Primary database for the affected schemas and roles:
BEGIN;
-- Revoke permissions to clear any partial state
REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM app_user;
-- Grant permissions back to trigger automatic xCluster replication
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
COMMIT;(Replace public with your actual schema name, and app_user with your target role).
3.b. Verify Replication on the DR Standby
Wait for the xCluster replication lag to drop to zero, then inspect the DR standby table once more:
\dp my_schema.my_tableThe privileges should now reflect identically to the Primary cluster.
Step 4: Configure Default Privileges for New Tables
To ensure that newly created tables automatically inherit correct privileges on both the Primary and the DR standby, configure default privileges on the Primary cluster. Default privileges are database-level DDL operations and will replicate automatically.
Execute the following commands on the Primary cluster:
-- Grant select/write on future tables automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
-- Grant usage/select on future sequences automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO app_user;
Step 5: Recommended Standard Operating Runbook for DR Bootstrap
To prevent privilege discrepancies in future DR setups or expansions, always follow this operational order of operations:
-
Phase 1 (Sync Global Roles): Export and restore all global roles on the DR cluster using the
--roles-onlydump. - Phase 2 (Database Bootstrap): Perform the initial database backup on the Primary and restore it onto the DR cluster. Because roles already exist on the target DR cluster during this phase, all schema-level and table-level privileges in the dump file will attach successfully.
- Phase 3 (Setup xCluster Replication): Enable xCluster DR replication.
-
Phase 4 (Ongoing Maintenance):
- If a new role is required, create it on the Primary AND manually sync/create it on the DR Standby first.
- Once the role is present on both clusters, perform any
GRANT,REVOKE, or schema modifications strictly on the Primary cluster. They will replicate to the DR Standby automatically.
Summary Rule of Thumb
| Action Scenario | Replicates Automatically? | Operational Requirement |
|---|---|---|
New table + GRANT (target role already exists on DR) |
✅ Yes | Normal flow. No manual actions needed. |
New table + GRANT (target role only exists on Primary) |
❌ No | Create/sync the role on the DR standby first, then execute the GRANT on the primary. |
CREATE ROLE / CREATE USER
|
❌ No | Create manually on DR standby (or automate roles-only sync). |
| Pre-existing grants (created before DR roles setup) | ❌ No | Align roles on DR standby, then re-apply privileges on Primary via REVOKE/GRANT transaction block. |
Executing a GRANT script on DR Standby directly
|
❌ No (Blocked) | Prohibited by design. Stands fail with DDL restrictions error. |
Comments
0 comments
Please sign in to leave a comment.