pg-stage 0.4.1

Anonymization of data in pg_dump

pg_stage

A utility for generating a database dump, the data in which will be obfuscated. This dump can be used in development and stage servers without fear of their theft.

Content

• How does it work?
• Usage example
• Supported types of obfuscation
• Why did I write my utility?
• Thanks for the inspiration

How does it work?

The utility processes the output of the pg_dump command line by line and decides whether to obfuscate data at the level of comments to a table or column.

Usage example

1. You need to create a file with approximately the following contents:

# main.py
from pg_stage.obfuscators.plain import PlainObfuscator
from pg_stage.obfuscators.custom import CustomObfuscator

# Initialize obfuscator based on dump format
# Use PlainObfuscator for default SQL text format
# Use CustomObfuscator for custom format dumps (pg_dump -Fc)
obfuscator = PlainObfuscator(locale='ru_RU')  # or CustomObfuscator(locale='ru_RU')
obfuscator.run()

2. Add comments to a column or table:

COMMENT ON COLUMN table_1.first_name IS 'anon: [{"mutation_name": "first_name"}]';

3. Run pg_dump and redirect the stream to the running script process:

# Run pg_dump with appropriate format and pipe to main.py for obfuscation
# For PlainObfuscator: use default SQL text format
pg_dump -d database | python3 main.py > backup.sql

# For CustomObfuscator: use custom format (-Fc)
pg_dump -Fc -d database | python3 main.py > backup.dump

4. After that you will get the obfuscated data in the table

Supported types of obfuscation

You can see the current list here.

Why did I write my utility?

I also adhere to the rule that you do not need to place third-party plugins in the working database for its security (most utilities are in the form of database extensions).

Also, in similar utilities, I could not find the functionality for uniform obfuscation of data in related tables. This prompted me to write my own utility that will be able to obfuscate data in related tables with the same result by a foreign key.

Example:

COMMENT ON COLUMN table_1.first_name IS 'anon: [{"mutation_name": "first_name", "relations": [{"table_name": "table_1", "column_name": "last_name", "from_column_name": "id", "to_column_name": "id"}]}]';

where relations - links on tables where it is necessary to obfuscate fields according to the current field.

Thanks for the inspiration

• triki
• fake_pipe