PGError: ERROR: permission denied for relation (wh

问题:

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?

回答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 pgbackups:capture SMALL_DB_NAME

3) restore the backup to the new DB: heroku pgbackups:restore BIG_DB_NAME BACKUP_ID (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

回答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.

回答3:

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 dont have pgbackups

heroku addons:add pgbackups

backup database and get backup id

heroku pgbackups:capture

add db with web interface

1. login to https://addons.heroku.com
2. search "Heroku Postgres"
3. select plan and application
4. add it

view heroku config, you should see new db URL

heroku config --remote heroku

restore backup to new db

heroku pgbackups:restore NEW_DB_URL BACKUP_ID

change DATABASE_URL

heroku pg:promote NEW_DB_URL

enable the app and workers

heroku maintenance:off
heroku ps:scale worker=1

回答4:

After deleting the extra rows, you won't get the insert privileges back immediately. In that case, delete the extra rows and just run heroku pg:info after that. This will refresh the privileges of your DB and you will get the access back in few minutes. Its not required to clone the existing DB into a new one and setting the new one as the DB of your app.

$ heroku pg:info

=== HEROKU_POSTGRESQL_BRONZE_URL, DATABASE_URL
Plan:        Hobby-dev
Status:      Available
Connections: 3/20
PG Version:  9.3.6
Created:     2014-03-01 13:47 UTC
Data Size:   1.25 GB
Tables:      4
Rows:        2098/10000 (Write access revoked) - refreshing
Fork/Follow: Unsupported
Rollback:    Unsupported
Add-on:      grinning-busily-5587

回答5:

I was in a situation where I exceeded row count limit.

This answer explained how to list number of rows in each table: http://stackoverflow.com/questions/12701711/heroku-row-count-incorrect

Very valuable to know.

You can get into the psql console through terminal, the connection settings are listed on heroku dashboard for your app!

回答6:

I had the similar problem on my Redmine application:

PG::InsufficientPrivilege: ERROR:  permission denied for relation settings
: SELECT  "settings".* FROM "settings"  WHERE "settings"."name" = 'plugin_redmine_wktime' LIMIT 1

My steps were:

1. I made a backup of old Redmine application and database.
2. I deployed a new version of Redmine - it worked perfectly
3. I restored old Redmine as a development server and I got an error when I tried to access the main webpage.

The cause of my problem was just in wrong username in old Redmine's config/database.yml