運用內建驗證機制管理 PostgreSQL 使用者

本頁面說明如何使用 PostgreSQL 內建的密碼驗證方法，建立、管理及移除 PostgreSQL 和 AlloyDB for PostgreSQL 的角色、使用者和群組。

在 AlloyDB 中建立 PostgreSQL 使用者

建立 PostgreSQL 使用者時，他們一開始只會擁有 LOGIN 權限。如要進一步瞭解這些權限，請參閱 CREATE USER。

您可以使用 ALTER ROLE 指令變更任何使用者的權限。如果您使用 psql 用戶端建立新使用者，可以選擇將其與不同角色建立關聯，或指派不同權限。

管理使用者

您可以使用 Google Cloud CLI 或 PostgreSQL 指令，建立及管理 AlloyDB 使用者。以下各節將示範如何使用這兩種方法執行常見使用者管理工作。

事前準備

如要使用 PostgreSQL 指令管理叢集上的使用者，您需要下列項目：

  • 存取psql用戶端
  • 存取 postgres 資料庫使用者，或具有適當管理權限的其他使用者角色

如要使用 Google Cloud 控制台或 Google Cloud CLI 管理叢集中的使用者，該叢集必須有主要執行個體。如果叢集沒有主要執行個體，您必須先建立主要執行個體，才能管理使用者。

建立資料庫使用者

如要建立資料庫使用者，直接使用使用者名稱和密碼向資料庫驗證 (也稱為內建驗證)，請按照本節中的步驟操作。

如要改為建立使用 Identity and Access Management (IAM) 驗證的資料庫使用者，請參閱「管理 IAM 驗證」。

控制台

  1. 前往「Clusters」(叢集) 頁面。

    前往叢集

  2. 按一下要新增使用者的叢集名稱。

  3. 按一下「Users」(使用者)。

  4. 按一下「Add user account」(新增使用者帳戶)。

  5. 維持選取「內建驗證」。

    如要使用 IAM 驗證及登入資料庫叢集，您必須完成額外步驟，準備專案並授予新使用者適當的權限。詳情請參閱「管理 IAM 驗證」。

  6. 輸入新使用者的名稱和密碼。

  7. 如要為使用者指派預設 alloydbsuperuser 以外的角色，請按照下列步驟操作：

    1. 在「角色」中輸入角色名稱。
    2. 如要新增角色，請按 Enter 鍵或 Tab 鍵。

    3. 選取要為該使用者移除的任何角色 x。

      詳情請參閱「預先定義的角色」。

  8. 按一下「新增」。

psql

CREATE USER USERNAME WITH PASSWORD PASSWORD;

如要進一步瞭解如何建立及定義資料庫使用者，請參閱CREATE USER。

建立使用者後，您可以使用 psql 用戶端的 ALTER ROLE 指令變更其權限。

gcloud

使用下列 gcloud alloydb users create 指令：

gcloud alloydb users create USERNAME \
--password=PASSWORD \
--cluster=CLUSTER_ID \
--region=REGION_ID

請替換下列變數：

  • USERNAME：使用者的使用者名稱。USERNAME 必須遵守 SQL ID 的規則：不得包含任何特殊字元，或以雙引號括住。例如：db_user_1。

  • PASSWORD：使用者的密碼。如要建立沒有密碼的使用者，請省略這個引數。

  • CLUSTER_ID：要新增使用者的叢集 ID。

  • REGION_ID：叢集所在的區域 ID。例如：us-central1。

建立使用者後，您可以使用 gcloud alloydb users set-roles 指令變更使用者的權限。

此外，您可以在建立使用者時授予角色或超級使用者權限。如要這麼做，請加入 --db-roles 或 --superuser 引數，如下一節所述。

將角色授予資料庫使用者

控制台

如要將角色授予資料庫使用者，請使用 psql 或 gcloud CLI，而非 Google Cloud 控制台。

psql

GRANT ROLE to USERNAME;

請替換下列變數：

  • USERNAME：使用者的使用者名稱。
  • ROLE：要授予使用者的角色。舉例來說，如要授予使用者超級使用者權限，請授予 alloydbsuperuser。

gcloud

使用下列 gcloud alloydb users set-roles 指令，指定您要使用者擁有的所有角色：

gcloud alloydb users set-roles USERNAME \
--db-roles=ROLES \
--cluster=CLUSTER_ID \
--region=REGION_ID

