介紹如何在 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