polar_sql_inception (SQL Audit)-PolarDB(PolarDB)-阿里云帮助中心

In team collaboration or automated release processes, inconsistent SQL standards and unaudited change operations can cause data errors, performance bottlenecks, or even failures in the production environment. The polar_sql_inception plugin addresses this pain point by providing an automated SQL audit engine that integrates auditing and execution. You can customize rules to automatically check SQL during development, testing, and release phases, enforce coding standards, and block high-risk operations—ensuring the quality and security of database changes.

Applicable Scope

The supported version of PolarDB for PostgreSQL (Compatible with Oracle) is Oracle syntax compatible 2.0, with a minor engine version of 2.0.14.19.38.0 or later.

Note

You can check the minor engine version number in the console View the minor engine version number, or use the SHOW polardb_version; statement. If your minor engine version does not meet the requirement, upgrade the minor engine version Upgrade the minor engine version.

Quick Start

This section uses a simple example to demonstrate a core feature of polar_sql_inception: how to block a non-compliant `CREATE TABLE` statement in under three minutes.

Install the polar_sql_inception plugin by running the following command in your database.
```
CREATE EXTENSION polar_sql_inception;
```
Set an audit rule to require a primary key for all new tables. Run the SET command to enable this rule in the current session.
```
SET polar_sql_inception.table_rule_check_primary_key = ON;
```
Call the audit function to check a CREATE TABLE statement that does not include a primary key. Run the following query and set the execute parameter to FALSE to perform audit only, without execution.
```
SELECT * FROM polar_sql_inception(
    sql_statements := 'CREATE TABLE users (id INT, name TEXT);',
    execute := FALSE
);
```

Expected result: The audit fails and returns a clear error message. The error_level field is error, and the error_message field clearly states the violation: set a primary key. This confirms that the plugin successfully blocked the non-compliant SQL.

 sql_id |             sql_statement              |  stage  | error_level |   error_message   | affected_rows 
--------+----------------------------------------+---------+-------------+-------------------+---------------
      1 | CREATE TABLE users (id INT, name TEXT) | checked | warning     | set a primary key+|             0
        |                                        |         |             |                   |

How it works

Configuration Modes

Choose the configuration method that best fits your needs.

Use the SET command to configure parameters for the current database. This method is flexible and convenient, requires no cluster restart, and applies only to the current application scope.

Session-level configuration:

-- Only effective in the current session
SET polar_sql_inception.dml_rule_check_dml_where = ON;

User level:

-- Only effective for the current user
ALTER username SET polar_sql_inception.dml_rule_check_dml_where = ON;

Database-level configuration:

-- Only effective in the current database
ALTER databasename SET polar_sql_inception.dml_rule_check_dml_where = ON;

Execution Model

When you call the polar_sql_inception function and pass multiple SQL statements, it follows this processing flow:

Serial processing: The plugin processes SQL statements one by one in order. The flow is: audit statement A → (if passed and execute is TRUE) execute statement A → audit statement B → (if passed and execute is TRUE) execute statement B → …
Fault interruption: During auditing or execution, if a statement triggers an error-level fault, or triggers a warning-level fault and warnings are not configured to be ignored (ignore_warning_when_executing = OFF), the execution flow for all subsequent statements is interrupted. However, the plugin continues to audit all remaining statements and returns the complete audit results.

Execute SQL Audit and Changes

This section describes how to use the polar_sql_inception function to audit SQL statements and execute changes as needed.

Function Definition

polar_sql_inception(
    sql_statements TEXT,
    execute BOOLEAN DEFAULT FALSE,
    schema TEXT DEFAULT NULL
)

Parameter description

