Hologres:Data masking

• Importing data from tables and columns with masking rules to tables and columns without masking rules is not supported. The related error message is as follows.

  Error: ERROR: The insert table has not set SECURITY LABEL

• UNION and DISTINCT queries on tables with masking rules are not supported. The related error message is as follows.

  Error: ERROR: UNION is not support on security item

• Data masking can affect query performance. The impact depends on the masking method and data volume. Performance may decrease by 10% to 20%. In extreme scenarios, performance may decrease by several times.

• Setting masking rules for foreign tables is not supported.

Set masking rules

Hologres supports setting data masking for target columns or target users. Before setting data masking, you must enable the data masking GUC parameter. For more information, see Preparations.

• Syntax

  • Set a masking policy for a column

    To mask data in multiple columns, run this statement multiple times.

    SECURITY LABEL FOR hg_anon ON COLUMN <tablename>.<col_name> IS <label_name>/'default_value';

  • Set a masking policy for a user

     SECURITY LABEL FOR hg_anon ON ROLE <user_name> IS '[<label_name>|all]:[masked|unmasked]';

• Parameter description

  Parameter	Description
  hg_anon	`hg_anon` is an extension function encapsulated within Hologres. You must call this extension function to enable the data masking feature.
  tablename	The name of the table that contains the column to be masked.
  col_name	The name of the column to be masked.
  label_name	A preset masking function in the system. You can run SHOW hg_anon_labels; to view the `label_name` set for the current database.
  user_name	The account ID. You can obtain it from the User Information page.
  masked|unmasked	masked: Use data masking. unmasked: Do not use data masking.

  The following table describes the preset label_name values.

  Data type	label_name	Masking policy	Masking type	Description	Masking example
  TEXT	name	name	mask	Name masking.	Before: Li Hua; After: * Hua. Before: Wang Xiaoqiang; After: ** Qiang.
  	email	email	mask	Email address masking.	Before: lihu***@alibaba.com; After: lih***@alibaba.com.
  	ip	ip	mask	IP address masking.	Before: 1.2.3.4; After: 1.*.*.*.
  	id	id	mask	ID card number masking.	Before: 110345188812011234; After: 1****************4.
  	phone	phone	mask	Phone number masking.	Before: 1390000****; After: *********34.
  	bank_id	bank_id	mask	Bank card or credit card account masking.	Before: 2349867902834701928; After: ***************1928.
  	hash	md5	hash	Use the MD5 algorithm for masking.	Before: Wenyi West Road, Hangzhou, Zhejiang; After: dbf894b409d4a2ef17dfd9c7fdcafcd8.
  	first_mask	first_mask	mask	A `first_mask` rule is defined to display only the first character.	Before: 123456789; After: 1********.
  Types such as INT and FLOAT	Default	default_value	default_value	Mask with a default value. Note If the `label_name` does not exist when you set data masking, the system falls back to default value masking. This is supported only in Hologres V3.1 and later.	The mapping between data types and default masked values is as follows: TEXT/VARCHAR/CHAR: *** BOOLEAN: false INT8/INT4/INT2: 0 TIMESTAMPTZ/TIMESTAMP: 2000-01-01 00:00:00 FLOAT8/FLOAT4/DECIMAL/NUMERIC: 0.0 MONEY: 0 DATE: 2000-01-01 TIME/TIMETZ: current time UUID: 00000000-0000-0000-0000-000000000000

View set masking rules

• View masking set for a column

  -- View masking set for a column
  SELECT c.relname, a.attname, provider, label
  FROM pg_seclabel s
  JOIN pg_class c ON s.objoid = c.oid
  JOIN pg_attribute a ON s.objoid = a.attrelid AND s.objsubid = a.attnum;

• View masking set for a user

  -- View masking rules set for a user
  SELECT
      usename,
      label
  FROM
      pg_shseclabel s
      INNER JOIN pg_catalog.pg_user u ON s.objoid = u.usesysid;

Modify custom masking rules

If the set masking rules do not meet your needs, you can customize them by modifying the hg_anon_lables GUC parameter.

• Syntax

  -- label_name is your custom name, and method is a built-in Hologres method.
  ALTER DATABASE <db_name> SET hg_anon_labels = '[   
  {"label": <label_name1>, "method":<method1>},  
  {"label": <label_name2>, "method":<method2>}, 
  ...  ]'; 

  After running the ALTER DATABASE command, the change does not take effect in the current connection. You must establish a new connection. You can use the following command to check if the setting has taken effect.

  SHOW hg_anon_enable;

