在 PostgreSQL 中管理角色與角色屬性

簡介

PostgreSQL 使用各種機制來實作資料庫叢集內的身份驗證、授權和物件所有權。其中核心概念是角色。

PostgreSQL 角色結合了使用者和群組的概念，成為單一且彈性的實體。它們是使用者在資料庫系統中採用的身分，是身份驗證系統接受或拒絕連線的實體，以及所有範圍的權限管理規則的主體。

本指南將涵蓋什麼是角色，以及如何在 PostgreSQL 資料庫叢集中管理它們。更具體來說，本指南將涵蓋與角色屬性相關的角色管理。如需更廣泛地了解角色如何融入更大的圖景，請查看身份驗證與授權指南簡介。若要了解如何變更特定資料庫物件的角色權限，請查看我們關於角色授與的指南。

什麼是角色？

在 PostgreSQL 中，角色是特定功能、權限和「擁有」實體的群組。PostgreSQL 使用角色來表示「使用者」和「群組」這兩種概念，而不是擁有不同的概念。「角色」可以對應到現實世界中的個人，也可以作為一個群組運作，其他角色可以成為其成員並具有特定的存取權。

角色是 PostgreSQL 內部的錨點，用於決定身份驗證和授權策略適用於誰。任何非普遍適用的策略都需要身分概念來定義要限制誰和允許誰。在 PostgreSQL 中，此身分由角色表示。

PostgreSQL 的身份驗證系統有許多不同的組件，每個組件都與角色相關聯。為了用於初始連線到資料庫叢集，角色首先必須設定 LOGIN 屬性。CREATE USER 命令會自動新增此屬性，而 CREATE ROLE 命令則不會。身份驗證規則本身定義在名為 pg_hba.conf 的主機型組態檔中。每個規則都定義了可能限定於個別角色的身份驗證方法。針對設定為密碼身份驗證的角色，必須設定密碼屬性，以便系統可以驗證提供的使用者密碼。

就授權而言，角色是在資料庫叢集層級定義的，這在 PostgreSQL 中表示它們在資料庫之間共用。由於角色跨越資料庫，因此授權系統控制每個角色對每個資料庫實體的存取層級。由於角色可以代表人群組，因此在如何設定存取權方面具有很大的彈性。

角色對於 PostgreSQL 內部的物件所有權概念也至關重要。例如，每個資料庫和表格都恰好有一個角色設定為擁有者。除了 superuser 之外，擁有者角色是唯一可以修改或刪除實際物件的角色。

總之，角色是大多數實際資料庫操作的核心。它們的彈性使它們既可以充當使用者識別碼，又可以充當使用者類別。資料庫叢集內的每個動作都會根據角色的權限進行檢查，並且每次連線到資料庫叢集的成功與否都取決於使用者驗證的角色。由於角色在如此多的核心操作中至關重要，因此掌握良好的角色管理非常重要。

角色屬性

角色屬性是角色本身的標記，用於決定其在資料庫叢集層級上擁有的一些核心權限。這些屬性可以在初始建立角色時設定，也可以隨時由具有適當屬性的任何角色（在本例中為 SUPERUSER 或 CREATEROLE）變更。

可以應用於角色的屬性包括

• LOGIN：允許使用者使用此角色初始連線到資料庫叢集。CREATE USER 命令會自動新增此屬性，而 CREATE ROLE 命令則不會。
• SUPERUSER：允許角色繞過所有權限檢查，除了登入權限。只有其他 SUPERUSER 角色才能建立具有此屬性的角色。
• CREATEDB：允許角色建立新的資料庫。
• CREATEROLE：允許角色建立、變更和刪除其他角色。此屬性也允許角色指派或變更角色成員資格。例外情況是，具有 CREATEROLE 屬性的角色，若沒有同時具有 SUPERUSER 屬性，則無法變更 SUPERUSER 角色。
• REPLICATION：允許角色啟動串流複寫。具有此屬性的角色也必須具有 LOGIN 屬性。
• PASSWORD：為角色指派密碼，該密碼將與 password 或 md5 身份驗證機制一起使用。此屬性在屬性關鍵字之後直接採用引號括起來的密碼作為引數。
• INHERIT：決定角色是否繼承其所屬角色的權限。若沒有 INHERIT，成員必須使用 SET ROLE 切換到另一個角色，才能存取這些獨有的權限。預設情況下，新角色會設定此屬性。

