Ubuntu下PostgreSQL权限管理详解

一、PostgreSQL权限模型概述

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

• 用户（User）：具有登录权限的角色（LOGIN属性），相当于“用户账号”；
• 组角色（Group Role）：无登录权限的角色，用于集中管理权限（类似“用户组”）。
  权限类型包括数据库级（连接、创建数据库）、模式级（创建表、使用模式）、表级（增删改查、删除表）、列级（特定列的访问权限）等，遵循“最小权限原则”（仅授予完成任务所需的最低权限）。

二、准备工作：安装PostgreSQL与pgAdmin

在Ubuntu上安装PostgreSQL和pgAdmin（图形化管理工具）：

# 更新软件包列表
sudo apt update
# 安装PostgreSQL（含contrib扩展，提供额外功能）
sudo apt install postgresql postgresql-contrib
# 安装pgAdmin4（Web界面管理工具）
sudo apt install pgadmin4

安装完成后，pgAdmin可通过浏览器访问（默认端口5050），需设置初始管理员账号。

三、命令行方式管理权限

1. 切换至postgres用户

PostgreSQL默认超级用户为postgres，需通过sudo切换：

sudo su - postgres

切换后进入postgres用户环境，可直接使用psql命令行工具。

2. 创建角色与用户

• 创建角色（无登录权限）：

  CREATE ROLE read_only_role;
  

• 创建用户（有登录权限）：通过WITH LOGIN指定，可设置密码：

  CREATE USER dev_user WITH LOGIN PASSWORD 'SecurePass123';
  

• 创建可创建数据库的用户：添加CREATEDB属性：

  CREATE USER db_admin WITH LOGIN PASSWORD 'AdminPass456' CREATEDB;
  

• 创建超级用户：添加SUPERUSER属性（谨慎使用）：

  CREATE USER super_admin WITH LOGIN PASSWORD 'SuperPass789' SUPERUSER;
  

3. 分配权限

• 数据库级权限：
  • 授予用户对某数据库的所有权限：

    GRANT ALL PRIVILEGES ON DATABASE mydb TO dev_user;
    

  • 授予组角色对某数据库的连接权限：

    GRANT CONNECT ON DATABASE mydb TO read_only_role;
    

• 模式级权限：
  • 授予用户对某模式的所有权限（如public模式）：

    GRANT ALL PRIVILEGES ON SCHEMA public TO dev_user;
    

• 表级权限：
  • 授予用户对某表的所有权限：

    GRANT ALL PRIVILEGES ON TABLE employees TO dev_user;
    

  • 授予组角色对某表的查询权限：

    GRANT SELECT ON TABLE employees TO read_only_role;
    

• 列级权限（细化控制）：
  • 授予用户对某表特定列的更新权限：

    GRANT UPDATE (salary) ON TABLE employees TO hr_user;
    

4. 角色成员管理（权限继承）

将用户添加到组角色，使其继承组角色的权限：

GRANT read_only_role TO dev_user;

此时dev_user将拥有read_only_role的所有权限（如public模式的查询权限）。

5. 撤销权限

• 撤销表级权限：

  REVOKE DELETE ON TABLE employees FROM dev_user;
  

• 撤销角色成员身份：

  REVOKE read_only_role FROM dev_user;
  

• 撤销数据库级权限：

  REVOKE ALL PRIVILEGES ON DATABASE mydb FROM dev_user;
  

6. 查看权限

• 查看用户权限：

  \du  -- 查看所有角色及属性
  \l   -- 查看数据库权限
  \z   -- 查看表权限（需指定表名，如\z employees）
  

• 查看当前用户权限：

  SELECT * FROM current_user;
  

四、pgAdmin图形化管理权限

pgAdmin提供了直观的Web界面管理权限，步骤如下：

1. 连接PostgreSQL服务器

启动pgAdmin，右键点击“Servers”→“Create”→“Server…”，输入连接信息（主机localhost、端口5432、用户名postgres、密码），点击“Save”连接。

2. 创建角色/用户

• 展开“Servers”→选中目标服务器→点击“Roles”→右键“Create”→“Role…”；
• 输入角色名（如dev_user），勾选“Login”（用户）或“Group”（组角色），设置密码，点击“Save”。

3. 分配权限

• 数据库级权限：展开“Databases”→选中目标数据库→右键“Properties”→切换至“Permissions”标签→点击“Add”→选择用户/角色→设置权限（如“All Privileges”），点击“Save”。
• 模式/表级权限：展开“Databases”→选中数据库→展开“Schemas”→选中模式/表→右键“Properties”→切换至“Permissions”标签→添加用户/角色并设置权限。

五、安全注意事项

1. 修改默认密码：超级用户postgres的默认密码需及时修改，避免未授权访问：

   sudo -u postgres psql
   \password postgres  -- 修改密码
   \q
   

2. 配置pg_hba.conf：位于/etc/postgresql/<version>/main/pg_hba.conf，控制客户端连接权限（如仅允许本地IP访问）：

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   local   all             postgres                                peer
   host    all             all             127.0.0.1/32            md5
   host    all             all             ::1/128                 md5
   

   修改后重启PostgreSQL生效：

   sudo systemctl restart postgresql
   

3. 启用SSL加密：在postgresql.conf中设置ssl = on，并配置证书，确保数据传输安全。
4. 定期审计：通过pgAdmin的“Audit”功能或查询pg_stat_activity视图，监控数据库操作日志，及时发现异常。

通过以上步骤，可在Ubuntu环境下完成PostgreSQL的权限管理，确保数据库安全与合规性。权限设置需根据实际业务需求调整，始终坚持“最小权限原则”。