Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

CREATE EXTENSION fails for a role that is not a superuser

ErrorERROR: 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.

  • Applies to PostgreSQL 13 to 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1trusted is false and the hint says: Must be superuser to create this extensionThe extension is not marked trustedFix
    2. 2trusted 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. 3ERROR: 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

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.