PostgreSQL 8.4授予一个角色对所有表的DML权限

Question

PostgreSQL 8.4授予一个角色对所有表的DML权限

11

我该如何在PostgreSQL 8.4中授予模式(schema)内所有表的DML权限（SELECT，INSERT，UPDATE，DELETE）？我希望这个授权也能持续到未来新建表的情况。

我看到了9.0版本的解决方案，但因为Debian stable附带8.4版本，所以我无法使用。

我尝试了以下基础代码，但它不起作用，最终导致“拒绝访问关系X”的错误：

GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;

我已经仔细阅读了文档，但似乎找不到合适的解决方案。

- pointyhat

为什么你不能升级呢？肯定有一种方法可以升级PostgreSQL。当前版本都可以从Postgres网站下载。 - user330315

肯定有升级的方法，但我不想这样做，因为供应商提供的软件包已经很好地集成、测试和理解了。我们的计划是在Debian wheezy被宣布为稳定版时同时升级到9.1。 - pointyhat

我永远不会理解为什么有些操作系统会如此娇惯用户。 - user330315

我们确实有选择（Debian backports/切换到CentOS+供应商软件包），但我们选择不采取它们，因为它们需要更多的维护。其实我们很懒 :-) - pointyhat

1

@pointyhat：懒惰是可以的，但是自从发布后，Debian稳定版就为懒人提供了PostgreSQL 9.1。有一个官方后移版本。我们在几台服务器上使用它，效果非常好。升级到下一个Debian版本也很顺利。首先阅读backports.debian.org上的说明。 - Erwin Brandstetter

显示剩余2条评论

5个回答

2

PostgreSQL中的授权不是递归的；对数据库进行的GRANT设置了对数据库对象的权限，但不影响包含的模式或它们的表、视图、函数等。

在数据库上授予ALL PRIVILEGES将授予CREATE、CONNECT和TEMPORARY权限。

请参见psql中的\h GRANT，或者8.4版本中GRANT的文档，以了解DATABASE中ALL PRIVILEGES的含义。

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

有一些 plpgsql 函数和各种第三方脚本可用于使用 pg_catalog 或 information_schema 动态构建 GRANT 并递归设置权限。搜索 "postgresql 递归授权"。

这些方法不能帮助您设置新表的默认访问权限。PostgreSQL 有 ALTER DEFAULT PRIVILEGES 可以允许您设置新表的默认表权限，但仅支持 Pg 9.1 及更高版本。在旧版本或在创建表后设置权限时，需要为每个表显式地进行 GRANT。

正如您所指出的，较新的版本通过 GRANT ... ALL TABLES 提供了多个授权的功能，但您的问题是针对 8.4 版本的。

- Craig Ringer

1

如果您有另一个用户拥有DML权限，则在postgresql 8.x/9.x中可以使用：

grant <userWithDMLPrivileges> to testuser;

希望它有所帮助。

- Marco Luly

1

我相信你不能这样做。但是你可以使用信息模式来生成授权，这样你就不必为10,000个表手动执行此操作。请参考下面的链接以获取相关示例，并访问链接网站以获取非常好的信息。

http://www.postgresonline.com/journal/archives/30-DML-to-generate-DDL-and-DCL-Making-structural-and-Permission-changes-to-multiple-tables.html

- Kuberchaun

-1

给所有表添加所有权限：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO [username];

- andrew

ALL TABLES IN SCHEMA 只在 PostgreSQL 9.0 及以上版本中有效。 - undefined

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- A.H. · Accepted Answer

我希望这个授权能够持续适用于未来的新建表，但是我已经查遍了文档，似乎找不到合适的解决方案。

因为在9.0之前没有类似功能，您只能为已存在的表设置权限。在9.0之前，您必须为每个表执行一个GRANT操作，因为没有"批量"模式可用。请参见8.4和9.0版本的SQL语法：8.4 和 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 ]

你所缺少的是新的ALL TABLES IN SCHEMA部分。

此外，像你在问题中提到的在数据库级别设置权限是不会帮助你的：你只会设置数据库上的权限，但不会对任何"包含"的内容如表进行设置。相关部分：

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE dbname [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

这意味着你只能在数据库本身上设置CREATE、CONNECT和TEMP权限，但不能设置SELECT、INSERT等权限。

至于可行的方法，您可以采取以下措施：

通过授予权限给角色而不是用户来减少权限管理的数量。然后将角色添加到各个用户中。当创建新表时，您只需要调整一个或两个角色，而不是数百个用户。
查询系统目录并创建相应的GRANT命令。将它们保存到文件中并执行该文件。这样做可以使启动更加容易。

这样的查询可能如下所示：

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