Insufficient privilege to create extension

Hello

My application requires the use of the extension hstore. However while migrating my database, my migrations were stopped due to
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied to create extension “hstore”

How would I be able to enable hstore given that we are not given superuser privileges

Render Database has already installed extension hstore. You can find a full list of pre-installed PostgreSQL extensions: Databases | Render · Cloud Hosting for Developers

In that case do you know what might cause this error to appear while migrating the database?

ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied to create extension “hstore”

I’m deploying a rails application with a postgres database

While trying to add a column of type hstore, i received this error message instead

Render Database doesn’t grant the user permission to install extensions. You can add IF NOT EXISTS to CREATE EXTENSION in your PostgreSQL sql PostgreSQL: Documentation: 9.1: CREATE EXTENSION

Thanks but as you mentioned hstore should already be installed. Also this actually occurred on a redeployment of our database. Initially we were able to run our migrations with no issues, and the issue seems to be new

Hi Gary,

Could you share how your team seeded your database with data for the first time? Did you use any tool/framework/library?
Thanks.

Haoji

Hi,

We just ran rails db:create, rails db:migrate and rails db:seed

However we actually resolved the problem by creating a new database service as opposed to just dropping the old database


I keep seeing hstore does not exist?.

Is the hstore extension only available for the upgraded versions?

Hey,

Not exactly sure where you’re trying to create the extension from, here. Have you tried to add the extension connecting to your database via psql?

Regards.
Jérémy, Render Support

Thank you very much.

I was able to create the extension on the DB, with the following:

CREATE EXTENSION IF NOT EXISTS hstore;