Why can only a superuser CREATE EXTENSION hstore, but not on Heroku?

postgresql heroku hstore
Peeja picture Peeja · Dec 21, 2013 · Viewed 23.3k times · Source

When I attempt to enable hstore on my database:

=> CREATE EXTENSION IF NOT EXISTS hstore;
ERROR:  permission denied to create extension "hstore"
HINT:  Must be superuser to create this extension.

My user is not a superuser, but is the owner of the database.

According to the CREATE EXTENSION docs:

Loading an extension requires the same privileges that would be required to create its component objects. For most extensions this means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension's script.

What is hstore doing that requires superuser privileges? Is it affecting parts of the cluster outside the database I'm adding it to?

Further confundity:

The DB user Heroku Postgres provides is not a superuser:

Heroku Postgres users are granted all non-superuser permissions on their database. These include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

However, that user is able to CREATE EXTENSION hstore:

To create any supported extension, open a session with heroku pg:psql and run the appropriate command:

$ heroku pg:psql
Pager usage is off.
psql (9.2.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

ad27m1eao6kqb1=> CREATE EXTENSION hstore;
CREATE EXTENSION
ad27m1eao6kqb1=>

(For context, I'm attempting to set up a Dokku deployment, so the comparison to Heroku is especially important.)

Answer

Peter Eisentraut picture Peter Eisentraut · Dec 23, 2013

The hstore extension creates functions that call code from an external dynamic object, which requires superuser privilege. That's why creating the hstore extension requires superuser privilege.

As for Heroku, it is my understanding that they are running with a special extension whitelisting module, which allows users to create certain extensions even though they are not superusers. I believe it is based on this code: https://github.com/dimitri/pgextwlist. You can try to install that code yourself if you want the same functionality in your databases.

Related questions

Heroku "psql: FATAL: remaining connection slots are reserved for non-replication superuser connections"

I'm developing an app on Heroku with a Postgresql backend. Periodically, I get this error message when trying to access the database, both from the CLI and from loading a page on the server: psql: FATAL: remaining connection slots are …

Read More
Getting "[archiver] unsupported version (1.13) in file header" when running pg_restore

I just upgraded to postgres 10.2 on mac os which matches 10.2 on heroku. I'm trying to download a copy of the database and restore it locally. Before the upgrade the restore would work fine. I run pg_restore --verbose --clean --no-acl …

Read More
Connect to a heroku database with pgadmin

I would like to manage my heroku database with pgadmin client. By now, I've been doing this with psql. When I use data from heroku pg:credentials to connect de DB using pgadmin, I obtain: An error has occurred: Error …

Read More