PostgreSQL 安全管理数据脱敏 Anonymizer 导出脱敏(Anonymous Dumps)

1 背景知识

Anonymous Dumps 功能是指调用 pg_dump 时进行数据脱敏。

2 pg_dump

2.1 环境准备

DROP TABLE IF EXISTS  people CASCADE;
CREATE TABLE people (id TEXT, firstname TEXT, 
					 lastname TEXT, phone TEXT);
INSERT INTO people VALUES ('T1','Sarah', 'Conor','0609110911');
SELECT * FROM people;

//屏幕输出：
CREATE TABLE
=# SELECT * FROM people;
 id | firstname | lastname |   phone
----+----------+----------+------------
 T1 | Sarah    | Conor    | 0609110911
(1 row)

2.2 加载动态脱敏功能

CREATE EXTENSION IF NOT EXISTS anon CASCADE;
SELECT anon.start_dynamic_masking();

//屏幕输出：
NOTICE:  extension "anon" already exists, skipping
CREATE EXTENSION
 start_dynamic_masking 
-----------------------
 t
(1 row)

2.3 声明脱敏规则

SECURITY LABEL FOR anon ON COLUMN people.lastname
IS 'MASKED WITH FUNCTION anon.fake_last_name()';

SECURITY LABEL FOR anon ON COLUMN people.phone
IS 'MASKED WITH FUNCTION anon.partial(phone,2,$******$,2)';

2.4 创建一个脱敏用户

1. 创建脱敏用户

CREATE ROLE dump_anon LOGIN PASSWORD 'dump_anon'; 

2. 针对dump_anon 用户配置动态脱敏开关参数。

ALTER ROLE dump_anon SET anon.transparent_dynamic_masking = True; 

3. 针对 dump_anon 用户配置安全标签。

SECURITY LABEL FOR anon ON ROLE dump_anon IS 'MASKED';

关于 SECURITY LABEL SQL 语句适用于数据访问控制(MAC)控制方法。

2.5 授予该用户的读取权限

GRANT USAGE ON SCHEMA public TO dump_anon;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dump_anon;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dump_anon;

2.6 调用pg_dump 导出脱敏后的数据

pg_dump postgres \
        -U dump_anon \
        --no-security-labels \
        -t public.people \
        -Fp \
        -f /tmp/people.sql 

这里导出的数据将会被脱敏。

cat /tmp/people.sql 

//屏幕输出：
--
-- PostgreSQL database dump
--

-- Dumped from database version 16.0
-- Dumped by pg_dump version 16.0
...........

COPY public.people (id, firstname, lastname, phone) FROM stdin;
T1      Sarah   Bowman  06******11
\.

...........


--
-- PostgreSQL database dump complete
--

3 pg_dump_anon

此命令和 pg_dump 命令大部分相同。也支持 PostgreSQL 环境变量和 PostgreSQL .pgpass 。

3.1 安装pg_dump_anon

1. 安装Go 语言组件

sudo dnf install go -y

1. 使用Go安装

go install gitlab.com/dalibo/postgresql_anonymizer/pg_dump_anon@latest

3. 如果不想在生产服务器上安装Go 语言组件，可以使用以下命令编译二进制文件。

docker run --rm -v "$PWD":/go/bin golang go get gitlab.com/dalibo/postgresql_anonymizer/pg_dump_anon
sudo install pg_dump_anon $(pg_config --bindir)

3.2 调用 pg_dump_anon 导出脱敏数据

cd go/bin 
 ./pg_dump_anon -U dump_anon -f people.sql  -t public.people  -W -v postgres 

//屏幕输出：
Password: 2024/03/29 14:37:44 pg_dump_anon:  [1. Open the transaction]
2024/03/29 14:37:44 pg_dump_anon:  [disable sslmode and retry]
2024/03/29 14:37:44 pg_dump_anon:  [2. Dump the pre-data section]
2024/03/29 14:37:44 pg_dump_anon:  [/usr/local/pgsql/bin/pg_dump --section=pre-data --no-security-labels --exclude-schema=anon --exclude-schema=mask --dbname=postgres --username=dump_anon --table=public.people --verbose --snapshot=00000003-00000004-1]
2024/03/29 14:37:44 pg_dump_anon:  [3. Dump the tables data]
2024/03/29 14:37:44 pg_dump_anon:  [psql --quiet --tuples-only --no-align --no-psqlrc --dbname=postgres --username=dump_anon --command=BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;SET TRANSACTION SNAPSHOT '00000003-00000004-1';COPY (SELECT id,firstname,CAST(anon.fake_last_name() AS text) AS lastname,CAST(anon.partial(phone,2,$******$,2) AS text) AS phone FROM public.people ) TO STDOUT WITH CSV;ROLLBACK;]
2024/03/29 14:37:44 exit status 2

3.3 限制场景