Search code examples
ruby-on-railsdatabasepostgresqlherokunavicat

ERROR: permission denied for relation after Heroku pg_dump and import to development database

I am pretty new to heroku & postgresql. I am trying to dump my production database into my local development database in my local machine.

I dumped like this (my actual info redacted):

pg_dump --host=<myhost> --port=<port> --username=<username> --password --dbname=app_production  > output.sql

Then I imported to my local app_development database like this:

psql -d app_development  -f output.sql

but now, when I start my server I get this:

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



I also use Navicat to see the local database, and now I can't open any of my tables. Each time I try I get 

ERROR:  permission denied for relation <nameofwhatevertable>

How can I reset permissions for my app_development database when I've dumped my app_production database.

Solution

  • It seems like your db permissions have been mangled. Do you have PgAdmin III installed? http://www.pgadmin.org/download/

    PgAdmin III will let you reset all db and user parameters. It will also allow you to see what is going on in your database(s) and tables so you can debug them.

    It's a PostGreSQL development tool that is very much like MySQL Workbench. Free download from PostGreSQL. Runs on multiple platforms. Easy to set up.

    It is most handy in cases like this, when you're trying to track down an intractable error. Highly recommended tool. Takes the guessing out of pg work.