主页 / dba / 问题 / 117109
Accepted
Jim Dennis
Asked: 2015-10-06 19:09:41 +0800 CST

如何在 DATABASE vs SCHEMA 上管理用户的默认权限？

  • 772

我想将一个相当简单的内部数据库驱动应用程序从 SQLite3 迁移到 PostgreSQL 9.3，并在执行过程中收紧数据库中的权限。

该应用程序当前包含一个更新数据的命令；和一个查询它。当然，我还需要以其他方式维护数据库（创建新表、视图、触发器等）。

虽然这个应用程序最初将是唯一托管在服务器上的应用程序，但我更愿意假设它将来可能会托管在具有其他数据库的服务器上，而不是在以后有必要时不得不争先恐后未来。

我认为这些将是一组相当常见的要求，但我很难找到一个简单的教程来解释如何在 PostgreSQL 中设置一个新的数据库，并使用这种用户/权限分离。关于组、用户、角色、数据库、模式和域的参考资料很详细；但我发现它们令人困惑。

这是我迄今为止尝试过的（从内部psql作为'postgres'）：

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;

但我没有得到预期的语义。我想对其进行配置，以便只有hostdb_admin可以创建（以及删除和更改）表；默认情况下hostdb_mgr可以对所有表进行读取、插入、更新和删除；并且hostdb_usr只能读取所有表（和视图）。

当我尝试这个时，我发现我能够以hostdb任何这些用户的身份创建表；但是，对于每个用户，我只能读取或修改该用户创建的表——除非我使用显式的GRANT.

我猜在CREATE DATABASEand之间缺少CREATE SCHEMA一些东西，可以应用SCHEMA到DATABASE?

（随着事情变得更先进，我也会有问题需要对TRIGGERS、存储过程VIEWS和其他对象应用类似的限制）。

我在哪里可以找到一个不错的指南、教程或视频系列？

database-design postgresql

1 个回答

  1. Best Answer

    Postgres 14添加了预定义的角色 pg_read_all_data和pg_write_all_data，它允许使用快捷方式来实现名称所暗示的目的。

    我在哪里可以找到一个不错的指南、教程或视频系列？

    您会在手册中找到所有内容。下面的链接。
    诚然，这件事并非微不足道，有时甚至令人困惑。这是用例的配方：

    食谱

    我想对其进行配置，以便只有hostdb_admin可以创建（以及删除和更改）表；
    默认情况下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参阅下面的注释。

    Postgres 14添加了预定义的非登录角色pg_read_all_data，并pg_write_all_data为所有模式中的所有对象提供只读/只写访问权限。这超出了这里的要求，但可能有用。看：

    随着事情变得越来越先进，我也会有问题要对TRIGGERS、存储过程VIEWS和可能的其他对象应用类似的限制。

    观点很特别。

    手册：

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

    对于可更新视图：

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

    触发器也很特殊。您需要在TRIGGER桌子上的特权，并且：

    重要笔记

    所有权

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

    删除对象或以任何方式更改其定义的权利不被视为可授予的特权；它是所有者固有的，不能授予或撤销。（但是，通过授予或撤销拥有该对象的角色的成员资格可以获得类似的效果；见下文。）所有者也隐含地拥有该对象的所有授予选项。

    ALTER TABLE some_tbl OWNER TO schma_admin;

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

    预先存在的对象

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

    如果您创建具有未DEFAULT PRIVILEGES设置角色的对象（例如 superuser ），这同样适用postgres。手动重新分配所有权schma_admin并设置权限 - 或同时设置权限DEFAULT PRIVILEGES（postgres连接到正确的数据库时！）：

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

    默认权限

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

    默认权限仅适用于当前数据库。所以你不会弄乱数据库集群中的其他数据库。文档：

    对于在当前数据库中创建的所有对象

    您可能还想为FUNCTIONSand TYPES（不仅仅是TABLESand SEQUENCES）设置默认权限，但可能不需要这些权限。

    默认权限PUBLIC

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

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

    大胆强调我的。通常，这一个命令（包括在顶部）就是您所需要的：

    REVOKE ALL ON DATABASE hostdb FROM public;

    特别是，没有PUBLIC为新模式授予默认权限。名为“public”的默认模式ALL以PUBLIC. 这只是一个方便的功能，可以简化新创建的数据库的启动。它不会以任何方式影响其他模式。您可以在模板数据库中撤销这些权限template1，然后此集群中所有新创建的数据库都将在没有它们的情况下启动：

    \connect template1
REVOKE ALL ON SCHEMA public FROM public;

    特权TEMP

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

    GRANT TEMP ON DATABASE hostdb TO schma_mgr;

    search_path

    不要忘记设置search_path. 如果集群中只有一个数据库，则可以在postgresql.conf. 否则（更有可能）将其设置为数据库的属性，或者仅用于涉及的角色，甚至两者的组合。细节：

    如果您也schma, public使用公共模式，或者甚至（不太可能）$user, schma, public...

    search_path另一种方法是使用默认模式“public”，除非您更改它，否则它应该与默认设置一起使用。PUBLIC在这种情况下，请记住撤销权限。

    有关的

    • 119

相关问题