Prevent accidental whole-table DELETEs and UPDATEs

From 5ff016ab8d8b75c64e6a51cadd981cc0fb09a964 Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <x4mmm@flight.local>
Date: Thu, 2 Feb 2023 09:50:21 -0800
Subject: [PATCH] Add GUCs to preven some accidental massive DML

Add 2 new GUCs prevent_unqualified_deletes and
prevent_unqualified_updates to prevent user from accidental
DELETEs and UPDATEs accordingly. Both are off by default.
---
 src/backend/optimizer/plan/planner.c          | 23 ++++++++++++++++++-
 src/backend/utils/misc/guc_tables.c           | 18 +++++++++++++++
 src/backend/utils/misc/postgresql.conf.sample |  3 +++
 src/include/optimizer/planmain.h              |  2 ++
 4 files changed, 45 insertions(+), 1 deletion(-)

diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index db5ff6fdca..441bd23f44 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -71,7 +71,9 @@
 /* GUC parameters */
 double		cursor_tuple_fraction = DEFAULT_CURSOR_TUPLE_FRACTION;
 int			force_parallel_mode = FORCE_PARALLEL_OFF;
-bool		parallel_leader_participation = true;
+bool           parallel_leader_participation = true;
+bool		prevent_unqualified_deletes = false;
+bool		prevent_unqualified_updates = false;
 
 /* Hook for plugins to get control in planner() */
 planner_hook_type planner_hook = NULL;
@@ -279,6 +281,25 @@ planner(Query *parse, const char *query_string, int cursorOptions,
 		result = (*planner_hook) (parse, query_string, cursorOptions, boundParams);
 	else
 		result = standard_planner(parse, query_string, cursorOptions, boundParams);
+	if (!superuser() && parse->jointree != NULL)
+	{
+		if (prevent_unqualified_deletes && parse->commandType == CMD_DELETE)
+		{
+			if (parse->jointree->quals == NULL)
+			{
+				elog(ERROR,"DELETE query with empty WHERE is forbidden by"
+							" some_fancy_guc");
+			}
+		}
+		if (prevent_unqualified_updates && parse->commandType == CMD_UPDATE)
+		{
+			if (parse->jointree->quals == NULL)
+			{
+				elog(ERROR,"UPDATE query with empty WHERE is forbidden by"
+							" some_fancy_guc");
+			}
+		}
+	}
 	return result;
 }
 
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index b46e3b8c55..df5f6d9ee4 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -875,6 +875,24 @@ struct config_bool ConfigureNamesBool[] =
 		true,
 		NULL, NULL, NULL
 	},
+	{
+		{"prevent_unqualified_deletes", PGC_USERSET, UNGROUPED,
+			gettext_noop("Some proper description."),
+			NULL
+		},
+		&prevent_unqualified_deletes,
+		false,
+		NULL, NULL, NULL
+	},
+	{
+		{"prevent_unqualified_updates", PGC_USERSET, UNGROUPED,
+			gettext_noop("Some more proper description."),
+			NULL
+		},
+		&prevent_unqualified_updates,
+		false,
+		NULL, NULL, NULL
+	},
 	{
 		{"enable_memoize", PGC_USERSET, QUERY_TUNING_METHOD,
 			gettext_noop("Enables the planner's use of memoization."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index d06074b86f..66aa9be3af 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -391,6 +391,9 @@
 #enable_sort = on
 #enable_tidscan = on
 
+#enable prevent_unqualified_deletes = off
+#enable prevent_unqualified_updates = off
+
 # - Planner Cost Constants -
 
 #seq_page_cost = 1.0			# measured on an arbitrary scale
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 5fc900737d..098a555dc4 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -20,6 +20,8 @@
 /* GUC parameters */
 #define DEFAULT_CURSOR_TUPLE_FRACTION 0.1
 extern PGDLLIMPORT double cursor_tuple_fraction;
+extern PGDLLIMPORT bool prevent_unqualified_deletes;
+extern PGDLLIMPORT bool prevent_unqualified_updates;
 
 /* query_planner callback to compute query_pathkeys */
 typedef void (*query_pathkeys_callback) (PlannerInfo *root, void *extra);
-- 
2.37.3