Error restoring postgres database backup

Question

Hi everyone. I've setup a system to automatically download and store a db dump from my Heroku deployed rails app. Everything is working great but when I download the dump and restore it to my local postgres server then try and run my local app off that restored database I get this error

ActiveRecord::StatementInvalid in HomeController#index

PGError: ERROR:  permission denied for relation users : SELECT     "users".* FROM       "users"  WHERE     ("users"."id" = 1) LIMIT 1

Anyone have any suggestions on what that could be? I've checked my postgres permissions and all tables and the database itself belong to the postgres user. I've tried GRANT ALL as well with no success.

Answer 1

Most probably the PostgreSQL user you are using is different one that is owner of this table. I suppose you have different user on that database and on this one?

Answer 2

Go through your pgdump.sql files and remove the lines after each CREATE TABLE statement that say something like:

ALTER TABLE public.users OWNER TO eqrunyvndu;

Then run your restore from that and it should work. Those lines change the owner of the tables to your heroku app's autogenerated db username, which is meaningless locally, so you can just remove them.

I'd also recommend greping for any other occurrences of that username and removing those too.