连接层：pg_hba.conf

当客户端连接到数据库服务器时，会指定以哪个 PostgreSQL 数据库用户名进行连接，这类似于用户以特定身份登录 Unix 的方式。在 SQL 环境中，活跃的数据库用户名决定了访问数据库对象的权限。

HBAC 机制

PostgreSQL 使用 Host-Based Access Control (HBAC) 机制，通过 pg_hba.conf 文件定义连接规则。当数据目录通过 initdb 初始化时，会生成一个默认的 pg_hba.conf 文件。配置文件路径通常为$PGDATA/pg_hba.conf。

配置字段解析

每条规则都指定了连接类型、客户端 IP 地址范围、数据库名称、用户名以及用于匹配这些参数的连接认证方法。系统会使用第一条匹配连接类型、客户端地址、请求数据库和用户名的规则来执行认证。pg_hba.conf 按自上而下顺序匹配，一旦命中即停止。若无任何匹配，则拒绝访问。

每条规则包含以下字段：

TYPE DATABASE USER ADDRESS METHOD

  • TYPE：连接类型（local / host / hostssl / hostgssenc）

  • DATABASE：允许访问的数据库

    • all：匹配所有数据库。
    • replication：特殊值，用于流复制（备份和高可用）。
    • sameuser：请求的数据库名与用户名一致时才匹配。
    • samerole：请求的用户是与数据库同名的角色的成员时匹配。
    • 具体名称：如 mydb, db2（多个名称用逗号分隔）。

  • USER：允许连接的角色

    • all：匹配所有用户。
    • 具体用户名：如 alice, bob。
    • +groupname：匹配属于该角色的任何用户。

  • ADDRESS：客户端地址范围（CIDR）

    • 定义连接是从哪台机器发起的。如果是 local 类型，这个字段会被跳过。
    • IPv4 地址：如 192.168.1.100/32（单台机器）或 192.168.1.0/24（整个子网）。
    • IPv6 地址：如 ::1/128（本地回环）。
    • all：匹配任何 IP 地址。
    • samehost：匹配服务器自己的任何 IP。
    • samenet：匹配服务器所属的整个子网。
    • 域名：如 .example.com（以点开头匹配该域下的所有主机）。

  • METHOD：认证方式

    • trust：无条件信任。不需要密码。
    • reject：无条件拒绝。常用于封禁特定黑名单 IP。
    • scram-sha-256：执行 SCRAM-SHA-256 验证用户密码。
    • md5：较旧的密码加密方式，容易受到暴力破解。
    • password：发送明文密码。极度不安全，绝不要在生产环境使用。
    • peer：用于 local 连接。Linux 用户名和数据库用户名一致时放行。
    • ident：类似于 peer，但通过 TCP/IP 询问远程服务器的 identd 服务（已过时且不安全）。
    • cert：使用客户端 SSL 证书认证。
    • ldap / radius / pam：集成外部的身份验证系统。

常见配置示例

本地 Unix Socket 连接

## TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

允许内网特定IP访问

## TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.12.10/32        scram-sha-256

从特定域名访问

## TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             .example.com            scram-sha-256

角色层：Role 与成员关系

连接成功后，权限控制会进入角色系统。

在 PostgreSQL 里，不存在真正的 User 与 Group ，唯一的身份实体是 Role 。所有角色信息都存储在全局系统表 pg_authid 中（通过 pg_roles 视图查看）。在一个实例里创建的角色，在所有数据库中都可见。

角色属性（Attribute）

角色属性是角色本身所固有的，作用于全局。

  • LOGIN：允许登录
  • SUPERUSER：超级用户，绕过登录权限以外的所有权限检查
  • CREATEDB：允许创建数据库
  • CREATEROLE：允许管理角色
  • INHERIT：默认开启，自动继承所属组的权限
  • BYPASSRLS：绕过行级安全策略（RLS）

角色权限（Privilege）

权限（Privilege） 是授予特定角色对特定数据库对象执行特定操作的许可。

权限树的实现（Membership）

PostgreSQL 中不存在直接的组，所以经常利用 GRANT 建立成员关系来实现复杂的权限控制。通常我们会创建一个不可登录的角色作为“组”（很像 OOP 中的抽象类不是吗）。

