数据库教程FGMT52‑PostgreSQL用户权限与安全管理

## 前言

数据库安全是业务系统不可忽视的核心防线，权限管控、访问接入控制、传输加密、密码策略、审计日志共同构成PostgreSQL纵深安全防御体系。很多线上安全事件，根源来自账号权限过度开放、访问策略宽松、明文传输、缺少操作审计。风哥教程本文以两台实验主机`fgedu‑net‑cn1`、`fgedu‑net‑cn2`开展实操，硬件规格统一8CPU、64GB内存，全部数据路径统一为`/fgedudb`；实例名、业务数据库名、业务账号统一使用`fgedudb`、`fgedudb`、`fgedu`。

风哥教程本文完整覆盖PostgreSQL角色账号体系、多层级权限管控、RLS行级安全、pg_hba.conf访问控制、SSL单向与双向加密连接、密码安全策略、日志审计、pgaudit审计插件、等保合规加固方案。面向DBA、运维工程师、数据库架构师；学习完成之后，能够按照最小权限原则完成业务账号规划，搭建加密传输与审计体系，满足企业等保测评安全要求。

### 本文内容大纲

1. PostgreSQL角色账号体系理论，系统内置默认角色

2. PostgreSQL多层权限模型：数据库、Schema、对象、列级、行级RLS安全

3. 业务生产权限完整实战案例

4. pg_hba.conf主机访问控制配置实操

5. PostgreSQL SSL单向、双向安全连接配置

6. 密码安全策略、密码有效期与复杂度管控

7. 原生日志审计配置，pgBadger日志分析工具实战

8. pgaudit审计插件部署与细粒度审计实战

9. 等保评测对应的数据库安全加固实施方案

10. 风哥针对本文总结

---

## 一、PostgreSQL角色账号体系理论，系统内置默认角色

PostgreSQL不存在传统意义独立“用户”概念，全部账号统一为**Role角色**。角色可以拥有`LOGIN`属性，此时该角色可以作为登录账号；没有LOGIN属性的角色，仅作为权限集合，用于权限继承分配。角色支持继承INHERIT、SET属性，可以实现权限分组管理，这是PostgreSQL权限体系非常重要的设计思想。

### 1.1 核心角色属性说明

|属性 |说明 |

|---|---|

|`LOGIN` |允许角色登录数据库，业务账号必须开启 |

|`SUPERUSER` |超级管理员，几乎绕过全部权限校验，生产严格限制使用 |

|`CREATEDB` |允许创建数据库 |

|`CREATEROLE` |允许创建其他角色 |

|`INHERIT` |继承被授予角色的全部权限，默认开启 |

|`NOINHERIT` |关闭继承，需要SET ROLE手动切换身份 |

|`PASSWORD` |设置登录密码 |

|`VALID UNTIL` |密码过期时间，实现密码有效期管控 |

> 风哥 itpux‑com

### 1.2 系统内置默认角色

PostgreSQL内置一套预定义系统角色，用于分配细分权限，避免直接授予superuser超级权限：

1. `pg_read_all_settings`：读取全部配置参数

2. `pg_read_all_stats`：读取全部统计信息，pg_stat_*视图

3. `pg_signal_backend`：可以终止其他backend会话进程，运维账号常用

4. `pg_monitor`：监控合集角色，包含读配置、读统计信息权限

`public`角色是一个特殊内置角色，**所有角色默认自动属于public**，新建对象默认会给public授予USAGE权限，生产环境安全加固必须回收public不必要权限，防止越权访问。

### 1.3 实验环境基础准备

主机`fgedu‑net‑cn1`，PostgreSQL实例数据目录`/fgedudb/pg_data/fgedudb`，操作系统postgres用户。

```bash

su - postgres

export PGDATA=/fgedudb/pg_data/fgedudb

export PATH=/fgedudb/pg_soft/pg_bin/bin:$PATH

#确认实例正常运行

pg_ctl status -D $PGDATA

psql -U postgres postgres

```

业务库预先创建：

```sql

CREATE DATABASE fgedudb ENCODING 'UTF8' LC_COLLATE 'en_US.UTF‑8' LC_CTYPE 'en_US.UTF‑8' TEMPLATE template0;

\c fgedudb

CREATE SCHEMA fgedu;

SET search_path TO fgedu,public;

CREATE TABLE t_biz_order (

id bigserial primary key,

order_no text,

cust_no text,

amount numeric(12,2),

create_time timestamp default now()

);

INSERT INTO t_biz_order(order_no,cust_no,amount) VALUES

('ORD2026001','CUST001',1200.00),

('ORD2026002','CUST002',3400.50);

```

