使用 pgAudit 稽核 PostgreSQL

本頁說明如何使用 pgAudit 擴充功能進行資料庫稽核，這有助於您設定許多常見的記錄，以符合政府、金融和 ISO 認證的要求。

如要瞭解 Cloud SQL 中的 PostgreSQL 擴充功能，請參閱「PostgreSQL 擴充功能」。

總覽

您可以透過開源 pgAudit 擴充功能，在 PostgreSQL 適用的 Cloud SQL 中執行資料庫稽核作業。

使用這個擴充功能，您可以選擇記錄並追蹤針對特定資料庫執行個體執行的 SQL 作業。擴充功能提供稽核功能，可監控及記錄所選的部分作業。

pgAudit 擴充功能適用於已執行的 SQL 指令和查詢。相反地，您應使用 Cloud 稽核記錄來稽核 Cloud SQL 執行個體上執行的管理和維護作業。

如要進一步瞭解 Cloud SQL 中的稽核記錄，請參閱「稽核記錄」頁面。

在 Cloud SQL 中設定資料庫稽核

使用 pgAudit 擴充功能進行稽核記錄的步驟如下：

  1. 在 Cloud SQL 中啟用 cloudsql.enable_pgaudit 標記。
  2. 執行指令來建立 pgAudit 擴充功能。
  3. 設定 pgaudit.log 旗標的值。

設定資料庫稽核後，您可以查看記錄，並視需要停用記錄功能。

設定稽核

本節將說明設定資料庫稽核作業的基本概念。

啟用稽核功能的初始標記

在 Cloud SQL 中，您可以使用資料庫標記執行許多作業，包括調整 PostgreSQL 參數和設定執行個體。cloudsql.enable_pgaudit 標記可為特定資料庫執行個體啟用稽核功能。您可以透過 Google Cloud 控制台或 gcloud 指令變更 cloudsql.enable_pgaudit 標記的值。

使用標準旗標指示語啟用 cloudsql.enable_pgaudit 旗標，並將值設為 on。舉例來說，如要使用 gcloud 指令，請指定以下指令，並將 [INSTANCE_NAME] 替換為執行個體名稱：

gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_pgaudit=on

cloudsql.enable_pgaudit 標記會與其他支援的標記一併列出，且僅適用於 Cloud SQL。

執行指令來建立 pgAudit 擴充功能

啟用資料庫旗標後，請使用相容的 psql 用戶端執行 CREATE EXTENSION 指令。下列指令會為 Cloud SQL 執行個體中的所有資料庫建立 pgAudit 擴充功能：

CREATE EXTENSION pgaudit;

設定 pgaudit.log 旗標的值

請使用標準旗標指示語，為 pgaudit.log 旗標設定值。

舉例來說，如要為執行個體上的所有資料庫作業啟用稽核功能，您可以使用下列 gcloud 指令：

  gcloud sql instances patch [INSTANCE_NAME] --database-flags \
  cloudsql.enable_pgaudit=on,pgaudit.log=all

設定資料庫的其他設定

如要設定資料庫的稽核設定，請按照「自訂資料庫稽核記錄」一節中的程序操作。

查看資料庫稽核記錄

如要查看稽核記錄，請啟用專案的資料存取稽核記錄。系統會將特定執行個體產生的 pgAudit 記錄，以資料存取稽核記錄的形式傳送至 Cloud Logging。使用者可以透過 記錄檔探索工具應用程式查看產生的 pgAudit 記錄。

在 Logs Explorer 應用程式中，選取 cloudaudit.googleapis.com/data_access 記錄篩選器，即可查看 pgAudit 記錄。

或者，您也可以使用下列查詢，顯示指定 Cloud SQL 專案的所有 pgAudit 記錄：

resource.type="cloudsql_database"
logName="projects/<your-project-name>/logs/cloudaudit.googleapis.com%2Fdata_access"
protoPayload.request.@type="type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"

pgAudit 的記錄格式

