PostgreSQL 資料庫角色、使用者權限管理設定教學

介紹如何在 PostgreSQL 資料庫中透過角色（roles）來授權與管理資料庫使用者的權限。

PostgreSQL 可以直接針對資料庫的使用者設定權限，也可以先對不同的應用設定好不同的規則（roles），再將不同的規則套用至需要授權的使用者，這樣的方式可以讓管理上夠有彈性，但是可能也會讓管理的操作更為複雜。

使用者、群組與角色

在 PostgreSQL 資料庫中，使用者、群組與角色本質上都是相同的，差異只在於使用者預設具有登入的權限，CREATE USER 與 CREATE GROUP 指令事實上只是 CREATE ROLE 指令的別名（aliases）。

若要建立 PostgreSQL 資料庫的使用者帳號，可以使用 CREATE USER 指令：

-- 建立使用者帳號
CREATE USER myuser WITH PASSWORD 'secret_passwd';

或是使用 CREATE ROLE 指令也可以：

-- 建立使用者帳號
CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd'

以上兩種建立帳號的方式雖然指令不同，但是效果完全一樣。

新建立的使用者預設只會繼承 public 角色中所開放的權限，其餘權限都沒有開放。

public Schema 與 public 角色

當一個全新的 PostgreSQL 資料庫被建立時，預設會自動建立一個名稱為 public 的 schema，並將存取權限授予 public 這一個角色，也就是說所有新增的使用者預設都會有權限可以在 public schema 中新增物件。

當我們在指定物件時，如果沒有明確指定 schema，PostgreSQL 會根據 schema 搜尋路徑（search path）來尋找可能的 schema，而預設的搜尋路徑可以使用以下指令查詢：

-- 查詢 Schema 搜尋路徑
SHOW search_path;

 search_path
--------------
 "$user", public

在搜尋路徑中的第一個 "$user" 會解譯為目前的使用者名稱，而在預設的狀況下這個 schema 是不存在的，所以當沒有指定 schema 的時候，就會使用第二個 public schema，而所有人都有 public schema 的寫入權限，所以一般的狀況下都可以正常建立物件。

然而如果我們需要建立一個唯讀的使用者帳號（只能讀取、不可寫入資料）時，任何人都可以在 public schema 中建立物件就會造成問題，要修正這個問題，就必須將 public 角色中的可在 public schema 建立物件的權限拿掉：

-- 撤除 PUBLIC 角色在 public Schema 中的建立權限
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

另外亦可將 public 角色連線至指定資料庫的權限撤除：

-- 撤除 PUBLIC 角色連線至 mydatabase 資料庫的權限
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

經過這樣的設定之後，所有需要連線至 mydatabase 資料庫的使用者都需要明確以指令進行授權，否則都不可以連線至 mydatabase 資料庫。

撤除 public 角色的權限會直接影響到所有的使用者，所以如果是在正式服務的伺服器中操作，必須先對有權限的使用者進行授權，再將 public 角色權限撤除，才不會影響服務的運作。

建立資料庫角色

在 PostgreSQL 中的權限有分為資料庫（database）、schema 與物件（object）三個層級，假設我們想要授權使用者存取一張資料表（table），就要同時確認該使用者也擁有該資料表所在資料庫（database）以及 schema 的存取權限，如果中間缺少了任何一層的權限，就會無法存取。

唯讀角色

首先用以下指令建立一個唯讀用的角色 readonly：

-- 建立 readonly 角色
CREATE ROLE readonly;

上面這一行指令所建立的 readonly 只是一個基本的角色，尚未授予任何權限，也沒有設定密碼，所以無法用來登入資料庫。

授予這個角色可連線至 mydatabase 資料庫的權限：

-- 授予 readonly 角色可連線至 mydatabase 資料庫
GRANT CONNECT ON DATABASE mydatabase TO readonly;

授予這個角色可存取指定 schema 的權限，假設我們的 schema 為 myschema，則執行：

