Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

PG Permision denied on setup_hstore migration #99

Closed
guidomb opened this Issue Apr 15, 2013 · 3 comments

Comments

Projects
None yet
5 participants

guidomb commented Apr 15, 2013

When I run the setup_hstore migration I get the following error

==  SetupHstore: migrating ====================================================
-- execute("CREATE EXTENSION IF NOT EXISTS hstore")
rake aborted!
PG::Error: ERROR:  permission denied to create extension "hstore"
HINT:  Must be superuser to create this extension.
: CREATE EXTENSION IF NOT EXISTS hstore
/vagrant/db/migrate/20130415193403_setup_hstore.rb:3:in `up'
Tasks: TOP => db:migrate:up
(See full trace by running task with --trace)

I am using Postgres 9.1 on Ubuntu 12.04 LTS installed via apt-get. I have installed the postgresql-contrib package.

The only way a can get hstore to work is by executing the following command

sudo su postgres -c "psql MY_DATABASE -c 'CREATE EXTENSION hstore;'"

Super database user is a requirement for this gem to work?

To setup my PostgreSQL database I ran the following commands

sudo su postgres
psql -c "create user foo with password 'foobar'"
psql -c "create database my_database_development owner foo"
psql -c "create database my_database_test owner foo"
psql -c "alter user foo createdb"

and this is my database.yml

development:
  adapter: postgresql
  encoding: utf8
  database: my_database_development
  pool: 5
  timeout: 5000
  username: foo
  password: foobar
  host: localhost

test:
  adapter: postgresql
  encoding: utf8
  database: my_database_test
  pool: 5
  timeout: 5000
  username: foo
  password: foobar
  host: localhost

I had this issue today deploying to a server where don't have direct access to the Postgres database. It's not the gems fault that Postgres requires these permissions to add the extension to the database.

You can either:

+1 for the command you shared, @guidomb. That's the only solution that worked for me.

@diogob diogob closed this Oct 20, 2014

I followed the instructions given here, however it kept leading to more errors such as:

ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied to create extension "uuid-ossp"

In the end what worked for me was running the following 2 commands (assuming your using a user called "postgres"):
psql -d postgres
ALTER USER postgres WITH SUPERUSER;

source: https://stackoverflow.com/questions/22135792/permission-denied-to-create-extension-uuid-ossp

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment