将数据库中特定模式的所有内容授予 PostgreSQL 中的组角色

Grant all on a specific schema in the db to a group role in PostgreSQL

提问by punkish

Using PostgreSQL 9.0, I have a group role called "staff" and would like to grant all (or certain) privileges to this role on tables in a particular schema. None of the following work

使用 PostgreSQL 9.0，我有一个名为“staff”的组角色，我想在特定模式中的表上向该角色授予所有（或某些）权限。以下都没有工作

GRANT ALL ON SCHEMA foo TO staff;
GRANT ALL ON DATABASE mydb TO staff;

Members of "staff" are still unable to SELECT or UPDATE on the individual tables in the schema "foo" or (in the case of the second command) to any table in the database unlessI grant all on that specific table.

“staff”的成员仍然无法对模式“foo”中的单个表或（在第二个命令的情况下）数据库中的任何表进行 SELECT 或 UPDATE，除非我在该特定表上授予所有权限。

What can I do make my and my users' lives easier?

我可以做些什么让我和我的用户的生活更轻松？

Update:Figured it out with the help of a similar question on serverfault.com.

更新：在 serverfault.com上的类似问题的帮助下解决了这个问题。

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO staff;

回答by Erwin Brandstetter

You found the shorthand to set privileges for all existingtables in the given schema. The manual clarifies:

您找到了为给定模式中的所有现有表设置权限的简写。手册阐明：

(but note that ALL TABLESis considered to include viewsand foreign tables).

（但请注意，这ALL TABLES被认为包括视图和外部表）。

Bold emphasis mine. serialcolumns are implemented with nextval()on a sequence as column default and, quoting the manual:

大胆强调我的。serial列nextval()在序列上作为列默认值实现，并引用手册：

For sequences, this privilege allows the use of the currvaland nextvalfunctions.

对于序列，此权限允许使用currval和nextval函数。

So if there are serialcolumns, you'll also want to grant USAGE(or ALL PRIVILEGES) on sequences

因此，如果有serial列，您还需要在序列上授予USAGE（或ALL PRIVILEGES）

GRANT USAGE ON ALL SEQUENCES IN SCHEMA foo TO mygrp;

Note: identity columnsin Postgres 10 or later use implicit sequences that don't require additional privileges. (Consider upgrading serialcolumns.)

注意：Postgres 10 或更高版本中的标识列使用不需要额外权限的隐式序列。（考虑升级serial列。）

What about newobjects?

怎么样的新对象？

You'll also be interested in DEFAULT PRIVILEGESfor users or schemas:

您还会DEFAULT PRIVILEGES对用户或模式感兴趣：

ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT ALL PRIVILEGES ON TABLES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT USAGE          ON SEQUENCES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo REVOKE ...;

This sets privileges for objects created in the future automatically - but not for pre-existing objects.

这会自动为将来创建的对象设置权限 - 但不会为预先存在的对象设置权限。

Default privileges are onlyapplied to objects created by the targeted user (FOR ROLE my_creating_role). If that clause is omitted, it defaults to the current user executing ALTER DEFAULT PRIVILEGES. To be explicit:

默认权限仅适用于目标用户 ( FOR ROLE my_creating_role)创建的对象。如果省略该子句，则默认为当前用户执行ALTER DEFAULT PRIVILEGES. 明确地说：

ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo GRANT ...;
ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo REVOKE ...;

Note also that all versions of pgAdmin III have a subtle bug and displaydefault privileges in the SQL pane, even if they do not apply to the current role. Be sure to adjust the FOR ROLEclause manually when copying the SQL script.

另请注意，所有版本的 pgAdmin III 都有一个微妙的错误，并在 SQL 窗格中显示默认权限，即使它们不适用于当前角色。FOR ROLE复制SQL脚本时一定要手动调整子句。

回答by Basil Bourque

My answer is similar to this one on ServerFault.com.

我的回答类似于ServerFault.com上的这个。

To Be Conservative

保守

If you want to be more conservative than granting "all privileges", you might want to try something more like these.

如果您想比授予“所有权限”更保守，您可能想尝试更多类似的东西。

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO some_user_;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO some_user_;

The use of publicthere refers to the name of the default schema created for every new database/catalog. Replace with your own name if you created a schema.

使用publicthere 是指为每个新数据库/目录创建的默认模式的名称。如果您创建了架构，请替换为您自己的名称。

Access to the Schema

访问架构

To access a schema at all, for any action, the user must be granted "usage" rights. Before a user can select, insert, update, or delete, a user must first be granted "usage" to a schema.

要完全访问模式，对于任何操作，都必须授予用户“使用”权限。在用户可以选择、插入、更新或删除之前，必须首先授予用户对模式的“使用权”。

You will not notice this requirement when first using Postgres. By default every database has a first schema named public. And every user by default has been automatically been granted "usage" rights to that particular schema. When adding additional schema, then you must explicitly grant usage rights.

首次使用 Postgres 时，您不会注意到此要求。默认情况下，每个数据库都有一个名为public. 默认情况下，每个用户都被自动授予对该特定架构的“使用”权限。添加其他架构时，您必须明确授予使用权限。

GRANT USAGE ON SCHEMA some_schema_ TO some_user_ ;

Excerpt from the Postgres doc:

摘自Postgres 文档：

For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables. Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access.

对于模式，允许访问包含在指定模式中的对象（假设还满足对象自己的权限要求）。本质上，这允许受让人在模式中“查找”对象。没有此权限，仍然可以查看对象名称，例如通过查询系统表。此外，在撤销此权限后，现有后端可能具有先前执行过此查找的语句，因此这不是防止对象访问的完全安全的方法。

For more discussion see the Question, What GRANT USAGE ON SCHEMA exactly do?. Pay special attention to the Answerby Postgres expert Craig Ringer.

有关更多讨论，请参阅问题，GRANT USAGE ON SCHEMA 究竟是做什么的？. 请特别注意Postgres 专家Craig Ringer的答案。

Existing Objects Versus Future

现有对象与未来

These commands only affect existing objects. Tables and such you create in the future get default privileges until you re-execute those lines above. See the other answer by Erwin Brandstetterto change the defaults thereby affecting future objects.

这些命令仅影响现有对象。您将来创建的表等将获得默认权限，直到您重新执行上面的那些行。请参阅Erwin Brandstetter的另一个答案以更改默认值，从而影响未来的对象。