-- 授予 readonly 角色可使用 myschema 的權限
GRANT USAGE ON SCHEMA myschema TO readonly;

上面這一行指令是讓 readonly 角色可以在 myschema 這個 schema 中進行操作，如果沒有這行這項授權，縱使 readonly 角色有 myschema 中資料表的使用權限，依然還是不能進行任何操作的。

授予 readonly 角色可以對 mytable1 與 mytable2 兩個資料表進行 SELECT 操作的權限：

-- 授予 readonly 角色可對 mytable1 與 mytable2 資料表進行 SELECT 操作
GRANT SELECT ON TABLE mytable1, mytable2 TO readonly;

若需要讓 readonly 角色可以對 myschema 中的所有資料表進行 SELECT 操作的話，可以執行：

-- 授予 readonly 角色可對 myschema 的所有資料表進行 SELECT 操作
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;

上面這行指令是讓 readonly 可以對現有的所有資料表進行 SELECT 操作，但是未來若有新增的資料表，readonly 預設是不能進行任何操作的。若需要讓 readonly 可以對任何後來新增的資料表進行 SELECT 操作，可以執行：

-- 設定 readonly 角色對 myschema 新資料表的預設權限
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

讀寫角色

讀寫角色的建立流程跟唯讀角色很類似，首先建立基本角色：

-- 建立基本 readwrite 角色
CREATE ROLE readwrite;

授予這個角色可連線至 mydatabase 資料庫的權限：

-- 授予 readwrite 角色可連線至 mydatabase 資料庫
GRANT CONNECT ON DATABASE mydatabase TO readwrite;

授予這個角色可存取 myschema schema 的權限：

-- 授予 readwrite 角色可使用 myschema 的權限
GRANT USAGE ON SCHEMA myschema TO readwrite;

若希望讓 readwrite 角色可以在 myschema 中建立新的物件（例如資料表），則將上面的指令改為以下的指令：

-- 授予 readwrite 角色可在 myschema 中使用或建立物件
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;

接著設定讓 readwrite 角色的資料表存取權限，這裡同樣可以只開放指定的資料表或是全部開放，亦可針對新增的資料表設定預設權限。

若只要開放指定的資料表，可以執行：

-- 授予 readwrite 角色可對 myschema 的特定資料表進行各種操作
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable1, mytable2 TO readwrite;

若要開放 myschema 中所有的資料表，則執行：

-- 授予 readwrite 角色可對 myschema 的所有資料表進行各種操作
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;

若需要讓 readwrite 可以對任何後來新增的資料表進行各種操作，可以執行：

-- 設定 readwrite 角色對 myschema 新資料表的預設權限
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

對於讀寫角色而言，通常都會需要使用 sequences，所以要對 sequences 的使用進行授權：

-- 授予 readwrite 角色可以使用指定的 Sequence
GRANT USAGE ON SEQUENCE myseq1, myseq2 TO readwrite;

亦可直接設定讓 readwrite 角色可使用所有的 sequences：

-- 授予 readwrite 角色可以使用所有的 Sequences
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;

若需要讓 readwrite 可以使用任何後來新增的 sequences，可以執行：

-- 設定 readwrite 角色對 myschema 新 Sequences 的預設權限
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

這裡授予的各種操作權限都可以依照需求自由增減，關於更詳細的物件權限與授權語法，可以參考 PostgreSQL 資料庫的 GRANT 指令文件。

建立資料庫使用者

建立好資料庫的角色之後，要建立資料庫的使用者帳號就非常簡單了。首先建立基本的使用者帳號：

-- 建立 myuser1 使用者帳號，並設定密碼
CREATE USER myuser1 WITH PASSWORD 'mypassword';

再套用角色的權限設定即可：

-- 將 readonly 權限授予 myuser1 使用者
GRANT readonly TO myuser1;

這樣就建立好一個具有唯讀權限（readonly）的 myuser1 使用者帳號了。

