Hologres:Data masking

• You cannot import data from a table with masking rules into a table where masking rules are not defined. The following error is returned:

  ERROR: The insert table has not set SECURITY LABEL

• You cannot run UNION or DISTINCT queries on tables with masking rules. The following error is returned:

  ERROR: UNION is not support on security item

• Data masking can affect query performance. The impact varies based on the masking method and data volume. Performance may degrade by 10% to 20%, and by a larger margin in extreme cases.

• You cannot set masking rules for foreign tables.

Configure masking rules

Hologres lets you set data masking rules for specific columns or users. Before configuring masking rules, you must enable the data masking GUC parameter. For more information, see Prerequisites.

• Syntax

  • Set a masking policy for a column

    To mask data in multiple columns, you must run this statement for each column.

    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]';

• Parameters

  Parameter	Description
  hg_anon	The hg_anon extension provides data masking functionality. You must specify hg_anon as the provider in the SECURITY LABEL statement.
  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	For the system-preset desensitization functions, you can use SHOW hg_anon_labels; to view the label_name set in the current database.
  user_name	The account ID. You can find it on the Security Settings page.
  masked|unmasked	masked: Use data masking. unmasked: Do not use data masking.

  The following table describes the predefined label_name values.

  Data type	label_name	Masking policy	Masking type	Description	Example
  TEXT	name	name	mask	Masks a name.	Before masking: Li Hua; After masking: * Hua. Before masking: Wang Xiaoqiang; After masking: ** Qiang.
  	email	email	mask	Masks an email address.	Before masking: lihuang@alibaba.com; After masking: lih***@alibaba.com.
  	ip	ip	mask	Masks an IP address.	Before masking: 1.2.3.4; After masking: 1.*.*.*.
  	id	id	mask	Masks an ID card number.	Before masking: 110345188812011234; After masking: 1****************4.
  	phone	phone	mask	Masks a phone number.	Before masking: 13900001234; After masking: *******1234.
  	bank_id	bank_id	mask	Masks a bank or credit card number.	Before masking: 2349867902834701928; After masking: ***************1928.
  	hash	md5	hash	Masks data by using the MD5 algorithm.	Before masking: Wenyi West Road, Hangzhou, Zhejiang; After masking: dbf894b409d4a2ef17dfd9c7fdcafcd8.
  	first_mask	first_mask	mask	The first_mask rule displays only the first character.	Before masking: 123456789; After masking: 1********.
  INT, FLOAT, and other numeric types	Default	default_value	default_value	Masks data with a default value. Note If the specified label_name does not exist when you configure data masking, the system uses default value masking as a fallback. Supported only in Hologres V3.1 and later.	Mappings between data types and their default masked values: 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 masking rules

• To view the masking rules set for columns:

  --View the masking rules set for columns
  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;

• To view the masking rules set for users:

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

Customize masking rules

If the existing data masking rules do not meet your requirements, you can create custom rules by modifying the hg_anon_lables GUC parameter.

• Syntax

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

  After you execute the ALTER DATABASE command, the current connection becomes invalid. You must establish a new connection. You can use the following command to check whether 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":""}}]';

  Parameters:

  Item	Description	Example result
  {"desensType":"mask", "type":"ip"}	Masks an IP address.	192.*.*.*
  {"desensType":"mask", "type":"email"}	Masks an email address.	abc***@example.net
  {"desensType":"mask", "type":"name"}	Masks a name.	*Wu
  {"desensType":"hash", "type":"md5", "salt":""}	Masks data by using a hash function.	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}}	Allows for custom masking patterns, such as keeping a specified number of characters at the beginning or end of a string.	N/A

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 column masking rule

--Create the hg_anon extension.
CREATE EXTENSION IF NOT EXISTS hg_anon;
--Enable data masking 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';

In a new connection, run 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)

Remove column masking

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;

Run the following command to view the result after removing the masking rules for the columns.

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)

Remove user masking

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

Run the following command to view the result after removing the masking rule for the 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 on 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)