Aurora PostgreSQL pg_columnmask data movement scenarios - Amazon Aurora
ReplicasClones and restoresLogical replicationBlue/Green deploymentsZero-ETL and CDCAmazon DMSData exportsViewsData dump and restore Foreign data wrapper
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Aurora PostgreSQL pg_columnmask data movement scenarios

pg_columnmask behavior varies across different data movement operations depending on whether the operation occurs at the storage, logical, or application layer. Storage-level operations (such as cloning) behave differently from logical operations (such as pg_dump) and application-level operations (such as FDW queries). This section describes masking behavior for common scenarios including replication, backups, exports, and migrations, and explains the security implications for each.

Aurora Global Database and Read Replicas

Aurora pg_columnmask policies are stored in database system tables within the cluster volume. All replicas access the same policies and return consistently masked results. For Aurora Global Database deployments, pg_columnmask policies replicate to secondary Amazon Web Services Regions along with other database system tables, ensuring consistent data protection across regions. During failover scenarios, all pg_columnmask policies remain intact and functional.

Database clone and snapshot restore

Aurora Fast Clone and snapshot restore operations preserve all pg_columnmask policies, roles, and configurations as part of the database system tables. The cloned or restored database inherits all existing policies from the source cluster. After cloning or restoration, each database cluster maintains independent pg_columnmask policies.

Logical replication

During initial synchronization, logical replication uses standard SQL COPY operations, and pg_columnmask policies are enforced based on the replication user's permissions. During ongoing CDC (change data capture), masking policies are not applied and unmasked data is replicated through WAL records. Users with pg_create_subscription privileges can potentially exfiltrate unmasked data by setting up replication to a system they control.

Blue/Green deployments

During snapshot restoration, pg_columnmask policies are automatically included. The green environment starts with an identical copy of all policies from the blue environment. During replication from blue to green, data is not masked. Subsequent masking policy changes (DDL commands) on the blue cluster do not replicate to the green cluster and invalidate RDS blue/green deployments.

Zero-ETL and CDC streams

Data replication is not affected by pg_columnmask policies. Zero-ETL supports DDL replication but doesn't replicate pg_columnmask or RLS policies. No masking policies are applied to replicated data in Zero-ETL.

Amazon Database Migration Service

Initial data sync is masked or unmasked based on the user selected for the DMS task. CDC data is always unmasked. While pg_columnmask related internal RLS policies may be migrated, they won't function on non-pg_columnmask-enabled targets.

Data exports

pg_columnmask treats exports like any other query operation—masking is applied based on the executing user's permissions. This applies to SQL commands like COPY, SELECT INTO, CREATE TABLE AS, and Aurora PostgreSQL's S3 export functionality.

Note

When masked users export data, the resulting files contain masked values that may violate database constraints when restored.

Views and materialized views

Keep the following considerations in mind when using views:

  • Regular views – Always use INVOKER semantics. The current user's masking policies apply when querying the view, regardless of who created the view.

  • Materialized views – When refreshed, the masking policies of the materialized view owner apply, not the policies of the user performing the refresh. If the owner has masking policies, the materialized view always contains masked data.

Data dump and restore

pg_dump operates as a regular database user and applies masking policies based on the connecting user's permissions. If a masked user performs a dump, the backup file contains masked data. pg_columnmask policies are included in the dump as part of the database schema. Successful restoration requires that all referenced roles exist in the target database and that the target has the pg_columnmask extension installed.

Note

Starting with PostgreSQL 18, pg_dump supports the —no-policies option which excludes both Row Level Security (RLS) and pg_columnmask masking policies from database dumps. For more information, see pg_dump.

Foreign data wrapper

When using foreign data wrappers, masking policies on remote tables are applied based on the mapped user's permissions on the source server, not the local querying user's permissions, and while you can access masked remote data through FDW, you cannot create DDM or RLS policies directly on foreign tables in your local database.