postgresql – 如何在DATABASE与SCHEMA上管理USER的DEFAULT PRIV

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;

Where can I find a decent guide,tutorial or video series on this?

您可以在手册中找到所有内容.以下链接.
当然,这件事并非微不足道,有时令人困惑.以下是用例的配方：

食谱

I want to have it configured so only the hostdb_admin can create
(and drop and alter) tables;
the hostdb_mgr can read,insert,update and delete on all tables by default;
and the hostdb_usr can only read all tables (and views).

作为超级用户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';

如果您想要一个更强大的管理员,也可以管理数据库和角色,请添加上面的role attributes CREATEDB and 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,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

For and drop and alter请参阅下面的注释.

As things get more advanced I’ll also have questions to apply similar
restrictions on TRIGGERS,stored procedures,VIEWS and perhaps other objects.

意见很特别. For one:

… (but note that ALL TABLES is considered to include views and foreign tables).

而对于Updatable Views：

Note that the user performing the insert,update or delete on the view
must have the corresponding insert,update or delete privilege on the
view. In addition the view’s owner must have the relevant privileges
on the underlying base relations,but the user performing the update
does not need any permissions on the underlying base relations (see
07003).

触发器也很特别.您需要表格上的TRIGGER权限,并且：

> What are the privileges required to execute a trigger function in PostgreSQL 8.4?

但我们已经过度扩大了这个问题的范围……

重要笔记

所有权

如果要允许schma_admin(单独)删除和更改表,请使角色拥有所有对象. The documentation:

The right to drop an object,or to alter its definition in any way,is not treated as a grantable privilege; it is inherent in the owner,and cannot be granted or revoked.
(However,a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.)
The owner implicitly has all grant options for the object,too.

ALTER TABLE some_tbl OWNER TO schma_admin;

或者创建具有schma_admin角色的所有对象,然后您无需显式设置所有者.它还简化了默认权限,然后您只需为一个角色设置：

预先存在的对象

默认权限仅适用于新创建的对象,仅适用于创建它们的特定角色.您还需要调整现有对象的权限：

> Permission denied for relation <table>

如果您创建的对象具有未设置DEFAULT PRIVILEGES的对象(如超级用户postgres),则同样适用.将所有权重新分配给schma_admin并手动设置权限 – 或者为postgres设置DEFAULT PRIVILEGES(连接到正确的数据库时！)：

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

默认权限

您错过了ALTER DEFAULT PRIVILEGES命令的一个重要方面.除非另有说明,否则它适用于当前角色：

> Can’t alter default privileges

默认权限仅适用于当前数据库.所以你不要乱用数据库集群中的其他数据库. The documentation:

for all objects created in the current database

您可能还想为FUNCTIONS和TYPES(不仅仅是TABLES和SEQUENCES)设置默认权限,但可能不需要这些权限.

PUBLIC的默认权限

授予PUBLIC的默认权限是基本的,并被一些人高估. The documentation:

PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
columns,schemas or tablespaces. For other types,the default
privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE
privilege for languages.

大胆强调我的.通常上面的一个命令足以涵盖所有内容：

REVOKE ALL ON DATABASE hostdb FROM public;

特别是,没有为PUBLIC授予新架构的默认权限.名为“public”的默认模式以PUBLIC的所有权限开头可能会令人困惑.这只是一个方便的功能,可以简化新创建的数据库的开始.它不会以任何方式影响其他模式.您可以在模板数据库template1中撤消这些权限,然后在没有它们的情况下从此群集中的所有新创建的数据库启动：

connect template1
REVOKE ALL ON SCHEMA public FROM public;

特权TEMP

由于我们从PUBLIC撤消了hostdb上的所有权限,因此除非我们明确允许,否则普通用户无法创建临时表.您可能想要也可能不想添加此内容：

GRANT TEMP ON DATABASE hostdb TO schma_mgr;

SEARCH_PATH

不要忘记设置search_path.如果您只在集群中获得了一个数据库,则可以在postgresql.conf中设置全局默认值.
否则(更有可能)将其设置为数据库的属性,或仅用于涉及的角色甚至两者的组合.细节：

> How does the search_path influence identifier resolution and the “current schema”

你可能想要将它设置为schma,public如果你也使用公共模式,甚至(不太可能)$user,schma,public ……

另一种方法是使用默认模式“public”,它应该与search_path的默认设置一起使用,除非你改变了它.在这种情况下,请记住撤消PUBLIC的权限.

有关

> Grant privileges for a particular database in PostgreSQL
> PostgreSQL – DB user should only be allowed to call functions