tencent cloud

TencentDB for PostgreSQL

DocumentationTencentDB for PostgreSQLKernel Version IntroductionProprietary Kernel FeaturesSQL Firewall

SQL Firewall

Download
Focus Mode
Font Size
Last updated: 2026-10-27 14:19:23
AI-Translated
SQL Firewall is a self-developed kernel capability of TencentDB for PostgreSQL, implemented based on the self-developed plugin pg_savior. It supports all existing capabilities of pg_savior and is used to prevent data loss and high-risk changes caused by accidental operations. After loading, the following protections can be applied to statements executed within the instance:
Intercept full-table UPDATE/DELETE without a WHERE clause: includes modification statements in CTEs (WITH clauses) to prevent accidental clearing of business tables.
Limit rows affected by batch changes: UPDATE/DELETE statements whose optimizer-estimated affected rows exceed the threshold will be rejected.
Force online index creation: intercept index creation statements that are not CONCURRENTLY to avoid table locking.
Protect DDL on large tables: intercept TRUNCATE, DROP TABLE, and ALTER TABLE operations that may trigger a full-table rewrite on tables exceeding the row threshold.
Intercept high-cost SQL: statements whose planner-estimated execution cost exceeds the threshold are rejected before execution.
Limit rows returned by queries: if the number of rows output by a statement exceeds the upper limit, an error is reported and the statement is aborted to prevent large result sets from affecting the instance.
Intercept database deletion: DROP DATABASE statements will be rejected.

Supported Versions

PostgreSQL Version
Supported Version
PostgreSQL 17
v17.10_r1.19 or later
PostgreSQL 18
v18.4_r1.10 or later
Note:
You can view the kernel version of the current instance on the instance details page in the console, or by executing SHOW tencentdb_version;.

Must-Knows

Preloading the plugin: Before use, find the shared_preload_libraries parameter on the parameter settings page in the console, select pg_savior in its running value, save the changes, and restart the instance.
Enabled by default after the plugin is loaded: protection takes effect by default once the plugin is loaded (pg_savior.enabled defaults to on), with no additional operations required.
Protection scope: protection applies to statements from all users in the instance, including administrators (tencentdb_superuser) and regular users.
Table creation with constraints will be intercepted: when a CREATE TABLE statement includes PRIMARY KEY or UNIQUE constraints, it is also intercepted by the non-concurrent index creation protection (except for temporary tables). For handling, see FAQ.
Row quantity determination is based on statistics: the affected row quantity and large table determination use optimizer estimates. If they deviate significantly from actual values, run ANALYZE first to update statistics.

Parameter Description

The following parameters are all at the user level (they can be set directly in a session via SET) and can also be modified in the console parameter settings.
Parameter
Default Value
Description
pg_savior.enabled
on
Master protection switch. When off, disables all protection.
pg_savior.bypass
off
Session-level bypass switch. When on, the current session skips all protection.
pg_savior.max_rows_affected
0
Upper limit of estimated affected rows for UPDATE/DELETE. Requests are blocked when the limit is exceeded. 0 means no limit.
pg_savior.large_table_threshold_rows
1000000
Large table threshold (rows). Tables exceeding this row count are protected by DDL protection.
pg_savior.max_query_cost
0
Upper limit of estimated statement execution cost (total_cost). Statements are blocked when the limit is exceeded. 0 means no limit.
pg_savior.max_result_rows
0
Upper limit of returned rows. An error is reported and execution is aborted when the limit is exceeded. 0 means no limit.

Usage

The following example is executed with the administrator account root (a member of tencentdb_superuser). You can directly reproduce the example table created in this section.

1. Enabling the Firewall

1. Log in to the TencentDB for PostgreSQL console.
2. In the instance list, click the Instance ID or Manage in the Operation column to go to the instance details page.
3. Select Parameter Settings.
4. Search for the shared_preload_libraries parameter, select pg_savior in its running value, save the changes, and restart the instance.
After the restart, you can confirm that the plugin has been loaded:
postgres=> SHOW shared_preload_libraries;
                                        shared_preload_libraries
--------------------------------------------------------------------------------------------------------
 pg_textsearch, pg_cron, pg_squeeze, timescaledb, pg_jieba, pg_bigm, tencentdb_pwdcheck, pg_savior
