Do not fail migration if postgresql extension is already installed and user is not superuser

[minusf]

Currently Django facilitates installing postgres extensions using migration operations: ​https://docs.djangoproject.com/en/3.0/ref/contrib/postgres/operations/

But having a superuser is a tall order in certain environments, and the manual helpfully points out:

If the Django database user doesn’t have superuser privileges, you’ll have to create the extension outside of Django migrations with a user that has the appropriate privileges.

This is fairly often the case with Salt and other orchestration systems.

However having a non-superuser will break any migrations using these operations because unfortunately CREATE EXTENSION even with IF NOT EXISTS still requires superuser privileges... In other words, if the extension is already in place, the migration will fail...

I think a failsafe like this around CREATE/DROP EXTENSION would make the migrations more robust and user friendly.

diff --git a/django/contrib/postgres/operations.py b/django/contrib/postgres/operations.py
index 0bb131ddf2..46fc11f63f 100644
--- a/django/contrib/postgres/operations.py
+++ b/django/contrib/postgres/operations.py
@@ -21,7 +21,13 @@ class CreateExtension(Operation):
             not router.allow_migrate(schema_editor.connection.alias, app_label)
         ):
             return
-        schema_editor.execute("CREATE EXTENSION IF NOT EXISTS %s" % schema_editor.quote_name(self.name))
+
+        cur = schema_editor.connection.cursor()
+        cur.execute("SELECT * FROM pg_extension WHERE extname = %s", [self.name])
+        if not cur.fetchone():
+            schema_editor.execute(
+                "CREATE EXTENSION IF NOT EXISTS %s" % schema_editor.quote_name(self.name)
+            )
         # Clear cached, stale oids.
         get_hstore_oids.cache_clear()
         get_citext_oids.cache_clear()
@@ -33,7 +39,13 @@ class CreateExtension(Operation):
     def database_backwards(self, app_label, schema_editor, from_state, to_state):
         if not router.allow_migrate(schema_editor.connection.alias, app_label):
             return
-        schema_editor.execute("DROP EXTENSION %s" % schema_editor.quote_name(self.name))
+
+        cur = schema_editor.connection.cursor()
+        cur.execute("SELECT * FROM pg_extension WHERE extname = %s", [self.name])
+        if cur.fetchone():
+            schema_editor.execute(
+                "DROP EXTENSION %s" % schema_editor.quote_name(self.name)
+            )
         # Clear cached, stale oids.
         get_hstore_oids.cache_clear()
         get_citext_oids.cache_clear()

[Claude Paroz]

I'm in favor. Please check again the Has patch box when a GitHub PR is available.

[minusf]

thank you for looking into this.

​https://github.com/django/django/pull/12950

[minusf]

please have a look at this one: ​https://github.com/django/django/pull/12952

git got the better of me :/

[Mariusz Felisiak <felisiak.mariusz@…>]

In f3ed42c8:

Refs #31615 -- Improved creating extension docs.

[Mariusz Felisiak <felisiak.mariusz@…>]

In d693a086:

Fixed #31615 -- Made migrations skip extension operations if not needed.

• Don't try to create an existing extension.
• Don't try to drop a nonexistent extension.

[Mariusz Felisiak <felisiak.mariusz@…>]

In a79d0c8c:

[3.1.x] Refs #31615 -- Improved creating extension docs.

Backport of f3ed42c8ad3757e7238bf2f326532f5b129aa102 from master

[Mariusz Felisiak <felisiak.mariusz@…>]

In 22a59c01:

Refs #31615 -- Added EXISTS clauses to extension operations.