角色、权限与日常管理

PG 用 ROLE 统一用户与组；权限可细化到表、列、schema。与 /roadmaps/postgresql-dba 管理节点对齐。

学习目标

• 创建角色并授予 CONNECT、USAGE、SELECT 等权限
• 使用 \du、\dp 审计权限
• 掌握 VACUUM、ANALYZE 与连接数查看

核心概念

角色与授权

CREATE ROLE app_read LOGIN PASSWORD 'readpass';
CREATE DATABASE codeforge_pg OWNER dba;

\c codeforge_pg
CREATE SCHEMA app AUTHORIZATION dba;
GRANT USAGE ON SCHEMA app TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO app_read;

只读账号不给 CREATE、INSERT；应用账号与 DBA 超级用户分离。

日常巡检 SQL

SELECT count(*) FROM pg_stat_activity;
SELECT datname, numbackends FROM pg_stat_database;

SELECT schemaname, relname, n_live_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

# 维护（低峰执行）
VACUUM (VERBOSE, ANALYZE) app.cf_track;

pg_hba.conf 片段

# TYPE  DATABASE        USER            ADDRESS         METHOD
host    codeforge_pg    app_read        10.0.0.0/8      scram-sha-256

改 pg_hba.conf 后需 SELECT pg_reload_conf(); 或重启实例。

与 MySQL/Spring Boot 对照

项	PostgreSQL	MySQL 8
用户模型	ROLE（可 LOGIN）	USER
授权	GRANT ON schema/table	GRANT ON db.table
只读	GRANT SELECT	GRANT SELECT
维护	VACUUM / autovacuum	无等价（InnoDB purge 自动）
Spring 用户	spring.datasource.username	同左，换驱动即可

本站 MySQL 应用账号通常具备 DDL 迁移权限（Flyway）；PG 生产应拆 迁移账号 与 运行时账号。

练习

• 创建 app_rw（读写）与 app_read，分别用 psql 验证权限
• 对一张练习表执行 VACUUM ANALYZE，查看 pg_stat_user_tables 变化
• 列出当前 5 个活跃连接及 state、query 前缀

小结

最小权限 + 定期 VACUUM/ANALYZE 是 DBA 管理基线。 下一课 DBA 视角 SQL 与系统目录。