若要建立具有讀寫權限的帳號，只要將授權角色改為 readwrite 即可。

PostgreSQL 資料庫的 CREATE USER 指令文件中有更詳細的建立帳號說明，例如設定帳號使用期限，或是授權建立資料庫等。

撤銷、更改使用者權限

使用角色的方式來管理權限的話，撤銷或更改使用者權限都變得非常簡單，如果要撤銷 myuser2 使用者的讀寫權限，只要執行以下一行指令即可：

-- 撤銷 myuser2 使用者的 readwrite 角色權限
REVOKE readwrite FROM myuser2;

若要將 myuser2 改為唯讀權限，則再授予 readonly 角色的權限即可：

-- 將 readonly 權限授予 myuser1 使用者
GRANT readonly TO myuser2;

使用量監控

若希望紀錄每個 PostgreSQL 帳號的使用量，可以參考 PostgreSQL 資料庫的紀錄檔說明文件， 設定 log_connections 與 log_disconnections 之後，即可記錄下每一個帳號的連線與斷線相關資訊。

如果需要更詳細的使用記錄資料，可以使用 pgAudit 擴充功能。

資料庫權限總整理列表

若要查詢資料庫中每一個使用者帳號所對應的權限角色，可以執行以下指令：

-- 列出所有使用者與角色以及對應的權限角色
SELECT
  r.rolname,
  ARRAY(SELECT b.rolname
    FROM pg_catalog.pg_auth_members m
    JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
    WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',
                        'rds_replication','rds_superuser',
                        'rdsadmin','rdsrepladmin')
ORDER BY 1;

    rolname     |    memberof     
----------------+-----------------
 app_user       | {readwrite}
 postgres       | {rds_superuser}
 readonly       | {}
 readwrite      | {}
 reporting_user | {readonly}

這個指令在清查資料庫帳號時非常有用。

一個使用者可以同時授予多個不同角色的權限，這種狀況下該使用者就會擁有所有角色權限的聯集。

另外我們亦可透過 pg_roles 資料表，查詢帳號的使用期限，或是允許同時連線的數量。

若要查詢指定使用者有權限使用的資料表，可以使用以下指令：

-- 查詢 app_user 使用者有權限使用的資料表
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'app_user';

實際範例

在實際的應用上，我們在安裝好 PostgreSQL 資料庫之後，會先建立一個 mydatabase 資料庫（當然名稱可以自由更換）：

-- 建立 mydatabase 資料庫
CREATE DATABASE mydatabase;

然後在連接至資料庫後，在資料庫中建立 schema：

-- 連接 mydatabase 資料庫
connect mydatabase;

-- 建立 Schema
CREATE SCHEMA myschema;

然後在 myschema 中建立應用程式所需要的資料表：

-- 在 myschema 中建立資料表
CREATE TABLE myschema.table1 (
   id serial PRIMARY KEY,
   name VARCHAR (50) NOT NULL
);

-- 在 myschema 中建立資料表
CREATE TABLE myschema.table2 (
   id serial PRIMARY KEY,
   my_time TIMESTAMP
);

如果希望建立兩個唯讀權限的使用者帳號，以及兩個具有讀寫權限的使用者帳號，就可以依照上面敘述的方式來建立：

-- 撤除 PUBLIC 角色在 public Schema 中的權限
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

-- 建立 readonly 角色
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

-- 建立 readwrite 角色
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

-- 建立使用者帳號
CREATE USER reporting_user1 WITH PASSWORD 'mypassword';
CREATE USER reporting_user2 WITH PASSWORD 'mypassword';
CREATE USER app_user1 WITH PASSWORD 'mypassword';
CREATE USER app_user2 WITH PASSWORD 'mypassword';

-- 對使用者授予權限
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

這樣就完成四個帳號的建立與授權了，reporting_user1 與 reporting_user2 是只有唯讀權限的帳號，而 app_user1 與 app_user2 則是具有讀寫權限的帳號。

參考資料：AWS、StackOverflow