(1 row)
If the output contains pg_savior, the plugin has been loaded. Check the protection parameter status:
postgres=> SELECT name, setting, context FROM pg_settings WHERE name LIKE 'pg_savior%' ORDER BY name;
                name                 | setting | context
--------------------------------------+---------+---------
 pg_savior.bypass                     | off     | user
 pg_savior.enabled                    | on      | user
 pg_savior.large_table_threshold_rows | 1000000 | user
 pg_savior.max_rows_affected          | 0       | user
(4 rows)
When context is user, all parameters can be adjusted directly in a session via SET without restarting the instance.

2. Blocking UPDATE/DELETE Without WHERE

Create a sample table and insert data:
postgres=> CREATE TABLE orders (
    id int,
    customer text,
    amount numeric
);
CREATE TABLE
postgres=> INSERT INTO orders VALUES (1, 'alice', 100.00), (2, 'bob', 250.50), (3, 'carol', 78.90);
INSERT 0 3
postgres=> ANALYZE orders;
ANALYZE
UPDATE/DELETE statements without a WHERE clause will be intercepted:
postgres=> DELETE FROM orders;
ERROR:  pg_savior: DELETE without WHERE clause is blocked
HINT:  Add a WHERE clause, or set pg_savior.bypass = on for this session.
postgres=> UPDATE orders SET amount = 0;
ERROR:  pg_savior: UPDATE without WHERE clause is blocked
HINT:  Add a WHERE clause, or set pg_savior.bypass = on for this session.
Modification statements without a WHERE clause in a CTE (WITH clause) are also intercepted:
postgres=> WITH moved AS (DELETE FROM orders RETURNING *) SELECT count(*) FROM moved;
ERROR:  pg_savior: DELETE without WHERE clause is blocked
HINT:  Add a WHERE clause, or set pg_savior.bypass = on for this session.
Statements with a WHERE clause are executed normally:
postgres=> DELETE FROM orders WHERE id = 0;
DELETE 0

3. Limiting the Number of Rows Affected by Batch Changes

After pg_savior.max_rows_affected is set, UPDATE/DELETE statements whose optimizer-estimated affected rows exceed this value will be intercepted. In the following example, the limit is set to 2, and the sample table orders contains 3 rows:
root=> SET pg_savior.max_rows_affected = 2; DELETE FROM orders WHERE id > 0;
SET
ERROR:  pg_savior: DELETE estimated to affect 3 rows, exceeds pg_savior.max_rows_affected (2)
HINT:  Refine the WHERE clause, raise pg_savior.max_rows_affected, or set pg_savior.bypass = on. Run ANALYZE if the estimate looks wrong.
When the estimated affected rows do not exceed the limit, the statement is executed normally:
root=> SET pg_savior.max_rows_affected = 2; UPDATE orders SET amount = 0 WHERE id <= 2;
SET
UPDATE 2
root=> SET pg_savior.max_rows_affected = 0;
SET
Note:
The default value of this parameter is 0, which means no limit.
This parameter takes effect only in the current session and must be executed in the same session as the statement being checked. To apply a default limit to the entire instance, modify it in the console parameter settings.

4. Forcing Online Index Creation

Index creation statements that are not CONCURRENTLY will lock the table for a long time and will be intercepted:
postgres=> CREATE INDEX idx_orders_customer ON orders (customer);
ERROR:  pg_savior: CREATE INDEX without CONCURRENTLY is blocked
HINT:  Use CREATE INDEX CONCURRENTLY (it cannot run in a transaction block), or set pg_savior.bypass = on for this session.
Use CREATE INDEX CONCURRENTLY to create the index online:
postgres=> CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer);
CREATE INDEX
Note:
CREATE INDEX CONCURRENTLY cannot be executed within a transaction block.
Non-concurrent index creation on temporary tables does not lock other sessions and is not subject to this protection limit.

5. Protecting DDL on Large Tables