請替換下列變數：

  • USERNAME：使用者的使用者名稱。

  • ROLES：以半形逗號分隔的清單，列出您要授予使用者的所有角色。請務必列出所有角色，包括現有和新角色。

    如要查看指派給使用者的現有角色清單，請執行下列指令：

    gcloud alloydb users list --cluster=CLUSTER_ID --region=REGION_ID

    請替換下列變數：

    • CLUSTER_ID：包含使用者的叢集 ID。

    • REGION_ID：叢集所在的區域 ID。例如：us-central1。

    如要授予使用者超級使用者權限，您可以手動使用 psql 將 alloydbsuperuser 角色授予該使用者，也可以使用 --superuser=true 引數執行 gcloud alloydb users set-superuser 指令：

    gcloud alloydb users set-superuser USERNAME \
--superuser=true \
--cluster=CLUSTER_ID \
--region=REGION_ID

    請替換下列變數：

    • CLUSTER_ID：包含使用者的叢集 ID。

    • REGION_ID：叢集所在的區域 ID。例如：us-central1。

變更資料庫使用者的密碼

如要為標準 PostgreSQL 資料庫使用者設定新密碼，請按照本節中的步驟操作。

您無法變更或設定 IAM 使用者的密碼，因為 IAM 使用者不會使用密碼進行驗證。詳情請參閱「使用 IAM 帳戶連線」。

控制台

  1. 前往「Clusters」(叢集) 頁面。

    前往叢集

  2. 按一下包含資料庫使用者的叢集名稱。

  3. 按一下「Users」(使用者)。

  4. 在代表使用者的資料列中，按一下 「開啟這位使用者的相關動作」。

  5. 選取「變更密碼」。

  6. 指定密碼：

    • 如要為這位使用者設定新密碼，請在「密碼」欄位中輸入密碼。

    • 如要為使用者設定不需密碼，請選取「不需密碼」核取方塊。

  7. 按一下 [確定]。

psql

ALTER USER USERNAME WITH PASSWORD PASSWORD;

如要進一步瞭解如何變更使用者密碼，請參閱 ALTER ROLE。

gcloud

使用下列 gcloud alloydb users set-password 指令：

gcloud alloydb users set-password USERNAME \
--password=PASSWORD \
--cluster=CLUSTER_ID \
--region=REGION_ID

請替換下列變數：

  • USERNAME：使用者的使用者名稱。

  • PASSWORD：要指派給使用者的新密碼。

  • CLUSTER_ID：使用者所屬叢集的 ID。

  • REGION_ID：叢集所在的區域 ID。例如：us-central1。

撤銷資料庫使用者的角色

控制台

如要撤銷使用者的角色，請使用 psql 或 gcloud CLI，而非 Google Cloud 控制台。

psql

您可以撤銷一或多個角色先前授予的權限，也可以撤銷使用者在角色中的成員資格，如下所示：

REVOKE ROLE FROM USERNAME;

如要移除使用者的超級使用者權限，請撤銷該使用者的 alloydbsuperuser 角色。

gcloud

如要使用 Google Cloud CLI 撤銷角色，請使用下列 gcloud alloydb users set-roles 指令，指定使用者要擁有的所有角色，並省略要從使用者撤銷的角色：

gcloud alloydb users set-roles USERNAME \
--db-roles=ROLES \
--cluster=CLUSTER_ID \
--region=REGION_ID

請替換下列變數：

  • USERNAME：使用者的使用者名稱。

  • ROLES：以半形逗號分隔的清單，列出您要授予使用者的所有角色。請務必列出所有角色，包括現有和新角色。

    如要查看指派給使用者的現有角色清單，請執行下列指令：

    gcloud alloydb users list --cluster=CLUSTER_ID --region=REGION_ID

  • CLUSTER_ID：包含使用者的叢集 ID。

  • REGION_ID：叢集所在的區域 ID。例如：us-central1。

如要撤銷超級使用者權限，您可以手動使用 psql 移除使用者的 alloydbsuperuser 角色，也可以使用 --superuser=false 引數執行 gcloud alloydb users set-superuser 指令。

查看資料庫使用者清單

控制台

  1. 前往「Clusters」(叢集) 頁面。

    前往叢集

  2. 按一下要查看使用者的叢集名稱。

  3. 按一下「Users」(使用者)。

psql

使用下列 \du 指令列印所有資料庫使用者的表格，包括他們所屬的群組：

\du

gcloud

使用下列 gcloud alloydb users list 指令：

gcloud alloydb users list \
--cluster=CLUSTER_ID \
--region=REGION_ID

更改下列內容：

  • CLUSTER_ID：包含使用者的叢集 ID。

  • REGION_ID：叢集所在的區域 ID。例如：us-central1。

移除使用者依附元件

