共計(jì) 8446 個(gè)字符，預(yù)計(jì)需要花費(fèi) 22 分鐘才能閱讀完成。
本篇內(nèi)容主要講解“怎么使用 PostgreSQL 的插件 postgresql_anonymizer”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓丸趣 TV 小編來(lái)帶大家學(xué)習(xí)“怎么使用 PostgreSQL 的插件 postgresql_anonymizer”吧!
安裝
依賴 tsm_system_rows 和 ddlx 兩個(gè)擴(kuò)展，需提前安裝妥當(dāng)。
[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]$體驗(yàn)
創(chuàng)建擴(kuò)展，添加到加載庫(kù)中 shared_preload_libraries，重啟實(shí)例
[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]$插件提供的函數(shù)
[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)下面創(chuàng)建一張”隱私”表, 使用該插件實(shí)現(xiàn)動(dòng)態(tài)脫敏
[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=#啟用動(dòng)態(tài)脫敏，創(chuàng)建脫敏用戶 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打標(biāo)簽
[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 的數(shù)據(jù)
[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 登錄查詢得到的數(shù)據(jù)
[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=可以看到，數(shù)據(jù)已被“脫敏”。
到此，相信大家對(duì)“怎么使用 PostgreSQL 的插件 postgresql_anonymizer”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是丸趣 TV 網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
正文完
發(fā)表至： 數(shù)據(jù)庫(kù)
2023-07-26
