pgx_add_object_to_confidential_level(confidential_matrix_name varchar, confidential_level_name varchar, object_name json)

void

Only confidentiality management role for the specified confidentiality matrix can execute this function.

Adds the confidentiality object specified by object_name to the confidentiality level specified by confidential_level_name.

This function internally uses the GRANT statement to grant privileges to the confidentiality group role according to the confidentiality privileges associated with this confidentiality level.

However, if the confidentiality object is of type rowset, it internally uses the CREATE POLICY statement to grant privileges to the confidentiality group role according to the confidentiality privileges associated with this confidentiality level. Also, to enable POLICY, execute the ALTER TABLE statement on the target table with the ENABLE ROW LEVEL SECURITY clause.

Currently it is not possible to register a foreign table as a table type confidentiality object.

Be carefull when PUBLIC is granted to target confidentiality object. This is because granting privileges to PUBLIC is the same as granting privileges to all roles registered in the confidentiality matrix. This function will fail if a privilege granted indirectly to each role using PUBLIC is defined in the confidentiality privileges that should not be granted to that role. Similarly, this function also checks privileges granted indirectly through group roles that are not registered in the confidentiality matrix. In doing so, it recursively checks the chain of inheritance.

For rowset type confidentiality objects, this function will fail if the target table has a POLICY defined that was not created using this feature, regardless of what privileges are granted.

These checks only apply to the confidentiality group role or to roles that are registered with the confidentiality group. If a POLICY exists that targets a role that is not, the function will not fail.

Specify object_name as follows: Only the rowset is slightly different. The example below attempts to register multiple types of objects in one go.

'[{
  "type":"schema",
  "object":[
    {"schema":"schema1"},
    {"schema":"schema2"}
  ]
},

{
  "type":"table",
  "object":[
    {
      "schema":"schema1",
      "table":["table1","table2"]
    },
    {
      "schema":"schema2",
      "table":["table8","table9"]
    }
  ]
},

{
  "type":"column",
  "object":[
    {
      "schema":"schema1",
      "table":"table1",
      "column":["column1", "column2"]
    },
    {
      "schema":"schema1",
      "table":"table2",
      "column":["column8", "column9"]
    }
  ]
}]'

For the rowset type, you define rowset and give it a name, as in the example below. This name is used by the pgx_remove_object_from_confidential_level function to identify the rowset type confidentiality object when removing it.

This example shows:

• In schema1.table1, represents a set of rows (rowset) for which the conditional expression (user = current_user OR manger = current_user) is true.

• If manager=current_user is true, the col1 value of the record to be INSERTed or the record after UPDATE must be greater than zero.

  '[{
    "type":"rowset",
    "object":[
      {
        "schema":"schema1",
        "table":"table1",
        "rowset_name":"rowset1",
        "rowset_expression": [
          {
            "as":"permissive",
            "using":"user = current_user"
          },
          {
            "as":"permissive",
            "using":"manager = current_user",
            "with check":"col1 > 0"
          }
        ]
      }
    ]
  }]'

Each key (as, using, with check) has the same meaning as the clause of the same name in the CREATE POLICY statement.

As you can see from this, one element of the array specified in rowset_expression corresponds to one POLICY object created by the CREATE POLICY statement.

In fact, this function internally executes as many CREATE POLICY statements as there are elements in the array. The name of POLICY at this time is 'pgx_cms_policy_${cpolid}'.

${cpolid} is automatically numbered by this extension.

<Note>

Do not create policies with names that begin with pgx_cms_policy_. Because this function may fail.

pgx_remove_object_from_confidential_level(confidential_matrix_name varchar, confidential_level_name varchar, object_name json)

void

Removes confidentiality objects from the confidentiality level.

Only confidentiality management role for the specified confidentiality matrix can execute this function.

Removes the confidentiality object specified by object_name from the confidentiality level specified by confidential_level_name.

The format of object_name is the same as object_name in the pgx_add_object_to_confidential_level function. But the rowset_expression key is optional. If specified, it is simply ignored. Therefore, object_name in pgx_add_object_to_confidential_level can be specified to this function without modifying it.

At this time, any privileges granted to the confidentiality group based on confidentiality privileges are revoked.

If the confidentiality object is of rowset type, delete the internally created policy.

At this time, if there are zero policies associated with the table, the ALTER TABLE statement with the DISABLE ROW SECURITY clause is internally executed to disable row level security.