数据库教程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权限体系非常重要的设计思想。

> 风哥 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业务账号设计、访问接入控制、传输加密、审计体系搭建，完成等保安全测评相关数据库加固工作，为生产数据库安全运维打下坚实基础。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

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

相关推荐

联系我们