Debian系统PostgreSQL权限管理实践

在Debian系统上管理PostgreSQL权限，核心是通过角色（roles）和权限命令实现细粒度控制，以下是关键实践步骤：

一、基础权限管理

1. 创建角色与用户

   • 创建可登录角色（用户）：

     CREATE ROLE username WITH LOGIN PASSWORD 'password';  -- 普通用户
     CREATE ROLE superuser WITH SUPERUSER LOGIN PASSWORD 'password';  -- 超级用户
     

   • 创建非登录角色（用于权限继承）：

     CREATE ROLE readonly_role WITH NOLOGIN;  -- 不可登录的权限组
     

2. 分配权限

   • 数据库级别：

     GRANT ALL PRIVILEGES ON DATABASE dbname TO username;  -- 授予数据库全部权限
     REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;  -- 撤销权限
     

   • 表/视图级别：

     GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO username;  -- 授予表操作权限
     GRANT SELECT (col1, col2) ON table_name TO username;  -- 授予特定列权限
     

   • 序列级别（如自增ID）：

     GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO username;
     

3. 角色继承与权限批量管理

   • 子角色继承父角色权限：

     CREATE ROLE child_role WITH LOGIN INHERIT;
     GRANT parent_role TO child_role;
     

   • 批量设置未来对象的默认权限：

     ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;
     

二、高级权限控制

1. 连接访问控制

   • 编辑/etc/postgresql/<version>/main/pg_hba.conf，限制IP或用户访问：

     # 允许192.168.1.0/24网段用户使用密码认证连接
     host    all             all             192.168.1.0/24        md5
     # 仅允许本地超级用户连接
     host    postgres        postgres        127.0.0.1/32        peer
     

   • 重启服务生效：

     sudo systemctl reload postgresql
     

2. 权限审计与维护

   • 查看用户权限：

     \du  -- 查看所有用户及其角色
     \dp  -- 查看表/视图权限明细
     SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';
     

   • 删除角色（需先回收所有权限）：

     REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;
     DROP ROLE username;
     

三、安全最佳实践

• 最小权限原则：仅授予用户完成工作所需的最低权限，避免使用ALL PRIVILEGES。
• 定期审计：通过\du和\dp命令定期检查权限分配，清理冗余角色。
• 加密连接：在postgresql.conf中启用SSL，并配置证书。
• 备份权限配置：使用pg_dump备份数据库时，权限信息会包含在备份文件中，恢复时可还原权限结构。

参考来源：