CentOS PostgreSQL 权限设置指南

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

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

1. 安装PostgreSQL：使用yum包管理器安装核心数据库及扩展工具。

   sudo yum update -y
   sudo yum install -y postgresql-server postgresql-contrib
   

2. 初始化数据库集群：通过postgresql-setup命令创建初始数据库集群。

   sudo postgresql-setup --initdb
   

3. 启动并启用服务：启动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

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

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！