Debian系统PostgreSQL权限管理怎样进行 - 问答

Debian 上 PostgreSQL 权限管理实操指南

一核心概念与默认安全模型

PostgreSQL 使用角色 role统一表示用户与权限集合；安装后默认创建postgres超级用户与同名数据库，Debian 上本地连接默认多采用peer认证（以操作系统同名用户直接免密进入 psql），因此应先切换到 postgres 系统用户再操作数据库：sudo -u postgres psql。如需密码登录，可在 psql 中执行：ALTER USER postgres WITH PASSWORD 'new_password';。以上机制决定了日常权限管理应以“角色—数据库/模式—表/序列”的层级进行精细化授权。

二用户与角色管理常用命令

创建角色：CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongPass!';
修改密码：ALTER USER app_user WITH PASSWORD 'NewPass!';
删除角色：DROP ROLE IF EXISTS app_user;
命令行工具：createuser / dropuser 可快速创建/删除角色（需具备相应权限）。
查看用户：SELECT * FROM pg_user WHERE usename = 'app_user';
授权/回收示例：GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user;、REVOKE ALL PRIVILEGES ON DATABASE mydb FROM app_user;
以上命令覆盖日常增删改查与授权回收，适合在 psql 或 -c 方式批量执行。

三数据库与模式级权限配置

最小权限范式（只读示例）：

-- 1) 连接与模式使用
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT USAGE  ON SCHEMA public TO read_only;

-- 2) 现有表只读
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- 3) 未来新建表自动继承只读（关键！）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO read_only;

应用专属读写角色（按需细化到表/列/序列）：

-- 应用角色
CREATE ROLE app_rw WITH LOGIN PASSWORD 'AppPass!';

-- 库级连接
GRANT CONNECT ON DATABASE mydb TO app_rw;

-- 模式使用
GRANT USAGE  ON SCHEMA public TO app_rw;

-- 现有对象（示例：对单表授予增改查，不含删）
GRANT SELECT, INSERT, UPDATE ON TABLE public.orders TO app_rw;

-- 序列（如自增主键依赖）
GRANT USAGE, SELECT ON SEQUENCE public.orders_id_seq TO app_rw;

-- 未来对象默认授权（按需选择表/序列）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_rw;

撤销示例：REVOKE DELETE ON TABLE public.customers FROM sales_team;
要点：PostgreSQL 默认不会自动把新对象的权限授予已有角色，务必使用ALTER DEFAULT PRIVILEGES为“未来对象”设置默认授权，避免上线后权限缺失。

四连接与认证控制

配置文件路径：按版本位于 /etc/postgresql//main/，核心为 postgresql.conf 与 pg_hba.conf。
监听地址：在 postgresql.conf 中设置 listen_addresses = '*'（或指定内网地址）以允许远程监听。

客户端认证：在 pg_hba.conf 中按“TYPE DATABASE USER ADDRESS METHOD”逐条控制，例如：

# 本地 Unix 域套接字：保持 peer（或 local）更安全
local   all             all                                     peer

# 本机回环：使用 SCRAM-SHA-256 或 md5 密码
host    all             all             127.0.0.1/32            scram-sha-256

# 内网办公网段：仅允许特定网段密码登录
host    mydb            app_rw          192.168.10.0/24        scram-sha-256

# 其他来源：拒绝
host    all             all             0.0.0.0/0               reject

防火墙：如使用 UFW，放行端口 sudo ufw allow 5432/tcp。
变更生效：sudo systemctl restart postgresql。
安全建议：优先使用scram-sha-256；仅在受控网络或特定主机上使用 trust；为应用创建最小权限专用角色，避免长期使用超级用户直连应用。

五审计与权限核查

审计日志（按需开启，生产谨慎全量）：在 postgresql.conf 中启用

log_statement = 'all'            -- 或 'mod' / 'ddl'
log_connections = on
log_disconnections = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

重启后生效，用于追踪登录与关键操作。

权限核查 SQL（定位“谁对哪些对象有哪些权限”）：

-- 表级权限清单
SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'app_user';

-- 角色继承关系（组/角色成员）
SELECT roleid::regrole AS role, member::regrole AS member
FROM pg_auth_members;

日志分析：可用 pgBadger 定期生成 HTML 报告（安装：sudo apt-get install pgbadger），便于发现异常连接与慢查询。

0 赞

0 踩