Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#31615 closed Cleanup/optimization (fixed)

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

Reported by: minusf Owned by: minusf
Component: Migrations Version: 3.0
Severity: Normal Keywords: migration extension postgresql
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: yes UI/UX: no

Description

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()

Change History (9)

comment:1 by Claude Paroz, 4 years ago

Has patch: unset
Triage Stage: UnreviewedAccepted
Type: BugCleanup/optimization

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

comment:2 by minusf, 4 years ago

Has patch: set

thank you for looking into this.

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

comment:3 by minusf, 4 years ago

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

git got the better of me :/

comment:4 by Mariusz Felisiak, 4 years ago

Needs tests: set
Patch needs improvement: set

comment:5 by Mariusz Felisiak, 4 years ago

Needs tests: unset
Owner: changed from nobody to minusf
Patch needs improvement: unset
Status: newassigned
Triage Stage: AcceptedReady for checkin

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In f3ed42c8:

Refs #31615 -- Improved creating extension docs.

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: fixed
Status: assignedclosed

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.

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In a79d0c8c:

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

Backport of f3ed42c8ad3757e7238bf2f326532f5b129aa102 from master

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In 22a59c01:

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

Note: See TracTickets for help on using tickets.
Back to Top