PostgreSQL: Prevent accidental database deletion
When we interact with a PostgreSQL server, there is a risk of accidentally deleting an important database.
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
When we interact with a PostgreSQL server, there is a risk of accidentally deleting an important database.
We want to add a protective guardrail that prevents accidental deletion.
The Solution
Never write the DROP DATABASE command manually again, either in psql or in a text file in order to prepare the command.
Instead, use a command preparation function called get_drop_database_command, which will stop and tell you if the database is protected or doesn't exist. If the database exists and is unprotected, it will print the command, and then you can copy/paste and run it to delete the database.
The Solution: More Detail
The database owner role or any superuser role can always run the DROP DATABASE command. There is sadly no way to add a direct protective guardrail against this.
Event triggers and rewrite rules do not handle the DROP DATABASE command, so we can't intercept the command using these tools.
However, we can create a function called get_drop_database_command(db_name_1, db_name_2).
We can then insist on using this function instead of ever manually entering the words "DROP DATABASE", or writing out a DROP DATABASE command in a text file and then pasting it in.
The function requires manual confirmation of the database name (by writing it again as the second argument) and it will check the database name against a list of protected names.
It will then print the required DROP DATABASE command, which we can then copy/paste and run.
This does not prevent an engineer with sufficient access from using the DROP DATABASE command, but it does allow them to instead use this function by default in their manual work. It's safer.
Deliberately adding this friction is important for the database deletion process, because the consequences of making a mistake are potentially very great. In the future, you may be under pressure or just tired, and being in the habit of using this extra confirmation step could prevent a disaster.
The Solution: Actual Code
The commands in these notes can be run in the Postgres client psql.
Please read the linked article PostgreSQL: Background for how to connect to Postgres with psql and for additional background information.
StJohn Piano
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 rather than anywhere else.
CREATE OR REPLACE FUNCTION get_drop_database_command(db_name_1 text, db_name_2 text)
RETURNS text AS
$$
DECLARE
sql_statement text;
db_exists boolean;
BEGIN
IF db_name_1 <> db_name_2 THEN
RETURN 'ERROR: Database names do not match.';
END IF;
PERFORM 1 FROM pg_database WHERE datname = db_name_1;
db_exists := FOUND;
IF NOT db_exists THEN
RETURN format('ERROR: Database ''%s'' does not exist.', db_name_1);
END IF;
IF EXISTS (SELECT 1 FROM protected_database WHERE db_name = db_name_1) THEN
RETURN format('ERROR: Database %s is in the protected database list.', db_name_1);
END IF;
sql_statement := format('DROP DATABASE %I;', db_name_1);
RETURN sql_statement;
END;
$$
LANGUAGE plpgsql;
Create a table that stores the protected database names. Again, this table should be belong to the postgres.public schema.
CREATE TABLE protected_database (
db_name text PRIMARY KEY
);
Now, whenever you have an important database, insert its name into this table, like so:
INSERT INTO protected_database (db_name) VALUES ('my_db_1');
Worked Example
See 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)
List roles.
SELECT usename AS role_name
FROM pg_catalog.pg_user
ORDER BY role_name desc;
List roles and their permissions.
\du
The admin user is a superuser. We can't prevent it from being able to drop a database with the DROP DATABASE command.
Create the function get_drop_database_command.
List functions in the current schema.
\df
-- Or, to specify the schema:
\df public.*
See the existing code for a function.
SELECT pg_get_functiondef(oid)
FROM pg_proc
WHERE proname = 'get_drop_database_command';
Create the table protected_database.
Create two databases.
CREATE DATABASE test_db;
CREATE DATABASE test_db_2;
List databases.
SELECT datname FROM pg_database
WHERE datistemplate = false;
List databases and their owners.
SELECT d.datname AS database_name, u.usename AS owner
FROM pg_database d
JOIN pg_user u ON d.datdba = u.usesysid
WHERE d.datistemplate = false;
List databases and their characteristics.
\l+
Protect one of the databases:
INSERT INTO protected_database (db_name) VALUES ('test_db');
See the rows in protected_database.
SELECT * FROM protected_database;
Now, let's try out the protection.
Call the function on a database that doesn't exist.
SELECT get_drop_database_command('foo', 'foo');
postgres=# SELECT get_drop_database_command('foo', 'foo');
get_drop_database_command
---------------------------------------
ERROR: Database 'foo' does not exist.
(1 row)
Call the function on a protected database.
SELECT get_drop_database_command('test_db', 'test_db');
postgres=# SELECT get_drop_database_command('test_db', 'test_db');
get_drop_database_command
------------------------------------------------------------
ERROR: Database test_db is in the protected database list.
(1 row)
Call the function on an unprotected database.
SELECT get_drop_database_command('test_db_2', 'test_db_2');
postgres=# SELECT get_drop_database_command('test_db_2', 'test_db_2');
get_drop_database_command
---------------------------
DROP DATABASE test_db_2;
(1 row)
In the final case, calling the function on an unprotected database, the function prints a DROP DATABASE command, which we can now copy/paste and run.
Note that you won't be able to delete the database if you're connected to it.
See current database.
SELECT CURRENT_DATABASE();
postgres=# SELECT CURRENT_DATABASE();
current_database
------------------
postgres
(1 row)
postgres=# DROP DATABASE test_db_2;
DROP DATABASE
List databases.
postgres=# SELECT datname FROM pg_database
WHERE datistemplate = false;
datname
----------
postgres
test_db
(2 rows)
Sources
https://telablog.com/notes-on-postgres-user-management
https://telablog.com/postgresql-background
https://www.postgresql.org/docs/14/sql-dropdatabase.html
https://www.postgresql.org/docs/14/sql-createfunction.html
https://dba.stackexchange.com/questions/1285/how-do-i-list-all-databases-and-tables-using-psql
https://www.postgresql.org/docs/14/event-trigger-matrix.html
If you have any questions, comments, or suggestions - please contact StJohn Piano on Tela:
tela.app/id/stjohn_piano/7c51a6
Follow Tela Network on LinkedIn:
linkedin.com/company/tela-network
Follow Tela Network on Twitter:
twitter.com/tela_updates
Join Tela Network and become a consultant:
telablog.com/how-to-use-tela-for-consulting
Join the Tela Social channel on Telegram to get every new update:
t.me/tela_social
Follow Tela Network on Instagram:
instagram.com/tela_updates
Invest in Tela:
tela.network/invest
