Reference

PostgreSQL: Protect schemas against accidental deletion

There is a risk of accidentally deleting an important schema 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 schema 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 entered. It calls a function that checks the schema name against a list of protected schema 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 schema.

However, this does not protect against "fat-finger" / "oops" moments when a tired database administrator needs to drop a schema 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 article 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_schemas()
  RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
    schema_name_1 text;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        IF obj.object_type = 'schema' THEN

            schema_name_1 := obj.object_name;

            IF EXISTS (SELECT 1 FROM protected_schema WHERE schema_name = schema_name_1) THEN
               RAISE EXCEPTION 'ERROR: Schema ''%'' is in the protected schema list.', schema_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_schema_trigger;

CREATE EVENT TRIGGER drop_schema_trigger
ON sql_drop
EXECUTE FUNCTION protect_schemas();

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

CREATE TABLE protected_schema (
    schema_name text PRIMARY KEY
);

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

INSERT INTO protected_schema (schema_name) VALUES ('my_schema_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 two new test schemas.

CREATE SCHEMA test_schema_1;

CREATE SCHEMA test_schema_2;

List schemas:

SELECT schema_name 
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_toast', 'pg_catalog', 'information_schema');

postgres=# SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_toast', 'pg_catalog', 'information_schema');
 schema_name
-------------
 public
 test_schema_1
 test_schema_2
(2 rows)

Create the function, trigger, and table shown earlier.

Protect the name of the first test schema.

INSERT INTO protected_schema (schema_name) VALUES ('test_schema_1');

List functions and their characteristics.

\df

List functions and their owners and schemas:

SELECT routine_name AS function, grantee AS owner, routine_schema AS schema
FROM information_schema.routine_privileges
WHERE grantee = 'admin'
AND routine_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY routine_name;

More complex version that also gets the language.

SELECT
  routine_name AS function,
  grantee AS owner,
  routine_schema AS schema,
  pl.lanname AS language
FROM information_schema.routine_privileges AS rp
  INNER JOIN pg_proc AS pp ON pp.proname = rp.routine_name
  INNER JOIN pg_language pl ON (pp.prolang = pl.oid)
WHERE
  rp.grantee = 'admin'
  AND rp.routine_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY function;

List functions, including their language and code.

SELECT 
  pp.proname AS function,
  pn.nspname AS schema,
  pl.lanname AS language,
  pg_get_functiondef(pp.oid) AS function_code
FROM pg_proc pp
INNER JOIN pg_namespace pn ON (pp.pronamespace = pn.oid)
INNER JOIN pg_language pl ON (pp.prolang = pl.oid)
WHERE pl.lanname NOT IN ('c','internal') 
  AND pn.nspname NOT LIKE 'pg_%'
  AND pn.nspname <> 'information_schema';

List triggers:

\dy

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

postgres=# DROP SCHEMA foo;
ERROR:  schema "foo" does not exist

We try to drop a schema that is protected.

postgres=# DROP SCHEMA test_schema_1;
ERROR:  ERROR: Schema 'test_schema_1' is in the protected schema list.
CONTEXT:  PL/pgSQL function protect_schemas() line 15 at RAISE

We try to drop a schema that is protected, using the CASCADE option.

postgres=# DROP SCHEMA test_schema_1 CASCADE;
ERROR:  ERROR: Schema 'test_schema_1' is in the protected schema list.
CONTEXT:  PL/pgSQL function protect_schemas() line 15 at RAISE

We try to drop a schema that is not protected.

postgres=# DROP SCHEMA test_schema_2;
DROP SCHEMA

List schemas.

postgres=# SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_toast', 'pg_catalog', 'information_schema');
  schema_name
---------------
 public
 test_schema_1
(2 rows)

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