使用者管理
在 PostgreSQL 中，「使用者」實際上是 **Role（角色）**。Role 可以是：
- 可登入的使用者：具有 `LOGIN`
- 群組角色：通常不具有 `LOGIN`，用來集中管理權限
- 一個使用者也可以被加入多個群組角色
以下以 `psql` 指令為例。
---
## 1. 連線到 PostgreSQL
```bash
psql -U postgres -d postgres
```
若需要指定主機與連接埠：
```bash
psql -h localhost -p 5432 -U postgres -d postgres
```
查看目前連線使用者：
```sql
SELECT current_user;
```
列出所有使用者或角色：
```sql
\du
```
或使用 SQL：
```sql
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles
ORDER BY rolname;
```
---
## 2. 新增使用者
### 基本建立方式
```sql
CREATE USER app_user WITH PASSWORD '強密碼';
```
`CREATE USER` 等同於建立一個具有 `LOGIN` 權限的 Role。
也可以使用：
```sql
CREATE ROLE app_user LOGIN PASSWORD '強密碼';
```
### 指定有效期限
```sql
CREATE USER app_user
WITH PASSWORD '強密碼'
VALID UNTIL '2026-12-31';
```
### 建立具備建立資料庫權限的使用者
```sql
CREATE USER developer
WITH PASSWORD '強密碼'
CREATEDB;
```
### 建立超級使用者
```sql
CREATE USER admin_user
WITH PASSWORD '強密碼'
SUPERUSER;
```
通常不建議日常應用程式帳號使用 `SUPERUSER`，因為它可以繞過幾乎所有權限檢查。
### 使用 `createuser` 指令
在作業系統 Shell 執行：
```bash
createuser -U postgres -P app_user
```
其中：
- `-U postgres`：以 PostgreSQL 管理者連線
- `-P`：互動式要求輸入密碼
---
## 3. 修改使用者密碼
### 使用 SQL
```sql
ALTER USER app_user WITH PASSWORD '新的強密碼';
```
也可以寫成：
```sql
ALTER ROLE app_user PASSWORD '新的強密碼';
```
### 使用 `psql` 的互動式指令
```text
\password app_user
```
系統會要求輸入新密碼，通常比直接把密碼寫在 SQL 中更安全。
### 設定密碼期限
```sql
ALTER USER app_user
VALID UNTIL '2026-12-31';
```
讓密碼立即失效：
```sql
ALTER USER app_user VALID UNTIL '1970-01-01';
```
恢復永久有效：
```sql
ALTER USER app_user VALID UNTIL 'infinity';
```
---
## 4. 啟用或停用登入
### 禁止使用者登入
```sql
ALTER USER app_user NOLOGIN;
```
這適合暫停帳號，而不是直接刪除帳號。
### 恢復登入
```sql
ALTER USER app_user LOGIN;
```
---
## 5. 建立群組角色
建議將權限授予群組角色，再把使用者加入群組，方便集中管理。
```sql
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
```
將使用者加入群組：
```sql
GRANT app_readonly TO app_user;
```
或：
```sql
GRANT app_readwrite TO developer;
```
移除群組成員資格：
```sql
REVOKE app_readonly FROM app_user;
```
查看角色成員關係：
```sql
SELECT
member.rolname AS member,
parent.rolname AS role
FROM pg_auth_members m
JOIN pg_roles member ON m.member = member.oid
JOIN pg_roles parent ON m.roleid = parent.oid;
```
---
## 6. 資料庫層級權限
### 允許使用者連線至資料庫
```sql
GRANT CONNECT ON DATABASE mydb TO app_user;
```
移除連線權限：
```sql
REVOKE CONNECT ON DATABASE mydb FROM app_user;
```
### 允許建立 Schema
```sql
GRANT CREATE ON DATABASE mydb TO developer;
```
### 切換到指定資料庫
```sql
\c mydb
```
---
## 7. Schema 權限
### 允許使用 Schema
```sql
GRANT USAGE ON SCHEMA public TO app_user;
```
### 允許建立物件
```sql
GRANT CREATE ON SCHEMA public TO developer;
```
### 移除 Schema 權限
```sql
REVOKE CREATE ON SCHEMA public FROM app_user;
```
注意：使用者即使具有資料表權限，通常也需要 Schema 的 `USAGE` 權限才能存取其中的物件。
---
## 8. 資料表權限
### 授予查詢權限
```sql
GRANT SELECT ON TABLE public.customers TO app_readonly;
```
### 授予新增、修改、刪除權限
```sql
GRANT INSERT, UPDATE, DELETE
ON TABLE public.customers
TO app_readwrite;
```
### 授予所有資料表權限
```sql
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO app_readwrite;
```
### 移除權限
```sql
REVOKE DELETE
ON TABLE public.customers
FROM app_readwrite;
```
### 授予所有權限
```sql
GRANT ALL PRIVILEGES
ON TABLE public.customers
TO app_admin;
```
實務上建議只授予必要權限，而不要隨意使用 `ALL PRIVILEGES`。
---
## 9. 預設權限
`GRANT ... ON ALL TABLES` 只會套用到現有資料表。若希望未來建立的資料表也自動套用權限，可以設定預設權限：
```sql
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_readonly;
```
對可讀寫角色：
```sql
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLES TO app_readwrite;
```
序列也要另外授予權限，尤其是使用 `SERIAL` 或序列欄位時：
```sql
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;
```
函式權限則可設定為：
```sql
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT EXECUTE ON FUNCTIONS TO app_user;
```
注意：`ALTER DEFAULT PRIVILEGES` 預設是針對「執行這個指令的物件擁有者」所建立的未來物件。若資料表由其他角色建立，需使用 `FOR ROLE`：
```sql
ALTER DEFAULT PRIVILEGES FOR ROLE db_owner IN SCHEMA public
GRANT SELECT ON TABLES TO app_readonly;
```
---
## 10. 變更使用者屬性
### 允許或禁止建立資料庫
```sql
ALTER USER developer CREATEDB;
ALTER USER developer NOCREATEDB;
```
### 允許或禁止建立角色
```sql
ALTER USER role_admin CREATEROLE;
ALTER USER role_admin NOCREATEROLE;
```
### 設定連線數上限
```sql
ALTER USER app_user CONNECTION LIMIT 10;
```
取消限制：
```sql
ALTER USER app_user CONNECTION LIMIT -1;
```
### 設定使用者預設資料庫
PostgreSQL 使用者本身沒有固定的「預設資料庫」屬性，通常是在連線時指定：
```bash
psql -U app_user -d mydb
```
也可以透過 `ALTER ROLE ... SET` 設定工作階段參數，例如：
```sql
ALTER ROLE app_user SET search_path = public;
```
---
## 11. 查看權限
在 `psql` 中查看資料表權限：
```text
\dp
```
查看指定資料表：
```text
\dp public.customers
```
查看 Schema：
```text
\dn+
```
查看資料庫：
```text
\l+
```
使用 SQL 查詢資料表權限：
```sql
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'app_user'
ORDER BY table_schema, table_name;
```
檢查某使用者是否具有特定權限：
```sql
SELECT has_table_privilege(
'app_user',
'public.customers',
'SELECT'
);
```
---
## 12. 刪除使用者
### 直接刪除
```sql
DROP USER app_user;
```
或：
```sql
DROP ROLE app_user;
```
但是，如果該使用者仍然：
- 擁有資料庫物件
- 擁有資料表、Schema 或函式
- 擁有其他物件
- 在其他物件上具有相依關係
刪除可能會失敗。
### 先將物件所有權轉移
```sql
REASSIGN OWNED BY app_user TO db_owner;
```
### 移除該使用者在目前資料庫中的物件與權限
```sql
DROP OWNED BY app_user;
```
常見完整流程：
```sql
REASSIGN OWNED BY app_user TO db_owner;
DROP OWNED BY app_user;
DROP USER app_user;
```
如果該使用者在多個資料庫中有權限或物件，必須分別連線到各資料庫執行相關指令。
### 強制刪除角色及其相依物件
```sql
DROP ROLE app_user CASCADE;
```
`CASCADE` 可能刪除相依物件，風險很高，使用前應確認影響範圍。
---
## 13. 常見的權限管理設計
例如建立一個只能讀取資料的應用程式帳號：
```sql
CREATE ROLE app_readonly NOLOGIN;
GRANT CONNECT ON DATABASE mydb TO app_readonly;
\c mydb
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_readonly;
CREATE USER report_user WITH PASSWORD '強密碼';
GRANT app_readonly TO report_user;
```
建立可讀寫的應用程式帳號：
```sql
CREATE ROLE app_readwrite NOLOGIN;
GRANT CONNECT ON DATABASE mydb TO app_readwrite;
\c mydb
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO app_readwrite;
GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA public
TO app_readwrite;
CREATE USER application_user WITH PASSWORD '強密碼';
GRANT app_readwrite TO application_user;
```
---
## 14. 實務安全建議
1. **不要讓應用程式使用 `postgres` 或超級使用者帳號。**
2. 使用群組角色集中管理權限。
3. 遵循最小權限原則，只授予必要的 `SELECT`、`INSERT`、`UPDATE` 等權限。
4. 密碼不要寫入 Shell 歷史紀錄、程式碼或公開設定檔。
5. 定期檢查不再使用的帳號，先執行：
```sql
ALTER USER old_user NOLOGIN;
```
6. PostgreSQL 的登入驗證還受到 `pg_hba.conf` 控制；即使 Role 具有 `LOGIN`，若 `pg_hba.conf` 不允許來源或驗證方式，仍可能無法連線。
7. 修改 `pg_hba.conf` 後通常需要重新載入設定：
```sql
SELECT pg_reload_conf();
```
或在作業系統執行 PostgreSQL 服務的 reload 指令。
相關學習地圖、教學課程
Python 資料工程