Fix "Permission Denied to Create Extension" in Django Migrations

Problem statement

A migration that worked locally fails during deploy:

django.db.utils.ProgrammingError: permission denied to create extension "pg_trgm"
HINT:  Must have CREATE privilege on current database to create this extension.

Or, for extensions that are not trusted:

ERROR:  permission denied to create extension "postgis"
HINT:  Must be superuser to create this extension.

Migrations using TrigramExtension(), UnaccentExtension(), HStoreExtension(), CITextExtension(), BtreeGinExtension(), or GeoDjango's PostGIS setup run CREATE EXTENSION IF NOT EXISTS .... Locally you connect as a superuser, so it works. In production the app role is, correctly, not a superuser and may not even own the database.

1. Create the extension once as an admin user: psql -U postgres -d appdb -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
2. Re-run migrate. The IF NOT EXISTS in Django's operation now succeeds without privileges.
3. For trusted extensions (pg_trgm, unaccent, hstore, citext, btree_gin on PostgreSQL 13+), granting CREATE on the database to the app role is enough.
4. On managed databases, use the provider's admin role (rds_superuser, cloudsqlsuperuser, azure_pg_admin) or allow-list the extension first.
5. Add the extension step to your provisioning so new environments do not fail.

Step-by-step solution

1. Create the extension as an administrator

Connect with a role that has the rights, into the same database the app uses:

sudo -u postgres psql -d appdb -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"

Extensions are installed per database, so appdb matters. Installing into postgres does not help the app. Then rerun:

python manage.py migrate

Django's CreateExtension operation checks pg_extension first and skips creation if the extension exists.

2. Or grant CREATE for trusted extensions

Since PostgreSQL 13, some extensions are marked trusted, and any role with CREATE on the database can install them:

GRANT CREATE ON DATABASE appdb TO app;

Trusted extensions include pg_trgm, unaccent, hstore, citext, btree_gin, btree_gist, fuzzystrmatch, and pgcrypto. PostGIS and pg_stat_statements are not trusted and need a superuser or provider admin role.

3. Managed database providers

• Amazon RDS and Aurora: the master user has rds_superuser, which can create supported extensions. Use it for the one-time step, not as the app user.
• Google Cloud SQL: the default postgres user is a member of cloudsqlsuperuser and can create supported extensions.
• Azure Database for PostgreSQL Flexible Server: add the extension to the azure.extensions server parameter first, or creation fails even for the admin.
• Supabase and Neon: enable extensions in the dashboard or as the project owner role.

4. Make it part of provisioning

Rather than relying on migrations, create extensions where you create the database: Terraform, Ansible, a Docker init script, or a CI setup step. For Docker:

-- docker/postgres-init/01-extensions.sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;

Mounted at /docker-entrypoint-initdb.d/, it runs when the volume is first initialized. In CI, run it before manage.py test, or create it in template1 so the test database inherits it.

5. Keep the migration operation

Leave TrigramExtension() in your migration. It documents the dependency, works in development, and becomes a no-op where the extension already exists.

Explanation

CREATE EXTENSION runs the extension's install script, which can create functions in C, types, and operators. Untrusted extensions can execute arbitrary code in the server process, so PostgreSQL restricts them to superusers. Trusted extensions are vetted to be safe and can be installed by anyone with CREATE on the database. Django migrations run as the app's database role, which in a well-run production setup has only the privileges it needs for its tables.

Edge cases / notes

• Schema of the extension. If public is not in the app role's search_path, functions such as similarity() may not be found even when installed. Install into public or adjust search_path.
• Test databases. Django's test runner creates a new database. It needs the extension too; creating it in template1 handles this.
• Version upgrades. ALTER EXTENSION postgis UPDATE after a PostGIS package upgrade also needs admin rights.

Next steps

• For the related schema privilege error, see Fix PostgreSQL Permission Denied for Schema Public.
• Use pg_trgm and unaccent well with Django PostgreSQL Full-Text Search in Production.
• Set up PostGIS properly with Deploy GeoDjango With PostGIS in Production.

FAQ

Should the Django database user be a superuser?

No. A superuser can read every database and run code on the server. Give the app role ownership of its tables and create extensions separately.

Why does it work locally?

Local PostgreSQL installations usually connect you as a superuser, so every extension can be created.

Does Django create the extension again on every migrate?

No. The operation runs once with its migration and uses IF NOT EXISTS, so an existing extension is left alone.

Can I remove the extension migration if I create it manually?

You can, but keeping it documents the dependency and helps development environments. It does no harm where the extension exists.