> 风哥教程 113257174

## 二、PostgreSQL多层权限模型：数据库、Schema、对象、列级、行级RLS安全

PostgreSQL权限是分层模型，权限从上至下：**数据库 → Schema → 表/视图 → 列权限 → 行级RLS行安全策略**。权限不会自动向下传递；新建对象不会自动继承库、schema已经授予的权限，需要`ALTER DEFAULT PRIVILEGES`设置对象默认权限，这是DBA非常容易踩坑的知识点。

### 2.1 层级权限拆解

1. **Database数据库级别权限**：CONNECT（登录数据库）、CREATE（在库下创建schema）、TEMP（创建临时表）；**即便账号拥有schema权限，如果没有database的CONNECT权限，仍然无法进入数据库**。

2. **Schema模式级别权限**：USAGE（可以访问schema内对象）、CREATE（可以在schema创建新对象）。

3. **表/视图对象级别权限**：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER。

4. **列级权限**：针对表部分字段单独授予SELECT/UPDATE，敏感字段（手机号、身份证）可以只允许部分账号访问。

5. **RLS行级安全策略Row‑Level Security**：行过滤，同一套表，不同账号只能看到符合条件的数据行，多租户隔离场景非常实用；RLS需要在表上`ENABLE ROW LEVEL SECURITY`开启，然后创建policy策略；注意超级用户、表拥有者默认绕过RLS策略检查。

> 网上搜索风哥教程可以学习全套数据库教程

### 2.2 关键语法说明

- `GRANT xxx ON DATABASE xxx TO role`：数据库层授权

- `GRANT xxx ON SCHEMA xxx TO role`：schema层授权

- `GRANT xxx ON ALL TABLES IN SCHEMA xxx TO role`：schema下全部已有表授权

- `ALTER DEFAULT PRIVILEGES IN SCHEMA xxx GRANT ... TO role`：未来新建对象自动授予权限

- `GRANT(col1,col2) ON TABLE xxx TO role`：列粒度权限

- `CREATE POLICY ... ON TABLE xxx`：RLS行安全策略

## 三、业务生产权限完整实战案例

业务场景：业务库`fgedudb`，schema `fgedu`；设计三类角色：

1. `fgedu_rw_role`：读写角色，业务应用账号使用；

2. `fgedu_ro_role`：只读角色，报表、数据分析；

3. `fgedu_dev_role`：开发人员，仅查询，禁止修改业务正式数据。

业务登录账号：`fgedu`（继承rw_role，业务程序连接），`fgedu_ro`（报表只读账号）。

### 3.1 创建角色与登录账号

```sql

\c fgedudb

--创建权限集合角色，无LOGIN

CREATE ROLE fgedu_rw_role NOLOGIN;

CREATE ROLE fgedu_ro_role NOLOGIN;

CREATE ROLE fgedu_dev_role NOLOGIN;

--创建可登录业务账号

CREATE ROLE fgedu WITH LOGIN PASSWORD 'Fgedu@Db123';

CREATE ROLE fgedu_ro WITH LOGIN PASSWORD 'FgeduRo@456';

--账号继承对应权限集合

GRANT fgedu_rw_role TO fgedu;

GRANT fgedu_ro_role TO fgedu_ro;

```

### 3.2 分层授予权限，遵循最小权限原则

```sql

--1、数据库CONNECT权限

GRANT CONNECT ON DATABASE fgedudb TO fgedu_rw_role,fgedu_ro_role,fgedu_dev_role;

--2、schema权限

GRANT USAGE,CREATE ON SCHEMA fgedu TO fgedu_rw_role;

GRANT USAGE ON SCHEMA fgedu TO fgedu_ro_role,fgedu_dev_role;

--3、已有全部表权限

GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA fgedu TO fgedu_rw_role;

GRANT SELECT ON ALL TABLES IN SCHEMA fgedu TO fgedu_ro_role,fgedu_dev_role;

--序列权限，业务写入需要sequence

GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA fgedu TO fgedu_rw_role;

--4、设置【未来新建对象】的默认权限，防止新建表没有权限访问

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA fgedu

GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO fgedu_rw_role;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA fgedu

GRANT SELECT ON TABLES TO fgedu_ro_role,fgedu_dev_role;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA fgedu

GRANT USAGE,SELECT ON SEQUENCES TO fgedu_rw_role;

```

### 3.3 列级权限实战，敏感字段隔离

