這次來分享如何在 PostgreSQL 上進行權限管控。
PostgreSQL 主要的權限管控為以下五個，都是針對 User 所擁有的權限進行管控：
pg_hba 規則、資料庫全域的權限、Database 權限、Schema 權限、Objects 權限
下面會針對這些權限的管理進行介紹。
1. pg_hba 規則：
資料庫內部防火牆，管控使用者能從哪個 IP 進行登入那些資料庫，僅透過設定檔進行管控，並非資料庫內部權限。
此規則設定在資料庫目錄下設定檔案 $PGDATA/pg_hba.conf
下面為一個簡單的範例，使用者在登入的時候必須要符合下面條件才可進行登入，規則由上往下檢查需要注意順序，調整完設定後會許要執行 Reload 來生效，若是使用者登入時報錯 no pg_hba.conf entry 代表著 pg_hba 規則可能有寫錯或漏寫。
注意此檔案若有填寫錯誤可能導致資料庫無法正常啟動。
TYPE：
可選擇 local 或是 host。
local 代表著透過本地的 socket 進行連線，若選擇 local 則不需要設置 ADDRESS，通常為本地 psql 等指令工具連入資料庫使用。
host 代表著透過網路進行連線登入。
DATABASE：
可登入的資料庫，replication 為 replication 同步與pg_basebackup等備份相關的連線會需要額外開啟的規則。
USER：
可登入的 User。
ADDRESS：
允許連線的來源 IP 或是 hostname，若使指定 IP 必須要完整填寫網路遮罩不然不會生效。
METHOD：
登入的驗證方法，主要常見的有以下幾種
peer：從本地登入時，會驗證登入的 DB User 與 OS User 名稱是否相同，若相同則允許登入。
trust：信任，無須驗證即可登入。
md5/scram-sha-256：驗證密碼登入，在比較新的版本預設都使用 scram-sha-256 為較安全驗證方式，除非 AP 端連線程式無法支援不然建議使用此方法，需注意資料庫內密碼的加密方式(password_encryption)需要對應，注意如果有修改密碼的加密方式，需要重新 alter password 才會將原本的密碼修改為新的加密方式。
另外也支援使用 SSL 和 LADP 等驗證方式，這兩個會需要額外進行設置。
2. 資料庫全域的權限：
使用者是否為 Superuser，是否可登入等等。
透過 \du 進行查詢，另外透過系統表 pg_roles 可以查看詳細資訊。
一般使用者擁有的權限為 Profile default (inherhit, canlogin)
其餘依據需求給予 Superuser、Replication 等等權限，另外 Cannot Login 的 User 可以做為 role(group) 的角色進行權限管控。
3. Database 權限：
三種權限類型，透過 \l 以及查詢系統表 pg_database 可檢視
C：Create Schema
T：Create TEMPORARY Table
c：Connect to database
這邊簡單說明一下 Access Privileges 如何解讀，在 Schema 以及 Objects 也是相同解讀方式。
被授予權限的帳號 =擁有權限 / 授予此權限的人
每一行代表著一條規則，
以 =c/enterprisedb 為例，前面留空代表著 PUBLIC (所有使用者)，c 代表著這些帳號可以連線，最後的 enterprisedb 代表此權限是 enterprisedb 所授予的。
補充：此處是 User 對於 Database 的權限，Database 本身另外有設定可以管控是否能登入，像是 template0 就是不可登入的資料庫，除非有特殊維運需求也不建議調整開啟登入，另外 template1 是模板資料庫，執行 CREATE DATABASE 的時候都會從 template1 複製出來。
4. Schema 權限：
透過 \dn+ 可以檢視整體狀況，以及檢視系統表 pg_namespace 查看詳細資訊
有兩種權限類型
U：USAGE 對 Schema 內物件的存取權限，但還要有對應物件的權限才能進行相關查詢
C：CREATE 允許在 Schema 下建立新物件，例如 Table、Function、Procedure 等
Create 權限允許在該 Schema 下創建 Table 等物件與刪除自己擁有的 Table 物件，不能刪除別人擁有的 Table 物件。
但有一種特殊的情況，Schema Owner 預設沒有權限訪問別人的創建的 Table ，但是可以刪除別人創建的 Table。
預設所有使用者擁有 public Schema 的 CREATE + USAGE 權限，預計在 PostgreSQL 15 開始會改成預設所有使用者擁有 public Schema 的 USAGE 權限。
透過 \dp 查看目前 search_path 下有能看到的表格 (必須要有 Schema USAGE 權限)
補充對 search_path 進行說明，search_path 表列目前連線所能看到的 Schema，透過調整 search_path 可以方便檢視不同 Schema 下的物件內容，不在 search_path 匡列範圍內的 Schema 底下物件依然可以透過 schema.table 的方式進行存取。
下面列出幾種權限類型 arwdDxtX
a：INSERT
r：SELECT
w：UPDATE
d：DELETE
D：TRUNCATE
x：REFERENCES -- allow create Foreign Key
t：TRIGGER
X：Execute
此處提醒需要注意到，預設情況下，當一個 Function/Procedure 被建立的時候，所有 User 都擁有執行此 SP 的權限(需要有該 Schema 的 USAGE 權限才能看到此 SP )，但如果 SP 內有使用到一些物件有權限不足依然會執行失敗，如果想要避免這樣的行為就需要透過 Schema USAGE 權限或是調整 Default Privilege 來改變。
6. 額外補充
除了上面列出幾個比較重要的權限，另外還有些分別的物件會需要管理權限，像是 Tablespace、Language、Foreign Server 等等，以及特定物件像是 Database 會有自己的屬性控制能不能使用/登入等等。
其中關於 Tablspace 權限，此權限只影響使用者是否能在此 tablspace 下建立物件，不影響 DML 操作，其中有一點比較特別的是，所有使用者預設會擁有該連線 Database 的 default_tablespace 的使用權限。
7. Predifined roles (https://www.postgresql.org/docs/14/predefined-roles.html)
在 PostgreSQL 中 User 和 Role 是完全一樣的，只是在管理上的概念會有區分，一般 User 指會登入的使用者，role 用來指管控權限的群組，可透過 grant role_A to user_B 的方式來將特定帳號所擁有的權限賦予給其他人，藉此能做到統一管理權限，將一個設定好權限的 user 權限 grant 給許多人進行管控。
在 PostgreSQL 中有一些預設的 role 可使用，在舊版本稱為 default roles，從 PostgreSQL 14 開始改稱為 Predifined roles，這些 role 可以幫助我們較方便的賦予一些管理權限。
像是在 PostgreSQL 14 開始新增以下兩個重要的 role：
pg_read_all_data：能讀取所有資料。
pg_write_all_data：能寫入任何表格。
在此之間只能透過手動賦予全部表格權限的方式來達成，或是透過調整 Default Privilege 權限來達成，但都需要一定的成本來達到。
8. Default Privilege (https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html)
PostgreSQL 中可以設定預設建立的物件權限，此處不多介紹可以參考手冊語法說明，僅提醒注意此設定僅能 By User 進行設定，若有多個帳號有此需求必須個別設定，因此使用上還是有一定的困難在。
可以透過一些兜 SQL 的方式來達到快速調整所有帳號預設權限。
select format('alter default privileges for role %s revoke execute on functions from PUBLIC;',rolname) from pg_roles where rolcanlogin = 't';
\gexec
