pgAudit를 사용한 PostgreSQL 감사

MySQL | PostgreSQL | SQL Server

이 페이지에서는 pgAudit 확장 프로그램을 사용하여 데이터베이스 감사를 수행하는 방법을 설명합니다. 이 확장 프로그램을 사용하면 종종 정부, 금융, ISO 인증 규제를 준수하기 위해 필요한 많은 로그를 구성할 수 있습니다.

Cloud SQL의 PostgreSQL 확장 프로그램에 대한 자세한 내용은 PostgreSQL 확장 프로그램을 참조하세요.

개요

PostgreSQL용 Cloud SQL에서 데이터베이스 감사는 오픈소스 pgAudit 확장 프로그램을 통해 제공됩니다.

이 확장 프로그램을 사용하면 지정된 데이터베이스 인스턴스에 대해 수행되는 SQL 작업을 선택적으로 레코딩 및 추적할 수 있습니다. 이 확장 프로그램은 선택한 하위 작업 집합을 모니터링하고 레코딩하기 위한 감사 기능을 제공합니다.

pgAudit 확장 프로그램은 실행된 SQL 명령 및 쿼리에 적용됩니다. 반면에 Cloud 감사 로그는 Cloud SQL 인스턴스에서 수행된 관리 및 유지보수 작업을 감사하기 위해 사용됩니다.

Cloud SQL의 감사 로깅에 대한 자세한 내용은 감사 로그 페이지를 참조하세요.

Cloud SQL에서 데이터베이스 감사 설정

pgAudit 확장 프로그램을 사용하여 감사 로깅하는 단계에는 다음이 포함됩니다.

Cloud SQL에서 cloudsql.enable_pgaudit 플래그 사용 설정
pgAudit 확장 프로그램을 만들기 위한 명령어 실행
pgaudit.log 플래그 값 설정

데이터베이스 감사를 설정한 후에는 로그를 볼 수 있으며 필요한 경우 로깅을 사용 중지할 수 있습니다.

감사 설정

이 섹션에서는 데이터베이스 감사 작업 설정의 기본사항을 설명합니다.

감사를 사용 설정하는 초기 플래그

Cloud SQL에서는 PostgreSQL 매개변수 조정 및 인스턴스 구성을 포함한 여러 작업에 데이터베이스 플래그를 사용합니다. cloudsql.enable_pgaudit 플래그는 지정된 데이터베이스 인스턴스에 감사를 사용 설정합니다. Google Cloud Console 또는 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 로그를 볼 수 있습니다.

로그 탐색기 애플리케이션에서 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 필드에 대한 설명도 참조하세요.
command. 예를 들면 ALTER TABLE 또는 SELECT입니다.
parameter. chunkIndex 필드는 이 필드의 콘텐츠를 결정할 수 있습니다. chunkIndex 필드에 대한 설명을 참조하세요. pgaudit.log_parameter 값이 설정되면 parameter 필드는 문 매개변수를 따옴표로 묶인 CSV 데이터로 포함할 수 있습니다. 매개변수가 없는 경우 이 필드에는 [none]이 포함됩니다. 그렇지 않은 경우에는 이 필드에 [not logged]가 포함됩니다.
statement. 백엔드에서 실행된 문입니다. chunkIndex 필드는 statement 필드의 콘텐츠를 결정할 수 있습니다. chunkIndex 필드에 대한 설명을 참조하세요.
statementId. 이 세션의 고유한 문 ID입니다. 각 문 ID는 백엔드 호출을 나타냅니다. 일부 문은 로깅되지 않더라도 문 ID는 순차적입니다.
substatementId. 기본 문 내의 각 하위 문에 대한 순차 ID입니다.

이러한 필드 중 일부는 pgAudit 문서에도 설명되어 있습니다.

감사 중지

데이터베이스 감사를 사용 중지하려면 cloudsql.enable_pgaudit 플래그의 값을 off로 설정합니다. 값은 Google Cloud Console 또는 gcloud 명령어를 통해 변경할 수 있습니다. 플래그에 대한 표준 안내를 사용하여 cloudsql.enable_pgaudit 플래그를 사용 중지합니다.

호환되는 psql 클라이언트를 사용하여 DROP EXTENSION 명령어를 실행하여 확장 프로그램 상태를 삭제합니다.

DROP EXTENSION pgaudit;

Cloud SQL에서 데이터베이스 감사 로깅 맞춤설정

이 섹션에서는 데이터베이스 인스턴스의 감사 동작을 맞춤설정하는 방법을 설명합니다.

확장 프로그램의 추가 기능은 pgAudit 문서를 참조하세요.

수퍼유저 권한 요구사항

Cloud SQL에서는 cloudsqlsuperuser 역할에 속하는 사용자만 확장 프로그램을 만들 수 있습니다. 새 PostgreSQL 인스턴스를 만들면 기본 PostgreSQL 사용자가 자동으로 생성됩니다(단, 사용자 비밀번호 설정 필요). 기본 PostgreSQL 사용자는 cloudsqlsuperuser 역할의 일부입니다. 자세한 내용은 PostgreSQL 사용자를 참조하세요.

인스턴스에 있는 모든 데이터베이스 작업의 감사 구성

인스턴스의 모든 데이터베이스에 감사를 구성하려면 시스템 수준에서 pgAudit 설정을 적용해야 합니다. 시스템 수준 감사 매개변수는 Google Cloud Console 또는 gcloud 명령어를 통해 데이터베이스 플래그로만 설정할 수 있습니다. 예를 들어 인스턴스의 모든 데이터베이스 작업에 대해 감사를 사용 설정하려면 다음 gcloud 명령어를 사용하면 됩니다.

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

모든 인스턴스 데이터베이스에서 특정 작업 구성

모든 인스턴스 데이터베이스의 감사에는 Google Cloud Console 또는 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 Console 및 gcloud 명령어를 통해 생성된 데이터베이스 사용자만 감사 설정을 수정할 수 있습니다.
세션 감사 로깅과 객체 감사 로깅을 모두 사용 설정하면 둘과 관련된 문이 로그에 추가됩니다. 세션 로깅과 객체 로깅은 서로 취소 또는 수정되지 않습니다.

PostgreSQL용 Cloud SQL의 pgAudit 확장 프로그램 제한사항

감사 로그는 인스턴스의 디스크에 임시로 기록되며 로그가 Cloud Logging으로 전송되기 전에 디스크 공간을 차지합니다. 따라서 이 기능을 사용하기 전에 다음 정보를 모두 검토하세요.

로그 수집 속도는 초당 4MB입니다. 로그 생성의 부하가 수집 속도를 초과하면 다음과 같은 결과가 발생할 수 있습니다.
- 원치 않는 디스크 사용량 증가가 발생할 수 있습니다.
- 디스크 공간이 소진될 수 있습니다.
이 기능을 사용 설정하고 감사 기준에 맞는 여러 쿼리를 실행하면 디스크 사용량이 너무 빠르게 증가할 수 있습니다.
이 기능을 사용하기 전에 다음에 유의하세요.
- 스토리지 자동 증가를 사용 설정합니다.
- 전체 디스크 사용량을 모니터링합니다. 로그 생성의 부하는 별도로 모니터링할 수 없습니다. 측정항목 탐색기에서 cloudsql.googleapis.com/database/disk/utilization 측정항목을 사용합니다.
- 필요한 경우 쿼리를 더 적게 실행하거나 감사를 줄여 디스크 사용량을 줄입니다.
사용 가능한 디스크 공간이 소진되면 일부 쿼리의 감사 로그가 손실될 수 있습니다.