CODEWITHSUNDEEP
ruby-on-railspostgresqlapache2
Victoria S.
Victoria S.

Reputation: 559

Rails: permission denied for relation schema_migrations

I'm trying to setup a local production environment for a Ruby on Rails web application. I can run the application with rails server command, which gives the development environment.

The production environment I'm trying to set up is purely local and I've followed this tutorial for setting it up with apache 2: https://www.digitalocean.com/community/tutorials/how-to-setup-a-rails-4-app-with-apache-and-passenger-on-centos-6

However when I go to the page of my application I get the following error:

PG::InsufficientPrivilege: ERROR: permission denied for relation schema_migrations : SELECT "schema_migrations".* FROM "schema_migrations"

in my database.yml I have these settings for development and production:

adapter: postgresql
database: whiteboard
username:
password:
pool: 5
timeout: 5000

I'm not allowed to change these settings, no matter what.

Is there any way to fix this? (if yes, step by step please)

Upvotes: 13

Views: 18178

Answers (2)

PHZ.fi-Pharazon
PHZ.fi-Pharazon

Reputation: 2121

I was using dbmate which also creates a table called schema_migrations on startup, and thus fails when a full dump is applied that also has the same table. Here are a few approaches

  1. PostgreSQL doesn't support creating dump with IF NOT EXISTS, unlike mysql:

See Can pg_dump be instructed to create tables with "IF NOT EXISTS"?

  1. However, PostgreSQL 9.1 and newer supports CREATE TABLE IF NOT EXISTS -syntax, so you could string replace the dump for example by using sed:

    sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' dump.sql

  2. But in practice, I ended to delete the lines from the dump with schema_migrations on it, and +1-2 lines following:

    sed -i '/CREATE TABLE public.schema_migrations (/,+2 d' dump.sql

    sed -i '/ALTER TABLE ONLY public.schema_migrations/,+1 d' dump.sql

Upvotes: 0

Olivier
Olivier

Reputation: 696

It seems you have to create a DB user with all needed privileges on your DB. For example I think you could do the trick by log in your DB console then do something like:

CREATE USER your_new_username WITH PASSWORD 'your_new_password';
CREATE DATABASE whiteboard;
GRANT ALL PRIVILEGES ON DATABASE whiteboard to your_new_username;
ALTER DATABASE whiteboard OWNER TO your_new_username;

Then update you database.yml like this:

adapter: postgresql
database: whiteboard
username: your_new_username
password: your_new_password
pool: 5
timeout: 5000

Hope it helps!

Upvotes: 11

Related Questions