資料存取稽核記錄中的每個 pgAudit 記錄項目都包含欄位，代表為查詢收集的資訊。

範例如下：

{
  protoPayload: {
    @type: "type.googleapis.com/google.cloud.audit.AuditLog"
    methodName: "cloudsql.instances.query"
    request: {
      @type: "type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"
      auditClass: "READ"
      auditType: "SESSION"
      chunkCount: "1"
      chunkIndex: "1"
      command: "SELECT"
      database: "finance"
      databaseSessionId: 2209692
      parameter: "[not logged]"
      statement: "SELECT * FROM revenue"
      statementId: 2
      substatementId: 1
      user: "alice"
    }
  }
}

以下是資料存取稽核記錄中的欄位說明：

  • auditClass。記錄的陳述式類型。可能的值為 READ、WRITE、FUNCTION、ROLE、DDL、MISC 和 MISC_SET。
  • auditType。SESSION 或 OBJECT。
  • chunkCount。資料分割可能會發生在 parameter 和 statement 欄位中提供的資料上。chunkCount 欄位會指出區塊總數。另請參閱 chunkIndex 欄位的說明。
  • chunkIndex。指定 parameter 和 statement 欄位 (在目前 request 容器中) 的資料區塊索引編號。初始編號為 1。另請參閱 chunkCount 欄位的說明。
  • 指令。例如 ALTER TABLE 或 SELECT。
  • 參數。chunkIndex 欄位可決定這個欄位的內容；請參閱 chunkIndex 欄位的說明。如果已設定 pgaudit.log_parameter 的值，parameter 欄位可包含陳述式參數，做為引號標記的 CSV 資料。如果沒有參數，這個欄位會包含 [none]。否則，這個欄位會包含 [not logged]。
  • 陳述式。在後端執行的陳述式。chunkIndex 欄位可決定 statement 欄位的內容；請參閱 chunkIndex 欄位的說明。
  • statementId。這個工作階段的專屬語句 ID。每個陳述式 ID 都代表一個後端呼叫。即使系統未記錄某些陳述式，陳述式 ID 仍會依序產生。
  • substatementId。主陳述式中每個子陳述式的序號 ID。

pgAudit 說明文件中也說明瞭部分這些欄位。

停用稽核功能

如要停用資料庫稽核功能，請將 cloudsql.enable_pgaudit 旗標的值設為 off。您可以透過 Google Cloud 主控台或 gcloud 指令變更這個值。使用旗標的標準指令停用 cloudsql.enable_pgaudit 旗標。

此外，請使用相容的 psql 用戶端執行 DROP EXTENSION 指令，移除擴充狀態：

DROP EXTENSION pgaudit;

自訂 Cloud SQL 中的資料庫稽核記錄

本節說明如何自訂資料庫執行個體的稽核行為。

如需瞭解擴充功能的其他功能，請參閱 pgAudit 說明文件。

超級使用者權限需求

在 Cloud SQL 中，只有具有 cloudsqlsuperuser 角色的使用者才能建立擴充功能。建立新的 PostgreSQL 執行個體時，系統會為您建立預設 PostgreSQL 使用者 (但您必須設定使用者的密碼)。預設的 PostgreSQL 使用者屬於 cloudsqlsuperuser 角色。詳情請參閱「PostgreSQL 使用者」。

為執行個體上的所有資料庫作業設定稽核

如要為執行個體中的所有資料庫設定稽核，您必須在系統層級套用 pgAudit 設定。系統層級稽核參數只能透過 Google Cloud 控制台或 gcloud 指令，設為資料庫標記。舉例來說，如要為執行個體上的所有資料庫作業啟用稽核功能，您可以使用下列 gcloud 指令：

  gcloud sql instances patch [INSTANCE_NAME] --database-flags \
  cloudsql.enable_pgaudit=on,pgaudit.log=all

設定所有執行個體資料庫的特定作業

