PolarDB:polar_sql_inception (SQL Audit)

Execution model

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

1. Audit statement A → (if passed and execute = TRUE) execute statement A

2. Audit statement B → (if passed and execute = TRUE) execute statement B

3. ...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

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;

Function signature

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

Parameters

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

Runtime parameters

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.

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

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

• Duplicate column names are not allowed

Index rules

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.

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.

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