Shulou(Shulou.com)05/31 Report--

This article focuses on "how to use the PostgreSQL plug-in postgresql_anonymizer", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use the plug-in postgresql_anonymizer of PostgreSQL.

Installation

Rely on tsm_system_rows and ddlx extensions and need to be installed in advance.

[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 code, compiling and installing

[pg12@localhost contrib] $git clone https://gitlab.com/dalibo/postgresql_anonymizer.git-b 0.5.0Cloning into 'postgresql_anonymizer'...remote: Enumerating objects: 2396, done.remote: Counting objects: 100%, done.remote: Compressing objects: 100% (854 Total 854), done.remote: Total 2396 (delta 1524), reused 2379 (delta 1513) Receiving objects: 100% (2396 KiB/s), 12.85 MiB | 55.00 KiB/s Done.Resolving deltas: 100%, done.Note: checking out 'c108008719a1394b55779ff7115f188511c6ec89'.You are in' detached HEAD' state. You can look around, make experimentalchanges and commit them, and you can discard any commits you make in thisstate without impacting any branches by performing another checkout.If you want to create a new branch to retain commits you create, you maydo so (now or later) by using-b with the checkout command again. Example: git checkout-b new_branch_name

Compilation and installation

[pg12@localhost contrib] $cd postgresql_anonymizer/ [PG12 @ localhost postgresql_anonymizer] $makemkdir-p anoncp anon.sql anon/anon--0.5.0.sqlcp 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.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-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 installmkdir-p anoncp anon.sql anon/anon--0.5.0.sqlcp 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] $

Experience

Create an extension, add it to the load library, shared_preload_libraries, and restart the instance

[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 restartwaiting for server to shut down.... Doneserver stoppedwaiting for server to start....2019-11-19 16 LOG 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-bit2019-11-19 16 listening on IPv4 address 41.940 CST [15439] LOG: listening on IPv4 address "0.0.0.0" Port 54322019-11-19 16 LOG 41V 45.940 CST [15439] LOG: listening on IPv6 address ":", port 54322019-11-19 16 V 41V 45.943 CST [15439] LOG: listening on Unix socket "/ data/pg12/.s.PGSQL.5432" 2019-11-19 16V 41V 46.013 CST [15439] LOG: redirecting log output to logging collector process2019-11-19 16V 41V 41V 46.013 CST: Future log output will appear in directory "pg_log". Doneserver started [pg12@localhost contrib] $

Functions provided by plug-ins

[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)

Create a "Privacy" table and use the plug-in to achieve dynamic desensitization

[local:/data/pg12]: 5432 pg12@testdb=# drop table tasking 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 ('Wang Wu', '230100') CREATE TABLE [local:/data/pg12]: 5432 pg12@testdb=# [local:/data/pg12]: 5432 pg12@testdb=# insert into t_masking (name,zipcode) values ('Zhang San', '440100'); INSERT 01 [local:/data/pg12]: 5432 pg12@testdb=# insert into t_masking (name,zipcode) values ('Li Si', '420100'); INSERT 01 [local:/data/pg12]: 5432 pg12@testdb=# insert into t_masking (name,zipcode) values ('Wang Wu', '230100') INSERT 0 1 [local:/data/pg12]: 5432 pg12@testdb=#

Enable dynamic desensitization to create desensitized user 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

Label

[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 's data.

[local:/data/pg12]: 5432 pg12@testdb=# select * from tasking masquerading; id | name | zipcode-+-+-1 | Zhang San | 440100 2 | Li Si | 420100 3 | Wang Wu | 230100 (3 rows) [local:/data/pg12]: 5432 pg12@testdb=#

Data obtained by user's masking login query

[pg12@localhost pgddl] $psql-U maskingExpanded display is used automatically.psql (12.1) Type "help" for help. [local:/data/pg12]: 5432 masking@testdb= > select * from tasking mapping; id | name | zipcode-- +-+-1 | Malagisi | 46864 2 | Hausner | 55255 3 | Degolyer | 82186 (3 rows) [local:/data/pg12]: 5432 masking@testdb= >

It can be seen that the data has been desensitized.

At this point, I believe you have a deeper understanding of "how to use PostgreSQL's plug-in postgresql_anonymizer". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.