Debian系统下PostgreSQL权限设置指南

在Debian系统中，PostgreSQL的权限管理通过角色（Roles）和权限语句实现，涵盖数据库、表、列、序列等多个层级，同时需结合pg_hba.conf文件控制客户端连接权限。以下是详细操作步骤：

一、前置准备：安装PostgreSQL

首先确保系统已安装PostgreSQL及contrib工具包：

sudo apt update
sudo apt install postgresql postgresql-contrib

安装完成后，PostgreSQL服务会自动启动，默认以postgres用户身份运行。

二、基础权限设置：角色与数据库

1. 登录PostgreSQL控制台

使用postgres超级用户登录：

sudo -u postgres psql

2. 创建角色与用户

创建角色（可登录型）：

CREATE ROLE myrole WITH LOGIN PASSWORD 'secure_password';

创建用户（等同于LOGIN角色）：

CREATE USER myuser WITH PASSWORD 'user_password';

3. 分配角色与数据库权限

将用户添加至角色（角色继承权限）：
```
GRANT myrole TO myuser;
```

授予数据库级权限（如ALL PRIVILEGES或特定权限）：

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myrole;
-- 或仅授予CONNECT权限（允许连接数据库）
GRANT CONNECT ON DATABASE mydatabase TO myuser;

4. 分配模式与表权限

授予模式级权限（允许用户使用模式中的对象）：
```
GRANT USAGE ON SCHEMA public TO myrole;
```

授予表级权限（如SELECT、INSERT等）：

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO myrole;

授予序列权限（用于自增字段）：

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myrole;

5. 列级别权限（细化控制）

若需限制用户仅能访问表的特定列：

GRANT SELECT (column1, column2) ON mytable TO myuser;

6. 权限回收

若需撤销已授予权限：

-- 撤销表级权限
REVOKE SELECT, INSERT ON mytable FROM myuser;
-- 撤销角色分配
REVOKE myrole FROM myuser;

7. 设置默认权限（自动应用至新对象）

为将来创建的对象预设权限（如mydatabase数据库中的新表自动授予myrole SELECT权限）：

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

8. 查看权限

查看角色权限：
```
\du
```

查看表级权限明细：

SELECT table_catalog, table_schema, table_name, privilege_type 
FROM information_schema.table_privileges 
WHERE grantee = 'myuser';

9. 修改用户密码

ALTER USER myuser WITH PASSWORD 'new_secure_password';

10. 删除角色

DROP ROLE IF EXISTS myuser;

三、连接控制：pg_hba.conf配置

pg_hba.conf文件（路径：/etc/postgresql/<version>/main/pg_hba.conf）用于控制客户端连接的身份验证方式。编辑该文件以限制访问：

示例配置

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5       # 本地IPv4连接，MD5加密密码
host    all             all             ::1/128                 md5       # 本地IPv6连接，MD5加密密码
host    mydatabase      myuser          192.168.1.0/24          md5       # 仅允许192.168.1.0/24网段的myuser访问mydatabase
hostssl all             all             0.0.0.0/0               cert      # 所有SSL加密连接（需配置证书）

生效配置

修改后需重新加载PostgreSQL服务：

sudo systemctl reload postgresql

注意事项

METHOD可选值：trust（无密码）、md5（密码加密）、cert（SSL证书）、peer（操作系统用户匹配）。
建议遵循最小权限原则，仅开放必要IP段和认证方式。

四、高级权限管理

1. 角色继承

创建父角色并分配权限，子角色继承父角色权限：

-- 创建父角色
CREATE ROLE parent_role WITH LOGIN PASSWORD 'parent_pass';
-- 分配权限给父角色
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO parent_role;
-- 创建子角色并继承父角色
CREATE ROLE child_role WITH LOGIN PASSWORD 'child_pass' INHERIT;
GRANT parent_role TO child_role;

2. SSL/TLS加密连接（增强安全性）

编辑postgresql.conf文件（路径：/etc/postgresql/<version>/main/postgresql.conf）：

ssl = on
ssl_cert_file = '/etc/postgresql/<version>/main/server.crt'
ssl_key_file = '/etc/postgresql/<version>/main/server.key'

重启服务使配置生效：

sudo systemctl restart postgresql

3. 备份与恢复（权限间接管理）

使用pg_dump备份数据库（需具备相应权限）：

pg_dump -U myuser -W -F c mydatabase > mydatabase_backup.dump

使用pg_restore恢复数据库：

pg_restore -U myuser -d mydatabase -W mydatabase_backup.dump

通过以上步骤，可在Debian系统上实现PostgreSQL的精细化权限管理，确保数据库安全。

Debian PostgreSQL权限设置