PostgreSQL 数据脱敏方法

文章来源：https://developer.aliyun.com/article/93516

测试数据

postgres=# create table test(id int primary key, username text, phonenum text, addr text, pwd text, crt_time timestamp);  
CREATE TABLE  
  
postgres=# insert into test select id, 'test_'||id, 13900000000+(random()*90000000)::int, '中国杭州xxxxxxxxxxxxxxxxxx'||random(), md5(random()::text), clock_timestamp() from generate_series(1,10000000) t(id);  
INSERT 0 10000000  
  
postgres=# select * from test limit 10;  
 id | username |  phonenum   |                    addr                     |               pwd                |          crt_time            
----+----------+-------------+---------------------------------------------+----------------------------------+----------------------------  
  1 | test_1   | 13950521974 | 中国杭州xxxxxxxxxxxxxxxxxx0.953363882377744 | 885723a5f4938808235c5debaab473ec | 2017-06-02 15:05:55.465132  
  2 | test_2   | 13975998000 | 中国杭州xxxxxxxxxxxxxxxxxx0.91321265604347  | 7ea01dc02c0fbc965f38d1bf12b303eb | 2017-06-02 15:05:55.46534  
  3 | test_3   | 13922255548 | 中国杭州xxxxxxxxxxxxxxxxxx0.846756176557392 | 7c2992bdc69312cbb3bb135dd2b98491 | 2017-06-02 15:05:55.46535  
  4 | test_4   | 13985121895 | 中国杭州xxxxxxxxxxxxxxxxxx0.639280265197158 | 202e32f0f0e3fe669c00678f7acd2485 | 2017-06-02 15:05:55.465355  
  5 | test_5   | 13982757650 | 中国杭州xxxxxxxxxxxxxxxxxx0.501174578908831 | b6a42fc1ebe9326ad81a81a5896a5c6c | 2017-06-02 15:05:55.465359  
  6 | test_6   | 13903699864 | 中国杭州xxxxxxxxxxxxxxxxxx0.193029860965908 | f6bc06e5cda459d09141a2c93f317cf2 | 2017-06-02 15:05:55.465363  
  7 | test_7   | 13929797532 | 中国杭州xxxxxxxxxxxxxxxxxx0.192601112183183 | 75c12a3f14c7ef3e558cef79d84a7e8e | 2017-06-02 15:05:55.465368  
  8 | test_8   | 13961108182 | 中国杭州xxxxxxxxxxxxxxxxxx0.900682372972369 | 5df33d15cf7726f2fb57df3ed913b306 | 2017-06-02 15:05:55.465371  
  9 | test_9   | 13978455210 | 中国杭州xxxxxxxxxxxxxxxxxx0.87795089604333  | cbe233f00cdd3c61c67415c1f8691846 | 2017-06-02 15:05:55.465375  
 10 | test_10  | 13957044022 | 中国杭州xxxxxxxxxxxxxxxxxx0.410478914622217 | cdf2f98b0ff5a973efaca6a82625e283 | 2017-06-02 15:05:55.465379  
(10 rows)  

脱敏

脱敏的手段很多，用户对脱敏的需求也可能很多。

常见的例如

1. 隐藏字符串中间的内容，使用*表示，同时保持原始长度

2. 隐藏字符串中间的内容，使用*表示，不保持原始长度

3. 返回加密值

不管什么需求，实际上就是数据的转换，从原始值，转换为目标值。在PostgreSQL中可以通过function实现这样的转换，对不同的需求，编写不同的转换逻辑即可。

例子，将字符串中间部分模糊化，只显示字符串头2个，末尾1个。

select id, substring(username,1,2)||'******'||substring(username,length(username),1),   
substring(phonenum,1,2)||'******'||substring(phonenum, length(phonenum),1),   
substring(addr,1,2)||'******'||substring(addr, length(addr),1),   
substring(pwd,1,2)||'******'||substring(pwd, length(pwd),1),   
crt_time  
from test  
TABLESAMPLE bernoulli (1);  
  
   id    | ?column?  | ?column?  |  ?column?   | ?column?  |          crt_time            
---------+-----------+-----------+-------------+-----------+----------------------------  
      69 | te******9 | 13******5 | 中国******9 | c0******2 | 2017-06-02 15:32:26.261624  
     297 | te******7 | 13******2 | 中国******1 | d9******6 | 2017-06-02 15:32:26.262558  
     330 | te******0 | 13******5 | 中国******3 | bd******0 | 2017-06-02 15:32:26.262677  
     335 | te******5 | 13******5 | 中国******6 | 08******f | 2017-06-02 15:32:26.262721  
     416 | te******6 | 13******6 | 中国******2 | b3******d | 2017-06-02 15:32:26.26312  
     460 | te******0 | 13******4 | 中国******8 | e5******f | 2017-06-02 15:32:26.26332  
     479 | te******9 | 13******1 | 中国******1 | 1d******4 | 2017-06-02 15:32:26.263393  
     485 | te******5 | 13******0 | 中国******3 | a3******8 | 2017-06-02 15:32:26.263418  
     692 | te******2 | 13******9 | 中国******4 | 69******8 | 2017-06-02 15:32:26.264326  
    1087 | te******7 | 13******9 | 中国******3 | 8e******5 | 2017-06-02 15:32:26.266091  
    1088 | te******8 | 13******8 | 中国******7 | 37******e | 2017-06-02 15:32:26.266095  
    1116 | te******6 | 13******8 | 中国******2 | 4c******3 | 2017-06-02 15:32:26.266235  
    1210 | te******0 | 13******4 | 中国******8 | 49******c | 2017-06-02 15:32:26.266671  
......  

如果需要更复杂的转换，写PostgreSQL的UDF对字段值进行转换即可。

将采样结果抽取到其他平台的方法也很多，例如copy到stdout，或者ETL工具等。

例子

psql test -c "copy (select id, substring(username,1,2)||'******'||substring(username,length(username),1),   
substring(phonenum,1,2)||'******'||substring(phonenum, length(phonenum),1),   
substring(addr,1,2)||'******'||substring(addr, length(addr),1),   
substring(pwd,1,2)||'******'||substring(pwd, length(pwd),1),   
crt_time  
from test  
TABLESAMPLE bernoulli (1)  
) to stdout" > ./sample_test.log  
  
  
less sample_test.log   
54      te******4       13******4       中国******3     52******b       2017-06-02 15:32:26.261451  
58      te******8       13******6       中国******3     23******a       2017-06-02 15:32:26.261584  
305     te******5       13******6       中国******9     c0******4       2017-06-02 15:32:26.262587  
399     te******9       13******5       中国******4     71******7       2017-06-02 15:32:26.26298  
421     te******1       13******0       中国******4     21******3       2017-06-02 15:32:26.263139  
677     te******7       13******5       中国******5     e2******7       2017-06-02 15:32:26.264269  
874     te******4       13******9       中国******2     a6******9       2017-06-02 15:32:26.265159