• Example

  ALTER DATABASE test_db SET hg_anon_labels = '[
  {"label":"ip", "method":{"desensType":"mask", "type":"ip"}},
  {"label":"email", "method":{"desensType":"mask", "type":"email"}},
  {"label":"name", "method":{"desensType":"mask", "type":"name"}},
  {"label":"first_mask", "method":{"desensType":"mask", "type":"user_define", "before":1, "after":0}},
  {"label":"hash", "method":{"desensType":"hash", "type":"md5", "salt":""}}]';

  Parameter description:

  Masking item	Masking content description	Masking result example
  {"desensType":"mask", "type":"ip"}	IP address masking.	192.*.*.*
  {"desensType":"mask", "type":"email"}	Email address masking.	abc***@example.net
  {"desensType":"mask", "type":"name"}	Name masking.	*Five
  {"desensType":"hash", "type":"md5", "salt":""}	Hash masking.	e086aa137fa19f67d27b39d0eca186103228f322c9c98a125554a24f875f0f7e
  {"label":"first_mask", "method":{"desensType":"mask", "type":"user_define", "before":1, "after":0}}{"label":"last_mask", "method":{"desensType":"mask", "type":"user_define", "before":0, "after":1}}	Custom content masking.	None

Sample data

Prepare a data source. You can also use your own business data.

-- Create a database
CREATE DATABASE hg_anon_demo;
-- Create a sample data table
DROP TABLE IF EXISTS personal_basic_information;
CREATE TABLE personal_basic_information  
(
    name TEXT
    ,email TEXT
    ,ip TEXT
    ,id TEXT 
    ,phone TEXT
    ,bank_id TEXT);
-- Insert sample data
INSERT INTO personal_basic_information(name,email,ip,id,phone,bank_id) VALUES 
('Zhang San','jiaxi***@alibaba-inc.com','127.0.0.1','142732199104050022','18157161223','4514610803067088'),
('Li Si','wb-hy583***@antgroup.com','127.0.0.1','510622198412248000','15757121834','6252470010027800'),
('Li Xiaoyao','wb-hy583***@antgroup.com','172.21.4.234','511025198812271696','18215451832','6252470010027800');

Set a masking rule for a column

-- Create the hg_anon extension function.
CREATE EXTENSION IF NOT EXISTS hg_anon;

-- Enable the data masking feature for the hg_anon_demo database.
ALTER DATABASE hg_anon_demo SET hg_anon_enable = on;

-- Set the masking rule for each column.
SECURITY LABEL FOR hg_anon ON COLUMN personal_basic_information.name IS 'name';
SECURITY LABEL FOR hg_anon ON COLUMN personal_basic_information.id IS 'id';
SECURITY LABEL FOR hg_anon ON COLUMN personal_basic_information.phone IS 'phone';
SECURITY LABEL FOR hg_anon ON COLUMN personal_basic_information.email IS 'email';
SECURITY LABEL FOR hg_anon ON COLUMN personal_basic_information.bank_id IS 'bank_id';
SECURITY LABEL FOR hg_anon ON COLUMN personal_basic_information.ip IS 'ip';

After establishing a new connection, use the following command to view the masked results.

SELECT * FROM personal_basic_information;

The following result is returned.

name |         email          |      ip      |         id         |    phone    |     bank_id      
------+------------------------+--------------+--------------------+-------------+------------------
 * Si  | wb-***@antgroup.com    | 127.*.*.*    | 5****************0 | *********34 | ************7800
 ** Yao | wb-***@antgroup.com    | 172.**.*.*** | 5****************6 | *********32 | ************7800
 * San  | jia***@alibaba-inc.com | 127.*.*.*    | 1****************2 | *********23 | ************7088
(3 rows)

Cancel masking for a column

SECURITY LABEL FOR hg_anon ON COLUMN personal_basic_information.name IS null;
SECURITY LABEL FOR hg_anon ON COLUMN personal_basic_information.id IS null;

Use the following command to view the result after canceling masking for a column.

SELECT name,id FROM personal_basic_information;

The following result is returned.

SELECT name,id FROM personal_basic_information;
  name  |         id         
--------+--------------------
 Li Si   | 510622198412248000
 Li Xiaoyao | 511025198812271696
 Zhang San   | 142732199104050022
(3 rows)

Cancel masking rules for a user

-- Cancel all masking for the BASIC$test account.
SECURITY LABEL FOR hg_anon ON ROLE "BASIC$test" IS 'all:unmasked';

Use the following command to view the result after canceling masking for a user.

SELECT * FROM personal_basic_information;

The following result is returned.

  name  |          email           |      ip      |         id         |    phone    |     bank_id      
--------+--------------------------+--------------+--------------------+-------------+------------------
 Li Si   | wb-hy583***@antgroup.com | 127.0.0.1    | 510622198412248000 | 15757121834 | 6252470010027800
 Li Xiaoyao | wb-hy583***@antgroup.com | 172.21.4.234 | 511025198812271696 | 18215451832 | 6252470010027800
 Zhang San   | jiaxi***@alibaba-inc.com | 127.0.0.1    | 142732199104050022 | 18157161223 | 4514610803067088
(3 rows)

Set masking for a JOIN column

-- Example of setting masking for a JOIN column
CREATE TABLE tbl1 (
    id text
);
INSERT INTO tbl1
    VALUES ('142732199102290022');

-- The id column of the personal_basic_information table is already masked.
SELECT * FROM personal_basic_information a JOIN tbl1 b ON a.id = b.id;

The following result is returned.

name | email | ip | id | phone | bank_id | id 
------+-------+----+----+-------+---------+----
(0 rows)