PGError: ERROR: permission denied for relation (when using Heroku)

Question

I've recently gone through the database migration process as outlined here:

https://devcenter.heroku.com/articles/migrating-from-shared-database-to-heroku-postgres

Now I'm seeing a number of errors in the logs like this:

PGError: ERROR: permission denied for relation

Any ideas on what I should do to fix it?

Answer 1

I had a similar problem but the root cause was that my app was pointing to the old dev database which had exceeded it's limit of 10,000 rows.

Although I created a new Basic db and backed everything up, the app was still pointing the old dev DB.

heroku pg:info 

Check to see the rows: 10300/10000 (then you have a problem)

You will need to

1) Create new DB with more rows (Basic or the "Production" ones -> Heroku seems to be forcing an upgrade to make more money errrrrr)

2) backup the old DB using pgbackups: heroku pg:backups:capture SMALL_DB_NAME

3) restore the backup to the new DB: heroku pg:backups:restore BACKUP_ID BIG_DB_NAME (see links below for more details)

4) PROMOTE the new DB to the primary for the app: heroku pg:promote BIG_DB_NAME

can always utilize:

heroku maintenance:on (to disable the app while updating)

heroku maintenance:off

heroku pg:info (to check the status)

If this is the problem you may want to check out: https://devcenter.heroku.com/articles/heroku-postgres-starter-tier https://devcenter.heroku.com/articles/migrating-from-shared-database-to-heroku-postgres

Answer 2

UPDATE: Ashton's answer nails it in this situation, which is very Heroku specific. If you found this from a search for PostgreSQL error messages or problems but are not using Heroku, please look for other questions more likely to apply to your situation.

---

At a guess, the PostgreSQL user ID you're connecting with is not the owner of your tables, and you haven't issued any explicit GRANT statements to give it access to them. Without seeing exactly what you ran when you migrated it's hard to say more - and Heroku hides many of the internals anyway.

Let's work out what the current situation is. Try connecting with psql and running:

\dp the_problem_table 

and show the permisions reported. Also show the result of:

SHOW current_user; 

run from psql and when run as an SQL query from inside your application.

Edit your question to add that information and the full, exact text of the error message you get.