如何在DATABASE vs SCHEMA上为用户管理默认特权？

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;
\connect hostdb
CREATE SCHEMA hostdb;
CREATE USER hostdb_admin WITH PASSWORD 'youwish';
CREATE USER hostdb_mgr   WITH PASSWORD 'youwish2';
CREATE USER hostdb_usr WITH PASSWORD 'youwish3';

GRANT ALL PRIVILEGES ON DATABASE hostdb TO hostdb_admin;
GRANT CONNECT ON DATABASE hostdb TO hostdb_mgr, hostdb_usr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hostdb_mgr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT ON TABLES TO hostdb_usr;

#1 楼

在哪里可以找到合适的指南，教程或视频系列？


您将在手册中找到所有内容。请点击下面的链接。
当然，这件事并不容易，有时会造成混乱。这是用例的配方：

食谱


我想要对其进行配置，以便只有hostdb_admin可以创建
（并删除和alter）表;
默认情况下hostdb_mgr可以读取，插入，更新和删除所有表;
hostdb_usr只能读取所有表（和视图）。


作为超级用户postgres：

CREATE USER schma_admin WITH PASSWORD 'youwish';
-- CREATE USER schma_admin WITH PASSWORD 'youwish' CREATEDB CREATEROLE; -- see below
CREATE USER schma_mgr   WITH PASSWORD 'youwish2';
CREATE USER schma_usr   WITH PASSWORD 'youwish3';

如果您想要功能更强大的管理员也可以管理数据库和角色，请在上面添加角色属性CREATEDB和CREATEROLE。

将每个角色授予更高的级别，因此所有级别都至少“继承”下一个更低的级别（级联）中的特权：

GRANT schma_usr TO schma_mgr;
GRANT schma_mgr TO schma_admin;

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;  -- see notes below!

GRANT CONNECT ON DATABASE hostdb TO schma_usr;  -- others inherit

\connect hostdb  -- psql syntax

我正在命名架构schma（而不是hostdb，这会造成混淆）。选择任何名称。 （可选）将schma_admin设为架构的所有者：

CREATE SCHEMA schma AUTHORIZATION schma_admin;

SET search_path = schma;  -- see notes

ALTER ROLE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inherited
ALTER ROLE schma_mgr   IN DATABASE hostdb SET search_path = schma;
ALTER ROLE schma_usr   IN DATABASE hostdb SET search_path = schma;

GRANT USAGE  ON SCHEMA schma TO schma_usr;
GRANT CREATE ON SCHEMA schma TO schma_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT SELECT                           ON TABLES TO schma_usr;  -- only read

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO schma_mgr;  -- + write, TRUNCATE optional

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO schma_mgr;  -- SELECT, UPDATE are optional 

有关and drop and alter的信息，请参见下面的注释。


随着事情的发展，我对于在TRIGGERS，存储过程，VIEWS以及其他对象上施加类似的限制也将有疑问。视图是特殊的。例如：



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


对于可更新视图：


请注意，在视图上执行插入，更新或删除的用户
必须在
上具有相应的插入，更新或删除特权视图。另外，视图的所有者必须对基础基本关系具有相关的权限，但是执行更新的用户不需要对基础基本关系的任何权限（请参见38.5节）。


触发器也很特殊。您需要在表上具有TRIGGER特权，并且：


在PostgreSQL 8.4中执行触发器函数需要什么特权？

但是我们已经结束了-扩大此问题的范围...

重要说明

所有权

如果要允许schma_admin（单独）删除和更改表，使角色拥有所有对象。文档：


删除对象或以任何方式更改其定义的权利不视为可授予的特权；
（但是，通过授予或撤消拥有对象的角色的成员身份，可以获得类似的效果；请参见下文。）
也隐式地具有对象的所有授予选项。

ALTER TABLE some_tbl OWNER TO schma_admin;

或创建所有角色为schma_admin的对象，那么您无需设置明确的所有者。它还简化了默认特权，您只需为一个角色设置即可：

预先存在的对象

默认特权仅适用于新创建的对象，并且仅适用于与他们一起创建的特定角色。您还需要调整现有对象的权限：


关系

的权限被拒绝如果您创建具有角色的对象，则同样适用没有设置DEFAULT PRIVILEGES的用户，例如超级用户postgres。将所有权重新分配给schma_admin并手动设置权限-或也为DEFAULT PRIVILEGES设置postgres（在连接到正确的DB时！）： br />您错过了ALTER DEFAULT PRIVILEGES命令的重要方面。除非另有说明，否则它适用于当前角色：


不能更改默认特权

默认特权仅适用于当前数据库。因此，您不会与数据库集群中的其他数据库混淆。文档：
为当前数据库中创建的所有对象




您可能还希望为FUNCTIONS和TYPES（不仅是TABLES和SEQUENCES）设置默认特权，但要设置这些特权可能不需要。

PUBLIC的默认特权是基本的，并且被某些人高估了。文档：


PostgreSQL向
PUBLIC授予某些类型对象的默认特权。默认情况下，对表，
列，模式或表空间的PUBLIC不授予任何特权。对于其他类型，授予PUBLIC的默认
特权如下：PUBLIC和CONNECT用于数据库； CREATE TEMP TABLE功能特权；和EXECUTE
语言特权。


我的字体加粗。通常，上面的一个命令足以覆盖所有内容：

ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ...  -- etc.

特别地，对于新模式，没有为USAGE授予默认特权。名为“ public”的默认模式以PUBLIC的ALL特权开头可能会造成混淆。这只是一个便利功能，可以简化新创建数据库的入门。它不会以任何方式影响其他架构。您可以在模板数据库PUBLIC中撤消这些特权，然后该集群中所有新创建的数据库都将在没有它们的情况下启动：

REVOKE ALL ON DATABASE hostdb FROM public;

特权template1


由于我们从TEMP撤消了对hostdb的所有特权，因此除非我们明确允许，否则普通用户无法创建临时表。您可能想要也可能不想添加：

\connect template1
REVOKE ALL ON SCHEMA public FROM public;

PUBLIC

不要忘记设置search_path。如果您仅在集群中获得一个数据库，则可以在search_path中设置全局默认值。
否则（更有可能）将其设置为数据库的属性，或者仅将其设置为涉及的角色，甚至是两者的组合。详细信息：


search_path对标识符解析和“当前模式”有何影响？

如果您也使用公共模式，或者甚至（不太可能）postgresql.conf，则可能希望将其设置为schma, public。 ..

替代方法是使用默认模式“公共”，该模式应与$user, schma, public的默认设置一起使用，除非您进行了更改。在这种情况下，请记住撤销search_path的特权。

相关的


PostgreSQL中特定数据库的授予特权
PostgreSQL-DB用户仅应允许调用函数