您可以查看 PostgreSQL 關於角色屬性和 CREATE ROLE 命令的文件，以了解更多關於角色屬性的資訊。

什麼是 superuser 角色？

如上文簡要提及，稱為 superuser 的特殊權限允許對資料庫叢集進行不受限制的管理存取。這類似於 Linux 和類 Unix 作業系統中的 root 帳戶，但位於資料庫層級。

在每個資料庫叢集中，必須始終至少有一個具有 superuser 權限的角色。初始 superuser 帳戶是在安裝過程中建立的。初始 superuser 帳戶的名稱可能會因安裝過程而異，但最常見的情況是，此帳戶稱為 postgres。

不建議使用具有 superuser 權限的帳戶進行日常工作，原因在於其可能造成破壞性操作，並儘可能減少洩露具有廣泛存取權限的帳戶的機會。相反地，在大多數情況下，使用者應使用專用於他們正在處理的特定功能或資料物件的帳戶，僅在需要更強大的存取權限時才使用 superuser 帳戶。

檢查現有角色屬性

現在您已經大致了解什麼是角色屬性以及它們允許哪些類型的權限，您應該學習如何找出應用於整個 PostgreSQL 角色的屬性。本節將向您展示一些命令，以協助您找出一般角色以及您目前角色上設定的屬性。

\du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  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
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

-- turn on vertical display
\x on
SELECT * FROM pg_roles WHERE rolname !~ '^pg_';
-- turn off vertical display
\x off

-[ RECORD 1 ]--+---------
rolname        | postgres
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  | 
rolbypassrls   | t
rolconfig      | 
oid            | 10

SELECT rolname FROM pg_roles WHERE rolsuper;

 rolname
----------
 postgres
(1 row)

SELECT usename,usesuper FROM pg_user;

 usename  | usesuper
----------+----------
 postgres | t
 user1    | f
(2 rows)

SELECT rolname,rolsuper FROM pg_roles WHERE rolname !~ '^pg_';

 rolname  | rolsuper
----------+----------
 postgres | t
 user1    | f
(2 rows)

\du :USER

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

-- First, turn on vertical output
\x on
SELECT * FROM pg_roles WHERE rolename = CURRENT_ROLE;
-- Change back to normal output
\x off

-[ RECORD 1 ]--+---------
rolname        | postgres
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolbypassrls   | t
rolconfig      |
oid            | 10

SHOW is_superuser;

 is_superuser
--------------
 on
(1 row)

SELECT rolname as "Users who can manage roles" FROM pg_roles WHERE rolsuper OR rolcreaterole;

 Users who can manage roles
----------------------------
 postgres
(1 rows)

SELECT 'Yes' AS "Can I manage roles?" FROM pg_roles WHERE rolname = :'USER' AND (rolsuper OR rolcreaterole);

 Can I manage roles?
---------------------
 Yes
(1 row)

建立角色

一旦您驗證您具有角色管理權限，您就可以開始在 PostgreSQL 中建立、修改或移除角色。

設定角色屬性的一種選項是在您建立角色時宣告它們。這允許您設定角色的初始條件，但如果您想變更角色的存取層級，您仍然可以在之後修改它們。您可以找到更多關於CREATE ROLE 命令的資訊，我們將使用它來讓您熟悉基本語法。

建立角色的一種方法是從命令列。PostgreSQL 包含一個 createuser 命令，它將在資料庫叢集中建立具有 LOGIN 權限的角色。

一般語法為

createuser <options> <rolename>

例如，若要建立一個名為 admin 且具有 superuser 權限的角色，同時提示輸入密碼，您可以輸入

createuser --superuser admin

然後，您將能夠根據pg_hba.conf 檔案中概述的身份驗證方法，使用 admin 帳戶登入。

