Add config option to require a transaction for destructive statements

When this option is on, any statement that is deemed destructive
(through the use of the `destructive_warning` config option) will
not be executed unless a transaction has been started.

4 files changed, 34 insertions, 9 deletions

diff --git a/AUTHORS b/AUTHORS
index d7656eb7..bf1f6461 100644
--- a/AUTHORS
+++ b/AUTHORS
@@ -126,6 +126,7 @@ Contributors:
     * Rigo Neri (rigoneri)
     * Anna Glasgall (annathyst)
     * Andy Schoenberger (andyscho)
+    * Damien Baty (dbaty)
 
 Creator:
 --------
diff --git a/changelog.rst b/changelog.rst
index a8b268b0..af4d6db4 100644
--- a/changelog.rst
+++ b/changelog.rst
@@ -4,6 +4,8 @@ Upcoming
 Features:
 ---------
 
+* New `destructive_statements_require_transaction` config option to refuse to execute a
+  destructive SQL statement if outside a transaction. This option is off by default.
 * Changed the `destructive_warning` config to be a list of commands that are considered
   destructive. This would allow you to be warned on `create`, `grant`, or `insert` queries.
 * Destructive warnings will now include the alias dsn connection string name if provided (-D option).
diff --git a/pgcli/main.py b/pgcli/main.py
index 5626a5ed..13e1b74a 100644
--- a/pgcli/main.py
+++ b/pgcli/main.py
@@ -64,6 +64,7 @@ from .config import (
 from .key_bindings import pgcli_bindings
 from .packages.formatter.sqlformatter import register_new_formatter
 from .packages.prompt_utils import confirm, confirm_destructive_query
+from .packages.parseutils import is_destructive
 from .packages.parseutils import parse_destructive_warning
 from .__init__ import __version__
 
@@ -234,6 +235,9 @@ class PGCli:
         self.destructive_warning_restarts_connection = c["main"].as_bool(
             "destructive_warning_restarts_connection"
         )
+        self.destructive_statements_require_transaction = c["main"].as_bool(
+            "destructive_statements_require_transaction"
+        )
 
         self.less_chatty = bool(less_chatty) or c["main"].as_bool("less_chatty")
         self.null_string = c["main"].get("null_string", "<null>")
@@ -432,15 +436,20 @@ class PGCli:
         except OSError as e:
             return [(None, None, None, str(e), "", False, True)]
 
-        if (
-            self.destructive_warning
-            and confirm_destructive_query(
+        if self.destructive_warning:
+            if (
+                self.destructive_statements_require_transaction
+                and not self.pgexecute.valid_transaction()
+                and is_destructive(query, self.destructive_warning)
+            ):
+                message = "Destructive statements must be run within a transaction. Command execution stopped."
+                return [(None, None, None, message)]
+            destroy = confirm_destructive_query(
                 query, self.destructive_warning, self.dsn_alias
             )
-            is False
-        ):
-            message = "Wise choice. Command execution stopped."
-            return [(None, None, None, message)]
+            if destroy is False:
+                message = "Wise choice. Command execution stopped."
+                return [(None, None, None, message)]
 
         on_error_resume = self.on_error == "RESUME"
         return self.pgexecute.run(
@@ -706,6 +715,15 @@ class PGCli:
 
         try:
             if self.destructive_warning:
+                if (
+                    self.destructive_statements_require_transaction
+                    and not self.pgexecute.valid_transaction()
+                    and is_destructive(text, self.destructive_warning)
+                ):
+                    click.secho(
+                        "Destructive statements must be run within a transaction."
+                    )
+                    raise KeyboardInterrupt
                 destroy = confirm = confirm_destructive_query(
                     text, self.destructive_warning, self.dsn_alias
                 )
diff --git a/pgcli/pgclirc b/pgcli/pgclirc
index b32eda0d..2d0563f1 100644
--- a/pgcli/pgclirc
+++ b/pgcli/pgclirc
@@ -28,17 +28,21 @@ multi_line_mode = psql
 
 # Destructive warning will alert you before executing a sql statement
 # that may cause harm to the database such as "drop table", "drop database",
-# "shutdown", "delete", or "update". 
+# "shutdown", "delete", or "update".
 # You can pass a list of destructive commands or leave it empty if you want to skip all warnings.
 # "unconditional_update" will warn you of update statements that don't have a where clause
 destructive_warning = drop, shutdown, delete, truncate, alter, update, unconditional_update
 
 # Destructive warning can restart the connection if this is enabled and the
 # user declines. This means that any current uncommitted transaction can be
-# aborted if the user doesn't want to proceed with a destructive_warning 
+# aborted if the user doesn't want to proceed with a destructive_warning
 # statement.
 destructive_warning_restarts_connection = False
 
+# When this option is on (and if `destructive_warning` is not empty),
+# destructive statements are not executed when outside of a transaction.
+destructive_statements_require_transaction = False
+
 # Enables expand mode, which is similar to `\x` in psql.
 expand = False