如要刪除資料庫使用者，必須先移除該使用者擁有的所有資料庫物件，並撤銷授予他們的任何權限。否則刪除嘗試會失敗並產生錯誤 (例如 ERROR: role "username" cannot be dropped because some objects depend on it)。

如要找出並移除這些依附元件，請使用 psql 用戶端連線至資料庫，並以 postgres 使用者或任何具有 alloydbsuperuser 角色的帳戶身分登入，然後針對叢集中的每個資料庫執行下列步驟：

  1. 執行下列查詢，列出要刪除使用者擁有的物件，例如資料表、檢視區塊、結構定義和資料庫：

    SELECT
    n.nspname AS schema_name,
    c.relname AS object_name,
    CASE c.relkind
      WHEN 'r' THEN 'table'
      WHEN 'v' THEN 'view'
      WHEN 'm' THEN 'materialized view'
      WHEN 'i' THEN 'index'
      WHEN 'S' THEN 'sequence'
      WHEN 't' THEN 'TOAST table'
      WHEN 'f' THEN 'foreign table'
      WHEN 'p' THEN 'partitioned table'
      WHEN 'I' THEN 'partitioned index'
    END as object_type
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_get_userbyid(c.relowner) = 'USERNAME'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname !~ '^pg_toast';

    將 USERNAME 替換為要刪除的使用者名稱。

  2. 針對識別出的物件，您必須變更每個物件的擁有權。

    • 如要變更資料表或檢視表的擁有者，請按照下列步驟操作：

      ALTER TABLE SCHEMA_NAME.OBJECT_NAME OWNER TO NEW_OWNER;

      更改下列內容：

      • NEW_OWNER：要授予擁有權的使用者名稱。

    • 如要變更資料庫擁有者，請按照下列步驟操作：

      ALTER DATABASE DATABASE_NAME OWNER TO NEW_OWNER;

  3. 找出並撤銷權限。使用者也可能擁有其他物件的權限，或因角色成員資格而無法刪除。

    • 如要列出使用者的角色成員資格：

      WITH RECURSIVE cte AS (
SELECT oid ,oid as member_of,1 as d,''::name as path FROM pg_roles r where r.rolname !~ '^pg_'
    UNION
    SELECT m.roleid,m.member as member_of ,d+1,path||'->'||pg_get_userbyid(cte.oid) as path
    FROM cte
    JOIN pg_auth_members m ON m.member = cte.oid where d<15
)
SELECT
DISTINCT pg_get_userbyid(member_of)::varchar as USERNAME,
pg_get_userbyid(oid)::varchar as parent_role,
d::int as depth, substr(path::text||'->'||pg_get_userbyid(oid),3) as path
FROM cte
WHERE d > 1
ORDER BY depth;

    • 如要撤銷角色成員資格：

      REVOKE ROLE_NAME FROM USERNAME;

      將 ROLE_NAME 替換為要撤銷的角色成員資格。

    • 如要撤銷資料表權限，請按照下列步驟操作：

      1. 將資料表擁有者角色授予執行作業的使用者：

        GRANT TABLE_OWNER TO EXECUTOR_USER;

        更改下列內容：

        • TABLE_OWNER：擁有資料表的使用者名稱或角色。
        • EXECUTOR_USER：執行使用者的使用者名稱，例如 postgres。

      2. 撤銷資料表的權限：

        REVOKE ALL PRIVILEGES ON TABLE SCHEMA_NAME.TABLE_NAME FROM USERNAME;

        更改下列內容：

        • SCHEMA_NAME：包含資料表的結構定義名稱。
        • TABLE_NAME：資料表名稱。
        • USERNAME：要撤銷權限的使用者名稱。

刪除資料庫使用者

刪除使用者前，您必須捨棄或重新指派使用者擁有的所有物件，並撤銷使用者在其他物件上獲得的任何權限。詳情請參閱「移除使用者依附元件」。

控制台

  1. 前往「Clusters」(叢集) 頁面。

    前往叢集

  2. 按一下包含使用者的叢集名稱。

  3. 按一下「Users」(使用者)。

  4. 在代表使用者的資料列中，按一下 「開啟這位使用者的相關動作」。

  5. 選取「移除」。

  6. 在「要移除使用者帳戶嗎？」對話方塊中，按一下「移除」。

psql

使用下列設定：

DROP ROLE USERNAME;

gcloud

使用下列 gcloud alloydb users delete 指令：

gcloud alloydb users delete USERNAME \
--cluster=CLUSTER_ID \
--region=REGION_ID

更改下列內容：

  • USERNAME：使用者的使用者名稱。

  • CLUSTER_ID：使用者所屬叢集的 ID。

  • REGION_ID：叢集所在的區域 ID。例如：us-central1。

後續步驟