`t_biz_order`中`cust_no`客户编号属于敏感字段，报表账号禁止读取该列：

```sql

--回收报表账号对cust_no列读取权限

REVOKE SELECT(cust_no) ON TABLE fgedu.t_biz_order FROM fgedu_ro_role;

--仅允许读取其他列

GRANT SELECT(id,order_no,amount,create_time) ON TABLE fgedu.t_biz_order TO fgedu_ro_role;

```

### 3.4 RLS行级安全策略实战（多租户场景）

模拟租户隔离，不同业务账号只能看到自己租户的数据，开启RLS行安全：

```sql

ALTER TABLE fgedu.t_biz_order ENABLE ROW LEVEL SECURITY;

--创建策略：普通业务账号只能看到自己相关行，示例按cust_no做租户隔离

CREATE POLICY pol_biz_cust_isolation

ON fgedu.t_biz_order

FOR ALL

USING ( current_user = fgedu );

```

> 注意：表拥有者、superuser会绕过RLS策略；生产上线前必须使用普通业务账号登录验证RLS是否生效。

### 3.5 回收public危险权限（安全加固重点）

public角色默认全部账号都会继承，生产环境需要回收不必要权限，防止越权：

```sql

\c fgedudb

REVOKE ALL ON SCHEMA public FROM public;

GRANT USAGE ON SCHEMA public TO public;

```

> 风哥数据库教程 itpux‑com

### 3.6 权限查看运维命令

```sql

--查看角色列表

\du

--查看schema下对象权限

\dp fgedu.*

--查看数据库权限

\l

--查看RLS策略

\d+ fgedu.t_biz_order

```

## 四、pg_hba.conf主机访问控制配置实操

`pg_hba.conf`是PostgreSQL客户端接入控制文件，控制哪些IP、哪些账号可以连接哪个数据库，认证方式是什么；匹配规则**从上往下依次匹配，匹配到第一条规则即停止，顺序非常关键**。

TYPE类型：

1. `local`：unix‑socket本地套接字连接；

2. `host`：TCP/IP普通连接（可以SSL也可以非SSL）；

3. `hostssl`：**必须SSL加密TCP连接**，拒绝非SSL接入；

4. `hostnossl`：仅允许非SSL连接。

METHOD认证方式：`scram‑sha‑256`（推荐密码认证）、md5、trust、reject、password、ident。

> 生产禁止trust远程，trust不需要密码直接登录，高危风险。

### 4.1 编辑pg_hba.conf

文件路径`/fgedudb/pg_data/fgedudb/pg_hba.conf`

```ini

#本地unix套接字，scram‑sha‑256密码认证

local   all             all                                     scram‑sha‑256

#本地回环TCP

host    all             all             127.0.0.1/32            scram‑sha‑256

#业务应用服务器网段，强制SSL加密访问业务库fgedudb

hostssl fgedudb         fgedu           192.168.10.0/24         scram‑sha‑256

#报表账号仅允许报表服务器IP访问

hostssl fgedudb         fgedu_ro        192.168.20.10/32        scram‑sha‑256

#拒绝其他所有外来访问，放在规则末尾

host    all             all             0.0.0.0/0               reject

host    all             all             ::/0                    reject

```

修改完成重载配置，不需要重启实例：

```bash

pg_ctl reload -D /fgedudb/pg_data/fgedudb

```

> 上51CTO搜索风哥可以学习全套数据库教程

### 4.2 查看pg_hba配置是否加载生效

```sql

SELECT * FROM pg_hba_file_rules;

```

> 运维风险提示：规则顺序错误会直接导致业务无法连接；修改前务必备份pg_hba.conf文件。

## 五、PostgreSQL SSL单向、双向安全连接配置

业务跨网段、公网访问数据库，必须开启SSL/TLS加密，防止抓包窃取账号密码与业务数据。分为**单向SSL（服务端证书，客户端校验服务端）**、**双向SSL（客户端也必须提交证书，数据库校验客户端身份）**。

### 5.1 生成自签名证书（主机fgedu‑net‑cn1）

证书统一放置数据目录`/fgedudb/pg_data/fgedudb`，操作系统postgres用户，权限严格保护，私钥权限400。

```bash

cd /fgedudb/pg_data/fgedudb

#生成CA根证书

openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=fgedu‑ca"

openssl x509 -req -in root.csr -days 3650 -extfile /etc/pki/tls/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt

#生成服务端证书

openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=fgedu‑net‑cn1"

openssl x509 -req -in server.csr -days 3650 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

#权限设置，私钥禁止其他用户读取

chmod 400 server.key root.key

chown postgres:postgres *.crt *.key *.csr

```

