Copying to PostgreSQL database from a CSV on a server

Question

I am trying to update a PostgreSQL database with a CSV file that I am downloading to the server. I have tried for many hours and it works perfectly locally but cannot get it to work on the server. Here is my command:

path = "/data/reporting/releases/20150202181737/data/storyboards.csv"

sql = "COPY storyboards (id, name, category, service_line, account_id, account_name, account_salesforce_id, banner_image) FROM \'#{path}\' DELIMITER ',' CSV;"

ActiveRecord::Base.connection.execute(sql)

Here is the error message:

COPY storyboards (id, name, category, service_line, account_id, account_name, account_salesforce_id, banner_image) FROM '/data/reporting/releases/20150202181737/data/storyboards.csv' DELIMITER ',' CSV;
PG::InsufficientPrivilege: ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
: COPY storyboards (id, name, category, service_line, account_id, account_name, account_salesforce_id, banner_image) FROM '/data/reporting/releases/20150202181737/data/storyboards.csv' DELIMITER ',' CSV;
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
: COPY storyboards (id, name, category, service_line, account_id, account_name, account_salesforce_id, banner_image) FROM '/data/reporting/releases/20150202181737/data/storyboards.csv' DELIMITER ',' CSV;

Can you recommend a solution for this issue as I'm sure I am not the only one who has tried to do this. I am hosting my app on Engineyard. Is this an Engineyard configuation issue?

Answer 1

If you want to copy data from a CSV that's client-side to the server, then there should be no problem. You can do this (outside of Ruby entirely if you want, or you could shell out from within Ruby) using the psql client and its \copy command. Since it operates off the local file system with, you wouldn't have any issues so long as you can access the local file path.

For copying a CSV on the server, you have to be a superuser, as the error indicates.

Based on this answer, it would appear that the postgres user is configured to be a superuser in Engineyard, so if you use that user, you should be fine.

This would require a separate connection from your normal app connection in Rails, since that would presumably not be using the postgres user, but whatever user you created for your app (since your app should not generally run with superuser privileges for a variety of reasons).

Unless this is part of some sort of automated workflow that has to be server-side, I would recommend just using psql from your box with \copy.