pgstream supports column value transformations to anonymize or mask sensitive data during replication and snapshots. This is particularly useful for compliance with data privacy regulations.
pgstream integrates with existing transformer open source libraries, such as greenmask, neosync and go-masker, to leverage a large amount of transformation capabilities, as well as having support for custom transformations.
⚠️ Most transformers do not preserve uniqueness. Applying one to a column covered by a unique index or primary key produces duplicate key violations. See Uniqueness and unique indexes.
Uniqueness and unique indexes
Anonymization is lossy by design, and that conflicts directly with unique constraints. Masking609898123456 with masking type id yields 609898****: the transformer keeps a 6 character prefix, so every source value sharing that prefix becomes the same masked value. On a column covered by a unique index the load then fails with duplicate key value violates unique constraint, part way through the data, long after the run started.
Each transformer declares how it behaves with respect to uniqueness:
When a source Postgres URL is configured, pgstream reads the unique indexes, unique constraints and primary keys of every table in the transformation rules and checks them against the configured transformers. Columns with no rule, or with a
noop rule, keep their original value and are never flagged. Run the check on its own with:
lossy transformer on a covered column fails the rules with transformation rules break a unique index, because the target recreates the source’s indexes and the load would hit a duplicate key. With any other target (Kafka, Elasticsearch/OpenSearch, webhooks) there is no unique index to violate, so the same finding is reported as a warning and does not block the pipeline. A not_guaranteed transformer is always a warning.
To keep a lossy transformer on a covered column anyway — because the target does not enforce that index, or because the values are known not to collide — set allow_uniqueness_loss on that column rule:
Keeping a column unique
If you need an anonymized column to stay unique, useencrypted_aes_siv: it is deterministic, so equality relationships survive across rows, tables and runs, and no two distinct inputs share a token. Its output is a base64 token rather than a value in the original format.
⚠️ encrypted_aes_siv is pseudonymization, not anonymization. The output is reversible by anyone holding key_hex, so it remains personal data, and because tokens are stable they can be correlated across tables and across successive dumps. Treat key_hex as a secret: inject it from a secret store rather than committing it in the rules file, use a different key per environment, and set associated_data (for example schema.table.column) so tokens cannot be correlated between columns.
What the check does not cover
- Expression indexes. A unique index over an expression, such as
lower(email), cannot be resolved to a column, so pgstream reports a warning naming the index and leaves it to you to verify. - Target-only indexes. The check reads the source catalog. A unique index that exists only on the target is not seen.
- Indexes created after startup. Rules are validated once when the pipeline starts. A unique index added to the source later is not re-checked.
- Exclusion constraints with equality semantics (
EXCLUDE (email WITH =)) are not treated as unique indexes.
duplicate key value violates unique constraint on a transformed column, run pgstream validate rules against the source to see which rules the check flags.
Supported transformers
PostgreSQL Anonymizer
pg_anonymizer
Description: Integrates with the PostgreSQL Anonymizer extension to provide advanced data anonymization using built-in anonymizer functions. ⚠️ This transformer requires a PostgreSQL database connection to execute transformations, which may impact performance compared to other transformers in this document that generate values locally without database queries.
Notes:
- The transformer executes functions directly in PostgreSQL, ensuring compatibility with all anonymizer features
- Deterministic functions (pseudo_*, hash, digest) produce consistent output for the same input
- Functions that don’t require parameters (like anon.fake_*()) can be used without additional configuration
- PostgreSQL Anonymizer extension must be installed and enabled on the source (or the configured url)
- Extension must be loaded in
shared_preload_libraries - Run
SELECT anon.init();in order to use the faking functions
- Adding noise
- Randomization
- Faking
- Advanced faking
- Pseudoanonymization
- Generic hashing
- Partial scrambling
- Image blurring
Greenmask
greenmask_boolean
Description: Generates random or deterministic boolean values (true or false).
Uniqueness: lossy. The output space has two values. Cannot be used on a column covered by a unique index. See Uniqueness and unique indexes.
Example Configuration:
greenmask_choice
Description: Randomly selects a value from a predefined list of choices. Uniqueness:lossy. Any table with more rows than choices produces duplicates. Cannot be used on a column covered by a unique index. See Uniqueness and unique indexes.
Example Configuration:
greenmask_date
Description: Generates random or deterministic dates within a specified range.
Example Configuration:
greenmask_firstname
Description: Generates random or deterministic first names, optionally filtered by gender. Uniqueness:lossy. Names come from a fixed dictionary and repeat well before a table of any size is exhausted. Cannot be used on a column covered by a unique index. See Uniqueness and unique indexes.
gender can also be a dynamic parameter, referring to some other column. Please see the below example config.
Example Configuration:
greenmask_float
Description: Generates random or deterministic floating-point numbers within a specified range.greenmask_integer
Description: Generates random or deterministic integers within a specified range.
Example Configuration:
greenmask_string
Description: Generates random or deterministic strings with customizable length and character set.
Example Configuration:
greenmask_unix_timestamp
Description: Generates random or deterministic unix timestamps.greenmask_utc_timestamp
Description: Generates random or deterministic UTC timestamps.greenmask_uuid
Description: Generates random or deterministic UUIDs.Neosync
neosync_email
Description: Anonymizes email addresses while optionally preserving length and domain.
Example Configuration:
neosync_firstname
Description: Generates anonymized first names while optionally preserving length. Uniqueness:lossy. Names come from a fixed dictionary and repeat well before a table of any size is exhausted. Cannot be used on a column covered by a unique index. See Uniqueness and unique indexes.
Example Configuration:
neosync_lastname
Description: Generates anonymized last names while optionally preserving length. Uniqueness:lossy. Names come from a fixed dictionary and repeat well before a table of any size is exhausted. Cannot be used on a column covered by a unique index. See Uniqueness and unique indexes.
Example Configuration:
neosync_fullname
Description: Generates anonymized full names while optionally preserving length. Uniqueness:lossy. Names come from fixed dictionaries, so even first/last combinations repeat well before a large table is exhausted. Cannot be used on a column covered by a unique index. See Uniqueness and unique indexes.
max_length must be greater than 2. If preserve_length is set to true, generated value can might be longer than max_length, depending on the input length.
Example Configuration:
neosync_string
Description: Generates anonymized strings with customizable length.
Example Configuration:
Xata
template
Description: Transforms the data using go templates
This transformer can be used for any Postgres type as long as the given template produces a value with correct syntax for that column type. e.g It can be “5-10-2021” for a date column, or “3.14159265” for a double precision one.
Template transformer supports a bunch of useful functions. Use
.GetValue to refer to the value to be transformed. Use .GetDynamicValue "<column_name>" to refer to some other column. Other than the standard go template functions, there are many useful helper functions supported to be used with template transformer, thanks to greenmask’s huge set of core functions including masking function by go-masker and various random data generator functions powered by the open source library faker. Also, template transformer has support for the open source library sprig which has many useful helper functions.
With the below example config pgstream masks values in the column email of the table users, using go-masker’s email masking function. But first, this template checks if there’s a non-empty value to be used in the column email. If not, it simply looks for another column named secondary_email and uses that instead. Then we have another check to see if it’s a @xata email or not. Finally masking the value, only if it’s not a @xata email, passing it without a mask otherwise.
Example Configuration:
masking
Description: Masks string values using the provided masking function. Uniqueness:lossy. Every type replaces part of the value with *, so all values sharing the untouched part collapse to the same output — type: id keeps only a 6 character prefix. Cannot be used on a column covered by a unique index. See Uniqueness and unique indexes.
A custom mask that would cover zero characters (for example mask_begin: 3 with mask_end: 3, or unmask_begin: 0) is rejected at startup: it leaves values completely unmasked, which silently defeats anonymization. Use the noop transformer if passing a column through untouched is what you want.
Parameter Details:
Example Configuration:
With
custom type, the masking function is defined by the user, by providing beginning and end indexes for masking. If the input is shorter than the end index, the rest of the string will all be masked. See the third example below.
If the begin index is not provided, it defaults to 0. If the end is not provided, it defaults to input length.
Alternatively, since input length may vary, user can provide relative beginning and end indexes, as percentages of the input length.
Alternatively, user can provide unmask begin and end indexes. In that case, the specified part of the input will remain unmasked, while all the rest is masked.
Mask and unmask parameters cannot be provided at the same time.
json
Description: Transforms json data with set and delete operations
Parameter for each operation:
*Paths should follow sjson syntax
**Either
value or value_template must be provided if the operation is set. If both are provided, value_template takes precedence.
JSON transformer can be used for Postgres types json and jsonb. This transformer executes a list of given operations on the json data to be transformed.
All operations must be either set or delete.
set operations support literal values as well as templates, making use of sprig and greenmask’s function sets. See template transformer section for more details. Also, like the template transformer, .GetValue and .GetDynamicValue functions are supported. Unlike template transformer, here .GetValue refers to the value at given path, rather than the entire JSON object; whereas .GetDynamicValue is again used for referring to other columns.
delete operations simply delete the object at the given path.
Execution of an operation will be skipped if the given path does not exists and the parameter skip_not_exist is set to true, which is also the default behavior.
Execution of an operation errors out if the given path does not exists and the parameter error_not_exist - which is false by default - is set to true; unless the operation is skipped already.
JSON transformer uses sjson library for executing the operations. Operation paths should follow the synxtax rules of sjson
With the below config pgstream transforms the json values in the column user_info_json of the table users by:
- First, traversing all the items in the array named
purchases, and for each element, setting value to ”-” for key “item”. - Then, deleting the object named “country” under the top-level object “address”.
- Completely masking the “city” value under object “address”, using
go-masker’s default masking function supported bypgstream’s templating. - Finally, setting the user’s lastname after fetching it from some other column named
lastname, using dynamic values support. Assuming there’s such column, having the lastname info for users.
hstore
Description: Transforms hstore data with set and delete operations
Parameter for each operation:
*Either
value or value_template must be provided if the operation is set. If both are provided, value_template takes precedence.
Hstore transformer can be used for Postgres type hstore. This transformer executes a list of given operations on the hstore data to be transformed.
All operations must be either set or delete.
set operations support literal values as well as templates, making use of sprig and greenmask’s function sets. See template transformer section for more details. Also, like the template transformer, .GetValue and .GetDynamicValue functions are supported. Unlike template transformer, here .GetValue refers to the value for the given key, rather than the entire Hstore object; whereas .GetDynamicValue is again used for referring to other columns.
delete operations simply delete the pair with the given key.
Execution of an operation will be skipped if the given key does not exists and the parameter skip_not_exist is set to true, which is also the default behavior.
Execution of an operation errors out if the given key does not exists and the parameter error_not_exist - which is false by default - is set to true; unless the operation is skipped already.
A limitation to be aware of: When using hstore transformer templates, you cannot set a value to the string literal “<no value>”. This is because Go templates produce “<no value>” as output when the result is nil, creating an ambiguity. In such cases, pgstream will interpret it as nil and set the hstore value to NULL rather than storing the actual string “<no value>”.
With the below config pgstream transforms the hstore values in the column attributes of the table users by:
- First, updating the value for key “email” to the masked version of it, using email masking function. If the key “email” is not found, it simply ignores it, since
error_not_existis not set to true explicitly and it is false by default. - Then, deleting the pair where the key is “public_key”. If there’s no such key, errors out, because the parameter “error_not_exist” is set to true.
- Completely masking the value for key “private_key”, using
go-masker’s default masking function supported bypgstream’s templating. - Finally, updating the value for key “newKey” to “newValue”. Since “error_not_exist” is false by default, and there is no such key in the example below, this operation will be done by adding a new key-value pair.
literal_string
Description: Transforms all values into the given constant value. Uniqueness:lossy. Every value becomes the same literal. Cannot be used on a column covered by a unique index. See Uniqueness and unique indexes.
Below example makes all values in the JSON column
log_message to become {'error': null}.
This transformer can be used for any Postgres type as long as the given string literal has the correct syntax for that type. e.g It can be “5-10-2021” for a date column, or “3.14159265” for a double precision one.
Example Configuration:
phone_number
Description: Generates anonymized phone numbers with customizable length.
If the prefix is set, this transformer will always generate phone numbers starting with the prefix.
prefix can also be a dynamic parameter, referring to some other column. Please see the below example config.
Example Configuration:
Example Configuration:
encrypted_aes_siv
Description: Encrypts values with AES-SIV (RFC 5297), a deterministic authenticated encryption scheme. The same input, key and associated data always produce the same token, so equality relationships between column values are preserved across rows, tables and runs — while remaining reversible by holders of the key, unlike hashing. Tokens are authenticated: tampered or forged values fail decryption. The output is the ciphertext encoded as unpadded base64url, safe for URLs and file names. Uniqueness:preserved. Encryption is reversible with the key, so two distinct plaintexts cannot share a token. This is the only transformer that never collides on a column covered by a unique index. Note that being reversible makes it pseudonymization rather than anonymization — see Keeping a column unique.
Note on length-constrained columns: the token is always longer than the input —
ceil(4 × (input_length + 16) / 3) characters (36 for an 11-character input, 22 minimum). Length-constrained columns (varchar(n), char(n)) must be wide enough to hold the expanded token or writes to the target will fail; prefer text columns.
For bytea columns the raw bytes are encrypted (the transformer normalizes the hex-text form delivered during replication and the raw bytes delivered during snapshots to the same plaintext), and the token is stored as the ASCII bytes of the base64url text.
key_hex is the 64-byte AES-SIV key, hex-encoded (128 characters), e.g. generated with openssl rand -hex 64. AES-SIV requires the full 64-byte key (RFC 5297); shorter keys are rejected.
associated_data is authenticated but not encrypted: a token minted with one associated data value fails decryption under another. Use it to bind tokens to a context (such as a table/column name) so they cannot be replayed elsewhere.
Security note: the encryption is deterministic by design — equal inputs produce equal tokens, which reveals equality (and only equality) of the underlying values. Anyone holding the key can decrypt the tokens, so the anonymization guarantee is key custody: load the key from a secret manager and never store it alongside the transformed data.
Example Configuration:
Every run with the same key and parameters produces the same output. Tokens can be decrypted with any RFC 5297 AES-SIV implementation, for example Tink’s
daead/subtle package in Go.
Transformation rules
The rules for the transformers are defined in a dedicated yaml file with the following format:infer_from_security_labels option is enabled, the table transformers will be parsed from the source Postgres SECURITY LABELS for the anon extension. If the option is not enabled, the table transformers need to be explicitly provided.
Below is a complete example of a transformation rules YAML file:
strict or relaxed for all tables at once. Or it can be determined for each table individually, by setting the higher level validation_mode parameter to table_level. When it is set to strict, pgstream will throw an error if any of the columns in the table do not have a transformer defined. When set to relaxed, pgstream will skip any columns that do not have a transformer defined. Also in strict mode, all snapshot tables must be provided in the transformation config.
For details on how to use and configure the transformer, check the transformer tutorial.