1

CREATE ROLE group_role	-- LOGIN属性需要显式声明，不声明则为NOLOGIN

一旦组角色存在，你就可以使用 GRANT 和 REVOKE 命令来添加和移除成员：

1
2

GRANT group_role TO pt, ... ;
REVOKE group_role FROM pt, ... ;

不允许设置循环的成员关系链。当一个角色成为另一个角色的成员时，它可以通过两种方式使用权限：

  • 权限继承 (Inheritance)

    • 这是 PostgreSQL 的默认行为（如果你在创建角色时没有指定 NOINHERIT）。
    • pt 登录后，不需要任何额外操作，就可以直接调用 group_role 拥有的所有权限。

  • 身份切换 (SET ROLE)

    • 即使 pt 通过继承得到了 group_role 的权限，但在进行操作时依旧是以 pt 的身份，而不是组的身份。这在某些场景下依旧存在限制（比如修改表结构、创建新表时的 Owner 字段等），以及所有的角色属性（如建库）都无法通过继承获得。
    • 如需临时变更身份，执行：
    1

    SET ROLE group_role;	-- 执行 RESET ROLE 以恢复原角色
    • 如果需要更严格的权限管理，可以在授予成员关系时禁止继承：
    1

    GRANT role_group TO pt WITH INHERIT FALSE;
    • 这样可以强制在需要使用父权限时显式切换身份。

在成员关系中存在三个属性：

  • ADMIN ( WITH ADMIN OPTION ) —— 管理权
    • 当角色 A 被授予给角色 B，并带上 WITH ADMIN OPTION（或 ADMIN TRUE ）时，角色 B 获得了以下三项权限：
    • 转授权：角色 B 可以把角色 A 再授权给其他任何人。
    • 撤销权：角色 B 可以从其他人手里收回角色 A。
    • 修改权：角色 B 可以修改或删除角色 A（通常需要配合 CREATEROLE 权限）。
    • 作为拥有 CREATEROLE 权限的普通用户（非超级用户）创建一个新角色后，将自动获得该新角色的 ADMIN 权限。
  • INHERIT ( WITH INHERIT OPTION ) —— 继承权
    • 决定你是否自动拥有“角色A”的数据操作权限。
  • SET ( WITH SET OPTION ) —— 切换权
    • 决定你是否可以使用 SET ROLE 命令。

Schema：数据库的命名空间

从大到小，数据存储在这样的路径中：
实例 (Instance) ➔ 数据库 (Database) ➔ 模式 (Schema) ➔ 对象 (Table/View/Function) 如果没有 Schema，一个数据库里的所有表都堆在一起，会非常混乱。Schema 的引入解决了三个核心问题：

  • 命名空间隔离
    • 这允许你在同一个数据库里拥有同名的表，只要它们在不同的 Schema 下。
    • finances.orders (财务部的订单表)
    • logistics.orders (物流部的订单表)
    • 如果没有 Schema，你必须起名叫 finances_orders 和 logistics_orders。
  • 权限管理
    • 你可以按 Schema 授权。
    • 给会计分配 finances 的全部权限。
    • 给快递员分配 logistics 的只读权限。
    • 这样就不需要给几十张表一个个授权，只需要管理这几个“文件夹”即可。
  • 逻辑组织与模块化
    • 对于大型项目，你可以把“用户模块”、“订单模块”、“权限模块”的表分别放在不同的 Schema 中，让数据库结构清晰。

The Public Schema

每个新创建的数据库都会自动包含一个名为 public 的 Schema。 如果你创建表时不指定 Schema，Postgres 会默认把它放进 public。因此，以下两条语句是等效的：

1
2

CREATE TABLE products ( ... );
CREATE TABLE public.products ( ... );

搜索路径 (Search Path)

当你执行 SELECT * FROM mytable; 而不写 schema.mytable 时，数据库如何确认这是哪个 Schema 中的表？ 这取决于 search_path 变量（类似于环境变量）。

  • 查看当前路径：

    1

    SHOW search_path;

    在默认配置下，这将返回：

    1
2
3

     search_path
