ubuntu pgadmin权限分配最佳实践

Ubuntu 上 pgAdmin 权限分配最佳实践

一 架构与最小权限原则

• 明确职责边界：区分实例级（cluster）与数据库级管理，pgAdmin 左侧对象浏览器中的层级为：Servers → PostgreSQL 版本 → Databases / Login/Group Roles / Tablespaces。实例级对象（如角色、表空间）被所有数据库共享，数据库之间默认隔离。
• 统一用“角色 Role”管理权限：自 PostgreSQL 8.1 起，用户与组统一为角色，带 LOGIN 属性的角色才是可登录账户；不带 LOGIN 的作为“组角色”聚合权限，再通过 GRANT role TO member 赋予成员。
• 遵循最小权限：日常业务使用普通角色；超级用户 postgres仅用于安装、迁移、紧急修复；按需授予 CREATEDB / CREATEROLE 等高风险权限，避免滥用。
• 所有权与分工：每个业务库建议设置单一所有者（如团队负责人），对象创建默认归其所有，再通过角色授权给成员，减少所有权分散。

二 角色与权限模型设计

• 建议预置以下角色，并通过组成员关系继承权限，便于规模化治理：

• 成员关系示例：
  • GRANT app_dev, app_read TO alice;
  • GRANT etl_user TO etl_bot;
  • GRANT report_user TO bi_analyst;
• 说明：上述为“组角色”聚合权限的范式，成员获得的是“被授权”的能力，而非对象所有权，便于审计与回收。

三 在 pgAdmin 中的落地步骤

• 创建组角色与登录角色
  • 在左侧展开目标实例 → Login/Group Roles → 右键 Create → Role：
    • 组角色：取消勾选 Login，按需勾选 CREATEDB / CREATEROLE；
    • 登录角色：勾选 Login，设置强口令，必要时在 Membership 页将其加入相应组角色。
• 创建数据库并指定所有者
  • 在 Databases → 右键 Create → Database：设置 Name、Owner=app_owner，其他保持默认。
• 在目标库内授予模式与对象权限
  • 展开目标库 → Schemas → 选中业务 schema → Properties → Privileges：
    • 授予组角色：USAGE（必须）；
    • 如由 app_owner 创建对象，默认属主为 app_owner，无需额外更改属主；
  • 展开 Tables（或 Views/Functions）→ 批量编辑 Privileges：
    • 对 app_dev：勾选 SELECT/INSERT/UPDATE/DELETE/REFERENCES/TRIGGER；
    • 对 app_read：仅勾选 SELECT；
• 按需细化到列/行级（可选）
  • 列级：在表属性的 Columns → Privileges 为特定列授予/回收 SELECT/UPDATE；
  • 行级：创建安全策略（Row Level Security，RLS），在表 Properties → Security 中新建策略，仅对指定角色开放行访问。
• 验证
  • 使用对应登录角色连接目标库与 schema，执行 \dn+ / \dp 查看权限，或执行受限查询验证 RLS。

四 连接与安全加固

• 网络与认证
  • 仅开放必要来源 IP；在 postgresql.conf 设置 listen_addresses（如监听内网地址），在 pg_hba.conf 为不同来源配置合适的认证方式（如 hostssl + md5/scram-sha-256），并定期 reload/重启使配置生效。
• 传输加密
  • 在 pgAdmin 服务器属性中勾选 Use SSL 连接到数据库，避免凭据与数据在网络中明文传输。
• 系统侧加固
  • 禁用或删除无用账号，设置口令复杂度与有效期；
  • 加固 SSH（禁用 root 远程登录、启用密钥登录、限制失败重试）；
  • 部署 Fail2Ban 防暴力破解；
  • 配置日志与会话超时（如 TMOUT），便于审计与降低会话劫持风险。

五 自动化与审计建议

• 基础设施即代码：用 Ansible/ Terraform 管理角色、数据库、schema 与权限，模板化生成策略，避免人工遗漏与漂移。
• 变更留痕：在 pgAdmin 或通过脚本执行 DDL 时，开启数据库日志（log_statement=ddl 或更高），并将 pgAdmin 操作与数据库审计日志关联归档。
• 定期巡检：
  • 列出未使用的角色与登录：SELECT rolname FROM pg_roles WHERE NOT rolcanlogin AND NOT rolsuperuser AND rolname !~ ‘^pg_’;
  • 检查对象所有权与默认权限：\dp、\dn+；对异常授权及时回收；
  • 复核 pg_hba.conf 与监听地址，确保最小暴露面。