如何为现有的postgres数据库创建用户(读写和只读)和管理员角色？

Question

我们有一个现有的postgres数据库，其中包含一个超级用户adm，用于执行所有操作。我们的Web应用程序使用相同的用户连接到数据库，并且管理员(用于修补/更新等(也使用相同的凭据。

我们必须修复此问题才能拥有角色，以便我们可以拥有read-write，readonly和admin角色。 我们不希望我们的 Web 应用程序和管理员以superuser的形式连接到数据库。

话虽如此，我创建了以下 sql 脚本来制作适当的角色。 我不是数据库专家(还没有(，所以想知道问题或更好的方法来解决这个问题。

ALTER ROLE adm NOLOGIN;
CREATE role user_role NOINHERIT;
CREATE role readonlyuser_role NOINHERIT;
CREATE role admin_role CREATEDB CREATEROLE NOINHERIT;
CREATE ROLE u_service LOGIN PASSWORD '<some password>' INHERIT;
CREATE ROLE u_admin LOGIN PASSWORD '<some password>' INHERIT;
CREATE ROLE u_reader LOGIN PASSWORD '<some password>' INHERIT;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser_role;
GRANT ALL PRIVILEGES ON SCHEMA public TO admin_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_role, admin_role;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO user_role, admin_role;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO user_role, admin_role;
GRANT ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA public TO user_role, admin_role;
GRANT ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA public TO user_role, admin_role;
GRANT ALL PRIVILEGES ON SCHEMA audit TO admin_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA audit TO admin_role;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA audit TO admin_role;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA audit TO admin_role;
GRANT ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA audit TO admin_role;
GRANT ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA audit TO admin_role;
GRANT admin_role TO u_admin;
GRANT user_role TO u_service;
GRANT readonlyuser_role TO u_reader;

Answer 1

需要考虑的几件事。

详细说明user_role和readonlyuser_role可以做什么

首先撤销这两个角色的所有权限，然后仅在需要时重新添加它们。这使得你对角色应该做什么的意图更加清晰，在实践中更安全，因为比预期更高的特权不会意外潜入。

REVOKE ALL ON SCHEMA public FROM public;  --only authorized roles can do anything here.
REVOKE ALL ON SCHEMA public FROM user_role;
REVOKE ALL ON SCHEMA public FROM readonlyuser_role;

GRANT ...

数据库所有者是本地超级用户

我们通常将数据库所有者设置为一个额外的角色;一个只登录以创建或更改模式，然后优雅退出的角色。如果您的admin_role执行更多操作，请考虑添加owner_role。

公共角色是否需要连接？

考虑添加

REVOKE CONNECT ON DATABASE yourdb FROM public;

这阻止了在同一数据库服务器上创建的任何角色都可以登录到此数据库的漏洞。

在事务块中执行所有这些操作

在作业进行到一半时停止权限分配可能会导致各种麻烦，就像将钥匙锁在车里一样。尽可能将权限分配设置为单个事务，这样遗漏的分号就不会将您锁定。