database PGError：错误：关系的权限被拒绝（使用 Heroku 时）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11930996/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PGError: ERROR: permission denied for relation (when using Heroku)
提问by suttree
I've recently gone through the database migration process as outlined here:
我最近完成了此处概述的数据库迁移过程：
https://devcenter.heroku.com/articles/migrating-from-shared-database-to-heroku-postgres
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
PGError：错误：关系的权限被拒绝
Any ideas on what I should do to fix it?
关于我应该做些什么来解决它的任何想法？
回答by Ashton Thomas
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.
我遇到了类似的问题，但根本原因是我的应用程序指向的旧开发数据库已超过 10,000 行的限制。
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)
检查以查看行：10300/10000（那么您有问题）
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
您将需要
1）创建具有更多行的新数据库（基本或“生产”行 -> Heroku 似乎正在强制升级以赚取更多钱 errrrrr）
2）使用 pgbackups 备份旧数据库：heroku pg:backups:capture SMALL_DB_NAME
3）将备份恢复到新数据库：（heroku pg:backups:restore BACKUP_ID BIG_DB_NAME有关更多详细信息，请参见下面的链接）
4）将新数据库推广到应用程序的主数据库：heroku pg:promote BIG_DB_NAME
can always utilize:
总是可以利用：
heroku maintenance:on(to disable the app while updating)
heroku maintenance:on（在更新时禁用应用程序）
heroku maintenance:off
heroku maintenance:off
heroku pg:info(to check the status)
heroku pg:info（检查状态）
If this is the problem you may want to check out: https://devcenter.heroku.com/articles/heroku-postgres-starter-tierhttps://devcenter.heroku.com/articles/migrating-from-shared-database-to-heroku-postgres
如果这是您可能想要查看的问题：https: //devcenter.heroku.com/articles/heroku-postgres-starter-tier https://devcenter.heroku.com/articles/migrating-from-shared-database -to-heroku-postgres
回答by Craig Ringer
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 notusing Heroku, please look for other questions more likely to apply to your situation.
更新：Ashton 的回答说明了这种情况，这是 Heroku 特有的。如果您在搜索 PostgreSQL 错误消息或问题时发现了这一点，但没有使用 Heroku，请查找更可能适用于您的情况的其他问题。
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 GRANTstatements to give it access to them. Without seeing exactlywhat you ran when you migrated it's hard to say more - and Heroku hides many of the internals anyway.
据猜测，您所连接的 PostgreSQL 用户 ID 不是您的表的所有者，并且您没有发出任何显式GRANT语句来授予它访问权限。如果没有看到迁移时运行的确切内容，很难说更多 - Heroku 无论如何都隐藏了许多内部结构。
Let's work out what the current situation is. Try connecting with psqland running:
让我们弄清楚目前的情况。尝试连接psql并运行：
\dp the_problem_table
and show the permisions reported. Also show the result of:
并显示报告的权限。还显示以下结果：
SHOW current_user;
run from psqland when run as an SQL query from inside your application.
从psql应用程序内部作为 SQL 查询运行和何时运行。
Edit your question to add that information and the full, exact text of the error message you get.
编辑您的问题以添加该信息以及您收到的错误消息的完整准确文本。
回答by tomaszbak
Steps based on Ashton's answer to upgrade from Dev (10k rows limit) to Basic (10M rows limit)
基于 Ashton 从 Dev（10k 行限制）升级到 Basic（10M 行限制）的回答的步骤
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
如果你没有 pgbackups
heroku addons:add pgbackups
backup database and get backup id
备份数据库并获取备份 ID
heroku pg:backups:capture
add db with web interface
使用 Web 界面添加数据库
- login to https://addons.heroku.com
- search "Heroku Postgres"
- select plan and application
- add it
- 登录https://addons.heroku.com
- 搜索“Heroku Postgres”
- 选择计划和申请
- 添加它
view heroku config, you should see new db URL
查看 heroku 配置，你应该看到新的数据库 URL
heroku config --remote heroku
restore backup to new db
将备份恢复到新数据库
heroku pg:backups:restore BACKUP_ID NEW_DB_URL
change DATABASE_URL
更改 DATABASE_URL
heroku pg:promote NEW_DB_URL
enable the app and workers
启用应用程序和工作人员
heroku maintenance:off
heroku ps:scale worker=1
回答by manish_s
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:infoafter 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 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
回答by user991562
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
这个答案解释了如何列出每个表中的行数：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!
您可以通过终端进入 psql 控制台，连接设置列在您的应用程序的 heroku 仪表板上！
回答by Rapekaz
I had the similar problem on my Redmine application:
我的 Redmine 应用程序遇到了类似的问题：
PG::InsufficientPrivilege: ERROR: permission denied for relation settings
: SELECT "settings".* FROM "settings" WHERE "settings"."name" = 'plugin_redmine_wktime' LIMIT 1
My steps were:
我的步骤是：
- I made a backup of old Redmine application and database.
- I deployed a new version of Redmine - it worked perfectly
- I restored old Redmine as a development server and I got an error when I tried to access the main webpage.
- 我备份了旧的 Redmine 应用程序和数据库。
- 我部署了一个新版本的 Redmine - 它运行良好
- 我将旧的 Redmine 恢复为开发服务器，但在尝试访问主网页时出现错误。
The cause of my problem was just in wrong username in old Redmine's config/database.yml
我的问题的原因只是在旧 Redmine 的 config/database.yml 中的用户名错误
