PostgreSQL 角色和权限详解
-
这篇文章将解释 PostgreSQL 权限系统的工作原理以及如何查看各种对象的当前访问控制列表（ACL）。
PostgreSQL 是最受欢迎的关系型数据库管理系统之一。当你使用本地运行的 PostgreSQL 时，为了方便起见，你只需使用一个超级用户。但在生产环境中，你需要正确设置用户和权限。
然而，尽管有很多关于如何在PostgreSQL中插入和查询数据的文章，但其访问控制机制并没有得到很好的解释。本文总结了它的工作原理，作为PostgreSQL访问控制的入门指南。
角色、对象和权限
与其他访问控制机制一样，PostgreSQL的访问控制可以解释为“角色X允许在对象Z上执行Y操作”。在这里，角色是用户和组，对象是数据库、表等，权限是像
SELECT或INSERT这样的操作。从概念上讲，PostgreSQL的访问控制列表（ACL）条目可以解释为一个(role, object, privilege)元组。角色基本上是用户和组。它既可以作为用户，也可以作为组；你可以以角色身份登录，角色也可以属于另一个角色。每个角色都有像
LOGIN和INHERIT这样的属性，表示你是否可以以该角色登录，以及该角色是否从其所属角色继承权限。你可以使用GRANT ROLE ...命令将角色添加到另一个角色的成员中。在PostgreSQL中，对象包括数据库、表等。PostgreSQL对象具有树状结构。一个PostgreSQL实例可以拥有多个数据库。一个数据库可以拥有多个模式。一个模式可以拥有多个表。
特权是在PostgreSQL对象上定义的权限。例如，表上有一个
SELECT特权，这是在它们上面运行SELECT查询的权限。每种对象都有不同的特权集。通过这些元素，你可以表达类似于“角色
readonly_user被允许在accounts表上运行SELECT”的访问控制配置。你可以在 https://www.postgresql.org/docs/15/ddl-priv.html 中查看对象类型和权限的有效组合。你可以使用GRANT和REVOKE命令添加或删除(role, object, privilege)元组。继承仅发生在角色之间，而不是对象之间。由于 PostgreSQL 对象具有树状结构，你可能希望在数据库级别授予
SELECT权限，希望它将SELECT权限授予数据库中的所有表。但是，PostgreSQL 权限并不是这样工作的。对象所有者
每个PostgreSQL对象都有一个名为“所有者”的特殊角色。只有所有者才能执行某些操作，如
ALTER TABLE，而你不能将GRANT这样的权限授予非所有者。有时候，你可能希望为一个对象分配两个以上的所有者。假设你有两个角色，
app_user和sre_user，你希望这两个用户都能运行ALTER TABLE，而这只有所有者才能做到。由于每个对象只能有一个所有者，你不能直接让这两个用户都成为所有者。同时，ALTER TABLE不是你可以将其GRANT到角色的内容。你可以使用角色继承来解决此问题。创建
table_owner角色和GRANT table_owner TO app_user, sre_user，然后像ALTER TABLE my_table OWNER TO table_owner那样转移所有者角色。现在表的所有者是table_owner，但是因为app_user和sre_user是该角色的成员，所以他们也具有继承权限来运行ALTER TABLE。
默认权限
当创建一个对象时，初始时只有所有者可以访问该对象。例如，如果你创建了一个新表，只有你可以访问该表。你需要单独向其他角色授权。这很麻烦，因为你每次创建新表时都需要这样做。PostgreSQL具有一个功能，允许你配置新创建的对象的默认权限。
假设你想要为数据库和模式下的所有新表默认分配只读权限给一个只读角色。在 PostgreSQL v14 或更高版本中，有一个预定义的
pg_read_all_data角色，允许其成员读取所有数据库中的所有数据，但如果你想要将其限制在某个特定数据库，就不能使用这个角色。我们将通过使用默认权限为ro_user角色提供只读访问权限。对于现有的表格，我们可以运行
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro_user。这将为现有表格提供SELECT权限。然而，我们希望将此权限授予将来创建的表格。为了实现这一点，ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ro_user。这将改变默认权限。请注意，这些默认权限仅在授权者创建新表时应用。例如，假设我们有两个所有者角色
table_owner1和table_owner2。table_owner1发出ALTER DEFAULT PRIVILEGES ...，而另一个没有默认权限。在这种情况下，发出的ALTER DEFAULT PRIVILEGES仅与table_owner1相关，并且仅在table_owner1创建新表时应用。即使table_owner2创建了一个新表，它也不会拥有由table_owner1定义的默认权限。查看当前的****访问控制列表
通过使用
GRANT和REVOKE命令，你可以修改上述的ACL，但是我们如何查看当前的ACL呢？如果你使用psql命令行界面，有一些命令行内部命令可以显示它们：
出自 https://www.postgresql.org/docs/15/ddl-priv.html
然而，你可能无法轻松访问
psql；你可能可以通过Redash、Retool、Grafana等方式运行一个只读查询，但不能使用psql。即使在这种情况下，你也可以在PostgreSQL内部表上运行SELECT查询，以查看与psql命令相同的信息。psql命令在底层也会查询这些PostgreSQL内部表，并以友好的方式显示它们。你可以在https://github.com/postgres/postgres/blob/f4a9422c0c37ba638adbab853b8badb98a53ce04/src/bin/psql/describe.c#L3850 找到psql的\dt（显示表格）实现，那里有一个SELECT语句。以下是一些权限查询的示例。对象类型询问数据库选择数据名，pg_catalog.pg_get_userbyid（datdba），datacl FROM pg_database；模式选择 nspname, pg_catalog.pg_get_userbyid(nspowner), nspacl FROM pg_namespace;桌子SELECT relname, relacl FROM pg_class WHERE relacl IS NOT NULL AND relname NOT LIKE 'pg_%';默认权限从 pg_default_acl 中选择 pg_catalog.pg_get_userbyid(defaclrole)、defaclobjtype、defaultacl；
每个 ACL 条目都以缩写形式显示，看起来像
arwdDxt. 每个特权都缩短为一个字符。例如r在arwdDxtisSELECT和wis 中INSERT。你可以在帮助文档中看到映射。
概括
PostgreSQL 访问控制机制是基于角色、对象和权限构建的。有一种方法可以自动为新对象设置权限。为了查看当前配置，你可以使用
psqlCLI 或直接查询 PostgreSQL 内部表。