Another possible approach is data masking
via Logical Replication. To get
the data masked during replication, you need to set
the mask parameter on the publication. For example:
CREATE PUBLICATION mypublication FOR TABLE people WITH (mask=on);
Please check CREATE PUBLICATION for more details.
With this setup, the masking expression will be evaluated for each row during initial data synchronization and each time the data gets changed in the publisher database. Thus the subscriber database will always contain the value of the masking expression, as opposed to the column value in the publisher database.
If a column contains sensitive information and it is not needed in the subscriber database at all (for example, even masked value would not help in development and testing), you can - instead of setting a masking expression - simply pass a column list to CREATE PUBLICATION and omit that column. See Section 29.5 for more information.
When using column lists to hide data from the subscriber, please use
the USAGE privilege on your publications.
Section 29.11 contains more information
on that privilege.
In particular, you need to revoke the USAGE privilege
from the public role and to grant it to the role used
for connection in CREATE SUBSCRIPTION. Also make sure
that the columns you want to hide from the subscriber are not exposed via
other publications with less restrictive USAGE
privilege. Finally, make sure
that publication_security
configuration parameter is on, otherwise
the USAGE privilege on publications will not be
enforced.