PGError: ERROR: permission denied for relation (when using Heroku)
Steps based on Ashton's answer to upgrade from Dev (10k rows limit) to Basic (10M rows limit)
check db rows exceded limit
heroku pg:info
disable the app and workers to ensure no db changes during db upgrade
heroku maintenance:on
heroku ps:scale worker=0
if you don't have pgbackups
heroku addons:add pgbackups
backup database and get backup id
heroku pg:backups:capture
add db with web interface
- login to https://addons.heroku.com
- search "Heroku Postgres"
- select plan and application
- add it
view heroku config, you should see new db URL
heroku config --remote heroku
restore backup to new db
heroku pg:backups:restore BACKUP_ID NEW_DB_URL
change DATABASE_URL
heroku pg:promote NEW_DB_URL
enable the app and workers
heroku maintenance:off
heroku ps:scale worker=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
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.