CREATE EXTENSION fails for a role that is not a superuser
ERROR: permission denied to create extension "hstore"Short answer
Most extensions need superuser rights to install. Only extensions marked trusted can be installed by a normal role, and only if that role has CREATE on the current database. Check the trusted flag in pg_available_extension_versions, then either grant CREATE, or ask a superuser to run the statement once.
Fix it fast
1Find out which cause you have
Ask the server whether the extension is installable by a normal role. The trusted column and the hint in the error message together tell you which cause applies.
SELECT name, version, superuser, trusted FROM pg_available_extension_versions WHERE name = 'hstore';2Match what you see, then jump to its fix
- 1
trusted is false and the hint says: Must be superuser to create this extensionThe extension is not marked trustedFix - 2
trusted is true and the hint says: Must have CREATE privilege on current databaseThe extension is trusted, but your role lacks CREATE on the databaseFix - 3
ERROR: extension "name" is not available, with a hint that it must first be installed on the system where PostgreSQL is runningThe extension files are not installed on the serverFix
- 1
What does the error look like?
ERROR: permission denied to create extension "hstore"HINT: Must be superuser to create this extension.HINT: Must have CREATE privilege on current database to create this extension.ERROR: extension "foo" is not available
Cause 1 of 3
The extension is not marked trusted
1Check
Loading an extension normally needs the privileges required to create its objects, which for many extensions means a superuser. Only extensions marked trusted in their control file are an exception.
SELECT name, trusted FROM pg_available_extension_versions WHERE name = 'postgis';2Fix
Have a superuser (or the admin of your managed database service) run CREATE EXTENSION once in the target database. Your application role can then use it.
-- as a superuser, connected to the target database
CREATE EXTENSION IF NOT EXISTS postgis;3Verify
The extension is listed in that database.
SELECT extname, extversion FROM pg_extension;Confirmed by postgresql.org
Cause 2 of 3
The extension is trusted, but your role lacks CREATE on the database
1Check
A trusted extension can be installed by any user who has CREATE privilege on the current database. The error code path picks the hint from the trusted flag, so this hint means the privilege is what is missing.
SELECT has_database_privilege(current_user, current_database(), 'CREATE');2Fix
Grant the privilege to the role that runs migrations, then retry the statement as that role.
GRANT CREATE ON DATABASE app_db TO app_user;3Verify
The check query now returns true and the extension installs.
SELECT has_database_privilege('app_user', 'app_db', 'CREATE');
CREATE EXTENSION IF NOT EXISTS pgcrypto;Confirmed by postgresql.org
Cause 3 of 3
The extension files are not installed on the server
1Check
This is a different failure from a privilege error. The extension's control file is missing, so no role can create it.
SELECT name FROM pg_available_extensions WHERE name = 'postgis';2Fix
Install the extension's files on the database server (the way depends on your distribution or hosting). After that, create it as in the first cause.
CREATE EXTENSION IF NOT EXISTS postgis;3Verify
The check query returns a row for the extension.
SELECT name FROM pg_available_extensions WHERE name = 'postgis';Confirmed by raw.githubusercontent.com
How do I stop it coming back?
- List the extensions your app needs in the setup docs and create them from an admin role, not the app role.
- Check the trusted column before you pick an extension for a database where you are not a superuser.
- Keep CREATE EXTENSION in a migration run by a role that is allowed to do it.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix