在PostgreSQL中用户权限的最佳实践是什么？

Question

在PostgreSQL中用户权限的最佳实践是什么？

6

我是postgresql的新手，正在尝试找出如何在运行web应用程序时最好地保护我的数据库。

在MySQL中，我倾向于使用管理用户来创建数据库、创建表格、修改模式、删除表格等所有不应在数据库被某些应用程序持续使用时发生的活动。然后，我有第二个用户（每个应用程序一个），该用户拥有应用程序所需的权限，如插入、更新等。这避免了应用程序错误/黑客攻击造成灾难性后果的可能性。

现在我正在试图找出如何在Postgres中执行类似的操作。与MySQL不同，Postgres数据库似乎有一个“所有者”。如果我使用上述计划，管理用户是否应拥有所有应用程序的所有数据库？还是这里有一些最佳实践？

补充：我刚刚发现授予权限“所有表”似乎意味着“当前定义的所有表”，并且不包括未来的表。与MySQL不同，“对foo.*”表示所有当前和未来的表。这正确吗？在授予语句中是否有一种方法也包括未来的表，还是我必须每次重新执行它？

- Johannes Ernst

1

一个MySQL数据库相当于一个Postgres模式。 - Neil McGuigan

3个回答

4

一个管理员用户是操作系统用户postgres，他有根据pg_hba.conf的访问权限。这是一个/该系统帐户。不要乱搞它。

当然，数据库管理员必须拥有自己的登录名，并使用选项superuser创建它。

对于您的Web应用程序，您需要创建一个Web应用程序登录名和一个以Web应用程序为数据库所有者的数据库。

通常这是必要的，因为大多数Web应用程序都希望创建和更改它们的表，而不是像数据中心设置中惯常的将此工作委托给管理员。

这样，您就可以在不同的数据库之间实现访问分离。

顺便说一下，PostgreSQL文档非常好，只需阅读它，例如关于PG数据库角色, 数据库所有者和客户端身份验证，这是PG的专业特长。

- Str.

1

最好保持简单。如果你的双层设置适合你，我建议保持它，而不是引入额外的管理开销来创建一个额外的数据库所有权角色。如果你需要添加更多不同级别的特权，那么你可以重新考虑。

- Politank-Z

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Denis Davydov · Accepted Answer

我的最佳实践：

为应用程序创建自定义数据库
为自定义应用程序数据库创建自定义模式
创建 DDL 角色
创建 DML 角色
从 PUBLIC 中撤销对已创建的自定义数据库的所有权限
撤销 DB-ses postgres 和已创建的自定义数据库中的 schema PUBLIC 的创建权限
为 DDL 角色授予自定义模式的 USAGE 和 CREATE 权限
仅为 DML 角色授予自定义模式的 USAGE 权限
为 DDL 应用程序操作创建具有 DDL 角色的用户（用于 Flyway 或 Liquibase 迁移、创建/修改/删除实体等操作）
为 DML 应用程序操作创建具有 DML 角色的用户（SELECT、INSERT、UPDATE、DELETE 等操作）
！在 DDL 用户下！将默认特权更改为 DML 角色以访问自定义模式

它看起来像这样：

我的脚本：

01-init.sql

CREATE DATABASE mysuperdb;

REVOKE ALL ON DATABASE mysuperdb FROM PUBLIC;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

CREATE ROLE ddl_custom_role WITH ENCRYPTED PASSWORD 'PWDPWD';

GRANT
    CONNECT
    ON DATABASE mysuperdb TO ddl_custom_role;

GRANT
    TEMPORARY
    ON DATABASE mysuperdb TO ddl_custom_role;

CREATE ROLE dml_custom_role WITH ENCRYPTED PASSWORD 'PWDPWD';

GRANT
    CONNECT
    ON DATABASE mysuperdb TO dml_custom_role;

GRANT
    TEMPORARY
    ON DATABASE mysuperdb TO dml_custom_role;

02-init.sql

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA IF NOT EXISTS mysuperschema;
CREATE USER flywayuser WITH ENCRYPTED PASSWORD 'PWDPWD';
CREATE USER appuser WITH ENCRYPTED PASSWORD 'PWDPWD';
GRANT
    USAGE,
    CREATE
    ON SCHEMA mysuperschema TO ddl_custom_role;
GRANT
    USAGE
    ON SCHEMA mysuperschema TO dml_custom_role;
GRANT
    ALL
    ON ALL SEQUENCES mysuperschema TO ddl_custom_role;
GRANT
    USAGE,
    SELECT
    ON ALL SEQUENCES mysuperschema TO dml_custom_role;
GRANT ddl_custom_role TO flywayuser;
GRANT dml_custom_role TO appuser;

03-init.sql

ALTER DEFAULT PRIVILEGES IN SCHEMA mysuperschema
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dml_custom_role;

运行顺序(注意脚本要在哪个用户下运行！)：

在数据库postgres的用户postgres下：

psql -U postgres -d postgres -a -f 01-init.sql
在数据库MYSUPERDB的用户postgres下：

psql -U postgres -d mysuperdb -a -f 02-init.sql
在数据库MYSUPERDB的用户FLYWAYUSER下：

psql -U flywayuser -d mysuperdb -a -f 03-init.sql

因此，您有一个用于迁移的用户 (flywayuser)，该用户可以创建、修改和删除表格，以及另一个用户 (appuser)，该用户只能访问由flywayuser创建的表格进行SELECT、INSERT、DELETE、UPDATE操作。