12

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)

Aniket Tiwari
  • 3,561
  • 4
  • 21
  • 61
Victoria S.
  • 549
  • 1
  • 6
  • 20

2 Answers2

11

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!

Olivier
  • 696
  • 4
  • 12
  • I cannot change the database.yml file, unfortunately. If the other team got things running with this file, it should be possible. Any idea If making an empty user is possible? – Victoria S. Nov 19 '15 at 15:08
  • May be they were using solution like capistrano to deploy the code. You should take a look on the great Railscasts about setup a VPS for a Rails app http://railscasts.com/episodes/335-deploying-to-a-vps – Olivier Nov 19 '15 at 15:21
  • I forgot why, but I tried `GRANT ALL PRIVILEGES ON DATABASE whiteboard TO nobody` and now the application runs! Apparently somewhere I made a user nobody for apache. I'll look into the link you posted as well, It'll probably be a much better solution. – Victoria S. Nov 19 '15 at 15:32
  • Good to here, even if I'm not sure it's the best way to have a bullet proof environment. I'd suggest you to learn more about how to setup a production environment, Capistrano is quiet simple to learn and can be a game changer for you, even if it's not the only choice you have, it's used by a lot of people in production. – Olivier Nov 19 '15 at 15:41
  • I did this and my postgres keeps on giving this same error. Could you please take a look at [link](http://stackoverflow.com/questions/41424654/postgres-permission-denied-for-relation-schema-migrations) – Code-MonKy Jan 04 '17 at 09:26
0

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

PHZ.fi-Pharazon
  • 1,479
  • 14
  • 15