管理PostgreSQL中的角色和角色屬性

簡介

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

PostgreSQL角色是將使用者和組的概念組合成一個單一、靈活的實體。它們是使用者在資料庫系統中的身份，是身份驗證系統接受或拒絕連線的實體，也是所有範圍許可權管理規則的主體。

本指南將介紹什麼是角色以及如何在PostgreSQL資料庫叢集中管理它們。更具體地說，本指南將涵蓋與角色屬性相關的角色管理。有關角色如何融入更大圖景的更廣泛概述，請參閱身份驗證和授權簡介指南。要了解如何更改特定資料庫物件上的角色許可權，請檢視我們關於角色授權的指南。

什麼是角色？

在PostgreSQL中，角色是特定能力、許可權和“擁有”實體的分組。PostgreSQL不使用“使用者”和“組”的獨立概念，而是使用角色來表示這兩個概念。一個角色可以對應現實世界中的一個人，或者它可以作為一個具有特定訪問許可權的組，其他角色可以成為其成員。

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

PostgreSQL的身份驗證系統有許多不同的元件，每個元件都與角色繫結。為了用於與資料庫叢集的初始連線，角色必須首先設定LOGIN屬性。身份驗證規則本身定義在名為pg_hba.conf的基於主機的配置檔案中。每個規則定義可以作用於單個角色的身份驗證方法。對於配置為密碼身份驗證的角色，必須設定密碼屬性，以便系統可以驗證提供的使用者密碼。

在授權方面，角色在資料庫叢集級別定義，這意味著它們在PostgreSQL中是資料庫之間共享的。由於角色跨越多個數據庫，授權系統控制每個角色對每個資料庫實體的訪問級別。因為角色可以代表人群，所以在配置訪問方面有很大的靈活性。

角色對於PostgreSQL中的物件所有權概念也至關重要。例如，每個資料庫和表都只有一個角色被配置為所有者。除了超級使用者之外，所有者角色是唯一可以修改或刪除實際物件的角色。

總而言之，角色是大多數實際資料庫操作的核心。它們的靈活性使其既可以充當使用者識別符號，也可以充當使用者類別。資料庫叢集中的每個操作都根據角色的許可權進行檢查，每次連線到資料庫叢集的成功都由進行身份驗證的角色決定。由於角色管理在許多核心操作中都非常重要，因此掌握角色管理至關重要。

角色屬性

角色屬性是角色本身的標誌，它們決定了角色在資料庫叢集級別的一些核心許可權。這些屬性可以在角色最初建立時設定，也可以由具有適當屬性（本例中為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-like作業系統中的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許可權才能執行這些命令。

一個複雜因素是，如果角色仍然被資料庫中的物件引用，則不能刪除它們。這意味著您必須刪除或轉移角色擁有的任何物件的所有權。之後，您還必須撤銷角色對資料庫物件的任何額外許可權。

Erwin Brandstetter在Database Administrators Stack Exchange網站上提供了關於如何適當重新分配和刪除許可權的詳細解釋。以下使用相同的過程。

首先，您可以使用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>;