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

Question

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

Quinone

I guess that's because it needs to load/bind native libraries (dll/so) which only a superuser is allowed to do. – Overlord

i'm with @a_horse_with_no_name, it needs to modify the server process, so think that is why it requires SU, I normally load it as part of bootstrap using SU, before switching over to DB owner, but cannot find docs supporting – Nutrition

Hmm. That makes sense, but see my addition about Heroku. Is there something Heroku does in advance while setting up the DB that makes CREATE EXTENSION hstore possible for non-superusers? – Quinone

"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." so presumably hstore doesn't need to do anything superuser-ish or Heroku has adjusted how CREATE EXTENSION works. – Tati

Answer 1

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.

Supernumerary

Could you tell which external object extension uses? As I suppose this object is inside database infrastructure. – Loxodromic

Answer 2

ALTER USER myuser WITH SUPERUSER;

If you run this command from a superuser, this solves your CREATE EXTENSION issue. You may check your available users with \du to find a superuser.

Biparous

Downvote. This is a question about Heroku. And as a user of a Heroku Web Service, you cannot just become a superuser, instead, you can only install a chosen range of extensions as a nosuperuser. That is why this answer does not answer the question. Same with other Web Server providers where I got a reply from the support saying: '"Untrusted" language extensions cannot be supported as they would compromise our ability to guarantee the highest possible service level.' – Thickwitted

Even though this answer isn't pointing towards Heroku, I'd still give it a thumbs up because it helped me in my own situation – Conciliar

You may have to raise ticket with heoku for this as hstore is a n extension for as semi-structured data or rows with many attributes that are rarely queried. This solution is applicaple if you have full controll of you db instance. – Biparous

Answer 3

This is not related to heroku.

This is how I solved this issue in ubuntu 18.04.

1. Provide postgres super user access.

   sudo su postgres

2. Then I run:

   psql -U postgres your_database_name -c 'create extension hstore;'

Now I can alter table your_database_name and add hstore type columns in it.

• Connect to your database

  psql -d your_database_name -U your_user_role

And

alter table your_table_name add your_column_name HSTORE;

Though there might be saveral different ways to do it, but I solve it in this way.

Hope this will help novice users like me.

Ruprecht

Downvote. It is related to Heroku (and other Web Services). You cannot become a superuser when you use a Web Server of an external party that gives you only nosuperuser rights. – Thickwitted