Debian PostgreSQL权限管理指南

一、权限管理基础概念

PostgreSQL的权限体系以**角色（Roles）**为核心，角色是一组权限的集合，可分为两类：

• 登录角色（Login Roles）：具备连接数据库的权限，相当于“用户”；
• 非登录角色（Non-Login Roles）：仅用于权限分组，不具备登录权限，相当于“组”。

权限可分配至数据库、模式（Schema）、表、列、序列等不同层级，遵循最小权限原则（仅授予完成任务所需的最低权限）。

二、前置准备：安装PostgreSQL

在Debian系统上，通过以下命令安装PostgreSQL及常用扩展：

sudo apt update
sudo apt install postgresql postgresql-contrib

安装完成后，postgres用户（超级用户）会自动创建，初始密码为空或通过sudo passwd postgres设置。

三、创建与管理角色（用户）

1. 创建登录角色

使用CREATE ROLE命令创建具备登录权限的角色，需指定密码：

CREATE ROLE myuser WITH LOGIN PASSWORD 'SecurePassword123!';

• LOGIN：允许角色连接数据库；
• PASSWORD：设置登录密码（建议使用强密码）。

2. 创建非登录角色（权限组）

若需将权限分组管理，可创建非登录角色：

CREATE ROLE db_admin;  -- 数据库管理员角色（无登录权限）

3. 分配角色给用户

将非登录角色授予登录角色，实现权限继承：

GRANT db_admin TO myuser;  -- myuser继承db_admin的权限

4. 修改角色密码

使用ALTER USER命令修改角色密码：

ALTER USER myuser WITH PASSWORD 'NewSecurePassword456!';

5. 删除角色

彻底移除角色及其权限：

DROP ROLE IF EXISTS myuser;  -- 安全删除（避免角色不存在时报错）

四、权限分配：分级控制

1. 数据库级别权限

授予角色对整个数据库的访问权限：

-- 授予所有权限（谨慎使用）
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

-- 授予特定权限（推荐）
GRANT CONNECT, TEMPORARY ON DATABASE mydatabase TO myuser;  -- 仅允许连接和创建临时表

2. 模式级别权限

控制角色对模式（如public默认模式）的访问：

-- 授予使用模式的权限（允许创建对象）
GRANT USAGE ON SCHEMA public TO myuser;

-- 授予创建对象的权限（需结合表/序列权限）
GRANT CREATE ON SCHEMA public TO myuser;

3. 表级别权限

针对具体表分配增删改查权限：

-- 授予所有表操作权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

-- 授予特定权限（推荐）
GRANT SELECT, INSERT, UPDATE ON mytable TO myuser;  -- 只读+写入
GRANT DELETE ON mytable TO admin_role;  -- 仅管理员可删除

4. 列级别权限

细化到表的特定列（如敏感字段）：

-- 授予特定列的读取权限
GRANT SELECT (id, name, email) ON mytable TO myuser;

-- 授予特定列的写入权限
GRANT UPDATE (name, email) ON mytable TO myuser;  -- 仅允许修改姓名和邮箱

5. 序列级别权限

控制序列（如自增ID）的使用：

-- 授予序列的使用权限（用于INSERT时自动生成ID）
GRANT USAGE, SELECT ON SEQUENCE mytable_id_seq TO myuser;

五、角色继承：简化权限管理

通过INHERIT属性让子角色自动继承父角色的权限：

-- 创建父角色（带权限）
CREATE ROLE read_only WITH LOGIN PASSWORD 'ReadOnlyPass' INHERIT;
GRANT CONNECT ON DATABASE mydatabase TO read_only;
GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- 创建子角色（继承父角色权限）
CREATE ROLE report_user WITH LOGIN PASSWORD 'ReportPass' INHERIT;
GRANT read_only TO report_user;  -- report_user自动拥有read_only的权限

子角色可直接使用父角色的权限，无需重复分配。

六、默认权限：自动化未来对象权限

使用ALTER DEFAULT PRIVILEGES设置未来创建对象的默认权限，避免重复操作：

-- 设置未来在public模式下创建的表，自动授予read_only角色SELECT权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO read_only;

-- 设置未来创建的序列，自动授予report_user角色USAGE权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT USAGE ON SEQUENCES TO report_user;

七、权限回收：撤销不必要的权限

使用REVOKE命令撤销已授予的权限：

-- 撤销表的更新权限
REVOKE UPDATE ON mytable FROM myuser;

-- 撤销角色的数据库连接权限
REVOKE CONNECT ON DATABASE mydatabase FROM myuser;

-- 撤销继承的权限（需明确指定角色）
REVOKE read_only FROM report_user;  -- 移除子角色的父角色继承

八、连接控制：通过pg_hba.conf限制访问

pg_hba.conf文件（位于/etc/postgresql/<version>/main/）用于控制客户端连接的身份验证和授权，需修改后重新加载配置：

sudo systemctl reload postgresql

示例配置：

# TYPE    DATABASE    USER        ADDRESS                 METHOD
host    all         all         127.0.0.1/32            md5       # 本地IP允许md5密码登录
host    mydatabase  myuser      192.168.1.0/24          md5       # 特定IP段可访问特定数据库
hostssl all         all         0.0.0.0/0               cert      # 所有IP强制使用SSL证书连接（更安全）

• METHOD可选：trust（无密码）、md5（密码加密）、cert（SSL证书）、reject（拒绝连接）。

九、权限查看与审计

1. 查看角色权限

-- 查看所有角色及其权限
\du

-- 查看特定角色的权限详情
SELECT * FROM pg_roles WHERE rolname = 'myuser';

2. 查看表级权限

-- 查询特定用户对表的权限
SELECT table_catalog, table_schema, table_name, privilege_type 
FROM information_schema.table_privileges 
WHERE grantee = 'myuser';

3. 查看当前权限

-- 查看当前角色的权限
\z

十、安全注意事项

1. 最小权限原则：仅授予用户完成任务所需的权限，避免使用ALL PRIVILEGES；
2. 定期审计：通过\du和information_schema查看权限分配，及时回收闲置权限；
3. 强化连接控制：通过pg_hba.conf限制访问IP，优先使用cert（SSL证书）认证；
4. 密码策略：要求用户使用强密码（包含大小写字母、数字、特殊字符），定期更换；
5. 备份权限：修改权限前备份pg_roles和information_schema数据，便于恢复。