PostgreSQL 中管理用户、角色和权限的基本命令

PostgreSQL 的用户、角色和权限决定了谁能连接、能读取什么以及能更改什么。如果这些权限靠猜测来管理，你的数据库将变得难以审计且容易过度暴露。

PostgreSQL 使用同一个概念——角色，来同时表示登录用户和组。具有 LOGIN 属性的角色可以连接。没有 LOGIN 属性的角色通常用作承载共享权限的组。

创建和修改角色

创建一个带有密码的应用登录角色：

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

创建一个用于共享只读访问的组角色：

CREATE ROLE reporting_readonly NOLOGIN;

常见的角色属性包括：

属性	允许的操作
LOGIN	连接到数据库服务器。
CREATEDB	创建数据库。
CREATEROLE	创建、修改和删除许多角色，但对超级用户角色有重要限制。
REPLICATION	使用复制连接。
SUPERUSER	绕过常规权限检查。仅用于严格控制的管理员角色。
INHERIT	使用其所属角色的权限。这是默认行为。

使用 ALTER ROLE 更改密码或禁用登录：

ALTER ROLE app_user WITH PASSWORD 'new_strong_password';
ALTER ROLE old_employee NOLOGIN;

在删除角色之前，处理其拥有的所有对象以及已被授予的权限：

REASSIGN OWNED BY old_employee TO postgres;
DROP OWNED BY old_employee;
DROP ROLE old_employee;

REASSIGN OWNED 转移拥有的对象。DROP OWNED 移除授予该角色的权限，并可能删除当前数据库中仍由其拥有的对象，因此在运行前请仔细检查。

使用组角色实现更清晰的访问控制

将权限授予组角色，然后将人员或应用角色添加到这些组中。

GRANT reporting_readonly TO alice, bob;
REVOKE reporting_readonly FROM bob;

WITH ADMIN OPTION 允许成员为其他人授予和撤销该角色成员身份。它本身不会使成员成为超级用户或赋予对数据库的无限制权限。

GRANT reporting_readonly TO team_lead WITH ADMIN OPTION;

这种模式使访问审计更容易。无需检查 50 个用户的表权限，只需检查组角色即可。

授予对象权限

使用 GRANT 允许对特定对象执行特定操作。

GRANT SELECT ON TABLE production.orders TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.orders TO app_user;

常见的表权限包括 SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES 和 TRIGGER。

模式权限与表权限是分开的。角色通常需要模式上的 USAGE 权限以及内部表上的权限：

GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_user;

对于使用序列的插入操作，也需要授予序列访问权限：

GRANT USAGE, SELECT ON SEQUENCE app.orders_id_seq TO app_user;

对于函数，显式授予执行权限：

GRANT EXECUTE ON FUNCTION app.audit_log_insert(text) TO app_user;

撤销权限

REVOKE 移除直接授予的权限。

REVOKE INSERT ON TABLE production.orders FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM reporting_readonly;

如果用户在撤销后仍然拥有访问权限，请检查组角色成员身份。用户可能通过另一个角色继承了相同的权限。

为将来的表设置权限

GRANT ON ALL TABLES 仅影响现有表。对于之后创建的表，请使用 ALTER DEFAULT PRIVILEGES，并由将创建这些表的角色执行。

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_readonly;

这是 PostgreSQL 权限设置中最常见的遗漏步骤之一。用户今天可以读取表，但明天创建的新表会被拒绝访问，因为从未配置默认权限。

审计当前访问权限

在 psql 中，这些命令是快速检查方法：

\du
\du+ app_user
\dp app.orders
\dn+

对于可重复的报告，目录视图更合适：

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

对于表权限：

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

值得养成的安全习惯

为应用程序分配专用角色，而不是使用人类 DBA 账户。授予应用程序所需的最小权限集。在日常工作中避免使用 SUPERUSER 和宽泛的 ALL PRIVILEGES 授权。

使用组角色进行共享访问，并定期审查成员身份。当有人更换团队时，移除一个组角色成员身份比逐个查找表级权限更安全。

实际可行的下一步是选择一个生产模式，列出其角色、成员身份、表权限和默认权限。先修复那里的漏洞，然后将相同的模式扩展到其他数据库。