Ruby-on-rails PG::InsufficientPrivilege: 错误: 关系 schema_migrations rake db:create 的权限被拒绝

PG::InsufficientPrivilege: ERROR: permission denied for relation schema_migrations rake db:create

提问by khoamle

I'm trying to create the database in Rails. In postgres I see the development and test database, however i'm getting a permissions error. I've tried to follow this link, didn't work for me.

我正在尝试在 Rails 中创建数据库。在 postgres 中，我看到了开发和测试数据库，但是我收到了权限错误。我试图按照这个链接，对我不起作用。

Error: PG::InsufficientPrivilege: ERROR: permission denied for relation schema_migrations : SELECT "schema_migrations".* FROM "schema_migrations"

错误： PG::InsufficientPrivilege: ERROR: permission denied for relation schema_migrations : SELECT "schema_migrations".* FROM "schema_migrations"

Rails: permission denied for relation schema_migrations

Rails：关系 schema_migrations 的权限被拒绝

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  host: localhost
  username: root
  password:

development:
  <<: *default
  database: svp-chicago_development

I log into postgres and did these commands.

我登录 postgres 并执行这些命令。

psql postgres
CREATE USER root
CREATE DATABASE svp-chicago_development
GRANT ALL PRIVILEGES ON DATABASE svp-chicago_development to root
ALTER DATABASE svp-chicago_development OWNER TO root

When I do \listI see the database is there.

当我这样做时，\list我看到数据库在那里。

采纳答案by Khanh Pham

I guess you missed create passwordfor your user. Try to create password as following:

我猜你错过了password为你的user. 尝试创建密码如下：

CREATE USER root WITH PASSWORD 'your_new_password';
CREATE DATABASE svp-chicago_development;
GRANT ALL PRIVILEGES ON DATABASE svp-chicago_development to root;
ALTER DATABASE svp-chicago_development OWNER TO root;

回答by ohkts11

I had same issue and I solved by adding "Superuser" to the role.

我遇到了同样的问题，我通过向角色添加“超级用户”来解决。

First, list users and their privileges. If you followed above commands, root user does not have "Superuser" Attributes.

首先，列出用户及其权限。如果您遵循上述命令，root 用户没有“超级用户”属性。

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 other     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      |                                                            | {}

Next, upgrade root to be a "Superuser".

接下来，将 root 升级为“超级用户”。

postgres=# ALTER USER root WITH SUPERUSER;
ALTER ROLE

Again, list users and their privileges. Now root has "Superuser".

再次列出用户及其权限。现在 root 有“超级用户”。

postgres=# \du
                               List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 other     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      | Superuser                                                  | {}

Hope it helps.

希望能帮助到你。

回答by Steve

Also, if you're using a service like Heroku, it's worth checking to see if you have overrun your row limit and write access has been revoked in the database.

此外，如果您使用的是 Heroku 之类的服务，则值得检查您是否超出了行限制并且数据库中的写访问权限已被撤销。

You can do that by going to the dashboard, click on the app, click on the postgres service icon, then check the row limit.

您可以通过转到仪表板，单击应用程序，单击 postgres 服务图标，然后检查行限制来执行此操作。

回答by ddreliv

Just in case someone else comes here with the same issue, I did try many other solutions and the one that worked for me the best was the following: Modify OWNER on all tables simultaneously in PostgreSQL

以防万一其他人遇到同样的问题，我确实尝试了许多其他解决方案，其中最适合我的是以下解决方案：在 PostgreSQL 中同时修改所有表上的 OWNER

• This worked since my user (e.g. rootor postgres) had Superuserprivileges so trying REASSIGN OWNEDgives error when trying to assign systemobjects
• ALTER DATABASEdidn't work since the issue is on a table object ownership and not in the DB ownership. Altering the owner on the DB doesn't propagate to the other object on that DB schema

• 这是有效的，因为我的用户（例如root或postgres）具有Superuser特权，因此在尝试分配对象时尝试REASSIGN OWNED会出错system
• ALTER DATABASE不起作用，因为问题在于表对象所有权而不是数据库所有权。更改数据库上的所有者不会传播到该数据库架构上的其他对象

回答by Kevin Cooper

Try listing your tables to see who the owner is. In my case, I had imported data via psql < dump.sqland all the imported tables were owned by postgresinstead of my user.

尝试列出您的桌子以查看所有者是谁。在我的情况下，我通过导入数据psql < dump.sql并且所有导入的表都由postgres我的用户而不是我的用户拥有。

To check this, start psqland enter the command \dtwithin your database. Look for the following line:

要检查这一点，请在您的数据库中启动psql并输入命令\dt。查找以下行：

public | schema_migrations | table | postgres

Not good! It's owned by postgresand you'll need to fix that:

不好！它归所有者所有postgres，您需要解决这个问题：

1. You can use @ddreliv's solutionhere to reassign the owner of every table, or
2. if you have a dump, simply drop the database and re-import it with the proper user. For example, use sudo -u my_user psql < dump.sqlinstead of sudo -u postgres psql < dump.sql.

1. 您可以在此处使用@ddreliv 的解决方案重新分配每个表的所有者，或者
2. 如果您有转储，只需删除数据库并使用适当的用户重新导入它。例如，使用sudo -u my_user psql < dump.sql代替sudo -u postgres psql < dump.sql。