Enforce SQL Audit & Block High-Risk DDL with polar_sql_inception - PolarDB - Alibaba Cloud - PolarDB

The polar_sql_inception plugin is an automated SQL audit engine for PolarDB for PostgreSQL. It checks SQL statements against configurable rules before execution — catching missing primary keys, unguarded UPDATE statements, forbidden data types, and more — so schema changes and DML operations are validated before they reach production.

Supported versions

PostgreSQL version	Minimum minor engine version
PostgreSQL 16	2.0.16.9.9.0 and later
PostgreSQL 15	2.0.15.15.7.0 and later
PostgreSQL 14	2.0.14.19.38.0 and later

To check your current minor engine version, run SHOW polardb_version; in the database, or view it in the console. If your version does not meet the requirement, upgrade the minor engine version before installing the plugin.

What the plugin checks

polar_sql_inception covers five categories of rules. Most configurable rules are disabled by default so you can enable only what your team needs. Built-in default rules are always enforced with no configuration required.

Category	Example rules
Table rules	Primary key required, no foreign keys, no partitioned tables, required columns
Column rules	CHAR length limit, no TEXT/JSON/TIMESTAMP types, NOT NULL required, default value required
Index rules	Index name required, max columns per index, max indexes per table, integer-only primary keys
Naming rules	Allowed character set for names, no SQL keywords as identifiers
DML rules	WHERE clause required, no `SELECT *`, row count limits for UPDATE/DELETE/INSERT

For the full list of parameters and trigger conditions, see Configuration reference.

Quick start

This example blocks a non-compliant CREATE TABLE statement in three steps.

Install the plugin.
```
CREATE EXTENSION polar_sql_inception;
```
Enable the "tables must have a primary key" rule for the current session.
```
SET polar_sql_inception.table_rule_check_primary_key = ON;
```

Audit a CREATE TABLE statement without a primary key. Set execute to FALSE to audit without executing.

SELECT * FROM polar_sql_inception(
    sql_statements := 'CREATE TABLE users (id INT, name TEXT);',
    execute := FALSE
);

The statement fails the audit. The result shows error_level: warning and error_message: set a primary key.

 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

Execution model

When you pass multiple SQL statements to polar_sql_inception, it processes them serially:

Audit statement A → (if passed and execute = TRUE) execute statement A
Audit statement B → (if passed and execute = TRUE) execute statement B
...and so on.

If a statement triggers an error-level result — or a warning-level result when ignore_warning_when_executing is OFF — execution stops for all subsequent statements. The plugin still audits every remaining statement and returns the complete results.

How error levels affect execution

`error_level`	At `checked` stage	At `executed` stage
`success`	Passed all rules	Execution succeeded
`warning`	Violated a configurable rule; execution continues unless `ignore_warning_when_executing = OFF`	Rare; occurs when a prior `warning` was ignored and execution still succeeded
`error`	Violated a built-in default rule; execution blocked	Execution failed

Configuration scope

Apply rules at the session, user, or database level without restarting the cluster.

-- Session level: applies only to the current session
SET polar_sql_inception.dml_rule_check_dml_where = ON;

-- User level: applies to all sessions for this user
ALTER username SET polar_sql_inception.dml_rule_check_dml_where = ON;

-- Database level: applies to all sessions in this database
ALTER databasename SET polar_sql_inception.dml_rule_check_dml_where = ON;

Modify parameters on the parameter settings page in the console for global persistence. Append polar_sql_inception to the shared_preload_libraries parameter. After the cluster restarts, you can directly modify all polar_sql_inception.* parameters on the parameter settings page.
Note
Modifying the shared_preload_libraries parameter triggers a cluster restart, causing service interruption. Perform this operation during off-peak hours or a planned maintenance window.

Audit and execute SQL

Function signature

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

Parameters

Parameter	Description	Version requirement
`sql_statements`	One or more SQL statements to audit.	All supported versions
`execute`	When `TRUE`, executes each statement after it passes the audit. Default is `FALSE` (audit only).	PostgreSQL 16: 2.0.16.10.11.0+<br>PostgreSQL 15: 2.0.15.15.7.0+<br>PostgreSQL 14: 2.0.14.19.40.0+
`schema`	Sets the default schema for SQL resolution, equivalent to temporarily running `SET search_path TO 'your_schema'`. Reverts automatically after the call; does not affect the current session's `search_path`. If `NULL` (default), uses the current session's `search_path`.	PostgreSQL 16: 2.0.16.10.12.0+<br>PostgreSQL 14: 2.0.14.20.41.0+

SQL statements supported for execution (when execute = TRUE):

