怎么使用PostgreSQL的插件postgresql_anonymizer
本篇内容主要讲解“怎么使用PostgreSQL的插件postgresql_anonymizer”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么使用PostgreSQL的插件postgresql_anonymizer”吧!
站在用户的角度思考问题，与客户深入沟通，找到金东网站设计与金东网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：网站建设、成都网站建设、企业官网、英文网站、手机端网站、网站推广、域名申请、虚拟空间、企业邮箱。业务覆盖金东地区。
安装
依赖tsm_system_rows和ddlx两个扩展，需提前安装妥当。
[local:/data/pg12]:5432 pg12@testdb=# create extension tsm_system_rows; CREATE EXTENSION [local:/data/pg12]:5432 pg12@testdb=# create extension ddlx; CREATE EXTENSION [local:/data/pg12]:5432 pg12@testdb=#
clone代码，编译安装
[pg12@localhost contrib]$ git clone https://gitlab.com/dalibo/postgresql_anonymizer.git -b 0.5.0 Cloning into 'postgresql_anonymizer'... remote: Enumerating objects: 2396, done. remote: Counting objects: 100% (2396/2396), done. remote: Compressing objects: 100% (854/854), done. remote: Total 2396 (delta 1524), reused 2379 (delta 1513) Receiving objects: 100% (2396/2396), 12.85 MiB | 55.00 KiB/s, done. Resolving deltas: 100% (1524/1524), done. Note: checking out 'c108008719a1394b55779ff7115f188511c6ec89'. You are in 'detached HEAD' state. You can look around, make experimental changes and commit them, and you can discard any commits you make in this state without impacting any branches by performing another checkout. If you want to create a new branch to retain commits you create, you may do so (now or later) by using -b with the checkout command again. Example: git checkout -b new_branch_name
编译安装
[pg12@localhost contrib]$ cd postgresql_anonymizer/ [pg12@localhost postgresql_anonymizer]$ make mkdir -p anon cp anon.sql anon/anon--0.5.0.sql cp data/default/* anon/ gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o anon.o anon.c -MMD -MP -MF .deps/anon.Po gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC anon.o -L/appdb/pg12/pg12.1/lib -Wl,--as-needed -Wl,-rpath,'/appdb/pg12/pg12.1/lib',--enable-new-dtags -shared -o anon.so [pg12@localhost postgresql_anonymizer]$ make install mkdir -p anon cp anon.sql anon/anon--0.5.0.sql cp data/default/* anon/ /bin/mkdir -p '/appdb/pg12/pg12.1/share/postgresql/extension' /bin/mkdir -p '/appdb/pg12/pg12.1/share/postgresql/extension/anon' /bin/mkdir -p '/appdb/pg12/pg12.1/lib/postgresql' /bin/install -c -m 644 .//anon.control '/appdb/pg12/pg12.1/share/postgresql/extension/' /bin/install -c -m 644 .//anon/* '/appdb/pg12/pg12.1/share/postgresql/extension/anon/' /bin/install -c -m 755 anon.so '/appdb/pg12/pg12.1/lib/postgresql/' [pg12@localhost postgresql_anonymizer]$
体验
创建扩展，添加到加载库中shared_preload_libraries，重启实例
[local:/data/pg12]:5432 pg12@testdb=# CREATE EXTENSION IF NOT EXISTS anon CASCADE; CREATE EXTENSION [local:/data/pg12]:5432 pg12@testdb=# alter system set shared_preload_libraries = 'anon'; ALTER SYSTEM [local:/data/pg12]:5432 pg12@testdb=# [local:/data/pg12]:5432 pg12@testdb=# exit [pg12@localhost contrib]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2019-11-19 16:41:45.940 CST [15439] LOG: starting PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit 2019-11-19 16:41:45.940 CST [15439] LOG: listening on IPv4 address "0.0.0.0", port 5432 2019-11-19 16:41:45.940 CST [15439] LOG: listening on IPv6 address "::", port 5432 2019-11-19 16:41:45.943 CST [15439] LOG: listening on Unix socket "/data/pg12/.s.PGSQL.5432" 2019-11-19 16:41:46.013 CST [15439] LOG: redirecting log output to logging collector process 2019-11-19 16:41:46.013 CST [15439] HINT: Future log output will appear in directory "pg_log". done server started [pg12@localhost contrib]$
插件提供的函数
[local:/data/pg12]:5432 pg12@testdb=# select * from pg_namespace where nspname='anon'; oid | nspname | nspowner | nspacl -------+---------+----------+-------- 17050 | anon | 10 | (1 row) [local:/data/pg12]:5432 pg12@testdb=# \d pg_proc Table "pg_catalog.pg_proc" Column | Type | Collation | Nullable | Default -----------------+--------------+-----------+----------+--------- oid | oid | | not null | proname | name | | not null | pronamespace | oid | | not null | proowner | oid | | not null | prolang | oid | | not null | procost | real | | not null | prorows | real | | not null | provariadic | oid | | not null | prosupport | regproc | | not null | prokind | "char" | | not null | prosecdef | boolean | | not null | proleakproof | boolean | | not null | proisstrict | boolean | | not null | proretset | boolean | | not null | provolatile | "char" | | not null | proparallel | "char" | | not null | pronargs | smallint | | not null | pronargdefaults | smallint | | not null | prorettype | oid | | not null | proargtypes | oidvector | | not null | proallargtypes | oid[] | | | proargmodes | "char"[] | | | proargnames | text[] | C | | [local:/data/pg12]:5432 pg12@testdb=# select proname from pg_proc where pronamespace = 17050; proname ------------------------------ dump generalize_int4range random_date_between random_date random_int_between add_noise_on_numeric_column add_noise_on_datetime_column shuffle_column load load isloaded unload random_string random_zip random_phone fake_first_name fake_last_name fake_email fake_city_in_country fake_city fake_region_in_country fake_region fake_country fake_company fake_iban fake_siren fake_siret lorem_ipsum random_first_name random_last_name random_email random_city_in_country random_city random_region_in_country random_region random_country random_company random_iban random_siren random_siret partial partial_email source_schema mask_schema anonymize_column anonymize_table anonymize_database static_substitution hasmask mask_columns mask_create mask_filters mask_create_view mask_drop_view get_copy_statement start_dynamic_masking mask_init stop_dynamic_masking mask_trigger mask_role unmask_role mask_enable mask_disable mask_update dump_ddl dump_data generalize_int8range generalize_numrange generalize_tsrange generalize_tstzrange generalize_daterange k_anonymity (72 rows)
下面创建一张”隐私”表,使用该插件实现动态脱敏
[local:/data/pg12]:5432 pg12@testdb=# drop table t_masking;
ERROR: table "t_masking" does not exist
[local:/data/pg12]:5432 pg12@testdb=# create table t_masking(id serial,name varchar(20),zipcode varchar(20));
values('王五','230100');
CREATE TABLE
[local:/data/pg12]:5432 pg12@testdb=#
[local:/data/pg12]:5432 pg12@testdb=# insert into t_masking(name,zipcode) values('张三','440100');
INSERT 0 1
[local:/data/pg12]:5432 pg12@testdb=# insert into t_masking(name,zipcode) values('李四','420100');
INSERT 0 1
[local:/data/pg12]:5432 pg12@testdb=# insert into t_masking(name,zipcode) values('王五','230100');
INSERT 0 1
[local:/data/pg12]:5432 pg12@testdb=#启用动态脱敏，创建脱敏用户masking
[local:/data/pg12]:5432 pg12@testdb=# SELECT anon.start_dynamic_masking(); start_dynamic_masking ----------------------- t (1 row) [local:/data/pg12]:5432 pg12@testdb=# [local:/data/pg12]:5432 pg12@testdb=# CREATE ROLE masking LOGIN with password 'root'; ERROR: syntax error at or near "with" LINE 1: CREATE ROLE masking LOGIN with password 'root'; ^ [local:/data/pg12]:5432 pg12@testdb=# CREATE user masking with password 'root'; CREATE ROLE
打标签
[local:/data/pg12]:5432 pg12@testdb=# SECURITY LABEL FOR anon ON ROLE masking IS 'MASKED'; SECURITY LABEL [local:/data/pg12]:5432 pg12@testdb=# SECURITY LABEL FOR anon ON COLUMN t_masking.name is 'MASKED WITH FUNCTION anon.fake_last_name()'; SECURITY LABEL [local:/data/pg12]:5432 pg12@testdb=# SECURITY LABEL FOR anon ON COLUMN t_masking.zipcode is 'MASKED WITH FUNCTION anon.random_zip()'; SECURITY LABEL [local:/data/pg12]:5432 pg12@testdb=#
t_masking的数据
[local:/data/pg12]:5432 pg12@testdb=# select * from t_masking; id | name | zipcode ----+------+--------- 1 | 张三 | 440100 2 | 李四 | 420100 3 | 王五 | 230100 (3 rows) [local:/data/pg12]:5432 pg12@testdb=#
用户masking登录查询得到的数据
[pg12@localhost pgddl]$ psql -U masking Expanded display is used automatically. psql (12.1) Type "help" for help. [local:/data/pg12]:5432 masking@testdb=> select * from t_masking; id | name | zipcode ----+----------+--------- 1 | Malagisi | 46864 2 | Hausner | 55255 3 | Degolyer | 82186 (3 rows) [local:/data/pg12]:5432 masking@testdb=>
可以看到，数据已被“脱敏”。
到此，相信大家对“怎么使用PostgreSQL的插件postgresql_anonymizer”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
本文标题：怎么使用PostgreSQL的插件postgresql_anonymizer
文章地址：http://mswzjz.cn/article/jchsoe.html