A table with more than pg_savior.large_table_threshold_rows rows (default: 1000000) is considered a "large table". Operations such as TRUNCATE, DROP TABLE, and ALTER TABLE that may trigger a full table rewrite will be intercepted. The following example creates a table with more than 1 million rows:
postgres=> CREATE TABLE big_orders AS SELECT g AS id, 'customer_' || g AS customer, g::numeric AS amount FROM generate_series(1, 1000100) g;
SELECT 1000100
postgres=> ANALYZE big_orders;
ANALYZE
The following operations on large tables will be intercepted:
postgres=> TRUNCATE big_orders;
ERROR:  pg_savior: TRUNCATE on a large table "big_orders" (1000100 rows) is blocked
HINT:  Verify the target, raise pg_savior.large_table_threshold_rows, or set pg_savior.bypass = on. Run ANALYZE if the row estimate looks wrong.
postgres=> DROP TABLE big_orders;
ERROR:  pg_savior: DROP TABLE on a large table "big_orders" (1000100 rows) is blocked
HINT:  Verify the target, raise pg_savior.large_table_threshold_rows, or set pg_savior.bypass = on. Run ANALYZE if the row estimate looks wrong.
postgres=> ALTER TABLE big_orders ADD COLUMN memo text DEFAULT 'N';
ERROR:  pg_savior: ALTER TABLE ADD COLUMN with DEFAULT on a large table (1000100 rows) is blocked
HINT:  This operation may rewrite the whole table (it is blocked conservatively). Plan a batched migration; raise pg_savior.large_table_threshold_rows; or set pg_savior.bypass = on. Run ANALYZE if the row estimate looks wrong.
postgres=> ALTER TABLE big_orders ALTER COLUMN amount TYPE numeric(12,2);
ERROR:  pg_savior: ALTER TABLE ALTER COLUMN TYPE on a large table (1000100 rows) is blocked
HINT:  This operation may rewrite the whole table (it is blocked conservatively). Plan a batched migration; raise pg_savior.large_table_threshold_rows; or set pg_savior.bypass = on. Run ANALYZE if the row estimate looks wrong.
ALTER TABLE statements that do not trigger a full table rewrite are not affected:
postgres=> ALTER TABLE big_orders ADD COLUMN memo text;
ALTER TABLE
Tables that do not reach the threshold are not subject to DDL protection restrictions:
postgres=> CREATE TABLE junk (id int);
CREATE TABLE
postgres=> DROP TABLE junk;
DROP TABLE
Note:
ALTER TABLE operations that may be intercepted include ADD COLUMN with DEFAULT and ALTER COLUMN TYPE, both of which may rewrite the entire table.
The row counts of partitioned tables and inherited tables are calculated cumulatively across all their child tables. Foreign key referencing tables associated with TRUNCATE ... CASCADE are also included in the check.

6. Blocking High-Cost SQL

After pg_savior.max_query_cost is set, statements whose planner-estimated execution cost (total_cost) exceeds this value will be intercepted before execution. In the following example, the limit is set to 100, and a full table statistics query on the sample table big_orders (with more than 1 million rows) will be intercepted:
root=> SET pg_savior.max_query_cost = 100; SELECT count(*) FROM big_orders;
SET
ERROR:  pg_savior: statement estimated cost 13569 exceeds pg_savior.max_query_cost (100)
HINT:  Refine the query, raise pg_savior.max_query_cost, or set pg_savior.bypass = on. Check the estimate with EXPLAIN.
EXPLAIN only generates an execution plan and does not actually execute the statement, so it is not subject to this protection limit. You can use it to view the estimated cost:
root=> SET pg_savior.max_query_cost = 100; EXPLAIN SELECT count(*) FROM big_orders;
SET
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=13569.07..13569.08 rows=1 width=8)
   ->  Gather  (cost=13568.85..13569.06 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=12568.85..12568.86 rows=1 width=8)
               ->  Parallel Seq Scan on big_orders  (cost=0.00..11527.08 rows=416708 width=0)
(5 rows)

root=> SET pg_savior.max_query_cost = 0;
SET
Note:
The default value 0 means no limit. This parameter takes effect only for the current session.
The cost is an estimate from the optimizer and can be viewed through EXPLAIN.
EXPLAIN ANALYZE actually executes the statement and is also subject to this protection check.

7. Limiting the Number of Rows Returned by Queries

After pg_savior.max_result_rows is set, a statement will be terminated with an error if its output row quantity exceeds this value. In the following example, the limit is set to 2, and the sample table orders contains 3 rows:
root=> SET pg_savior.max_result_rows = 2; SELECT id FROM orders;
SET
ERROR:  pg_savior: statement returned more than 2 rows
HINT:  Add a LIMIT, raise pg_savior.max_result_rows, or set pg_savior.bypass = on.
After LIMIT is added to the query, it returns normally:
root=> SET pg_savior.max_result_rows = 2; SELECT id FROM orders LIMIT 2;
SET
 id