Parameter	Description	Applicable Scope
`sql_statements`	One or more SQL statements to audit.	Same as the applicable scope of the feature.
`execute`	Execute SQL statements after a successful audit. The default is `FALSE`, which means only audit, not execute.	Cluster version: Oracle syntax compatible 2.0, with a minor engine version of 2.0.14.19.40.0 or later. Supported SQL statements for execution: `INSERT`, `UPDATE`, `DELETE` `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, `TRUNCATE TABLE` `CREATE INDEX`, `ALTER INDEX`, `DROP INDEX` `CREATE VIEW`, `DROP VIEW` `COMMENT`
`schema`	Specify the default schema that SQL statements rely on for execution. Note This parameter is equivalent to internally and temporarily running `SET search_path TO 'your_schema'` during this audit execution. It automatically reverts after execution and does not affect your current session's `search_path` setting. If `NULL` (default value), it relies on the current session's `search_path`.	Oracle syntax compatible 2.0, with a minor engine version of 2.0.14.20.41.0 or later.

Operation Examples

Scenario 1: Audit Multiple SQL Statements Only

Check whether two CREATE TABLE statements comply with the rule requiring a primary key, without actually creating the tables.

-- Enable the "table must have a primary key" rule
SET polar_sql_inception.table_rule_check_primary_key = ON;

-- Call the function to audit
SELECT * FROM polar_sql_inception(
    sql_statements := $$
        -- Error example: no primary key
        CREATE TABLE t1 (id INT);

        -- Correct example: with primary key
        CREATE TABLE t2 (id INT PRIMARY KEY);
    $$,
    execute := FALSE
);

Returned result: The first SQL statement reports an error because it violates the rule. The second SQL statement passes the audit.

 sql_id |                sql_statement                 |  stage  | error_level |    error_message    | affected_rows 
--------+----------------------------------------------+---------+-------------+---------------------+---------------
      1 |                                             +| checked | warning     | set a primary key  +|             0
        |         -- Error example: no primary key     +|         |             |                     | 
        |         CREATE TABLE t1 (id INT)             |         |             |                     | 
      2 |                                             +| checked | success     | no violations found |             0
        |                                             +|         |             |                     | 
        |         -- Correct example: with primary key |         |             |                     | 
        |         CREATE TABLE t2 (id INT PRIMARY KEY) |         |             |                     |

Scenario 2: Audit and Execute After Passing

Audit an UPDATE statement to verify compliance with the rule requiring a WHERE clause for DML statements. If it complies, execute it.

-- Prepare test data
CREATE TABLE products (id INT, stock INT);
INSERT INTO products VALUES (1, 100);

-- Enable the "DML must have WHERE" rule
SET polar_sql_inception.dml_rule_check_dml_where = ON;

-- Call the function to audit and execute
SELECT * FROM polar_sql_inception(
    sql_statements := 'UPDATE products SET stock = 99 WHERE id = 1;',
    execute := TRUE
);

Returned result: The SQL statement passes the audit and executes successfully (stage: executed and error_level: success). The affected_rows field shows that one row was updated.

 sql_id |                sql_statement                |  stage   | error_level |    error_message    | affected_rows 
--------+---------------------------------------------+----------+-------------+---------------------+---------------
      1 | UPDATE products SET stock = 99 WHERE id = 1 | executed | success     | no violations found |             1

Result set details

The polar_sql_inception function returns a result set. Each row corresponds to the audit and execution result of one SQL statement you provided.

Field	Description
`sql_id`	The ordinal number of the SQL statement, starting from 1.
`sql_statement`	The original text of the SQL statement being processed.
`stage`	The current processing stage of the SQL statement: `none`: No processing occurred, usually because parsing stopped early due to a syntax error in a previous statement. `checked`: Audit completed, but not executed. executed: Executed. This status applies regardless of whether execution succeeded or failed.
`error_level`	The severity level of the result: `checked` stage: `success`: Audit passed. `warning`: Violated a configured rule, but execution is unaffected. `error`: Violated a default rule, which affects execution. `executed` stage: `success`: Execution succeeded. `warning`: Rarely occurs, unless you set the runtime parameter `ignore_warning_when_executing` to ignore `warning` errors from the `checked` stage and proceed with execution, which then succeeds. `error`: Execution failed.
`error_message`	Detailed audit feedback or error message. If multiple violations exist, they are separated by line feeds. When the audit passes, it returns `no violations found`.
`affected_rows`	The number of affected rows. `checked` stage: Returns the optimizer's estimated number of affected rows by default. If `get_real_affected_rows` is enabled, `UPDATE`, `DELETE`, and `INSERT` statements are converted into corresponding `SELECT` statements and executed to obtain the actual number of affected rows. If this fails, an `ERROR`-level fault is raised. `executed` stage: Returns the actual number of rows affected after SQL execution.

Configuration templates

To help you configure rule parameters in batch, PolarDB provides the following configuration templates. Select and apply the rules that match your business needs.

-- Runtime configuration
SET polar_sql_inception.get_real_affected_rows = OFF;
SET polar_sql_inception.enable_utility_parse_analysis = ON;
SET polar_sql_inception.ignore_warning_when_executing = OFF;

-- Table-related rules
SET polar_sql_inception.table_rule_enable_partition = ON;
SET polar_sql_inception.table_rule_check_primary_key = OFF;
SET polar_sql_inception.table_rule_enable_foreign_key = ON;
SET polar_sql_inception.table_rule_merge_alter_table = OFF;
SET polar_sql_inception.table_rule_must_have_columns = 'column1,column2,column3';

-- Column-related rules
SET polar_sql_inception.column_rule_max_char_length = 0;
SET polar_sql_inception.column_rule_enable_text_type = ON;
SET polar_sql_inception.column_rule_enable_json_type = ON;
SET polar_sql_inception.column_rule_check_not_null = OFF;
SET polar_sql_inception.column_rule_enable_timestamp_type = ON;
SET polar_sql_inception.column_rule_check_timestamp_default = OFF;
SET polar_sql_inception.column_rule_check_timestamp_count = OFF;
SET polar_sql_inception.column_rule_check_default_value = OFF;

-- Index-related rules
SET polar_sql_inception.index_rule_enable_null_index_name = ON;
SET polar_sql_inception.index_rule_max_key_parts = 0;
SET polar_sql_inception.index_rule_max_primary_key_parts = 0;
SET polar_sql_inception.index_rule_check_pk_columns_only_int = OFF;
SET polar_sql_inception.index_rule_max_keys = 0;

-- Naming-related rules
SET polar_sql_inception.naming_rule_check_char = OFF;
SET polar_sql_inception.naming_rule_check_keyword = OFF;

-- DML-related rules
SET polar_sql_inception.dml_rule_check_insert_field = OFF;
SET polar_sql_inception.dml_rule_check_dml_where = OFF;
SET polar_sql_inception.dml_rule_enable_select_star = ON;
SET polar_sql_inception.dml_rule_enable_orderby_rand = ON;
SET polar_sql_inception.dml_rule_max_update_rows = 0;
SET polar_sql_inception.dml_rule_max_insert_rows = 0;
SET polar_sql_inception.dml_rule_max_delete_rows = 0;

-- Other rules
SET polar_sql_inception.check_schema_consistency = OFF;

Configuration reference

Runtime configuration

Parameter	Default value	Description
`polar_sql_inception.get_real_affected_rows`	`FALSE`	During the `checked` phase, when `polar_sql_inception.get_real_affected_rows` is enabled, `UPDATE`, `DELETE`, and `INSERT` statements are converted into equivalent `SELECT` statements and executed to obtain the actual number of affected rows instead of the optimizer's estimate.
`polar_sql_inception.enable_utility_parse_analysis`	`TRUE`	During the `checked` phase, check for semantic errors in SQL statements. Supported statements include DML, DDL, `CREATE TABLE`, `ALTER TABLE`, `CREATE INDEX`, `ALTER INDEX`, and `COMMENT`.
`polar_sql_inception.ignore_warning_when_executing`	`FALSE`	During the `executed` phase, ignore `warning` errors from the `check` phase and proceed with execution. Note Also supported: Oracle syntax compatibility 2.0, with minor engine version 2.0.14.19.40.0 or later.

Rule configuration

Table-related rules

Rule name	Parameter	Default value	Trigger scenario
Tables must have a primary key	`polar_sql_inception.table_rule_check_primary_key`	`FALSE`	`CREATE TABLE` without a primary key. `ALTER TABLE` drops a PRIMARY KEY constraint. `ALTER TABLE` drops a primary key column.
Partitioned tables are not allowed	`polar_sql_inception.table_rule_enable_partition`	`TRUE`	`CREATE TABLE` creates a partitioned table.
Foreign keys are not allowed	`polar_sql_inception.table_rule_enable_foreign_key`	`TRUE`	`CREATE TABLE` includes a foreign key constraint. `ALTER TABLE` adds a foreign key constraint.
Tables must contain specified columns	`polar_sql_inception.table_rule_must_have_columns`	`""` (no check)	`CREATE TABLE` must include certain columns. `ALTER TABLE` drops these columns.
Merge multiple ALTER TABLE statements	`polar_sql_inception.table_rule_merge_alter_table`	`FALSE`	Multiple consecutive `ALTER TABLE` statements on the same table.
Table does not exist	Default rule	-	`CREATE TABLE` references a non-existent table.
Only one primary key is allowed	Default rule	-	`CREATE TABLE` defines multiple primary keys. `ALTER TABLE` creates a new primary key index when one already exists, or creates two or more primary key indexes.
Table referenced by LIKE does not exist	Default rule	-	`CREATE TABLE LIKE` references a non-existent table.
User must have permission	Default rule	-	`CREATE TABLE` lacks permission on the target schema. `CREATE TABLE LIKE` lacks permission on the referenced table. `ALTER TABLE` lacks permission on the table.

Column-related rules

Rule name	Parameter	Default value	Trigger scenario
CHAR length limit	`polar_sql_inception.column_rule_max_char_length`	`0` (no check)	`CREATE TABLE` defines a column as `CHAR` type. `ALTER TABLE` changes a column to `CHAR` type. `ALTER TABLE` adds a column as `CHAR` type.
TEXT type is not allowed	`polar_sql_inception.column_rule_enable_text_type`	`TRUE`	`CREATE TABLE` defines a column as `TEXT` type. `ALTER TABLE` changes a column to `TEXT` type. `ALTER TABLE` adds a column as `TEXT` type.
JSON type is not allowed	`polar_sql_inception.column_rule_enable_json_type`	`TRUE`	`CREATE TABLE` defines a column as `JSON` type. `ALTER TABLE` changes a column to `JSON` type. `ALTER TABLE` adds a column as `JSON` type.
Columns must have a NOT NULL constraint	`polar_sql_inception.column_rule_check_not_null`	`FALSE`	`CREATE TABLE` defines a column as `NOT NULL`. `ALTER TABLE` changes a column to `NOT NULL`. `ALTER TABLE` adds a column as `NOT NULL`.
TIMESTAMP type is not allowed	`polar_sql_inception.column_rule_enable_timestamp_type`	`TRUE`	`CREATE TABLE` defines a column as `TIMESTAMP` type. `ALTER TABLE` changes a column to `TIMESTAMP` type. `ALTER TABLE` adds a column as `TIMESTAMP` type.
TIMESTAMP columns must have a default value	`polar_sql_inception.column_rule_check_timestamp_default`	`FALSE`	`CREATE TABLE` defines a `TIMESTAMP` column without a default value. `ALTER TABLE` changes a `TIMESTAMP` column without a default value. `ALTER TABLE` adds a `TIMESTAMP` column without a default value.
Only one `TIMESTAMP` column can use `CURRENT_TIMESTAMP` in its `DEFAULT` clause	`polar_sql_inception.column_rule_check_timestamp_count`	`FALSE`	`CREATE TABLE` defines two `TIMESTAMP` columns with `CURRENT_TIMESTAMP` as the default value.
All columns must define a default value, except for `TIMESTAMP`, auto-increment columns, primary key columns, `JSON`, computed columns, and `BYTEA` columns	`polar_sql_inception.column_rule_check_default_value`	`FALSE`	`CREATE TABLE` defines a column without a default value. `ALTER TABLE` adds a column without a default value. `ALTER TABLE` removes a column's default value.
Duplicate column names are not allowed	Default rule	-	`CREATE TABLE` defines duplicate column names. `ALTER TABLE` renames a column to a name that already exists. `ALTER TABLE` adds a column with a name that already exists.

Index-related rules

Rule name	Parameter	Default value	Trigger scenario
Indexes must have a name	`polar_sql_inception.index_rule_enable_null_index_name`	`TRUE`	`CREATE INDEX` without an index name.
Maximum number of columns in a regular index	`polar_sql_inception.index_rule_max_key_parts`	`0`	`CREATE INDEX` defines a regular index with more columns than `max_key_parts`.
Maximum number of columns in a primary key index	`polar_sql_inception.index_rule_max_primary_key_parts`	`0`	`CREATE TABLE` defines a primary key index with more columns than the parameter allows. `ALTER TABLE` creates a primary key index with more columns than the parameter allows.
Primary key columns must be integer type	`polar_sql_inception.index_rule_check_pk_columns_only_int`	`FALSE`	`CREATE TABLE` defines a primary key index with non-`INT` columns. `ALTER TABLE` creates a primary key index with non-`INT` columns.
Maximum number of indexes per single table	`polar_sql_inception.index_rule_max_keys`	`0`	`CREATE TABLE` defines more indexes than `index_rule_max_keys`. `CREATE INDEX` fails because the number of existing indexes has reached the `index_rule_max_keys` limit. `ALTER TABLE` creates a primary key or UNIQUE index when the existing number of indexes has reached `index_rule_max_keys`.
Columns specified when creating an index must exist	Default rule	-	`CREATE INDEX` specifies a non-existent column. `CREATE TABLE` specifies a non-existent column for a primary key or UNIQUE constraint. `ALTER TABLE` creates a primary key index using a non-existent column.
You can create an index only for a column that already exists.	Default rule	-	`CREATE INDEX` specifies duplicate columns. `CREATE TABLE` specifies duplicate columns for a primary key or UNIQUE constraint. `ALTER TABLE` The column specified for the (primary key index) contains duplicate values.
Index names must not be duplicated	Default rule	-	`CREATE INDEX` uses an index name that already exists. `ALTER INDEX` renames an index to a name that already exists. `ALTER TABLE` creates a primary key index with a name that already exists.
User must have permission	Default rule	-	`CREATE INDEX` lacks permission on the table.

Naming rules

Rule name	Parameter	Default value	Trigger scenario
Check name character set	`polar_sql_inception.naming_rule_check_char`	`FALSE`	`CREATE TABLE` uses characters outside [a-zA-Z0-9] in table or column names. `CREATE INDEX` uses characters outside [a-zA-Z0-9] in index names. `CREATE TABLE` uses characters outside [a-zA-Z0-9] in table-level primary key or UNIQUE index names. `ALTER TABLE` renames a table using characters outside [a-zA-Z0-9]. `ALTER TABLE` adds a column using characters outside [a-zA-Z0-9]. `ALTER TABLE` adds a primary key index using characters outside [a-zA-Z0-9]. `ALTER INDEX` renames an index using characters outside [a-zA-Z0-9_].
Check for keywords	`polar_sql_inception.naming_rule_check_keyword`	`FALSE`	`CREATE TABLE` uses a keyword as a table or column name. `CREATE INDEX` uses a keyword as an index name. `CREATE TABLE` uses a keyword as a table-level primary key or UNIQUE index name. `ALTER TABLE` renames a table using a keyword. `ALTER TABLE` adds a column using a keyword. `ALTER TABLE` adds a primary key index using a keyword. `ALTER INDEX` renames an index using a keyword.

DML rules

Rule name	Parameter	Default value	Trigger scenario
Insert list must be specified	`polar_sql_inception.dml_rule_check_insert_field`	`FALSE`	`INSERT/INSERT SELECT` without an insert list.
DML statements must include a WHERE clause	`polar_sql_inception.dml_rule_check_dml_where`	`FALSE`	`UPDATE/DELETE/SELECT/INSERT SELECT` without a `WHERE` clause.
`SELECT *` is not allowed	`polar_sql_inception.dml_rule_enable_select_star`	`TRUE`	`SELECT/INSERT SELECT` uses `SELECT *`.
`ORDER BY RAND()` is not allowed	`polar_sql_inception.dml_rule_enable_orderby_rand`	`TRUE`	`SELECT/INSERT SELECT` uses `ORDER BY RANDOM`.
Limit number of rows updated	`polar_sql_inception.dml_rule_max_update_rows`	`0`	`UPDATE` affects more estimated rows than `dml_rule_max_update_rows`.
Limit number of rows inserted	`polar_sql_inception.dml_rule_max_insert_rows`	`0`	`INSERT` inserts more estimated rows than `dml_rule_max_insert_rows`.
Limit number of rows deleted	`polar_sql_inception.dml_rule_max_delete_rows`	`0`	`DELETE` affects more estimated rows than `dml_rule_max_delete_rows`.
Tables and columns must exist	Default rule	-	DML statement references a non-existent table or column.
User must have permission	Default rule	-	DML statement lacks permission on the table.

Other rules

Rule name	Parameter	Default value	Trigger scenario
Check schema consistency	`polar_sql_inception.check_schema_consistency`	`FALSE`	Triggered when the schema explicitly specified in a DML or DDL statement differs from the schema parameter specified in the polar_sql_inception function. Note Also supported: Oracle syntax compatibility 2.0, with minor engine version 2.0.14.20.41.0 or later.