CentOS PostgreSQL 权限设置指南

一、前置准备：安装与启动PostgreSQL

在CentOS系统上，首先需要安装PostgreSQL数据库并启动服务，这是权限管理的基础环境。

安装PostgreSQL：使用yum包管理器安装核心数据库及扩展工具。
```
sudo yum update -y
sudo yum install -y postgresql-server postgresql-contrib
```
初始化数据库集群：通过postgresql-setup命令创建初始数据库集群。
```
sudo postgresql-setup --initdb
```
启动并启用服务：启动PostgreSQL服务并设置为开机自启，确保系统重启后服务自动运行。
```
sudo systemctl start postgresql
sudo systemctl enable postgresql
```

以上步骤完成后，PostgreSQL服务已正常运行，接下来可进行权限相关的配置。

二、角色（Roles）管理：权限的基础单元

PostgreSQL采用**角色（Roles）**模型管理权限，角色可以是用户（能登录数据库）或组（仅用于权限分配）。

1. 创建角色

创建普通角色：使用CREATE ROLE命令，指定角色名（如db_user）。
```
CREATE ROLE db_user;
```
创建可登录用户：添加LOGIN权限，使角色能作为数据库用户登录。
```
CREATE ROLE app_user WITH LOGIN PASSWORD 'SecurePassword123!';
```
创建组角色：用于集中管理权限，后续可将用户添加到组中。
```
CREATE ROLE dev_group;
```

2. 修改角色属性

更改密码：使用ALTER ROLE命令更新用户密码（生产环境建议定期更换复杂密码）。
```
ALTER ROLE app_user WITH PASSWORD 'NewSecurePassword456!';
```
提升权限：若需让用户拥有超级用户权限（谨慎使用，仅限管理员），可添加SUPERUSER属性。
```
ALTER ROLE admin_user WITH SUPERUSER;
```

3. 删除角色

删除角色：使用DROP ROLE命令，若角色拥有数据库对象（如表、视图），需先转移或删除这些对象。
```
DROP ROLE old_user;
```

以上命令均在psql命令行工具中执行（以postgres用户登录）。

三、权限类型与授予/撤销操作

PostgreSQL的权限分为数据库级、模式级、表级、列级等，需根据实际需求分配最小必要权限。

1. 数据库级权限

授予所有权限：允许用户对数据库进行所有操作（创建表、插入数据等）。
```
GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user;
```
授予特定权限：仅允许用户连接数据库（CONNECT）或创建临时表（TEMPORARY）。
```
GRANT CONNECT, TEMPORARY ON DATABASE mydb TO readonly_user;
```

2. 模式级权限

授予模式权限：允许用户在模式中创建对象（CREATE）或使用模式中的对象（USAGE）。
```
GRANT USAGE, CREATE ON SCHEMA public TO dev_group;
```
授予表级权限：针对模式中的具体表，授予SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）等权限。
```
GRANT SELECT, INSERT, UPDATE ON TABLE employees TO app_user;
```

3. 列级权限

授予列级权限：针对表的特定列（如salary）授予SELECT权限，限制用户访问敏感数据。
```
GRANT SELECT (id, name, department) ON TABLE employees TO readonly_user;
```

4. 撤销权限

撤销权限：使用REVOKE命令取消之前授予的权限（语法与GRANT相反）。
```
REVOKE INSERT, UPDATE ON TABLE employees FROM app_user;
```

5. 默认权限

设置默认权限：使用ALTER DEFAULT PRIVILEGES命令，让未来创建的对象自动继承指定权限（不影响现有对象）。
```
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
```

以上命令覆盖了常见的权限场景，可根据业务需求组合使用。

四、pg_hba.conf 配置：客户端访问控制

pg_hba.conf文件是PostgreSQL的客户端认证配置文件，用于控制哪些IP地址、用户能以何种方式访问数据库。

1. 文件位置

默认路径为/var/lib/pgsql/data/pg_hba.conf（PostgreSQL数据目录下）。

2. 常见配置项

本地连接（Unix套接字）：允许本地用户通过peer认证（无需密码，依赖系统用户）。
```
local   all             all                                     peer
```
本地IP连接（TCP）：允许本地IP（127.0.0.1）通过md5认证（密码加密）。
```
host    all             all             127.0.0.1/32            md5
```
远程IP连接：允许特定网段（如192.168.1.0/24）的用户访问所有数据库，使用md5认证。
```
host    all             all             192.168.1.0/24          md5
```

3. 应用配置

修改pg_hba.conf后，需重新加载PostgreSQL服务使配置生效：

sudo systemctl reload postgresql

安全提示：

限制远程访问的IP范围（如仅允许公司内网IP），避免暴露数据库到公网。
生产环境建议使用md5或scram-sha-256加密认证，避免明文传输密码。

五、安全增强措施

权限管理需配合以下安全措施，进一步提升数据库安全性：

1. 使用SSL加密连接

编辑postgresql.conf文件，启用SSL并指定证书路径：

ssl = on
ssl_cert_file = '/var/lib/pgsql/data/server.crt'
ssl_key_file = '/var/lib/pgsql/data/server.key'

重启服务使配置生效：

sudo systemctl restart postgresql

2. 定期审计权限

查看角色权限：使用\du命令查看所有角色的权限摘要，或查询pg_roles系统表获取详细信息。
```
\du
SELECT * FROM pg_roles WHERE rolname = 'app_user';
```
查看表级权限：使用\dp命令查看当前数据库中所有表的权限，或查询information_schema.table_privileges视图。
```
\dp
SELECT * FROM information_schema.table_privileges WHERE table_name = 'employees';
```

3. 定期备份

使用pg_dump工具定期备份数据库，防止数据丢失：

pg_dump -U postgres -d mydb -f mydb_backup.sql

以上措施能有效降低权限滥用、数据泄露等安全风险。

centos postgresql权限设置指南