postgresql 关系被拒绝

Permission denied for relation

提问by Boban

I tried to run simple sql command:

我尝试运行简单的 sql 命令：

select * from site_adzone;

and I got this error

我收到了这个错误

ERROR:  permission denied for relation site_adzone

What could be the problem here?

这里可能有什么问题？

I tried also to do select for other tables and got same issue. I also tried to do this:

我也尝试为其他表做选择并遇到同样的问题。我也尝试这样做：

GRANT ALL PRIVILEGES ON DATABASE jerry to tom;

but I got this response from console

但我从控制台得到了这个响应

WARNING:  no privileges were granted for "jerry"

Do you have some idea what can be wrong?

你知道什么是错误的吗？

回答by Chris Travers

GRANT on the database is not what you need. Grant on the tables directly.

数据库上的 GRANT 不是您所需要的。直接在表上授予。

Granting privileges on the database mostly is used to grant or revoke connect privileges. This allows you to specify who may do stuff in the database if they have sufficient other permissions.

授予数据库权限主要用于授予或撤销连接权限。这允许您指定谁可以在数据库中执行操作，如果他们有足够的其他权限。

You want instead:

你想要：

 GRANT ALL PRIVILEGES ON TABLE side_adzone TO jerry;

This will take care of this issue.

这将处理这个问题。

回答by sag

Posting Ron E answer for grant privileges on all tables as it might be useful to others.

发布 Ron E 对所有表的授予权限的答案，因为它可能对其他人有用。

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jerry;

回答by user2757813

Connect to the right database first, then run:

首先连接到正确的数据库，然后运行：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jerry;

回答by Klanjabrik

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to jerry;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to jerry;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to jerry;

回答by isapir

To grant permissions to all of the existing tables in the schema use:

要授予架构中所有现有表的权限，请使用：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> TO <role>

To specify default permissions that will be applied to future tables use:

要指定将应用于未来表的默认权限，请使用：

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> 
  GRANT <privileges> ON TABLES TO <role>;

e.g.

例如

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO admin;

If you use SERIALor BIGSERIALcolumns then you will probably want to do the same for SEQUENCES, or else your INSERTwill fail (Postgres 10's IDENTITYdoesn't suffer from that problem, and is recommended over the SERIALtypes), i.e.

如果您使用SERIAL或BIGSERIAL列，那么您可能希望对 执行相同的操作SEQUENCES，否则您INSERT将失败（Postgres 10IDENTITY不会遇到该问题，并且建议在SERIAL类型上使用），即

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT ALL ON SEQUENCES TO <role>;

See also my answer to PostgreSQL Permissions for Web Appfor more details and a reusable script.

另请参阅我对PostgreSQL Permissions for Web App 的回答以获取更多详细信息和可重用脚本。

Ref:

参考：

GRANT

授予

ALTER DEFAULT PRIVILEGES

更改默认权限

回答by zond

1st and importantstep is connect to your db:

第一个也是重要的步骤是连接到您的数据库：

psql -d yourDBName

2 step, grant privileges

2 步，授予权限

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO userName;

回答by Bruce

This frequently happens when you create a table as user postgres and then try to access it as an ordinary user. In this case it is best to log in as the postgres user and change the ownership of the table with the command:

当您以 postgres 用户身份创建表，然后尝试以普通用户身份访问它时，经常会发生这种情况。在这种情况下，最好以 postgres 用户身份登录并使用以下命令更改表的所有权：

alter table <TABLE> owner to <USER>;

回答by Brian McCall

Make sure you log into psql as the owner of the tables. to find out who own the tables use \dt

确保您以表的所有者身份登录 psql。找出谁拥有这些表使用\dt

psql -h CONNECTION_STRING DBNAME -U OWNER_OF_THE_TABLES

psql -h CONNECTION_STRING DBNAME -U OWNER_OF_THE_TABLES

then you can run the GRANTS

然后你可以运行 GRANTS

回答by Sergi Ramón

As you are looking for select permissions, I would suggest you to grant only select rather than all privileges. You can do this by:

当您正在寻找选择权限时，我建议您只授予选择权限而不是所有权限。您可以通过以下方式执行此操作：

GRANT SELECT ON <table> TO <role>;

回答by happydmitry

You should:

你应该：

1. connect to the database by means of the DBeaver with postgres user
2. on the left tab open your database
3. open Roles tab/dropdown
4. select your user
5. on the right tab press 'Permissions tab'
6. press your schema tab
7. press tables tab/dropdown
8. select all tables
9. select all required permissions checkboxes (or press Grant All)
10. press Save

1. 使用 postgres 用户通过 DBeaver 连接到数据库
2. 在左侧选项卡上打开您的数据库
3. 打开角色选项卡/下拉菜单
4. 选择你的用户
5. 在右侧选项卡上按“权限选项卡”
6. 按您的架构选项卡
7. 按表格选项卡/下拉列表
8. 选择所有表
9. 选择所有必需的权限复选框（或按全部授予）
10. 按保存