--------------
 "$user", public

    这会先搜索与用户名同名的 Schema，找不到再去 public 找。因此，在默认配置下，未限定的查询都会指向 public。

  • 添加路径：

    1

    SET search_path TO myschema,public;  -- 也可以不加public，但这样就必须通过显式指定来访问

模式与权限

Schema 作为一种数据库对象，也拥有自己的 Owner。默认为创建者，也可以显式指定其他角色作为 Owner。Schema有两种权限：USAGE 与 CREATE。

USAGE 权限

默认情况下，用户无法访问不属于其所有的 Schema 中的任何对象。要允许此类访问，Schema 的所有者必须授予对该模式的 USAGE 权限。默认情况下，所有用户对 public 模式都拥有此权限。

1

GRANT USAGE ON SCHEMA myschema TO myuser;

这里只是对此 Schema 的访问权限，要操作具体的内部对象，角色仍需要取得相应的对象的权限，两者缺一不可。

CREATE 权限

此权限允许在该 Schema 下创建新对象（表、函数等）。

Privileges：最细粒度的权限管理

Owner权限

当对象被创建时，它会被分配一个 Owner。Owner 通常是执行创建语句的角色。对于大多数类型的对象，初始状态是只有 Owner（或超级用户）才能对该对象执行任何操作。要允许其他角色使用它，必须授予权限。 可以通过 ALTER命令将对象分配给新的 Owner：

1

ALTER TABLE table_name OWNER TO new_owner;

这一操作存在两个限制：首先你需要是该对象的 Owner，或继承了 Owner的权限；同时你必须要能够 SET ROLE 到新的 Owner，这是为了确保资产始终在你有权管控的范围内（超级用户无视上述限制）。完成分配后，旧 Owner将失去所有相关权限。

权限分配

使用 GRANT命令来分配权限：

1
2

GRANT UPDATE ON accounts TO joe;
GRANT ALL ON products TO PUBLIC;

使用 ALL代替特定权限名称，可以授予该对象类型的所有权限。这里 PUBLIC是一个特殊的角色组，包含了全体角色，用于向系统中的每个角色授予权限，注意不要和前面提到的 “public Schema” 相混淆。

使用 REVOKE命令来撤销权限：

1

REVOKE ALL ON products FROM PUBLIC;

具体权限参考官方文档。

GRANT OPTION 权限依赖链

初始状态下，对象的 Owner 或超级用户拥有该对象的全部权限，并且可以授予或撤销权限。 GRANT OPTION（转授属性）：这是一种附加在特定权限（如 SELECT、UPDATE）上的特殊属性。当一个角色获得带有该属性的权限时，系统允许该角色充当“中间授权人”，将该权限进一步授予其他角色。 当非所有者角色使用其持有的 GRANT OPTION 向另一角色授权时，数据库内部会记录这种显式的依赖关系。

1
2
3

GRANT SELECT ON accounts TO alice WITH GRANT OPTION;  -- 由Owner执行
GRANT SELECT ON accounts TO bob;  -- 由alice执行
GRANT SELECT ON accounts TO charlie;  -- bob无法执行此命令，因为它没有取得GRANT OPTION

对象的 Owner 可以选择撤销自己的普通权限，例如使表对自己和他人均为只读。但 Owner 始终持有所有的GRANT OPTION，因此他们总是可以重新授予自己的权限。

当权限被撤销时，PostgreSQL 必须维护数据访问的合规性。这涉及两种处理模式：

级联撤销（CASCADE）

  • 当授权人撤销中间人（如 角色 A）的权限时，指定了 CASCADE。

    1

    REVOKE SELECT ON accounts FROM alice CASCADE;

  • 处理流程：

    1. 系统检索所有以角色 A 为 Grantor 的权限记录。

    2. 递归清理：所有直接或间接依赖于角色 A 的权限条目都会被系统自动删除。

  • 角色 B 以及 B 授权给 C 的所有相关权限都会失效。

限制撤销（RESTRICT）

  • 执行 REVOKE 时未指定 CASCADE（或显式指定 RESTRICT）。

    1

    REVOKE SELECT ON accounts FROM alice RESTRICT;

  • 如果系统发现该权限已经被进一步转授给他人（即存在下游依赖），则 REVOKE 操作将失败并报错。

  • 强制管理员在撤销上游权限前，必须先手动处理下游的权限分配。