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

以上措施能有效降低權限濫用、數據泄露等安全風險。