本篇内容主要讲解“怎么使用PostgreSQL的插件postgresql_anonymizer”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么使用PostgreSQL的插件postgresql_anonymizer”吧!

安装
依赖tsm_system_rows和ddlx两个扩展，需提前安装妥当。

[local:/data/pg12]:5432pg12@testdb=#createextensiontsm_system_rows;CREATEEXTENSION[local:/data/pg12]:5432pg12@testdb=#createextensionddlx;CREATEEXTENSION[local:/data/pg12]:5432pg12@testdb=#

clone代码，编译安装

[pg12@localhostcontrib]$gitclonehttps://gitlab.com/dalibo/postgresql_anonymizer.git-b0.5.0Cloninginto'postgresql_anonymizer'...remote:Enumeratingobjects:2396,done.remote:Countingobjects:100%(2396/2396),done.remote:Compressingobjects:100%(854/854),done.remote:Total2396(delta1524),reused2379(delta1513)Receivingobjects:100%(2396/2396),12.85MiB|55.00KiB/s,done.Resolvingdeltas:100%(1524/1524),done.Note:checkingout'c108008719a1394b55779ff7115f188511c6ec89'.Youarein'detachedHEAD'state.Youcanlookaround,makeexperimentalchangesandcommitthem,andyoucandiscardanycommitsyoumakeinthisstatewithoutimpactinganybranchesbyperforminganothercheckout.Ifyouwanttocreateanewbranchtoretaincommitsyoucreate,youmaydoso(noworlater)byusing-bwiththecheckoutcommandagain.Example:gitcheckout-bnew_branch_name

编译安装

[pg12@localhostcontrib]$cdpostgresql_anonymizer/[pg12@localhostpostgresql_anonymizer]$makemkdir-panoncpanon.sqlanon/anon--0.5.0.sqlcpdata/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-oanon.oanon.c-MMD-MP-MF.deps/anon.Pogcc-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-fPICanon.o-L/appdb/pg12/pg12.1/lib-Wl,--as-needed-Wl,-rpath,'/appdb/pg12/pg12.1/lib',--enable-new-dtags-shared-oanon.so[pg12@localhostpostgresql_anonymizer]$makeinstallmkdir-panoncpanon.sqlanon/anon--0.5.0.sqlcpdata/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-m644.//anon.control'/appdb/pg12/pg12.1/share/postgresql/extension/'/bin/install-c-m644.//anon/*'/appdb/pg12/pg12.1/share/postgresql/extension/anon/'/bin/install-c-m755anon.so'/appdb/pg12/pg12.1/lib/postgresql/'[pg12@localhostpostgresql_anonymizer]$

体验
创建扩展，添加到加载库中shared_preload_libraries，重启实例

[local:/data/pg12]:5432pg12@testdb=#CREATEEXTENSIONIFNOTEXISTSanonCASCADE;CREATEEXTENSION[local:/data/pg12]:5432pg12@testdb=#altersystemsetshared_preload_libraries='anon';ALTERSYSTEM[local:/data/pg12]:5432pg12@testdb=#[local:/data/pg12]:5432pg12@testdb=#exit[pg12@localhostcontrib]$pg_ctlrestartwaitingforservertoshutdown....doneserverstoppedwaitingforservertostart....2019-11-1916:41:45.940CST[15439]LOG:startingPostgreSQL12.1onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2019-11-1916:41:45.940CST[15439]LOG:listeningonIPv4address"0.0.0.0",port54322019-11-1916:41:45.940CST[15439]LOG:listeningonIPv6address"::",port54322019-11-1916:41:45.943CST[15439]LOG:listeningonUnixsocket"/data/pg12/.s.PGSQL.5432"2019-11-1916:41:46.013CST[15439]LOG:redirectinglogoutputtologgingcollectorprocess2019-11-1916:41:46.013CST[15439]HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted[pg12@localhostcontrib]$

插件提供的函数