### 5.2 postgresql.conf开启SSL（单向SSL）

修改`/fgedudb/pg_data/fgedudb/postgresql.conf`

```ini

ssl = on

ssl_ca_file = '/fgedudb/pg_data/fgedudb/root.crt'

ssl_cert_file = '/fgedudb/pg_data/fgedudb/server.crt'

ssl_key_file = '/fgedudb/pg_data/fgedudb/server.key'

```

重载配置或者重启实例：

```bash

pg_ctl reload -D /fgedudb/pg_data/fgedudb

```

### 5.3 单向SSL客户端连接测试

sslmode参数：`require`强制使用SSL；`verify‑ca`校验CA；`verify‑full`校验主机名匹配证书。

```bash

psql "host=fgedu‑net‑cn1 port=5432 dbname=fgedudb user=fgedu password='Fgedu@Db123' sslmode=require"

```

登录后查看连接是否为SSL加密：

```sql

SELECT ssl,version,cipher FROM pg_stat_ssl WHERE pid=pg_backend_pid();

```

返回ssl=t代表加密链路正常。

### 5.4 双向SSL配置（校验客户端证书身份）

1. 生成客户端证书，使用上面CA根签名；把`root.crt`、客户端`client.crt`、`client.key`下发给业务应用客户端机器。

2. 修改`pg_hba.conf`对应规则，增加`clientcert=verify‑ca`，要求客户端必须提交合法CA签发证书：

```ini

hostssl fgedudb  fgedu 192.168.10.0/24 scram‑sha‑256 clientcert=verify‑ca

```

3. 客户端连接指定证书文件：

```bash

psql "host=fgedu‑net‑cn1 port=5432 dbname=fgedudb user=fgedu sslmode=verify‑ca sslrootcert=root.crt sslcert=client.crt sslkey=client.key"

```

> 注意：双向SSL运维复杂度高，证书过期会直接导致业务断连，需要建设证书过期巡检机制。

## 六、密码安全策略、密码有效期与复杂度管控

PostgreSQL原生支持密码有效期`VALID UNTIL`；密码复杂度需要通过扩展模块`passwordcheck`实现，强制密码长度、大小写、数字符号，防止弱密码。

### 6.1 密码有效期设置实操

```sql

--设置账号密码，有效期90天

ALTER ROLE fgedu WITH LOGIN PASSWORD 'Fgedu@Db123' VALID UNTIL '2026‑12‑15';

--取消过期限制

ALTER ROLE fgedu VALID UNTIL 'infinity';

--查看角色密码过期时间

SELECT rolname,rolvaliduntil FROM pg_roles WHERE rolname IN ('fgedu','fgedu_ro');

```

### 6.2 passwordcheck密码复杂度扩展

编译版本需要启用`passwordcheck`预加载库；修改postgresql.conf，共享库预加载：

```ini

shared_preload_libraries = 'passwordcheck'

```

重启实例生效。之后设置弱密码会直接抛出报错，拒绝创建。

测试：设置过于简单密码会返回报错信息。

```sql

ALTER ROLE fgedu PASSWORD '123456';

```

> 生产运维提示：应用业务账号密码过期风险很高，业务程序不会自动修改密码；业务程序账号尽量不设置VALID UNTIL过期，人工定期轮换密码；人为操作的DBA运维账号开启密码有效期管控。

## 七、原生日志审计配置，pgBadger日志分析工具实战

PostgreSQL可以开启原生日志，记录登录登出、checkpoint、锁等待、慢SQL；pgBadger是开源日志解析工具，将原始日志生成HTML审计报告，适合运维审计、故障排查。

### 7.1 postgresql.conf审计相关日志参数（适配64G/8C主机）

编辑`/fgedudb/pg_data/fgedudb/postgresql.conf`

```ini

logging_collector = on

log_directory = '/fgedudb/pg_log'

log_filename = 'postgresql‑%Y%m%d.log'

log_destination = 'stderr'

log_connections = on

log_disconnections = on

log_checkpoints = on

log_lock_waits = on

log_temp_files = 0

log_autovacuum_min_duration = 0

log_min_duration_statement = 1000

log_error_verbosity = default

log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '

lc_messages = 'C'

```

创建日志目录，权限postgres，重启实例生效：

```bash

mkdir -p /fgedudb/pg_log

chown postgres:postgres /fgedudb/pg_log

pg_ctl restart -D /fgedudb/pg_data/fgedudb

```