INSERT, UPDATE, DELETE
CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE
CREATE INDEX, ALTER INDEX, DROP INDEX
CREATE VIEW, DROP VIEW
COMMENT

Example 1: audit multiple statements without executing

Check whether two CREATE TABLE statements satisfy the primary key rule, without creating any tables.

-- Enable the primary key rule
SET polar_sql_inception.table_rule_check_primary_key = ON;

-- Audit only (execute = FALSE)
SELECT * FROM polar_sql_inception(
    sql_statements := $$
        -- Missing primary key
        CREATE TABLE t1 (id INT);

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

Result: t1 fails; t2 passes.

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

Example 2: audit and execute on pass

Audit an UPDATE statement against the WHERE clause rule, then execute it if it passes.

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

-- Enable the WHERE clause rule
SET polar_sql_inception.dml_rule_check_dml_where = ON;

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

Result: the statement passes the audit and executes successfully. affected_rows shows one row 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 fields

Each row in the result corresponds to one input SQL statement.

Field	Description
`sql_id`	Ordinal number of the statement, starting from 1.
`sql_statement`	Original text of the statement.
`stage`	Processing stage: `none` (skipped due to an earlier syntax error), `checked` (audited, not executed), or `executed` (execution attempted).
`error_level`	Severity: `success`, `warning`, or `error`. See How error levels affect execution.
`error_message`	Audit feedback. Returns `no violations found` when the statement passes. Multiple violations are separated by line feeds.
`affected_rows`	At `checked` stage: the optimizer's estimated row count (or actual count if `get_real_affected_rows` is enabled). At `executed` stage: actual rows affected.

Configuration reference

Runtime parameters

Parameter	Default	Description
`polar_sql_inception.get_real_affected_rows`	`FALSE`	During the `checked` stage, converts `UPDATE`, `DELETE`, and `INSERT` into equivalent `SELECT` statements to return the actual row count instead of the optimizer's estimate. If the conversion fails, an `error`-level result is raised.
`polar_sql_inception.enable_utility_parse_analysis`	`TRUE`	During the `checked` stage, validates semantic correctness of DML and DDL statements, including `CREATE TABLE`, `ALTER TABLE`, `CREATE INDEX`, `ALTER INDEX`, and `COMMENT`. Supported from: PostgreSQL 16 (2.0.16.10.10.0+), PostgreSQL 15 (2.0.15.15.7.0+), PostgreSQL 14 (2.0.14.19.38.0+).
`polar_sql_inception.ignore_warning_when_executing`	`FALSE`	During execution, ignores `warning`-level audit results and proceeds. Supported from: PostgreSQL 16 (2.0.16.10.11.11+), PostgreSQL 15 (2.0.15.15.7.0+), PostgreSQL 14 (2.0.14.19.40.0+).

Table rules

Foreign keys and partitioned tables are disabled by default because they are difficult to maintain at scale — foreign key constraints break when data is split across database servers, and partitioned tables introduce operational complexity that many teams prefer to avoid until they have a clear need.

Rule	Parameter	Default	Triggered when
Tables must have a primary key	`polar_sql_inception.table_rule_check_primary_key`	`FALSE`	`CREATE TABLE` omits a primary key; `ALTER TABLE` drops a primary key constraint or 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` omits required columns; `ALTER TABLE` drops those columns.
Merge multiple ALTER TABLE statements	`polar_sql_inception.table_rule_merge_alter_table`	`FALSE`	Multiple consecutive `ALTER TABLE` statements target the same table.

Built-in default rules (always enforced, no parameter):

Table does not exist: triggered when CREATE TABLE references a non-existent table
Table referenced by LIKE must exist
Only one primary key per table
User must have permission on the target schema or table

Column rules

Rule	Parameter	Default	Triggered when
CHAR length limit	`polar_sql_inception.column_rule_max_char_length`	`0` (no check)	A column is defined or changed to `CHAR` type with length exceeding the limit.
TEXT type is not allowed	`polar_sql_inception.column_rule_enable_text_type`	`TRUE`	A column is defined or changed to `TEXT` type.
JSON type is not allowed	`polar_sql_inception.column_rule_enable_json_type`	`TRUE`	A column is defined or changed to `JSON` type.
Columns must have a NOT NULL constraint	`polar_sql_inception.column_rule_check_not_null`	`FALSE`	A column is defined without `NOT NULL`.
TIMESTAMP type is not allowed	`polar_sql_inception.column_rule_enable_timestamp_type`	`TRUE`	A column is defined or changed to `TIMESTAMP` type.
TIMESTAMP columns must have a default value	`polar_sql_inception.column_rule_check_timestamp_default`	`FALSE`	A `TIMESTAMP` column is defined without a default value.
Only one TIMESTAMP column can use `CURRENT_TIMESTAMP` as default	`polar_sql_inception.column_rule_check_timestamp_count`	`FALSE`	Two or more `TIMESTAMP` columns use `CURRENT_TIMESTAMP` as their default.
All columns must define a default value	`polar_sql_inception.column_rule_check_default_value`	`FALSE`	A column is defined without a default value. Exceptions: `TIMESTAMP`, auto-increment, primary key, `JSON`, computed, and `BYTEA` columns.

Built-in default rules (always enforced, no parameter):

Duplicate column names are not allowed

Index rules

Rule	Parameter	Default	Triggered when
Indexes must have a name	`polar_sql_inception.index_rule_enable_null_index_name`	`TRUE`	`CREATE INDEX` omits an index name.
Maximum columns in a regular index	`polar_sql_inception.index_rule_max_key_parts`	`0` (no limit)	A regular index is created with more columns than the limit.
Maximum columns in a primary key index	`polar_sql_inception.index_rule_max_primary_key_parts`	`0` (no limit)	A primary key is created with more columns than the limit.
Primary key columns must be integer type	`polar_sql_inception.index_rule_check_pk_columns_only_int`	`FALSE`	A primary key includes a non-integer column.
Maximum indexes per table	`polar_sql_inception.index_rule_max_keys`	`0` (no limit)	The number of indexes on a table would exceed the limit.

Built-in default rules (always enforced, no parameter):

Columns specified in an index must exist
Duplicate columns in an index are not allowed: triggered when CREATE INDEX specifies duplicate columns, CREATE TABLE specifies duplicate columns for a primary key or UNIQUE constraint, or ALTER TABLE specifies duplicate columns for a primary key index
Index names must be unique
User must have permission on the table

Naming rules

Enforcing naming conventions helps teams maintain consistency and avoid using reserved keywords as identifiers, which can cause parsing errors or ambiguous queries.

Rule	Parameter	Default	Triggered when
Check name character set	`polar_sql_inception.naming_rule_check_char`	`FALSE`	A table name, column name, or index name contains characters outside `[a-zA-Z0-9*]`.
Check for SQL keywords	`polar_sql_inception.naming_rule_check_keyword`	`FALSE`	A table name, column name, or index name is a reserved SQL keyword.

DML rules

Requiring a WHERE clause on UPDATE and DELETE statements prevents accidental full-table modifications — one of the most common causes of data loss in production environments.

Rule	Parameter	Default	Triggered when
Insert list must be specified	`polar_sql_inception.dml_rule_check_insert_field`	`FALSE`	`INSERT` or `INSERT SELECT` omits the column list.
DML statements must include a WHERE clause	`polar_sql_inception.dml_rule_check_dml_where`	`FALSE`	`UPDATE`, `DELETE`, `SELECT`, or `INSERT SELECT` omits a `WHERE` clause.
`SELECT *` is not allowed	`polar_sql_inception.dml_rule_enable_select_star`	`TRUE`	`SELECT` or `INSERT SELECT` uses `SELECT *`.
`ORDER BY RAND()` is not allowed	`polar_sql_inception.dml_rule_enable_orderby_rand`	`TRUE`	A query uses `ORDER BY RANDOM()`.
Limit rows updated	`polar_sql_inception.dml_rule_max_update_rows`	`0` (no limit)	An `UPDATE` would affect more rows than the limit.
Limit rows inserted	`polar_sql_inception.dml_rule_max_insert_rows`	`0` (no limit)	An `INSERT` would insert more rows than the limit.
Limit rows deleted	`polar_sql_inception.dml_rule_max_delete_rows`	`0` (no limit)	A `DELETE` would affect more rows than the limit.

Built-in default rules (always enforced, no parameter):

Tables and columns referenced in DML must exist
User must have permission on the table

Other rules

Rule	Parameter	Default	Triggered when
Check schema consistency	`polar_sql_inception.check_schema_consistency`	`FALSE`	The schema explicitly specified in a DML or DDL statement differs from the `schema` parameter passed to `polar_sql_inception`. Supported from: PostgreSQL 16 (2.0.16.10.12.0+), PostgreSQL 14 (2.0.14.20.41.0+).

Configuration template

Use this template to configure rules in batch. Uncomment and adjust the values that match your requirements.

-- 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 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 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 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 rules
SET polar_sql_inception.naming_rule_check_char = OFF;
SET polar_sql_inception.naming_rule_check_keyword = OFF;

-- DML 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;