Docs

RDS for PostgreSQL

Extensions

DML statement security protection (safeupdate)

Introduction

safeupdate is a lightweight PostgreSQL extension designed to enhance database operation security by enforcing that UPDATE and DELETE statements must include a WHERE clause. When it detects a DML statement that might accidentally modify or delete the entire table, it immediately throws an error and blocks execution.

This extension is particularly useful in scenarios where the database is exposed to external write operations through API gateways such as PostgREST. It effectively prevents catastrophic data loss caused by client errors or malicious attacks.

Applicable scenarios

Open API environments: prevent requests sent through RESTful APIs from accidentally modifying or deleting entire tables.

Development and testing environments: help developers develop good SQL writing habits and avoid data mishandling during development.

Multi-user collaborative databases: provide a basic security layer for teams to reduce the risk of human errors.

Create and delete extension

Enabling and disabling safeupdate is controlled by modifying the shared_preload_libraries parameter in PostgreSQL's configuration file postgresql.conf. The modification takes effect only after a database service restart.

Create extension (enable)

Modify the shared_preload_libraries parameter to add the safeupdate value. If other libraries already exist, separate them with commas. For detailed information on modifying parameters, please refer to Modifying parameters.

Restart the PostgreSQL service to load the extension.

Delete extension (disable)

Modify the shared_preload_libraries parameter to remove the safeupdate value.

Restart the PostgreSQL service.

Basic usage

After enabling extensions, any UPDATE or DELETE statement without a WHERE clause will be intercepted.

UPDATE statement without a WHERE clause

-- create a test table
CREATE TABLE products (id INT, name TEXT);
INSERT INTO products VALUES (1, 'Apple'), (2, 'Orange');

-- attempt to execute UPDATE without a WHERE clause (will be intercepted)
UPDATE products SET name = 'Banana';

Return error:

ERROR: UPDATE requires a WHERE clause

DELETE statement without a WHERE clause

-- attempt to execute DELETE without a WHERE clause (will be intercepted)
DELETE FROM products;

Return error:

ERROR: DELETE requires a WHERE clause

Correct usage

An UPDATE or DELETE statement only succeeds if it includes a WHERE clause.

warning

Safeupdate performs strict checks. Even conditions like WHERE 1=1, which logically apply to the entire table, are considered valid and pass the check. Therefore, it mainly prevents errors caused by forgetting to add a WHERE clause, not logical errors in the condition.

Extension parameter

safeupdate's behavior can be controlled at runtime through a GUC parameter.

safeupdate.enabled

Function: controls whether to enable extension enforcement of filtering conditions on DML statements.

Type: boolean

Default value: true (ON)

Last updated: 2026.05.09 16:35:06