** Summary: ** In some cases, certain columns of a table contain sensitive data. How can certain people see certain data?

1 the demand

In some cases, certain columns of a table may contain sensitive data (e.g., in a user information table, a user’s phone number). Naturally, we only want “admin” users to see this sensitive data, and we want other users to see “processed” — desensitized data.

2 Implementation scheme introduction

Solution 1: Use postgresQL_anonymizer for PG anonymization.

Option 2: Use view for desensitization;

2.1 Solution 1: Use postgresQL_Anonymizer for PG anonymization

(Examples from plug-in documentation)

-- Modify configuration file: shared_preload_libraries = 'pg_stat_statements, anon' -- 1\. CREATE EXTENSION IF NOT EXISTS anon CASCADE; SELECT anon.mask_init(); -- 2. Declare the blocked user CREATE ROLE skynet; COMMENT ON ROLE skynet IS 'MASKED'; COMMENT ON COLUMN people.name IS 'MASKED WITH FUNCTION anon.random_last_name()'; COMMENT ON COLUMN people.phone IS 'MASKED WITH FUNCTION anon.partial(phone,2,$******$,2)'; -- 4\. Query users who block sensitive information \! psql test -U skynet -c 'SELECT * FROM people; ' id | name | phone -----+----------+------------ T800 | n3xtchen | 13******11Copy the code

2.2 Scheme 2: Use view for desensitization

(Examples from local development environment)

-- 1\. Create test user create user root; create user normal_user; -- 2\. Set role root; create table user_phone_number(id int, user_name name, phone_number name); Insert into user_phone_number values(1, '12345678', '12345678'); Insert into user_phone_number values(1, '1 ', '56781234'); create or replace view member_phone_number as SELECT S.id, S.user_name, substring(S.phone_number, 1, 1) | | '* * * * * *' | | the substring (supachai panitchpakdi hone_number, 8, 8) as phone_number FROM user_phone_number as S. Revoke all on user_phone_number from public; grant all on member_phone_number to normal_user; -- 4\. Test the role of a normal user; set role normal_user; select * from user_phone_number; select * from member_phone_number;Copy the code

The results are as follows:

3 Comparison of advantages and disadvantages

In general, postgresql_anonymizer is a great plugin for lazy people who like to try new things. However, if stability is desired, views are recommended.

4 reference

PostgreSQL: anonymous (Anonymizer) tool’s official website: labs.dalibo.com/postgresql_…

2. The PostgreSQL: anonymous official documentation (Anonymizer) tools: PostgreSQL – Anonymizer. Readthedocs. IO/en/stable /

Click to follow, the first time to learn about Huawei cloud fresh technology ~