PostgreSQL 权限解释
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25691037/
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
PostgreSQL permissions explained
提问by ddreian
Please explain the output of the \zcommand in PostgreSQL. I understand the permission, I read the documentation, but somehow I missed the interpretation of the output of \z.
请解释该\z命令在 PostgreSQL 中的输出。我理解许可，我阅读了文档，但不知何故我错过了\z.
datastore_default=> \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-----------------+-------+-----------------------------------+--------------------------
public | _table_metadata | view | ckan_default=arwdDxt/ckan_default+|
| | | datastore_default=r/ckan_default +|
| | | readonlyuser=r/ckan_default +|
public | foo | table | ckan_default=arwdDxt/ckan_default+|
| | | datastore_default=r/ckan_default +|
| | | readonlyuser=r/ckan_default +|
Somehow readonlyuserseems to be able to read tables fooand _foo but in practice it cannot. Both commands return an error:
不知何故readonlyuser似乎能够读取表foo和 _foo 但实际上它不能。这两个命令都返回错误：
sudo -u postgres psql -d datastore_default -U readonlyuser -c 'SELECT * FROM foo'
sudo -u postgres psql -d datastore_default -U readonlyuser -c 'SELECT * FROM public.foo'
ERROR: permission denied for schema public
LINE 1: SELECT * FROM public.foo
Edit: apparently I had a poor understanding of how database and schema permissions work. First of all only the db admin (user postgres) or the owner of the database (in my case user ckan_default) can grant other users privileges on a specific database. The schema is only at a database level, so it's ok that I added readonlyuserthe permission to see the public schema, it cannot select from other databases anyway.
编辑：显然我对数据库和架构权限的工作方式了解甚少。首先，只有 db admin（用户 postgres）或数据库的所有者（在我的例子中是用户 ckan_default）可以授予其他用户对特定数据库的权限。模式只是在数据库级别，所以我添加readonlyuser了查看公共模式的权限是可以的，无论如何它不能从其他数据库中选择。
回答by Eelke
The error says permission denied for schema public(emphasis mine)
该错误表示架构公共权限被拒绝（强调我的）
You need to give readonlyuser rights on schema public:
您需要在架构 public 上授予 readonlyuser 权限：
GRANT USAGE ON SCHEMA public TO readonlyuser;
The contents of the ACL is explained on this page. The most relevant part quoted here:
本页解释了 ACL 的内容。此处引用的最相关部分：
rolename=xxxx -- privileges granted to a role =xxxx -- privileges granted to PUBLIC
r -- SELECT ("read") w -- UPDATE ("write") a -- INSERT ("append") d -- DELETE D -- TRUNCATE x -- REFERENCES t -- TRIGGER X -- EXECUTE U -- USAGE C -- CREATE c -- CONNECT T -- TEMPORARY arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects) * -- grant option for preceding privilege /yyyy -- role that granted this privilege
rolename=xxxx -- 授予角色的权限 =xxxx -- 授予 PUBLIC 的权限
r -- SELECT ("read") w -- UPDATE ("write") a -- INSERT ("append") d -- DELETE D -- TRUNCATE x -- REFERENCES t -- TRIGGER X -- EXECUTE U -- USAGE C -- CREATE c -- CONNECT T -- TEMPORARY arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects) * -- grant option for preceding privilege /yyyy -- role that granted this privilege
The + are part of the way psql formats the result, they are not part of the value.
+ 是 psql 格式化结果的方式的一部分，它们不是值的一部分。
