Here is an example of the concept of confidentiality levels and confidentiality groups.

This section assumes a simple business that handles customer purchase information.

First, create a confidentiality matrix for confidentiality management of this information.

SELECT pgx_create_confidential_matrix('matrix_purchase_managenet' , 'Confidentiality management of customer purchase information');

The data we process may also contain personally identifiable information. Access to such data should be restricted to those who have access to it to minimize the risk of information disclosure.

Customer purchase information, including personally identifiable information, is managed in the following table.

CREATE TABLE purchase.customer_info(     -- Customer information
    customer_id     integer,               -- Customer ID
    name            text,
    address         text,
    phone_number    char(12),
    rank            integer                -- Customer's service rank
);

CREATE TABLE purchase.history(           -- History of a customer's purchase of goods
    customer_id         integer,            -- ID of the customer who purchased
    purchase_date       date,               -- Date the goods were purchased
    item_code           char(12),           -- Code of the goods purchased 
    purchase_number     integer,            -- Quantity purchased 
    purchase_amount     integer             -- Amount of purchased goods 
);

Among customer information, name, address, and telephone number are personal information because they can identify an individual when combined. In order to properly handle such information, we have made it so that it can only be handled by employees belonging to a specific group who have received appropriate training.

Therefore, we have prepared two confidentiality levels: "level_personal_info", which means highly confidential personal information, and "level_customer_info", which means other information.

SELECT pgx_create_confidential_level('matrix_purchasee_management', 'level_personal_info',
                                       NULL, 'Personally identifiable information');
SELECT pgx_create_confidential_level('matrix_purchase_management', 'level_customer_info',
                                       NULL, 'Non-personally identifiable information');

In addition, we will prepare two confidentiality groups: "group_qualified " who have been educated about handling personal information and can handle personal information appropriately, and "group_non_qulified" who are not qualified.

SELECT pgx_create_confidential_group ('matrix_purchase_management', 'group_qualified',
                                       NULL, 'Qualified staff handling personal information');
SELECT pgx_create_confidential_group ('matrix_purchase_management', 'group_non_qulified',
                                       NULL, 'General employee');

Let's take a closer look at the data we're dealing with.

Since the customer information table contains personal information, it corresponds to personal information. However, the customer_id and rank contained in the customer information table are not personal information because they are not personally identifiable information. In addition, since this customer_id and rank are also information necessary for business analysis, it is inconvenient that only those who are qualified to handle personal information can access such information.

Therefore, the customer information table uses columns for confidentiality management. The entire customer information table is protected as personal information, and the range of access is expanded by making the columns that are not personal information general customer information.

Follow this policy to set confidentiality level privilege for confidentiality group.

SELECT pgx_grant_confidential_privilege('matrix_purchase_management',
                                       'level_personal_info',
                                       'group_qualified', '{"table":["ALL"]}');
SELECT pgx_grant_confidential_privilege('matrix_purchase_management',
                                       'level_customer_info',
                                       'group_qualified', '{"table":["ALL"]}');
SELECT pgx_grant_confidential_privilege('matrix_purchase_management',
                                       'level_customer_info',
                                       'group_not_qualified',
                                       '{"table":["ALL"], "column":["SELECT"]}');

Only "qualified personnel" can handle "personal information". "Customer information" can be handled by both "qualified personnel" and "general employees". Some columns of tables that handle "personal information" are allowed to be referred to as "customer information".

This completes the authorization settings in the confidentiality matrix.

Next, we will register the database objects that handle purchase information in the confidentiality matrix.

SELECT pgx_add_object_to_confidential_level('matrix_purchase_management', 'level_personal_info',
                                  '[{
                                       "type":"table",
                                       "object":[{
                                                     "schema": "purchase",
                                                     "table": ["customer_info"]
                                        }]
                                    }]');
SELECT pgx_add_object_to_confidential_level('matrix_purchase_management', 'level_customer_info',
                                  '[{
                                       "type":"column",
                                       "object":[{
                                                      "schema": "purchase",
                                                      "table": "customer_info",
                                                      "column": ["customer_id", "rank"]
                                        }]
                                    }]');
SELECT pgx_add_object_to_confidential_level('matrix_purchase_management', 'level_customer_info',
                                  '[{
                                       "type":"table",
                                       "object":[{
                                                      "schema": "purchase",
                                                      "table": ["history"]
                                    }]
                                    }]');

The entire customer information table is "personal information", the customer_id column and rank column of the customer information table are "customer information", and the entire purchase history table is also "customer information".

Finally, enroll the employee in the confidentiality group. "Alex" and "Bola" are "qualified persons" who have received training in personal information management. Also, "Charlie" and "Dana" are "general employees" because they have not yet received training on personal information management.

SELECT pgx_add_role_to_confidential_group('matrix_purchase_management',
                                         'group_qualified',
                                         '["Alex", "Bola"]');
SELECT pgx_add_role_to_confidential_group('matrix_purchase_management',
                                         'group_non_qualified',
                                         '["Charlie", "Dana"]');