### 7.2 安装pgBadger日志分析工具

```bash

yum install -y pgbadger

#执行日志分析，输出html审计报告

pgbadger /fgedudb/pg_log/postgresql‑*.log -o /fgedudb/pg_log/pg_report.html

```

报告包含登录统计、慢SQL、锁等待、checkpoint、autovacuum统计，用于日常审计排查。

## 八、pgaudit审计插件部署与细粒度审计实战

原生日志只能记录执行耗时，pgaudit扩展插件实现细粒度审计，可以记录DDL、DML、SELECT查询、角色变更，满足等保对于操作行为留痕的要求。

### 8.1 部署pgaudit扩展

编译安装pgaudit，修改postgresql.conf预加载库：

```ini

shared_preload_libraries = 'pgaudit,passwordcheck'

pgaudit.log = 'ddl,read,write'

pgaudit.log_parameter = on

```

重启实例。登录数据库创建扩展：

```sql

CREATE EXTENSION IF NOT EXISTS pgaudit;

```

### 8.2 pgaudit审计参数说明

- `ddl`：记录所有DDL语句，create/alter/drop；

- `read`：记录SELECT查询；

- `write`：INSERT、UPDATE、DELETE、TRUNCATE；

- `role`：记录角色账号权限变更（create role、alter role、grant/revoke）。

审计日志会输出到pg日志文件，可以配合pgBadger做统一解析。

> 性能提示：开启read会记录全部SELECT查询，高并发业务会产生大量日志，磁盘IO上升；生产根据安全需求取舍，不需要审计查询行为可以关闭read，只审计ddl+write+role。

## 九、等保评测对应的数据库安全加固实施方案

结合等保2.0数据库安全测评要求，风哥整理PostgreSQL生产加固检查清单，分为身份鉴别、访问控制、安全审计、数据完整性保密性、入侵防范。

1. **身份鉴别**

- 禁止超级用户superuser业务账号；业务账号使用scram‑sha‑256强哈希密码；

- 运维DBA账号设置密码有效期，启用passwordcheck密码复杂度；

- 禁止trust远程认证；本地socket也尽量使用密码认证。

2. **访问控制**

- pg_hba.conf严格限制来源IP，最小访问源；业务使用hostssl强制SSL加密；

- 回收public角色多余权限；严格执行最小权限模型，分层database‑schema‑table权限；

- 敏感业务表启用RLS行级安全，敏感字段使用列级权限隔离。

3. **安全审计**

- 开启日志收集，记录登录登出、锁、慢查询；部署pgaudit做DDL/DML审计；

- 审计日志目录权限保护，禁止普通用户修改；日志定期归档留存，满足6个月日志留存测评要求；

- 使用pgBadger定期生成审计报告。

4. **数据传输与存储**

- 业务访问强制SSL单向/双向加密，杜绝明文TCP访问数据库；

- 重要业务数据，应用层实现字段加密存储。

5. **运维管控**

- 禁止业务账号CREATEROLE、CREATEDB权限；

- 定期巡检角色账号，清理过期废弃登录账号；

- 定期巡检pg_hba.conf规则，禁止0.0.0.0宽泛来源规则。

## 风哥针对本文总结

风哥教程本文完整讲解PostgreSQL用户权限与安全管理体系，包含角色账号体系、系统内置默认角色、多层权限模型（数据库、Schema、表、列、RLS行级安全），完整生产业务权限实战案例，pg_hba.conf接入访问控制，SSL单向、双向加密连接配置，密码有效期与passwordcheck密码复杂度，原生日志审计、pgBadger日志解析工具，pgaudit细粒度审计插件，等保2.0数据库安全加固方案。

PostgreSQL权限核心关键点：角色继承机制、public角色默认权限风险；权限不会自动向下传递，新建对象需要`ALTER DEFAULT PRIVILEGES`配置默认权限；pg_hba从上向下顺序匹配规则，配置错误直接导致业务断连；RLS行安全策略不会对superuser、表拥有者生效，上线必须普通账号验证。

SSL单向加密解决传输明文风险；双向SSL安全性更高，但证书运维复杂度提升；pgaudit审计会带来一定性能开销，高并发业务需要权衡审计粒度。生产环境严格遵守最小权限原则，业务账号禁止superuser超级权限；审计日志需要满足等保周期留存。

掌握本套风哥教程全部实操，可以独立完成PostgreSQL业务账号设计、访问接入控制、传输加密、审计体系搭建，完成等保安全测评相关数据库加固工作，为生产数据库安全运维打下坚实基础。