如要稽核所有執行個體資料庫，您可以使用Google Cloud 控制台或 gcloud 指令。舉例來說，如要只針對執行個體上的讀取和寫入作業啟用稽核功能，您可以使用下列 gcloud 指令。本範例使用以清單為基礎的語法指定多個值：

  gcloud sql instances patch [INSTANCE_NAME] \
  --database-flags ^:^cloudsql.enable_pgaudit=on:pgaudit.log=read,write

這項指令會覆寫現有的資料庫標記。

為特定資料庫設定稽核

如要設定特定資料庫的稽核作業，請在資料庫層級設定 pgAudit 參數。舉例來說，您可以使用下列 SQL 指令，為名為 finance 的資料庫啟用讀取/寫入稽核功能：

finance=> ALTER DATABASE finance SET pgaudit.log = 'read,write';

設定關聯的稽核功能

稽核關聯的範圍比稽核特定資料庫的範圍更小。

稽核關係時，系統會將專屬稽核人員角色指派給 pgaudit.role 參數。系統會記錄授予此角色的任何物件或關聯。

舉例來說，如要針對 employee 資料庫中 salary 關聯的所有 SELECT 查詢設定稽核作業，您可以使用下列指令：

employee=> CREATE ROLE auditor WITH NOLOGIN;
employee=> ALTER DATABASE employee SET pgaudit.role = 'auditor';
employee=> GRANT SELECT ON salary TO auditor;

您也可以稽核特定關聯的部分資料欄。

舉例來說，下列指令會設定稽核記錄功能，只在從 salary 關聯存取資料欄 income 和 tax_status 時才會發生：

employee=> GRANT SELECT(income, tax_status) ON salary TO auditor;

為資料庫使用者設定稽核

您可以為特定使用者啟用稽核功能，方法是針對每個 ROLE 層級設定 pgaudit.log 參數。

舉例來說，下列 SQL 指令會為使用者 Alice 執行的所有資料庫作業設定稽核：

finance=> ALTER ROLE alice SET pgaudit.log = 'all';

Cloud SQL 稽核管理的提示

自訂稽核行為時，請注意下列事項：

  • 關閉資料庫標記 cloudsql.enable_pgaudit 後，系統會立即停止記錄稽核資料。不過，除非明確移除，否則已套用的 pgAudit 設定 (例如 pgaudit.log 參數設定) 會保留。
  • 每次變更 cloudsql.enable_pgaudit 的資料庫標記值時，資料庫執行個體都會重新啟動。
  • 透過明確的 CREATE ROLE 指令建立的資料庫使用者，缺乏修改稽核設定的權限。只有透過 Google Cloud 控制台和 gcloud 指令建立的資料庫使用者，才能修改稽核設定。
  • 同時啟用工作階段稽核記錄和物件稽核記錄時，系統會將這兩者相關的陳述式加入記錄。工作階段記錄和物件記錄不會互相取消或修改。

PostgreSQL 適用的 Cloud SQL 中 pgAudit 擴充功能的限制

稽核記錄會暫時寫入執行個體的磁碟，在記錄傳送至 Cloud Logging 之前會佔用磁碟空間。因此，請在使用這項功能前詳閱下列所有資訊：

  • 記錄擷取率為每秒 4 MB。當記錄檔產生作業的負載超過攝入率時，可能會發生下列情況：
    • 磁碟使用量可能會不必要地增加。
    • 磁碟空間可能會用盡。
  • 如果您已啟用這項功能，且執行許多符合稽核條件的查詢，磁碟用量可能會快速增加。
  • 使用這項功能前，請先考慮以下事項：
    • 啟用自動增加儲存空間功能。
    • 監控整體磁碟用量；無法個別監控記錄產生的負載。在指標瀏覽器中使用 cloudsql.googleapis.com/database/disk/utilization 指標。
    • 如有需要，請減少執行查詢或稽核次數，以減少磁碟用量。
  • 如果可用磁碟空間用盡，部分查詢的稽核記錄可能會遺失。