----
  1
  2
(2 rows)

root=> SET pg_savior.max_result_rows = 0;
SET

Note:
The default value 0 means no limit. This parameter takes effect only for the current session.
When the upper limit is exceeded, the statement is terminated with an error and no truncated result set is returned. Add LIMIT to the query or adjust the upper limit and retry.

8. Blocking Database Deletion

Existing databases cannot be deleted, and the statement will be directly intercepted. In the example, the postgres database is used, so the statement will not have any actual impact:
postgres=> DROP DATABASE postgres;
ERROR:  pg_savior: DROP DATABASE "postgres" is blocked
HINT:  Set pg_savior.bypass = on for this session if you really mean it.
Executing DROP DATABASE IF EXISTS on a non-existent database is an idempotent cleanup script practice and is skipped normally:
postgres=> DROP DATABASE IF EXISTS savior_demo_never;
NOTICE:  database "savior_demo_never" does not exist, skipping
DROP DATABASE

9. Session-Level Disabling and Allowing

After confirming that the operation is correct, you can temporarily disable the entire firewall for the current session only. For example, to DELETE all rows from a table:
root=> SET pg_savior.enabled = off; DELETE FROM orders;
SET
DELETE 3
root=> INSERT INTO orders VALUES (1, 'alice', 100.00), (2, 'bob', 250.50), (3, 'carol', 78.90);
INSERT 0 3
root=> SET pg_savior.enabled = on;
SET
You can also allow only the current session without affecting the protection status of other sessions:
root=> SET pg_savior.bypass = on; DELETE FROM orders;
SET
DELETE 3
root=> SET pg_savior.bypass = off;
SET
Note:
To disable protection permanently, change the runtime value of pg_savior.enabled to off in the console parameter settings.
Session-level modifications to bypass and enabled do not affect other sessions.

FAQs

Q1:CREATE TABLE with PRIMARY KEY or UNIQUE constraints reports the error "CREATE INDEX without CONCURRENTLY is blocked". How do I handle this?

A: Internal indexes are created along with table creation for primary key/unique constraints, and are also intercepted by the non-concurrent index creation protection (except for temporary tables):
postgres=> CREATE TABLE t3 (id int PRIMARY KEY, v text);
ERROR:  pg_savior: CREATE INDEX without CONCURRENTLY is blocked
HINT:  Use CREATE INDEX CONCURRENTLY (it cannot run in a transaction block), or set pg_savior.bypass = on for this session.
You can switch to the online method to create it step by step:
postgres=> CREATE TABLE t3 (id int, v text);
CREATE TABLE
postgres=> CREATE UNIQUE INDEX CONCURRENTLY t3_id_uidx ON t3(id);
CREATE INDEX
postgres=> ALTER TABLE t3 ADD CONSTRAINT t3_pkey PRIMARY KEY USING INDEX t3_id_uidx;
NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "t3_id_uidx" to "t3_pkey"
ALTER TABLE
postgres=> DROP TABLE t3;
DROP TABLE
To create the table in one step, you can also temporarily bypass the protection in the current session before executing the operation.

Q2: Why is the number of rows in the error message different from the actual number of rows?

A: The protection uses optimizer estimates, which are based on table statistics. After significant data changes, run ANALYZE first to update statistics, and then retry.

Q3: Why are operations on small tables also blocked as "large tables"?

A: Check the pg_savior.large_table_threshold_rows setting in the session or console to confirm whether the threshold has been adjusted to a value less than the number of rows in the table.

Q4: How do I temporarily execute a blocked statement?

A: After confirming that the operation is correct, run SET pg_savior.bypass = on; in the current session to bypass the protection. After the operation is complete, change it back to off. The bypass takes effect only for the current session.

Q5: How do I perform batch changes on large tables during the maintenance window?

A: You can temporarily set pg_savior.enabled to off in the session, or change it to off in the console parameter settings. After the change is complete, re-enable the protection.
Previous Topic: Custom Password ComplexityNext Topic: Audit Service Description

Help and Support

Was this page helpful?

Help us improve! Rate your documentation experience in 5 mins.

Feedback

Search
    Enter keywords to search documentation