postgresql 错误：在以只读用户身份尝试 SELECT 时，Postgres 上的关系表名的权限被拒绝
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13497352/
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
ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
提问by sorin
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
The readonly user can connect, see the tables but when it tries to do a simple select it gets:
只读用户可以连接，查看表格，但是当它尝试进行简单的选择时，它会得到：
ERROR: permission denied for relation mytable
SQL state: 42501
This is happening on PostgreSQL 9.1
这发生在 PostgreSQL 9.1
What I did wrong?
我做错了什么？
回答by sorin
Here is the complete solution for PostgreSQL 9+, updated recently.
这是最近更新的 PostgreSQL 9+ 的完整解决方案。
CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonly';
GRANT USAGE ON SCHEMA public to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
-- repeat code below for each database:
GRANT CONNECT ON DATABASE foo to readonly;
\c foo
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; --- this grants privileges on new tables generated in new database "foo"
GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
Thanks to https://jamie.curle.io/creating-a-read-only-user-in-postgres/for several important aspects
感谢https://jamie.curle.io/creating-a-read-only-user-in-postgres/的几个重要方面
If anyone find shorter code, and preferably one that is able to perform this for all existing databases, extra kudos.
如果有人找到更短的代码，最好是能够为所有现有数据库执行此操作的代码，那么额外的荣誉。
回答by sufleR
Try to add
尝试添加
GRANT USAGE ON SCHEMA public to readonly;
You probably were not aware that one needs to have the requisite permissions to a schema, in order to use objects in the schema.
您可能不知道需要对架构具有必要的权限才能使用架构中的对象。
回答by Dhwani Shah
This worked for me:
这对我有用：
Check the current role you are logged into by using: SELECT CURRENT_USER, SESSION_USER;
使用以下命令检查您登录的当前角色：SELECT CURRENT_USER, SESSION_USER;
Note: It must match with Owner of the schema.
注意：它必须与架构的所有者匹配。
Schema | Name | Type | Owner
--------+--------+-------+----------
架构 | 姓名 | 类型 | 业主
--------+--------+-------+---------
If the owner is different, then give all the grants to the current user role from the admin role by :
如果所有者不同，则通过以下方式将所有权限从管理员角色授予当前用户角色：
GRANT 'ROLE_OWNER' to 'CURRENT ROLENAME';
授予 'ROLE_OWNER' 到 'CURRENT ROLENAME';
Then try to execute the query, it will give the output as it has access to all the relations now.
然后尝试执行查询，它会给出输出，因为它现在可以访问所有关系。
回答by PuN1sh3r
make sure your user has attributes on its role. for example:
确保您的用户具有其角色的属性。例如：
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
flux | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
after performing the following command:
执行以下命令后：
postgres=# ALTER ROLE flux WITH Superuser;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
flux | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication | {}
it fixed the problem.
它解决了问题。
see tutorial for roles and stuff here: https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2
请参阅此处的角色和内容教程：https: //www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2
回答by yesy
You should execute the next query:
您应该执行下一个查询：
GRANT ALL ON TABLE mytable TO myuser;
Or if your error is in a view then maybe the table does not have permission, so you should execute the next query:
或者，如果您的错误出现在视图中，那么该表可能没有权限，因此您应该执行下一个查询：
GRANT ALL ON TABLE tbm_grupo TO myuser;
