Reference

PostgreSQL: Protect tables against accidental deletion

There is a risk of accidentally deleting an important table whenever we interact with a PostgreSQL server.

If you have any questions, comments, corrections, or suggestions - please contact StJohn Piano on Tela:
tela.app/id/stjohn_piano/7c51a6

Follow Tela Network on LinkedIn:
linkedin.com/company/tela-network

Join Tela Network and become a consultant:
telablog.com/how-to-use-tela-for-consulting

The Problem

There is a risk of accidentally deleting an important table whenever we interact with a PostgreSQL server.

We interact with a PostgreSQL server whenever we:

write statements manually in a client
prepare statements in a text file and copypaste them into a client
generate SQL statements in another programming language

We want to add a protective guardrail that prevents accidental deletion.

The Solution

We create an event trigger that fires when the DROP command is used. It calls a function that checks the table name against a list of protected table names.

Why not use privileges instead ?

For normal operation, we usually create a second database user with limited privileges that can insert / delete data, but does not own anything, and thus cannot drop a table.

However, this does not protect against "fat-finger" / "oops" moments when a tired database administrator needs to drop a table but makes a mistake.

Protecting against this possibility with a simple guardrail is cheaper than restoring from a backup.

Background Material

The commands in these notes can be run in the Postgres client psql.

The article PostgreSQL: Background. It covers:

how to connect to Postgres with psql
the structure of a Postgres database
some details about event triggers

Previous articles in this series:

The Solution: Actual Code

Create the following function in the database.

Note: A function is an object that belongs to a particular schema in a particular database.

You should be connected as the default superuser to the default postgres database and the default public schema, so that this function is stored here instead of anywhere else.

CREATE OR REPLACE FUNCTION protect_tables()
  RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
    table_name_1 text;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        IF obj.object_type = 'table' THEN

            table_name_1 := obj.object_name;

            IF EXISTS (SELECT 1 FROM protected_table WHERE table_name = table_name_1) THEN
               RAISE EXCEPTION 'ERROR: Table ''%'' is listed in the table ''protected_table''.', table_name_1;
            END IF;

        END IF;
    END LOOP;
END;
$$;

Create an event trigger that calls this function when a DROP command is used.

DROP EVENT TRIGGER IF EXISTS drop_table_trigger;

CREATE EVENT TRIGGER drop_table_trigger
ON sql_drop
EXECUTE FUNCTION protect_tables();

Create a table that stores the list of protected tables. Again, this table should be stored on postgres.public.

CREATE TABLE protected_table (
    table_name text PRIMARY KEY
);

Now, whenever you have an important table, insert its name into this table. Example:

INSERT INTO protected_table (table_name) VALUES ('my_table_1');

Worked Example

Document Postgres version.

SELECT version();

postgres=# SELECT version();
                                                            version
--------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.9 (Homebrew) on x86_64-apple-darwin22.4.0, compiled by Apple clang version 14.0.3 (clang-1403.0.22.14.1), 64-bit
(1 row)

See current role.

SELECT CURRENT_ROLE;

postgres=# SELECT CURRENT_ROLE;
 current_role
--------------
 admin
(1 row)

This is the superuser role.

See current database.

SELECT CURRENT_DATABASE();

postgres=# SELECT CURRENT_DATABASE();
 current_database
------------------
 postgres
(1 row)

See current schema.

SELECT CURRENT_SCHEMA;

postgres=# SELECT CURRENT_SCHEMA;
 current_schema
----------------
 public
(1 row)

Create the function, trigger, and table shown earlier.

Create two new test tables.

CREATE TABLE test_table_1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE test_table_2 (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL
);

Protect the name of the first test table.

INSERT INTO protected_table (table_name) VALUES ('test_table_1');

We try to drop a table that doesn't exist.

postgres=# DROP TABLE foo;
ERROR:  table "foo" does not exist

We try to drop a table that is protected.

postgres=# DROP TABLE test_table_1;
ERROR:  ERROR: Table 'test_table_1' is listed in the table 'protected_table'.
CONTEXT:  PL/pgSQL function protect_tables() line 13 at RAISE

We try to drop a table that is unprotected.

postgres=# DROP TABLE test_table_2;
DROP TABLE

List tables.

postgres=# \dt
              List of relations
 Schema |        Name        | Type  | Owner
--------+--------------------+-------+-------
 public | protected_database | table | admin
 public | protected_schema   | table | admin
 public | protected_table    | table | admin
 public | test_table_1       | table | admin
(4 rows)

So: We are now prevented from accidentally dropping a table, if its name is stored in the table postgres.public.protected_table.