本文翻译自:Permission denied for relation
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? 你知道什么是错的吗?
#1楼
参考:https://stackoom.com/question/137yp/关系允许被拒绝
#2楼
GRANT on the database is not what you need. 数据库上的GRANT不是您所需要的。 Grant on the tables directly. 直接在表格上授予权利。
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. 这将解决这个问题。
#3楼
发布Ron E回答所有表的授予权限,因为它可能对其他人有用。
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jerry;
#4楼
首先连接到正确的数据库 ,然后运行:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jerry;
#5楼
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>;
eg 例如
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO admin;
If you use SERIAL
or BIGSERIAL
columns then you will probably want to do the same for SEQUENCES
, or else your INSERT
will fail ( Postgres 10's IDENTITY
doesn't suffer from that problem, and is recommended over the SERIAL
types), ie 如果您使用SERIAL
或BIGSERIAL
列,那么您可能希望对SEQUENCES
执行相同的操作,否则您的INSERT
将失败( Postgres 10的IDENTITY
不会遇到该问题,建议使用SERIAL
类型),即
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT ALL ON SEQUENCES TO <role>;
See also my answer to PostgreSQL Permissions for Web App for more details and a reusable script. 有关更多详细信息和可重用脚本,另请参阅我对Web应用程序的PostgreSQL权限的答案。
Ref: 参考:
ALTER DEFAULT PRIVILEGES 更改默认权限
#6楼
1st and important step is connect to your db: 第一个重要的步骤是连接到您的数据库:
psql -d yourDBName
2 step, grant privileges 2步,授予权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO userName;