将数据库中的特定模式的所有数据授予PostgreSQL中的组角色 [英] Grant all on a specific schema in the db to a group role in PostgreSQL

问题描述

使用PostgreSQL 9.0，我有一个名为staff的组角色，并希望向特定模式的表格授予此角色的所有（或某些）特权。没有以下工作

  GRANT ALL ON SCHEMA foo To staff; 
授予工作人员maidb的数据库; 
  

工作人员的成员仍然无法在模式foo中的各个表上执行SELECT或UPDATE 或（在第二个命令的情况下）到数据库中的任何表格，除非我授予所有在该特定表格上。

什么可以

更新：借助 serverfault.com上的类似问题。

 授予员工所有表格上所有表格的所有权利; 
  

解决方案

您找到的解决方案是设置特权的速记现有对象新对象怎么样？

您还将对 DEFAULT PRIVILEGES ：

 在SCHEMA中更改默认特权foo给工作人员选择表格; 
在SCHEMA中更改默认权限foo REVOKE ...; 
 
 ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo GRANT ...; 
 ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo REVOKE ...; 
  

这将为将来创建的对象自动设置权限，而不会影响已存在的对象。 / p>

请注意，默认权限仅适用于目标用户创建的对象（ FOR ROLE my_creating_role ）。如果该子句被省略，它将默认为当前用户（执行 ALTER DEFAULT PRIVILEGES ）。

注意pgAdmin（默认GUI）的当前版本1.20有一个微妙的错误，并且在SQL窗格中显示默认权限，即使它们不适用于当前用户。复制该脚本时，请务必手动调整 FOR ROLE 子句。

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

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 unless I grant all on that specific table.

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.

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

解决方案

The solution you found is the shorthand to set privileges for existing objects. What about new objects?

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

ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT SELECT ON TABLES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo REVOKE ...;

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

This will set privileges automatically for objects created in the future - and not affect already-existing objects.

Note that default privileges are only applied to objects created by the targeted user (FOR ROLE my_creating_role). If that clause is omitted, it defaults to the current user (that executes ALTER DEFAULT PRIVILEGES).

Note also that the current version 1.20 of pgAdmin (default GUI) has a subtle bug and displays default privileges in the SQL pane, even if they do not apply to the current user. Be sure to adjust the FOR ROLE clause manually when you copy that script.

这篇关于将数据库中的特定模式的所有数据授予PostgreSQL中的组角色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！