若要使用 SQL 建立角色，一般語法如下所示

CREATE ROLE <role>;

可以使用 WITH 在角色名稱後指定屬性

CREATE ROLE <role> WITH <options>;

例如，若要建立一個名為 user1 且可以使用密碼 secretpassword 登入的角色，您可以輸入

CREATE ROLE "user1" WITH LOGIN PASSWORD 'secretpassword';

若要改為建立一個具有 superuser 權限（您也必須是 superuser 才能成功執行此命令）但無法登入的角色（使用者必須使用 SET ROLE 切換到此角色），您可以輸入

CREATE ROLE "user2" WITH SUPERUSER;

變更現有角色

若要修改現有角色的屬性，您可以改為使用 ALTER ROLE 命令。與角色建立一樣，您目前的角色也必須具有 superuser 或 CREATEROLE 權限。不具有這些權限的使用者只能使用 ALTER ROLE 命令來變更自己的密碼。

變更角色允許您在建立後變更指派給角色的屬性。角色建立章節中提及的相同屬性可以用於 ALTER ROLE 語法。一個不同之處在於，每個屬性類型都可以透過新增 NO 前綴來否定。例如，若要允許角色登入資料庫叢集，您可以給予它 LOGIN 屬性。若要移除該能力，您可以使用指定 NOLOGIN 來變更角色。

ALTER ROLE 命令僅變更明確提及的屬性。換句話說，ALTER ROLE 命令指定屬性的變更，而不是一整組新的屬性。

若要允許 user2 角色登入資料庫叢集，您可以輸入

ALTER ROLE "user2" WITH LOGIN;

請記住，雖然這啟用了登入能力，但允許的身份驗證方法仍然由 pg_hba.conf 檔案控制。

如果您希望 user2 能夠登入、建立角色和建立資料庫，您可以指定這三個屬性，並以空格分隔

ALTER ROLE "user2" WITH LOGIN CREATEROLE CREATEDB;

若要撤銷角色的 superuser 狀態（您只能使用另一個 superuser 角色執行此命令），請輸入

ALTER ROLE "user2" WITH NOSUPERUSER;

若要變更角色的密碼，您可以輸入以下內容（所有角色都應該能夠對自己的角色執行此命令，無論是否具有 CREATEROLE 或 superuser 權限）

ALTER ROLE <role> WITH PASSWORD '<password>';

雖然上述命令有效，但如果可能，最好使用 psql 中繼命令來變更密碼。psql 命令會自動提示輸入密碼，並在將其傳送至伺服器之前對其進行加密。這有助於避免在記錄中洩露敏感資料。

您可以使用 psql 透過輸入以下內容來變更角色的密碼

-- To change your own password
\password
-- To change the password for another role
\password <role>

您也可以使用 ALTER ROLE 命令來重新命名角色

ALTER ROLE <role> RENAME TO <newrole>

請記住，您無法重新命名您目前的會話角色。

刪除角色

刪除現有角色的模式與先前的命令類似。同樣地，您必須具有 CREATEROLE 或 superuser 權限才能執行這些命令。

一個複雜的因素是，如果角色仍然被資料庫內的物件引用，則無法移除角色。這表示您必須刪除或轉移角色擁有的任何物件的所有權。之後，您還必須撤銷角色對資料庫物件擁有的任何其他權限。

Database Administrators Stack Exchange 網站上的 Erwin Brandstetter 提供了關於如何適當重新指派和移除權限的詳細說明。以下使用了相同的流程。

首先，您可以使用 REASSIGNED OWNED 命令重新指派角色的所有已擁有物件。例如，如果您正準備刪除 user2 角色，您可以透過輸入以下內容將其物件指派給 postgres 角色

REASSIGN OWNED BY "user2" TO "postgres";

現在物件由 postgres 擁有，我們可以使用 DROP OWNED 命令撤銷我們被授予物件的所有其他權限。此命令也會刪除我們擁有的任何物件，但由於我們剛剛將它們轉移到 postgres 角色，因此 user2 角色不再擁有任何已擁有物件。因此，該命令將僅撤銷角色的任何其他權限