[local:/data/pg12]:5432pg12@testdb=#select*frompg_namespacewherenspname='anon';oid|nspname|nspowner|nspacl-------+---------+----------+--------17050|anon|10|(1row)[local:/data/pg12]:5432pg12@testdb=#\dpg_procTable"pg_catalog.pg_proc"Column|Type|Collation|Nullable|Default-----------------+--------------+-----------+----------+---------oid|oid||notnull|proname|name||notnull|pronamespace|oid||notnull|proowner|oid||notnull|prolang|oid||notnull|procost|real||notnull|prorows|real||notnull|provariadic|oid||notnull|prosupport|regproc||notnull|prokind|"char"||notnull|prosecdef|boolean||notnull|proleakproof|boolean||notnull|proisstrict|boolean||notnull|proretset|boolean||notnull|provolatile|"char"||notnull|proparallel|"char"||notnull|pronargs|smallint||notnull|pronargdefaults|smallint||notnull|prorettype|oid||notnull|proargtypes|oidvector||notnull|proallargtypes|oid[]|||proargmodes|"char"[]|||proargnames|text[]|C||[local:/data/pg12]:5432pg12@testdb=#selectpronamefrompg_procwherepronamespace=17050;proname------------------------------dumpgeneralize_int4rangerandom_date_betweenrandom_daterandom_int_betweenadd_noise_on_numeric_columnadd_noise_on_datetime_columnshuffle_columnloadloadisloadedunloadrandom_stringrandom_ziprandom_phonefake_first_namefake_last_namefake_emailfake_city_in_countryfake_cityfake_region_in_countryfake_regionfake_countryfake_companyfake_ibanfake_sirenfake_siretlorem_ipsumrandom_first_namerandom_last_namerandom_emailrandom_city_in_countryrandom_cityrandom_region_in_countryrandom_regionrandom_countryrandom_companyrandom_ibanrandom_sirenrandom_siretpartialpartial_emailsource_schemamask_schemaanonymize_columnanonymize_tableanonymize_databasestatic_substitutionhasmaskmask_columnsmask_createmask_filtersmask_create_viewmask_drop_viewget_copy_statementstart_dynamic_maskingmask_initstop_dynamic_maskingmask_triggermask_roleunmask_rolemask_enablemask_disablemask_updatedump_ddldump_datageneralize_int8rangegeneralize_numrangegeneralize_tsrangegeneralize_tstzrangegeneralize_daterangek_anonymity(72rows)

下面创建一张”隐私”表,使用该插件实现动态脱敏

[local:/data/pg12]:5432pg12@testdb=#droptablet_masking;ERROR:table"t_masking"doesnotexist[local:/data/pg12]:5432pg12@testdb=#createtablet_masking(idserial,namevarchar(20),zipcodevarchar(20));values('王五','230100');CREATETABLE[local:/data/pg12]:5432pg12@testdb=#[local:/data/pg12]:5432pg12@testdb=#insertintot_masking(name,zipcode)values('张三','440100');INSERT01[local:/data/pg12]:5432pg12@testdb=#insertintot_masking(name,zipcode)values('李四','420100');INSERT01[local:/data/pg12]:5432pg12@testdb=#insertintot_masking(name,zipcode)values('王五','230100');INSERT01[local:/data/pg12]:5432pg12@testdb=#

启用动态脱敏，创建脱敏用户masking

[local:/data/pg12]:5432pg12@testdb=#SELECTanon.start_dynamic_masking();start_dynamic_masking-----------------------t(1row)[local:/data/pg12]:5432pg12@testdb=#[local:/data/pg12]:5432pg12@testdb=#CREATEROLEmaskingLOGINwithpassword'root';ERROR:syntaxerroratornear"with"LINE1:CREATEROLEmaskingLOGINwithpassword'root';^[local:/data/pg12]:5432pg12@testdb=#CREATEusermaskingwithpassword'root';CREATEROLE

打标签

[local:/data/pg12]:5432pg12@testdb=#SECURITYLABELFORanonONROLEmaskingIS'MASKED';SECURITYLABEL[local:/data/pg12]:5432pg12@testdb=#SECURITYLABELFORanonONCOLUMNt_masking.nameis'MASKEDWITHFUNCTIONanon.fake_last_name()';SECURITYLABEL[local:/data/pg12]:5432pg12@testdb=#SECURITYLABELFORanonONCOLUMNt_masking.zipcodeis'MASKEDWITHFUNCTIONanon.random_zip()';SECURITYLABEL[local:/data/pg12]:5432pg12@testdb=#

t_masking的数据

[local:/data/pg12]:5432pg12@testdb=#select*fromt_masking;id|name|zipcode----+------+---------1|张三|4401002|李四|4201003|王五|230100(3rows)[local:/data/pg12]:5432pg12@testdb=#

用户masking登录查询得到的数据

[pg12@localhostpgddl]$psql-UmaskingExpandeddisplayisusedautomatically.psql(12.1)Type"help"forhelp.[local:/data/pg12]:5432masking@testdb=>select*fromt_masking;id|name|zipcode----+----------+---------1|Malagisi|468642|Hausner|552553|Degolyer|82186(3rows)[local:/data/pg12]:5432masking@testdb=>

可以看到，数据已被“脱敏”。

到此，相信大家对“怎么使用PostgreSQL的插件postgresql_anonymizer”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！