PostgreSQL 限制用户登录和访问非Owner数据库

在实际生产环境中，我们常常需要精细控制数据库用户的访问权限。本文将介绍如何在 PostgreSQL 18 中实现两个核心安全需求：

限制特定用户只能从特定位置登录
禁止用户访问非自己拥有的数据库

一、使用 pg_hba.conf 限制用户登录

pg_hba.conf 是 PostgreSQL 的客户端认证配置文件，通过它可以精确控制谁能、从哪里、使用什么方式连接数据库。

1. 场景描述

生产环境的安全要求：

postgres 超级用户：允许从外网登录（便于远程运维）
其他业务用户：仅允许从局域网访问（内网安全隔离）
其他来源：拒绝所有连接

2. 配置示例

编辑 pg_hba.conf 文件：

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

# 允许局域网用户连接（私有网段）
host    all             all             10.0.0.0/8              scram-sha-256
host    all             all             172.16.0.0/12           scram-sha-256
host    all             all             192.168.0.0/16          scram-sha-256
# 允许 postgres 从任意位置连接
host    all             postgres        0.0.0.0/0               scram-sha-256
# 拒绝其他所有连接
host    all             all             0.0.0.0/0               reject

前面几条是 PostgreSQL 自带的配置, 用户本地访问(不验证密码)和主从复制雍的规则.

如果要把本地访问加上密码验证, 需要把 trust 改成 peer.

配置文件匹配逻辑是从上到下的, 命中之后就返回了. 所以配置的顺序很重要.

3. 配置逻辑图

flowchart TD
    A[客户端连接请求] --> B{匹配 pg_hba.conf 规则}
    B --> C{来源是局域网?}
    C -->|是 10.0.0.0/8| D[允许所有用户]
    C -->|是 172.16.0.0/12| D
    C -->|是 192.168.0.0/16| D
    C -->|否| E{用户是 postgres?}
    E -->|是| F[允许连接]
    E -->|否| G[拒绝连接]
    D --> H[认证成功]
    F --> H

4. 规则解析

规则	地址范围	用户	结果	说明
16-18	局域网私有段	所有用户	允许	业务用户仅限内网
20	0.0.0.0/0	postgres	允许	超级用户可远程运维
22	0.0.0.0/0	所有用户	拒绝	兜底规则，禁止其他来源

5. 使配置生效

# 重新加载配置（不中断现有连接）
psql -c "SELECT pg_reload_conf();"

# 或重启服务
systemctl reload postgresql

# 或者使用 pg_ctl 工具（需切换到 postgres 用户）
pg_ctl reload -D /你的数据目录路径

二、禁止用户访问非Owner数据库

默认情况下，PostgreSQL 的 PUBLIC 角色对所有数据库拥有 CONNECT 权限。这意味着任何用户都可以连接到任何数据库。

1. 问题演示

-- 创建业务用户
CREATE USER app_user WITH PASSWORD 'secure123';

-- 创建另一个业务的数据库（owner 是 postgres）
CREATE DATABASE admin_db OWNER postgres;

-- app_user 竟然可以连接！
\c admin_db app_user
-- 连接成功，存在安全隐患

2. 解决方案：撤销 PUBLIC 的连接权限

-- 对每个数据库执行一次
REVOKE CONNECT ON DATABASE db_name FROM PUBLIC;

3. 完整操作流程

flowchart LR
    A[创建新数据库] --> B[撤销 PUBLIC 连接权限]
    B --> C[验证权限隔离]
    C --> D[安全隔离完成]

-- 1. 创建业务用户和数据库
CREATE USER app_user WITH PASSWORD 'secure_password';
CREATE DATABASE app_db OWNER app_user;

-- 2. 撤销 PUBLIC 的连接权限（关键步骤）
REVOKE CONNECT ON DATABASE app_db FROM PUBLIC;

-- 3. 验证：其他用户无法连接
\c app_db other_user
-- FATAL: permission denied for database "app_db"

4. 批量处理现有数据库

-- 生成批量撤销脚本
SELECT 'REVOKE CONNECT ON DATABASE "' || datname || '" FROM PUBLIC;'
FROM pg_database
WHERE datistemplate = false
  AND datname != 'postgres';

-- 执行生成的 SQL 语句
REVOKE CONNECT ON DATABASE app_db FROM PUBLIC;
REVOKE CONNECT ON DATABASE admin_db FROM PUBLIC;
REVOKE CONNECT ON DATABASE log_db FROM PUBLIC;

5. 权限验证

-- 查看数据库连接权限
SELECT datname, datacl FROM pg_database
WHERE datistemplate = false;

-- 安全配置后的结果示例
-- datname   |              datacl
-- ----------+----------------------------------
-- app_db    | {app_user=CTc/app_user}
-- admin_db  | {postgres=CTc/postgres}
--           -- 注意：没有 =c/postgres（即 PUBLIC 无权限）

三、完整安全架构

flowchart TB
    subgraph Network["网络层 - pg_hba.conf"]
        A[局域网 10/8, 172.16/12, 192.168/16]
        B[外网 0.0.0.0/0]
    end

    subgraph Users["用户层"]
        C[postgres<br/>超级用户]
        D[app_user<br/>业务用户]
        E[readonly_user<br/>只读用户]
    end

    subgraph Databases["数据库层 - REVOKE CONNECT"]
        F[app_db<br/>owner: app_user]
        G[admin_db<br/>owner: postgres]
        H[log_db<br/>owner: app_user]
    end

    A --> |所有用户| F
    A --> |所有用户| G
    A --> |所有用户| H
    B --> |仅 postgres| C
    B -.-> |拒绝| D
    B -.-> |拒绝| E

    C --> F
    C --> G
    C --> H
    D --> F
    D -.-> |无权限| G
    D --> H

    style B fill:#fbb,stroke:#333
    style G fill:#bbf,stroke:#333

安全配置清单

层级	配置项	作用	配置方式
网络层	pg_hba.conf	限制登录来源	文件配置
数据库层	REVOKE CONNECT	隔离数据库访问	SQL 命令
Schema层	GRANT/REVOKE	控制对象访问	SQL 命令

四、常见问题

Q1: 撤销 PUBLIC 权限后，Owner 是否仍能连接？

是的。数据库 Owner 拥有隐式权限，不受 REVOKE 影响。

Q2: 如何允许特定用户连接特定数据库？

-- 授予特定用户连接权限
GRANT CONNECT ON DATABASE app_db TO readonly_user;

Q3: 如何为新数据库自动设置权限？

不好搞, 要么借助自定义函数, 要么借助外部脚本.

Q4: 生产环境配置顺序？

flowchart LR
    A[配置 pg_hba.conf] --> B[重启/重载服务]
    B --> C[创建数据库]
    C --> D[撤销 PUBLIC 权限]
    D --> E[创建业务用户]
    E --> F[授权必要权限]

总结

安全措施	作用范围	配置位置	防护目标
pg_hba.conf	网络连接	文件配置	限制登录来源
REVOKE CONNECT	数据库	SQL 命令	隔离数据库访问

结合使用可实现：

✅ postgres 超级用户可从外网远程运维
✅ 业务用户仅限局域网访问
✅ 用户只能访问自己拥有的数据库
✅ 敏感数据得到有效保护

参考文档：PostgreSQL 18 官方文档 - The pg_hba.conf File