PostgreSQL 8.4将所有表的DML特权授予角色 [英] PostgreSQL 8.4 grant DML privileges on all tables to a role

问题描述

我如何在PostgreSQL 8.4中的模式中的所有表上授予DML（SELECT，INSERT，UPDATE，DELETE）？我也希望这笔赠款能在将来继续用于创建新表。

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.

我见过9.0的解决方案，但我坚持使用8.4。

I've seen solutions for 9.0 but I'm stuck with 8.4 as it ships with Debian stable.

我已经尝试将以下内容作为基准，但是它不起作用，导致不可避免的拒绝访问关系X：

I have tried the following as a baseline but it doesn't work, resulting in the inevitable "access to relation X denied":

GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;

我已经疏通了文档，但似乎找不到合适的解决方案。

I've dredged through the documentation and I can't seem to find a suitable solution.

推荐答案

我也希望这笔赠款在将来继续用于创建新表。
[...]我已经仔细阅读了文档，但似乎找不到合适的解决方案。

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.

因为9.0之前没有。您所能获得的就是设置现有表的权限。您必须为每个表做一个 GRANT ，因为在9.0之前没有批量模式。有关 8.4 和 9.0 ：

Because before 9.0 there is none. All you can get is to set the permissions for existing tables. You have to do one GRANT for each table, because before 9.0 there was no "bulk" mode. See the SQL grammer for 8.4 and 9.0:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

和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 ]

新的 SCHEMA中的所有表部分都是您所缺少的。

The new ALL TABLES IN SCHEMA part is the one you are missing.

另外：按照您的问题在数据库级别设置权限也无济于事您：您将仅设置数据库权限，而不是对任何包含内容（如表）设置权限。相关部分：

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 ]

这意味着您只能设置<$对数据库本身的c $ c> CREATE ， CONNECT 和 TEMP 权限，但没有 SELECT ， INSERT 等。

Which means you can only set CREATE, CONNECT and TEMP permissions on the database itself but no SELECT, INSERT etc.

到目前为止，是坏东西。您可以做的事情如下：

So far for the bad stuff. What you can do are 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.

查询系统目录并创建适当的 GRANT 命令。将它们保存到文件中并执行该文件。

Query the system catalogues and create appropriate GRANT commands. Save them into a file and execute that file. This should give you an easier startup.

这样的查询可能看起来像这样：

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');

这篇关于PostgreSQL 8.4将所有表的DML特权授予角色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！