DROP OWNED BY "user2";

如果沒有上面的 DROP OWNED 快捷方式，您將必須對角色具有權限的每個個別物件或物件類型執行 REVOKE ALL PRIVILEGES。

一旦您撤銷所有相關聯的權限，您就可以透過輸入以下內容來移除角色

DROP ROLE "user2";

使用 psql 登入

一旦您設定了新角色，並使用 pg_hba.conf 檔案設定了身份驗證詳細資訊，您就可以使用您的新角色登入資料庫叢集。psql 命令列用戶端提供了一種簡單的方法來執行此操作。

預設情況下，psql 假設您想要使用與您的作業系統使用者名稱相符的角色進行連線。因此，如果您以 john 身分登入您的電腦，psql 將假設您想要嘗試使用也稱為 john 的角色連線到資料庫。

若要覆寫此行為，您可以傳遞 -U 或 --username= 選項。例如，如果您想要登入名為 kerry 的角色，您可以輸入

psql -U kerry

psql 命令的成功與否將取決於 kerry 角色的存在、您嘗試連線的伺服器的可存取性，以及伺服器上定義的身份驗證規則。

在會話期間變更為不同的角色

有時，您可能想要暫時採用您有權存取的另一個角色的權限和身分。例如，如果您想獲得您所屬角色的權限，而您目前的角色沒有 INHERIT 屬性，則這是必要的。

若要了解其運作方式，您必須了解 PostgreSQL 用於分類作用中角色的術語

• 會話角色：會話角色是您在初始連線到 PostgreSQL 資料庫叢集期間登入的角色。它設定您的初始權限並決定您對系統的存取權限。此角色必須具有 LOGIN 屬性。
• 目前角色：相反地，目前角色是您目前正在擔任的角色。與目前角色相關聯的權限，無論是直接設定還是從其他角色繼承，都決定了您被允許執行的動作以及您可以存取的物件。

您可以透過輸入以下內容來檢視您的會話和目前角色值

SELECT SESSION_USER, CURRENT_USER;

 current_user | session_user
--------------+--------------
 postgres     | postgres
(1 row)

雖然變更會話角色的唯一方法是使用不同的角色啟動新連線，但您可以使用 SET ROLE 命令來變更您的目前角色。SET ROLE 命令用於暫時充當不同的角色。該命令也選擇性地接受以下修飾符

• SESSION：預設設定。這會導致 SET ROLE 命令影響整個資料庫會話。
• LOCAL：此修飾符將使命令僅針對目前的交易變更角色。

若要將目前角色變更為 user2 角色 (在本次會期剩餘時間內)，請輸入

SET ROLE "user2";

如果您檢查您的會期和目前角色值，您會看到目前角色值已變更

SELECT SESSION_USER, CURRENT_USER;

 current_user | session_user
--------------+--------------
 user2        | postgres
(1 row)

您所有的操作現在都將使用 user2 角色作為其上下文。

若要變更回您先前使用的會期角色，您可以輸入

SET ROLE NONE;

另一種達到相同結果的替代方案是

RESET ROLE;

結論

PostgreSQL 的角色、角色屬性、授權和身份驗證系統建立了一個彈性的系統，讓管理員能夠有效地管理權限和資料庫存取。本指南說明了角色究竟是什麼，以及它們如何涵蓋廣泛的使用案例。它還涵蓋了如何建立、修改和刪除角色，以及管理決定其全域功能角色屬性。為了保護您的資料庫並為您的合法使用者提供可用的存取權，理解如何管理這些身分至關重要。

常見問題

ALTER ROLE <role> WITH PASSWORD '<password>';

- To change your own password
\password
-- To change the password for another role
\password <role>

\du :USER

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

SELECT rolname as "Users who can manage roles" FROM pg_roles WHERE rolsuper OR rolcreaterole;

SELECT 'Yes' AS "Can I manage roles?" FROM pg_roles WHERE rolname = :'USER' AND (rolsuper OR rolcreaterole);

CREATE ROLE <role>;

CREATE ROLE <role> WITH <options>;