Ubuntu下PostgreSQL权限管理指南

一、准备工作：登录PostgreSQL

在Ubuntu系统中，PostgreSQL的超级用户默认为postgres。要通过命令行管理权限，需先切换至该用户并进入psql（PostgreSQL命令行工具）：

sudo su - postgres
psql

此时会进入psql交互界面，提示符显示为postgres=#，表示已准备好执行SQL命令。

二、创建用户与角色

1. 创建普通用户

使用CREATE USER命令创建用户，需指定密码（用单引号包裹）：

CREATE USER your_username WITH PASSWORD 'your_password';

若需赋予用户创建数据库的权限，可添加CREATEDB关键字：

CREATE USER your_username WITH PASSWORD 'your_password' CREATEDB;

若需创建超级用户（拥有所有权限，慎用），添加SUPERUSER关键字：

CREATE USER your_username WITH PASSWORD 'your_password' SUPERUSER;

执行后，用户即创建完成。

2. 创建角色（可选）

角色是一组权限的集合，可用于批量分配权限。创建角色时，可指定是否为LOGIN（允许登录）：

CREATE ROLE your_role_name WITH LOGIN PASSWORD 'your_password';

创建后，可将用户添加至角色，继承角色的权限：

GRANT your_role_name TO your_username;

三、分配权限

PostgreSQL权限体系分为实例级别（由pg_hba.conf控制客户端连接）和数据库级别（由SQL命令控制对象访问）。以下重点介绍数据库级别的权限分配：

1. 分配数据库级权限

（1）授予数据库所有权

创建数据库时，可直接指定所有者（该用户将拥有数据库的所有权限）：

CREATE DATABASE your_database OWNER your_username;

（2）授予现有数据库的权限

若数据库已存在，可使用GRANT命令分配权限：

授予所有权限（包括连接、创建模式、增删改查等）：

GRANT ALL PRIVILEGES ON DATABASE your_database TO your_username;

授予特定权限（如仅允许连接和查询）：
```
GRANT CONNECT, USAGE, SELECT ON DATABASE your_database TO your_username;
```
其中，CONNECT表示允许连接数据库，USAGE表示允许使用模式（schema），SELECT表示允许查询数据。

2. 分配表级权限

若需细化到表级别的权限（如允许用户操作某张表的特定操作），可使用以下命令：

授予所有表的权限（如增删改查）：
```
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_username;
```
其中，public是PostgreSQL的默认模式（schema），若用户有自己的模式，需替换为对应模式名。
授予特定表的权限（如仅允许操作employees表的查询和插入）：
```
GRANT SELECT, INSERT ON employees TO your_username;
```

撤销权限（如撤销用户的删除权限）：

REVOKE DELETE ON employees FROM your_username;

四、配置客户端连接权限（pg_hba.conf）

pg_hba.conf文件用于控制客户端连接的权限（如允许哪些IP地址的用户连接）。默认路径为/etc/postgresql/<version>/main/pg_hba.conf（<version>为PostgreSQL版本号，如14）。

1. 修改配置文件

使用文本编辑器（如nano）打开文件：

sudo nano /etc/postgresql/14/main/pg_hba.conf

添加或修改以下行，允许特定用户从特定IP地址连接数据库：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    your_database   your_username   192.168.1.0/24          md5

TYPE：连接类型（host表示TCP连接）；
DATABASE：允许访问的数据库名（all表示所有数据库）；
USER：允许访问的用户（all表示所有用户）；
ADDRESS：允许连接的IP地址范围（192.168.1.0/24表示192.168.1.0至192.168.1.255）；
METHOD：认证方式（md5表示密码认证，trust表示信任无需密码，生产环境建议用md5或scram-sha-256）。

2. 重启PostgreSQL服务

修改配置文件后，需重启服务使更改生效：

sudo systemctl restart postgresql

五、验证权限

1. 使用新用户连接数据库

退出psql（输入\q），然后以新用户身份连接数据库：

psql -d your_database -U your_username -h localhost

输入密码后，若成功进入psql界面，说明连接权限配置正确。

2. 测试表级权限

在psql中，尝试执行增删改查操作，验证表级权限是否生效：

-- 查询数据（需SELECT权限）
SELECT * FROM employees;

-- 插入数据（需INSERT权限）
INSERT INTO employees (name, age) VALUES ('John', 30);

-- 删除数据（需DELETE权限）
DELETE FROM employees WHERE id = 1;

若操作成功，说明权限分配正确；若提示“permission denied”，则需检查权限设置。

六、注意事项

最小权限原则：仅授予用户完成工作所需的最低权限，避免使用SUPERUSER或ALL PRIVILEGES（除非必要）。
密码安全：设置强密码（包含大小写字母、数字和特殊字符），并定期更换。
备份配置：修改权限或配置文件前，备份相关文件（如pg_hba.conf），以防误操作导致无法访问。
审计日志：可通过配置PostgreSQL的日志功能（如log_statement参数），记录用户操作，便于后续审计。

0 赞

0 踩

Ubuntu下PostgreSQL权限管理怎么做