pgadmin在ubuntu上如何进行权限管理

在 Ubuntu 上使用 pgAdmin 进行权限管理

一 管理范围与准备

• 权限分为两层：
  • 系统层：操作系统用户与组、sudo 权限、以及运行 pgAdmin 的系统服务账户（如通过本地桌面或 WSGI/容器运行）。
  • 数据库层：PostgreSQL 的角色、数据库/模式/表的GRANT/REVOKE 权限。
• 准备要点：
  • 使用具有管理员权限的账户登录 Ubuntu，并能以 postgres 系统用户进入 psql。
  • 已在 pgAdmin 中创建服务器连接，且连接用户具备足够的数据库权限（如 CREATEROLE/CREATEDB 等）。

二 在 pgAdmin 中管理数据库角色与权限

• 创建与编辑角色
  • 在左侧展开服务器 → 登录/组角色（Login/Group Roles）→ 右键创建 → 填写用户名与密码。
  • 在“定义/参数”页可设置角色属性（如 SUPERUSER、CREATEDB、CREATEROLE、INHERIT、LOGIN、REPLICATION 等），保存后角色即创建完成。
• 授予数据库级权限
  • 展开目标数据库 → 右键“权限”→ 选择角色 → 勾选相应权限（如 CONNECT、CREATE、TEMPORARY 等）→ 保存。
  • 或在“SQL”页执行：GRANT CONNECT, CREATE ON DATABASE dbname TO role_name;
• 授予模式与表级权限
  • 展开数据库 → 模式（Schemas）→ 目标模式 → 权限，为角色授予 USAGE；在“表/视图/序列”节点为角色授予 SELECT/INSERT/UPDATE/DELETE/TRUNCATE/REFERENCES/TRIGGER 等。
  • 或在“SQL”页执行示例：
    • GRANT USAGE ON SCHEMA public TO role_name;
    • GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO role_name;
    • 如需对未来表生效：ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role_name;
• 撤销与验证
  • 在相应节点“权限”页取消勾选，或用 REVOKE 撤销；通过切换为该角色登录或执行测试查询验证权限是否生效。

三 常用授权 SQL 模板（可在 pgAdmin 的 SQL 编辑器执行）

• 创建只读用户并授予对某库的只读
  • CREATE ROLE readonly WITH LOGIN PASSWORD ‘pwd’;
  • GRANT CONNECT ON DATABASE dbname TO readonly;
  • GRANT USAGE ON SCHEMA public TO readonly;
  • GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
  • ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
• 创建读写用户
  • CREATE ROLE app_rw WITH LOGIN PASSWORD ‘pwd’;
  • GRANT CONNECT ON DATABASE dbname TO app_rw;
  • GRANT USAGE ON SCHEMA public TO app_rw;
  • GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
  • ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
• 将某库的所有权转给某角色
  • ALTER DATABASE dbname OWNER TO new_owner;
• 撤销权限
  • REVOKE ALL PRIVILEGES ON DATABASE dbname FROM role_name;
  • REVOKE ALL PRIVILEGES ON SCHEMA public FROM role_name;
  • REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM role_name; 说明：上述为常见最小权限集，可按需增减（如加入 REFERENCES/TRIGGER、序列权限等）。

四 远程连接与访问控制（确保账户可被 pgAdmin 使用）

• 配置 PostgreSQL 监听地址
  • 编辑配置文件 postgresql.conf（路径通常为 /etc/postgresql/<版本>/main/postgresql.conf），设置：listen_addresses = ‘*’（或指定内网地址）。
• 配置客户端认证
  • 编辑 pg_hba.conf，为需要连接的网段添加规则，例如：
    • host all all 192.168.1.0/24 md5
    • host all all 0.0.0.0/0 md5（公网慎用）
• 重启数据库并验证
  • sudo systemctl restart postgresql
  • 本地验证：psql -h 服务器IP -U 用户名 -d 数据库名 -p 5432
  • 在 pgAdmin 中新增服务器连接，使用上述账户测试登录与权限。

五 安全与最佳实践

• 最小权限原则：应用账户仅授予所需的 CONNECT/SCHEMA USAGE/TABLE 权限，避免滥用 SUPERUSER。
• 默认权限：对新建对象使用 ALTER DEFAULT PRIVILEGES 统一授予，减少后续维护成本。
• 定期审计：在 pgAdmin 或通过 psql 查询系统视图（如 information_schema.table_privileges、pg_roles）核对权限分配。
• 系统层加固：仅授予必要用户 sudo 权限；为 pgAdmin 相关服务使用最小权限的系统账户；必要时结合 AppArmor/Polkit 等机制做进一步约束。