PostgreSQL 8.4 将所有表的 DML 权限授予角色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11599533/
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 8.4 grant DML privileges on all tables to a role
提问by pointyhat
How do I go about granting DML (SELECT,INSERT,UPDATE,DELETE) on all tables in a schema in PostgreSQL 8.4? I'd also like this grant to persist for new table creation in the future as well.
如何在 PostgreSQL 8.4 中的模式中的所有表上授予 DML（SELECT、INSERT、UPDATE、DELETE）？我还希望此赠款在未来也能用于新表的创建。
I've seen solutions for 9.0 but I'm stuck with 8.4 as it ships with Debian stable.
我已经看到了 9.0 的解决方案，但我坚持使用 8.4，因为它随附 Debian 稳定版。
I have tried the following as a baseline but it doesn't work, resulting in the inevitable "access to relation X denied":
我已经尝试了以下作为基准，但它不起作用，导致不可避免的“访问关系 X 被拒绝”：
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
I've dredged through the documentation and I can't seem to find a suitable solution.
我已经仔细阅读了文档，但似乎找不到合适的解决方案。
回答by A.H.
I'd also like this grant to persist for new table creation in the future as well. [...] I've dredged through the documentation and I can't seem to find a suitable solution.
我还希望此赠款在未来也能用于新表的创建。[...] 我已经仔细阅读了文档，但似乎找不到合适的解决方案。
Because before 9.0 there is none. All you can get is to set the permissions for existingtables. You have to do one GRANTfor each table, because before 9.0 there was no "bulk" mode. See the SQL grammer for 8.4and 9.0:
因为在 9.0 之前没有。您所能得到的只是为现有表设置权限。你必须GRANT为每个表做一个，因为在 9.0 之前没有“批量”模式。请参阅8.4和9.0的 SQL 语法：
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
and 9.0 here:
和 9.0 在这里：
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
The new ALL TABLES IN SCHEMApart is the one you are missing.
新的ALL TABLES IN SCHEMA部分是您缺少的部分。
Also: Setting permissions on the database level as in you question won't help you: You will "only" set the permissions on he database, but not on any "contained" stuff like tables. The relevant section:
另外：在您的问题中设置数据库级别的权限对您没有帮助：您将“仅”设置数据库的权限，而不是任何“包含”的东西，如表。相关部分：
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
Which means you can only set CREATE, CONNECTand TEMPpermissions on the database itself but no SELECT, INSERTetc.
这意味着你只能设置CREATE，CONNECT以及TEMP对数据库本身的权限，但没有SELECT，INSERT等等。
So far for the bad stuff. What you can doare the following things:
到目前为止，坏事。你可以做的是以下几件事：
Reduce the number of permission management by granting rights not to users but to roles. Then add roles to individual users. When a new table is created you only need to adjust one or two roles, but not hundreds of users.
Query the system catalogues and create appropriate
GRANTcommands. Save them into a file and execute that file. This should give you an easier startup.
通过不向用户而是向角色授予权限来减少权限管理的数量。然后为单个用户添加角色。创建新表时，您只需调整一两个角色，而不需要调整数百个用户。
查询系统目录并创建适当的
GRANT命令。将它们保存到一个文件中并执行该文件。这应该会让你更容易启动。
Such a query might look like this:
这样的查询可能如下所示：
select 'GRANT ALL ON ' || table_schema || '.' || table_name ||' to my_group;'
from information_schema.tables
where
table_type = 'BASE TABLE' and
table_schema not in ('pg_catalog', 'information_schema');
回答by Craig Ringer
Grants in PostgreSQL are not recursive; a GRANTon the database sets rights to the database object but does not affect the contained schemas or their tables, views, functions, etc.
PostgreSQL 中的授权不是递归的；GRANT数据库上的a设置对数据库对象的权限，但不影响包含的模式或其表、视图、函数等。
Granting ALL PRIVELEGESon the database grants CREATE, CONNECTand TEMPORARYrights.
授予ALL PRIVELEGES对数据库的授予CREATE、CONNECT和TEMPORARY权限。
See \h GRANTin psql, or the documentation for GRANTin 8.4, to see what ALL PRIVILEGESmeans for DATABASE:
请参阅\h GRANTpsql 或8.4 中的文档GRANT，了解以下内容的ALL PRIVILEGES含义DATABASE：
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
There are plpgsql functions and various 3rd party scripts available that use pg_catalogor information_schemato dynamically build GRANTs and recursively set rights. Search for "postgresql recursive grant".
有 plpgsql 函数和各种 3rd 方脚本可用，它们使用pg_catalog或information_schema动态构建GRANTs 并递归设置权限。搜索“postgresql 递归授权”。
These will not help you set default access rights for newtables. PostgreSQL doe have ALTER DEFAULT PRIVILEGESto allow you to set the default table rights for new tables, but it's only supported in Pg 9.1 and newer. Explicit GRANTs are required for each table in older versions or when permissions are to be set after table creation.
这些不会帮助您为新表设置默认访问权限。PostgreSQL 确实有ALTER DEFAULT PRIVILEGES允许您为新表设置默认表权限，但它仅在 Pg 9.1 和更新版本中受支持。GRANT在旧版本中或在创建表后设置权限时，每个表都需要显式s。
Newer versions, as you noted, have facilities for multiple grants via GRANT ... ALL TABLES, but your question is specific to 8.4.
正如您所指出的，较新的版本具有通过 进行多次授权的设施GRANT ... ALL TABLES，但您的问题特定于 8.4。
回答by Kuberchaun
I believe you can't do that. But you can use the information schema to generate the grants so you don't have to manually do it for 10,000 tables. See the link below for a relative example and the site linked to easy very good for info.
我相信你不能那样做。但是您可以使用信息架构来生成授权，因此您不必为 10,000 个表手动执行此操作。请参阅下面的链接以获取相关示例以及链接到 easy 非常有用的信息的站点。
回答by Marco Luly
If you have another user who have the DML privileges, it works in postgresql 8.x/9.x:
如果你有另一个拥有 DML 权限的用户，它可以在 postgresql 8.x/9.x 中工作：
grant <userWithDMLPrivileges> to testuser;
Hope it helps.
希望能帮助到你。
回答by andrew
Add all priviledges on all tables:
在所有表上添加所有权限：
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO [username];
