Ubuntu中PostgreSQL的權限管理方法

Ubuntu中PostgreSQL權限管理主要通過用戶/角色創建、權限分配及回收實現，核心命令如下：

一、用戶與角色管理

1. 創建用戶/角色

   • 用戶（可登錄）：
     CREATE USER 用戶名 WITH PASSWORD '密碼' [LOGIN] [CREATEDB] [CREATEROLE]
     示例：CREATE USER admin WITH PASSWORD '123456' LOGIN CREATEDB;
   • 角色（不可登錄，用于權限組）：
     CREATE ROLE 角色名 [CREATEDB] [CREATEROLE]
     示例：CREATE ROLE readonly NOLOGIN;

2. 修改用戶/角色屬性

   • ALTER USER 用戶名 WITH PASSWORD '新密碼' [SUPERUSER | NOSUPERUSER]
   • ALTER ROLE 角色名 ADD/DROP CREATEDB/CREATEROLE

3. 刪除用戶/角色
   DROP USER 用戶名; 或 DROP ROLE 角色名;

二、權限分配與回收

1. 數據庫級權限

   • 授予權限：
     GRANT {ALL PRIVILEGES | SELECT/INSERT/UPDATE/DELETE} ON DATABASE 數據庫名 TO 用戶/角色
     示例：GRANT ALL PRIVILEGES ON DATABASE testdb TO admin;
   • 回收權限：
     REVOKE {ALL PRIVILEGES | SELECT/INSERT/UPDATE/DELETE} ON DATABASE 數據庫名 FROM 用戶/角色

2. 表/模式級權限

   • 授予權限：
     GRANT {SELECT/INSERT/UPDATE/DELETE/TRUNCATE} ON 表名 TO 用戶/角色 [WITH GRANT OPTION]
GRANT USAGE ON SCHEMA 模式名 TO 用戶/角色（允許訪問模式）
   • 回收權限：
     REVOKE {SELECT/INSERT/UPDATE/DELETE/TRUNCATE} ON 表名 FROM 用戶/角色

3. 特殊權限

   • 超級用戶權限：僅限postgres用戶或通過ALTER ROLE賦予（需謹慎）
   • 連接權限：通過GRANT CONNECT ON DATABASE控制

三、常用操作示例

1. 創建可登錄用戶并授權：

   sudo -u postgres psql  
   CREATE USER dev WITH PASSWORD 'dev123' LOGIN CREATEDB;  
   GRANT ALL PRIVILEGES ON DATABASE devdb TO dev;  
   \q  
   

2. 創建只讀角色并應用到所有表：

   sudo -u postgres psql  
   CREATE ROLE readonly NOLOGIN;  
   GRANT CONNECT ON DATABASE testdb TO readonly;  
   GRANT USAGE ON SCHEMA public TO readonly;  
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;  
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;  
   \q  
   

四、驗證與工具

• 查看權限：
  • 用戶/角色列表：\du
  • 數據庫權限：\l（查看數據庫所有者及權限）
  • 表權限：\dp 表名
• 圖形化管理：使用pgAdmin（需安裝）通過界面操作用戶和權限。

注意事項

• 避免直接使用SUPERUSER權限，優先通過角色繼承控制權限。
• 修改權限后需確?？蛻舳酥匦逻B接以生效。
• 生產環境中建議通過pg_hba.conf限制IP訪問。

以上操作基于PostgreSQL原生權限模型，適用于Ubuntu系統下的數據庫權限管理。