運用內建驗證機制管理 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」x。

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

  8. 按一下「新增」。

psql

執行下列 CREATE USER 陳述式：

CREATE USER USERNAME WITH PASSWORD 'PASSWORD';

更改下列內容：

  • USERNAME：資料庫使用者的使用者名稱。

  • 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 識別碼規則：不含任何特殊字元，或以雙引號括住。例如：db_user_1。

  • PASSWORD：資料庫使用者的密碼。如要建立不含密碼的使用者，請省略這個引數。

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

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

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

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

將角色授予資料庫使用者

控制台

如要在 Google Cloud 控制台，將角色授予現有資料庫使用者，請使用 AlloyDB Studio 連線至資料庫，並執行下列 GRANT 陳述式：

GRANT ROLE TO USERNAME;

更改下列內容：

  • USERNAME：資料庫使用者的使用者名稱。

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

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

    更改下列內容：

    • USERNAME：資料庫使用者的使用者名稱。

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

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

變更資料庫使用者的密碼

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

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

控制台

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

    前往叢集

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

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

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

  5. 選取「變更密碼」。

  6. 指定密碼：

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

    • 如要不為這位使用者設定密碼，請勾選「No password」(不設定密碼) 核取方塊。

  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。

撤銷資料庫使用者的角色

控制台

如要在 Google Cloud 控制台撤銷使用者的角色，請使用 AlloyDB Studio 連線至資料庫，然後執行下列 REVOKE 陳述式：

REVOKE ROLE FROM USERNAME;

更改下列內容：

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

  • USERNAME：資料庫使用者的使用者名稱。

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 角色，或執行 gcloud alloydb users set-superuser 指令並搭配 --superuser=false 引數。

查看資料庫使用者清單

控制台

  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)。

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

  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;

      更改下列內容：

      • SCHEMA_NAME：包含資料表或檢視表的結構定義名稱。

      • OBJECT_NAME：資料表或檢視表的名稱。

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

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

      ALTER DATABASE DATABASE_NAME OWNER TO NEW_OWNER;

      更改下列內容：

      • DATABASE_NAME：資料庫名稱。

      • 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：要撤銷的角色成員資格。

      • USERNAME：要撤銷角色成員資格的使用者名稱。

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

      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. 在代表使用者的資料列中，按一下 「Open actions for this user」(開啟這位使用者的相關動作)。

  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。

後續步驟