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

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.

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.

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.