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

Question

I recently went through a database migration process as described here:

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

Now I see a few errors in the logs:

PGError: ERROR: permission denied for relationship

Any ideas on what I should do to fix this?

Answer 1

I had a similar problem, but the main reason was because my application was pointing to an old dev database that exceeded the 10,000 row limit.

Although I created a new base database and supported everything, the application still pointed to the old dev DB.

heroku pg: info

Look at the lines: 10300/10000 (then you have a problem)

You will need

1) Create a new database with a lot of rows (Basic or "Production") → Heroku seems to make you upgrade to make more money errrrrr)

2) backing up the old database using pgbackups: heroku pgbackups:capture SMALL_DB_NAME

3) restore the backup of the new database: heroku pgbackups:restore BIG_DB_NAME BACKUP_ID (for details, see the links below)

4) PROMOTE the new primary database for the application: heroku pg:promote BIG_DB_NAME

you can always use:

heroku maintenance: on (to disable the application when updating)

hero service: off

heroku pg: info (to check status)

If this is a problem, you can check: 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 calls it in this situation, which is very specific to Heroku. If you find this from looking for PostgreSQL error messages or problems, but don’t use Heroku, look at other questions that are more likely for your situation.

---

Guess that the PostgreSQL user ID you are connecting to does not own your tables, and you have not published any explicit GRANT statements to give them access to them. Not seeing exactly what you were running when you migrated a lot to say more - and Geroku still hides many of the internal elements.

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

 \dp the_problem_table 

and show the allowed messages. Also show the result:

 SHOW current_user; 

runs from psql and runs as an SQL query from your application.

Modify your question to add this information and the full, accurate error message text.

Answer 3

Steps based on Ashton's answer to upgrade from Dev (10k line limit) to Basic (10M line limit)

check db string chased limit

 heroku pg:info 

disable the application and workers to ensure no db changes during db update

 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 pgbackups:capture 

add db with web interface

• login to https://addons.heroku.com
• search "Heroku Postgres"
• select plan and application
• add it

view the heroku configuration, you should see the 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 application and workers

 heroku maintenance:off heroku ps:scale worker=1 

Answer 4

After removing extra rows, you won’t immediately get insert privileges. In this case, remove the extra lines and just run heroku pg:info after that. This will update the privileges of your database, and you will get access in a few minutes. It does not need to clone the existing database into a new one and install the new one as your application database.

 $ 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 

Answer 5

I was in a situation where I exceeded the limit of the number of rows.

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

Very valuable to know.

You can enter the psql console through the terminal, the connection parameters are listed on the heroku toolbar for your application!

Answer 6

I had a similar problem in 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:

• I made a backup of my old Redmine application and database.
• I deployed a new version of Redmine - it worked great
• I restored the old Redmine as a development server and got an error when I tried to access the main web page.

The cause of my problem was an invalid username in the old Redmine / database.yml configuration