CREATE AUDIT POLICY

Description

Creates a unified audit policy. The current version does not support this syntax.

Precautions

• Only the user with the POLADMIN or SYSADMIN permission, or initial user has the permission to create and maintain audit policies.

• Before creating an audit policy, the GUC parameter enable_security_policy must be set to on to make the masking policy takes effect.
• A SYSADMIN or POLADMIN can access the GS_AUDITING_POLICY, GS_AUDITING_POLICY_ACCESS, GS_AUDITING_POLICY_PRIVILEGES, and GS_AUDITING_POLICY_FILTERS system catalogs to query the created audit policies.
• The audit policy name must be unique to avoid conflicts with existing policies. You can use IF NOT EXISTS to check whether the specified audit policy exists to avoid repeated creation.

Syntax

CREATE AUDIT POLICY [ IF NOT EXISTS ] policy_name { { privilege_audit_clause | access_audit_clause } [, ... ] [ filter_group_clause ] [ ENABLE | DISABLE ] };

• privilege_audit_clause

  1	PRIVILEGES { DDL | ALL } [ ON LABEL ( resource_label_name [, ... ] ) ]

• access_audit_clause

  ACCESS { DML | ALL } [ ON LABEL ( resource_label_name [, ... ] ) ]

Parameters

• policy_name

  Specifies the audit policy name, which must be unique.

  Value range: a string of no more than 63 characters. It must comply with the naming conventions. If the value contains more than 63 characters, the database truncates it and retains the first 63 characters as the audit policy name. If an audit policy name contains uppercase letters, the database automatically converts the uppercase letters into lowercase letters. To create an audit policy name that contains uppercase letters, enclose the audit policy name with double quotation marks ("").

  

  The identifier must be lowercase letters, uppercase letters, underscores (_), digits (0–9), or dollar signs ($) and must start with a letter or underscore (_).

• resource_label_name

  Specifies the resource label name.

• DDL

  Specifies the operations that are audited in the database: CREATE, ALTER, DROP, ANALYZE, COMMENT, GRANT, REVOKE, SET, and SHOW.

  If this parameter is set to ANALYZE, both ANALYZE and VACUUM operations are audited.

• DML

  Specifies the operations that are audited within the database: SELECT, COPY, DEALLOCATE, DELETE, EXECUTE, INSERT, PREPARE, REINDEX, TRUNCATE, and UPDATE.

• ALL

  Specifies all operations supported by the specified DDL or DML statements in the database. When the form is { DDL | ALL }, ALL indicates all DDL operations. When the form is { DML | ALL }, ALL indicates all DML operations.

• FILTER_TYPE

  Specifies the types of information to be filtered by the policy, including APP, ROLES, and IP.

• filter_value

  Specifies the detailed information to be filtered.

• ENABLE|DISABLE

  Enables or disables the unified audit policy. If ENABLE|DISABLE is not specified, ENABLE is used by default.

Examples

• Creates audit policy for executing CREATE on the database.

  -- Create the adt1 policy.
  m_db=# CREATE AUDIT POLICY adt1 PRIVILEGES CREATE; 
  
  -- View the adt1 policy.
  m_db=# SELECT * FROM GS_AUDITING_POLICY;
   polname | polcomments |         modifydate         | polenabled 
  ---------+-------------+----------------------------+------------
   adt1    |             | 2023-11-06 16:41:40.947417 | t
  
  -- Check the location where the audit policy is stored.
  m_db=# SHOW audit_directory;
  
  -- Delete the audit policy adt1.
  m_db=# DROP AUDIT POLICY adt1;

• Create an audit policy to audit only the CREATE operation performed by the dev_audit user.

  -- Create user dev_audit.
  m_db=# CREATE USER dev_audit PASSWORD '********';
  
  -- Create the tb_for_audit table.
  m_db=# CREATE TABLE tb_for_audit(col1 text, col2 text, col3 text); 
  
  -- Create the adt_lb0 resource label based on the tb_for_audit table.
  m_db=# CREATE RESOURCE LABEL adt_lb0 add TABLE(public.tb_for_audit);
  
  -- Create the adt2 audit policy for the CREATE operation on the adt_lb0 resource.
  m_db=# CREATE AUDIT POLICY adt2 PRIVILEGES CREATE ON LABEL(adt_lb0) FILTER ON ROLES(dev_audit);
  
  -- Delete the audit policy adt2.
  m_db=# DROP AUDIT POLICY adt2;
  
  -- Delete the tb_for_audit table.
  m_db=# DROP TABLE tb_for_audit;
  
  -- Delete the dev_audit user.
  m_db=# DROP USER dev_audit;

• Create an audit policy to audit only the SELECT, INSERT, and DELETE operations performed on the adt_lb0 resource by user dev_audit using client tool gsql on the servers whose IP addresses are 10.20.30.40 and 127.0.0.0/24.

  -- Create user dev_audit.
  m_db=# CREATE USER dev_audit PASSWORD '********';
  
  -- Create the audit policy adt3.
  m_db=# CREATE AUDIT POLICY adt3 ACCESS SELECT ON LABEL(adt_lb0), INSERT ON LABEL(adt_lb0), DELETE FILTER ON ROLES(dev_audit), APP(gsql), IP('10.20.30.40', '127.0.0.0/24');
  
  -- Delete the audit policy adt3.
  m_db=# DROP AUDIT POLICY adt3;
  
  -- Delete the dev_audit user.
  m_db=# DROP USER dev_audit;

Helpful Links

ALTER AUDIT POLICY and DROP AUDIT POLICY