Deploying to Heroku "ERROR: must be superuser to COPY to or from a file"

Question

I'm having an issue migrating my application's database after it has been pushed to Heroku. The part of the code that triggers the error is the following:

execute "COPY countries FROM '#{Rails.root}/db/migrate/Countries.txt' DELIMITER ',' CSV HEADER;"
execute "COPY regions FROM '#{Rails.root}/db/migrate/Regions.txt' DELIMITER ',' CSV HEADER;"
execute "COPY cities FROM '#{Rails.root}/db/migrate/Cities.txt' DELIMITER ',' CSV HEADER;"

Here is the error I'm getting:

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 countries FROM '/app/db/migrate/Countries.txt' DELIMITER ',' CSV HEADER; rake aborted! An error has occurred, this and all later migrations canceled:

So far, I have attempted to use "\copy" and "COPY FROM STDIN" as some of the old questions advised but keep getting syntax errors. If anyone could point me in the right direction, that would be great.

EDIT: Here are the questions I'm referencing. One:

I tried this:

execute "COPY countries FROM STDIN '#{Rails.root}/db/migrate/Countries.txt' DELIMITER ',' CSV HEADER;"

and this:

execute "COPY countries FROM '#{Rails.root}/db/migrate/Countries.txt' STDIN DELIMITER ',' CSV HEADER;"

Two:

I tried this:

execute \copy countries FROM STDIN '#{Rails.root}/db/migrate/Countries.txt' DELIMITER ',' CSV HEADER

Edit two:

Here was another attempt:

execute "COPY countries '#{Rails.root}/db/migrate/Countries.txt' FROM STDIN DELIMITER ',' CSV HEADER;"
execute "COPY regions '#{Rails.root}/db/migrate/Regions.txt' FROM STDIN DELIMITER ',' CSV HEADER;"
execute "COPY cities '#{Rails.root}/db/migrate/Cities.txt' FROM STDIN DELIMITER ',' CSV HEADER;"

The error I got from this was:

PG::SyntaxError: ERROR: syntax error at or near "'/app/db/migrate/Countries.txt'" LINE 1: COPY countries '/app/db/migrate/Countries.txt' FROM STDIN DE... ^ : COPY countries '/app/db/migrate/Countries.txt' FROM STDIN DELIMITER ',' CSV HEADER; rake aborted! An error has occurred, this and all later migrations canceled:

PG::SyntaxError: ERROR: syntax error at or near "'/app/db/migrate/Countries.txt'" LINE 1: COPY countries '/app/db/migrate/Countries.txt' FROM STDIN DE...

Edit 3:

I wasn't able to resolve the issue I was having but found a simpler solution--creating a local dump and uploading that to heroku using their importing tools. Which can be found here.

Answer 1

Realizing that the question is fairly old, I'll provide another answer for the benefit of future generations since it is the first query that shows up on Google. One easy solution I found is:

• grant the user SUPERUSER powers temporarily.

(Note that SUPERUSER is PostgreSQL flag and does not mean that you need to become system root user). You need to run the following in psql under postgres user:

alter user <username> superuser

If you do so you would not need to fight with \copy or STDIN imports anymore. This is a very quick and efficient shortcut (albeit a bit dangerous).

You can revoke the superuser privilege after you are done importing by doing:

alter user <username> nosuperuser

This solution is inspired by this answer.