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

概述我想将一个相当简单的,内部的,数据库驱动的应用程序从SQLite3迁移到PostgreSQL 9.3,并在我去的时候收紧数据库中的权限. 应用程序当前包含更新数据的命令;和一个来查询它.当然,我还需要以其他方式维护数据库(创建新表,视图,触发器等). 虽然这个应用程序将是最初在服务器上托管的唯一应用程序,但我更愿意假设它可能在将来托管在具有其他数据库的服务器上,而不是必须在以后如果必要时加扰未来. 我想将一个相当简单的,内部的,数据库驱动的应用程序从sqlite3迁移到Postgresql 9.3,并在我去的时候收紧数据库中的权限.

应用程序当前包含更新数据的命令;和一个来查询它.当然,我还需要以其他方式维护数据库(创建新表,视图,触发器等).

虽然这个应用程序将是最初在服务器上托管的唯一应用程序,但我更愿意假设它可能在将来托管在具有其他数据库的服务器上,而不是必须在以后如果必要时加扰未来.

我认为这些是一组相当常见的要求,但是我很难找到一个简单的教程来解释如何在Postgresql中设置一个新的数据库,这种用户/权限分离.有关组,用户,角色,数据库,
模式和域;但我发现它们令人困惑.

这是我到目前为止所尝试的内容(从psql中作为’postgres’)：

CREATE DATABASE hostdb;REVOKE ALL ON DATABASE hostdb FROM public;\connect hostdbCREATE 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;

但我没有得到预期的语义.我想配置它,所以只有hostdb_admin可以创建(并删除和更改)表; hostdb_mgr默认可以读取,插入,更新和删除所有表;并且hostdb_usr只能读取所有表(和视图).

当我尝试这个时,我发现我能够在hostdb中创建任何这些用户的表;但是,对于每个用户,我只能读取或修改该用户创建的表 – 除非我使用显式GRANT.

我猜想CREATE DATABASE和CREATE SCHEMA之间缺少什么东西,将SCHEMA应用到DATABASE？

(随着事情变得更加先进,我也有问题对TRIGGERS,存储过程,VIEWS以及其他对象应用类似的限制).

我在哪里可以找到一个体面的指南,教程或视频系列？

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 belowCREATE 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 notesALTER RolE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inheritedALTER 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_adminGRANT SELECT                           ON tableS TO schma_usr;  -- only readALTER DEFAulT PRIVILEGES FOR RolE schma_adminGRANT INSERT,DELETE,TruncATE ON tableS TO schma_mgr;  -- + write,TruncATE optionalALTER DEFAulT PRIVILEGES FOR RolE schma_adminGRANT 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 template1REVOKE 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

总结

以上是内存溢出为你收集整理的postgresql – 如何在DATABASE与SCHEMA上管理USER的DEFAULT PRIVILEGES？全部内容，希望文章能够帮你解决postgresql – 如何在DATABASE与SCHEMA上管理USER的DEFAULT PRIVILEGES？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。