pgAudit
오픈 소스 PostgreSQL 감사 로깅

소개

PostgreSQL 감사 확장(pgAudit)은 표준 PostgreSQL 로깅 기능을 통해 세부적인 세션 및/또는 객체 감사 로깅을 제공합니다.

pgAudit의 목표는 PostgreSQL 사용자에게 정부, 금융 또는 ISO 인증을 준수하는 데 필요한 감사 로그를 생성할 수 있는 기능을 제공하는 것입니다.

감사는 일반적으로 독립 기관이 개인 또는 조직의 계정을 공식적으로 검사하는 것입니다. pgAudit이 수집한 정보는 적절히 감사 추적 또는 감사 로그라고 합니다. 이 문서에서는 감사 로그라는 용어를 사용합니다.

pgAudit이 필요한 이유?

기본적인 명령문 로깅은 log_statement = all 설정으로 표준 로깅 기능을 통해 제공될 수 있습니다. 이는 모니터링 및 기타 용도에는 적합하지만 일반적으로 감사에 필요한 수준의 세부 정보를 제공하지는 않습니다. 데이터베이스에서 수행된 모든 작업 목록만으로는 충분하지 않습니다. 감사자가 관심을 가질 특정 명령문을 찾을 수 있어야 합니다. 표준 로깅 기능은 사용자가 요청한 내용을 보여주는 반면, pgAudit은 데이터베이스가 요청을 처리하는 동안 발생한 세부 사항에 초점을 맞춥니다.

예를 들어, 감사자는 특정 테이블이 문서화된 유지 관리 기간 내에 생성되었는지 확인하려고 할 수 있습니다. 이는 grep에 간단한 작업처럼 보일 수 있지만, 다음과 같은 (의도적으로 난독화된) 예제가 주어지면 어떻게 될까요?

DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

표준 로깅은 다음과 같은 결과를 제공합니다:

LOG:  statement: DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

테이블이 동적으로 생성되는 경우 관심 있는 테이블을 찾으려면 코드에 대한 일부 지식이 필요할 수 있습니다. 테이블 이름만으로 검색할 수 있는 것이 더 바람직하기 때문에 이는 이상적이지 않습니다. pgAudit이 필요한 바로 그 지점입니다. 동일한 입력에 대해 로그에서 다음과 같은 출력을 생성합니다:

AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

DO 블록이 로깅될 뿐만 아니라, 하위 명령문 2에는 명령문 유형, 객체 유형 및 정규화된 이름과 함께 CREATE TABLE의 전체 텍스트가 포함되어 있어 검색이 용이합니다.

pgAudit은 SELECT 및 DML 명령문을 로깅할 때 명령문에서 참조된 각 관계에 대해 별도의 항목을 로깅하도록 구성할 수 있습니다. 특정 테이블을 다루는 모든 명령문을 찾기 위해 구문 분석이 필요하지 않습니다. 실제로 목표는 명령문 텍스트가 주로 심층 포렌식을 위해 제공되며 감사에 필수적이지 않아야 한다는 것입니다.

사용 고려 사항

설정에 따라 pgAudit이 엄청난 양의 로깅을 생성할 수 있습니다. 환경에서 정확히 감사 로깅이 필요한 것이 무엇인지 확인하여 너무 많이 로깅하지 않도록 주의하십시오.

예를 들어, OLAP 환경에서 작업할 때 큰 사실 테이블에 대한 삽입을 감사 로깅하는 것은 현명하지 않을 것입니다. 로그 파일의 크기는 삽입의 실제 데이터 크기보다 몇 배 더 클 수 있습니다. 로그 파일은 텍스트로 표현되기 때문입니다. 로그는 일반적으로 OS와 함께 저장되므로 디스크 공간이 매우 빠르게 소진될 수 있습니다. 특정 테이블로 감사 로깅을 제한할 수 없는 경우, 테스트 중 성능 영향을 평가하고 로그 볼륨에 충분한 공간을 할당하십시오. 이는 OLTP 환경에서도 마찬가지일 수 있습니다. 삽입 볼륨이 높지 않더라도 감사 로깅의 성능 영향이 지연 시간에 눈에 띄게 영향을 줄 수 있습니다.

SELECT 및 DML 명령문에 대해 감사 로깅되는 관계 수를 제한하려면 객체 감사 로깅을 사용하는 것을 고려하십시오 (객체 감사 참조). 객체 감사 로깅은 로깅할 관계를 선택할 수 있도록 하여 전체 로그 볼륨을 줄일 수 있습니다. 그러나 새 관계가 추가되면 명시적으로 객체 감사 로깅에 추가해야 합니다. 이 경우 특정 테이블을 로깅에서 제외하고 다른 모든 테이블을 포함하는 프로그래밍 방식의 솔루션이 좋은 옵션이 될 수 있습니다.

PostgreSQL 버전 호환성

pgAudit은 PostgreSQL 14 이상을 지원합니다.

각 PostgreSQL 릴리스에 도입된 새로운 기능을 지원하기 위해 pgAudit은 각 PostgreSQL 주요 버전(현재 PostgreSQL 14 - 19)에 대해 별도의 브랜치를 유지 관리하며, 이는 PostgreSQL 프로젝트와 유사한 방식으로 유지 관리됩니다.

버그 수정 외에는 안정적인 브랜치에 대한 추가 개발은 허용되지 않습니다. 새로운 개발(있는 경우)은 엄격히 다음 출시되지 않은 PostgreSQL 주요 버전을 위한 것입니다.

pgAudit 버전과 PostgreSQL 주요 버전의 관계는 다음과 같습니다:

pgAudit v19.X는 PostgreSQL 19를 지원하기 위한 것입니다.
pgAudit v18.X는 PostgreSQL 18를 지원하기 위한 것입니다.
pgAudit v17.X는 PostgreSQL 17를 지원하기 위한 것입니다.
pgAudit v16.X는 PostgreSQL 16를 지원하기 위한 것입니다.
pgAudit v1.7.X는 PostgreSQL 15를 지원하기 위한 것입니다.
pgAudit v1.6.X는 PostgreSQL 14를 지원하기 위한 것입니다.

컴파일 및 설치

pgAudit은 개발 패키지가 포함된 PostgreSQL 설치본에 대해 PGXS를 사용하여 컴파일할 수 있습니다. 다음 지침은 대부분의 Unix 계열 운영 체제에서 작동해야 합니다.

pgAudit 확장을 클론합니다:

git clone https://github.com/pgaudit/pgaudit.git

pgAudit 디렉토리로 변경합니다:

cd pgaudit

REL_19_STABLE 브랜치를 체크아웃합니다 (참고: 안정 브랜치는 출시되지 않은 PostgreSQL 버전에 대해 존재하지 않을 수 있습니다):

git checkout REL_19_STABLE

pgAudit을 빌드하고 설치합니다:

make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-19/bin/pg_config

테스트 및 개발에 대한 지침은 test에서 찾을 수 있습니다.

설정

설정은 슈퍼유저만 수정할 수 있습니다. 일반 사용자가 자신의 설정을 변경할 수 있도록 허용하면 감사 로그의 목적을 무효화할 수 있습니다.

설정은 전역적으로(postgresql.conf에서 또는 ALTER SYSTEM ... SET 사용), 데이터베이스 수준에서(ALTER DATABASE ... SET 사용), 또는 역할 수준에서(ALTER ROLE ... SET 사용) 지정할 수 있습니다. 설정은 일반 역할 상속을 통해 상속되지 않으며 SET ROLE은 사용자의 pgAudit 설정을 변경하지 않습니다. 이는 역할 시스템의 한계이며 pgAudit 자체의 문제는 아닙니다.

pgAudit 확장은 shared_preload_libraries에 로드되어야 합니다. 그렇지 않으면 로드 시 오류가 발생하고 감사 로깅이 수행되지 않습니다.

또한, 적절한 pgaudit 기능을 보장하려면 pgaudit.log가 설정되기 전에 CREATE EXTENSION pgaudit가 호출되어야 합니다. 이 확장은 DDL에 대한 추가 감사를 제공하는 이벤트 트리거를 설치합니다. pgAudit은 확장이 설치되지 않아도 작동하지만, DDL 명령문에는 객체 유형 및 이름에 대한 정보가 포함되지 않습니다.

pgaudit 확장이 삭제되고 다시 생성해야 하는 경우, 먼저 pgaudit.log를 해제해야 합니다. 그렇지 않으면 오류가 발생합니다.

pgaudit.log

세션 감사 로깅에 의해 로깅될 명령문 클래스를 지정합니다. 가능한 값은:

READ: 소스가 관계 또는 쿼리인 경우 SELECT 및 COPY.
WRITE: 대상이 관계인 경우 INSERT, UPDATE, DELETE, TRUNCATE 및 COPY.
FUNCTION: 함수 호출 및 DO 블록.
ROLE: 역할 및 권한과 관련된 명령문: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
DDL: ROLE 클래스에 포함되지 않은 모든 DDL.
MISC: 기타 명령어(예: DISCARD, FETCH, CHECKPOINT, VACUUM, SET).
MISC_SET: 기타 SET 명령어(예: SET ROLE).
ALL: 위의 모든 항목 포함.

여러 클래스는 쉼표로 구분된 목록으로 제공할 수 있으며, 클래스 앞에 - 기호를 붙여 클래스를 뺄 수 있습니다 (세션 감사 로깅 참조).

기본값은 none입니다.

pgaudit.log_catalog

명령문의 모든 관계가 pg_catalog에 있는 경우 세션 로깅을 활성화할지 여부를 지정합니다. 이 설정을 비활성화하면 psql 및 PgAdmin과 같은 도구가 카탈로그를 많이 쿼리하여 발생하는 로그 노이즈를 줄일 수 있습니다.

기본값은 on입니다.

pgaudit.log_client

로그 메시지가 psql과 같은 클라이언트 프로세스에 표시될지 여부를 지정합니다. 이 설정은 일반적으로 비활성화된 상태로 두어야 하지만 디버깅이나 다른 목적으로 유용할 수 있습니다.

pgaudit.log_level은 pgaudit.log_client가 on일 때만 활성화됩니다.

기본값은 off입니다.

pgaudit.log_level

로그 항목에 사용될 로그 수준을 지정합니다 (메시지 심각도 수준 참조, 유효한 수준은 ERROR, FATAL, PANIC은 허용되지 않음). 이 설정은 회귀 테스트에 사용되며 최종 사용자가 테스트나 다른 목적으로 유용할 수도 있습니다.

pgaudit.log_level은 pgaudit.log_client가 on일 때만 활성화됩니다. 그렇지 않으면 기본값이 사용됩니다.

기본값은 log입니다.

pgaudit.log_parameter

감사 로깅에 명령문과 함께 전달된 매개변수가 포함되어야 하는지 여부를 지정합니다. 매개변수가 있는 경우 명령문 텍스트 뒤에 CSV 형식으로 포함됩니다.

기본값은 off입니다.

pgaudit.log_parameter_max_size

이 설정(바이트 단위)보다 긴 매개변수 값은 로깅되지 않고 <long param suppressed>로 대체되어야 함을 지정합니다. 이는 바이트 단위로 설정되며 문자 단위가 아니므로 텍스트 매개변수 인코딩의 멀티바이트 문자를 고려하지 않습니다. 이 설정은 log_parameter가 off인 경우 효과가 없습니다. 이 설정이 0(기본값)인 경우 길이에 관계없이 모든 매개변수가 로깅됩니다.

기본값은 0입니다.

pgaudit.log_relation

세션 감사 로깅이 SELECT 또는 DML 명령문에서 참조된 각 관계(TABLE, VIEW 등)에 대해 별도의 로그 항목을 생성해야 하는지 여부를 지정합니다. 이는 객체 감사 로깅을 사용하지 않고 철저한 로깅을 위한 유용한 지름길입니다.

기본값은 off입니다.

pgaudit.log_rows

감사 로깅에 명령문에 의해 검색되거나 영향을 받은 행 수가 포함되어야 하는지 여부를 지정합니다. 활성화되면 행 필드가 매개변수 필드 뒤에 포함됩니다.

기본값은 off입니다.

pgaudit.log_statement

로깅에 명령문 텍스트와 매개변수(활성화된 경우)가 포함될지 여부를 지정합니다. 요구 사항에 따라 감사 로그에는 이것이 필요하지 않을 수 있으며, 로그가 덜 장황해집니다.

기본값은 on입니다.

pgaudit.log_statement_once

로깅이 명령문/하위 명령문 조합에 대한 첫 번째 로그 항목에만 명령문 텍스트와 매개변수를 포함할지, 아니면 모든 항목에 포함할지 여부를 지정합니다. 이 설정을 활성화하면 로깅이 덜 장황해지지만 로그 항목을 생성한 명령문을 결정하기가 더 어려워질 수 있습니다. 다만, 명령문/하위 명령문 쌍과 프로세스 ID가 이전 항목에 로깅된 명령문 텍스트를 식별하기에 충분해야 합니다.

기본값은 off입니다.

pgaudit.role

객체 감사 로깅에 사용할 마스터 역할을 지정합니다. 여러 감사 역할을 정의하여 마스터 역할에 부여할 수 있습니다. 이를 통해 여러 그룹이 감사 로깅의 다양한 측면을 담당할 수 있습니다.

기본값은 없습니다.

세션 감사 로깅

세션 감사 로깅은 백엔드에서 사용자가 실행한 모든 명령문에 대한 상세 로그를 제공합니다.

구성

세션 로깅은 pgaudit.log 설정으로 활성화됩니다.

모든 DML 및 DDL에 대해 세션 로깅을 활성화하고 DML 명령문의 모든 관계를 로깅합니다:

set pgaudit.log = 'write, ddl';
set pgaudit.log_relation = on;

MISC를 제외한 모든 명령에 대해 세션 로깅을 활성화하고 감사 로그 메시지를 NOTICE로 발생시킵니다:

set pgaudit.log = 'all, -misc';
set pgaudit.log_level = notice;

예제

이 예제에서는 DDL 및 SELECT 명령문 로깅에 세션 감사 로깅이 사용됩니다. WRITE 클래스가 활성화되지 않았으므로 insert 명령문이 로깅되지 않습니다.

SQL:

set pgaudit.log = 'read, ddl';

create table account
(
    id int,
    name text,
    password text,
    description text
);

insert into account (id, name, password, description)
             values (1, 'user1', 'HASH1', 'blah, blah');

select *
    from account;

로그 출력:

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account
(
    id int,
    name text,
    password text,
    description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select *
    from account,,<not logged>

객체 감사 로깅

객체 감사 로깅은 특정 관계에 영향을 미치는 명령문을 로깅합니다. SELECT, INSERT, UPDATE 및 DELETE 명령만 지원됩니다. TRUNCATE는 객체 감사 로깅에 포함되지 않습니다.

객체 감사 로깅은 pgaudit.log = 'read, write'의 더 세분화된 대체물로 사용됩니다. 따라서 함께 사용하는 것이 합리적이지 않을 수 있지만, 한 가지 가능한 시나리오는 각 명령문을 캡처하기 위해 세션 로깅을 사용하고 특정 관계에 대한 더 자세한 정보를 얻기 위해 객체 로깅으로 보완하는 것입니다.

구성

객체 수준 감사 로깅은 역할 시스템을 통해 구현됩니다. pgaudit.role 설정은 감사 로깅에 사용될 역할을 정의합니다. 감사 역할이 실행된 명령에 대한 권한을 가지고 있거나 다른 역할로부터 권한을 상속하는 경우 관계(TABLE, VIEW 등)가 감사 로깅됩니다. 이를 통해 어떤 컨텍스트에서든 단일 마스터 역할이 있지만 효과적으로 여러 감사 역할을 가질 수 있습니다.

pgaudit.role을 auditor로 설정하고 account 테이블에 SELECT 및 DELETE 권한을 부여합니다. 이제 account 테이블에 대한 모든 SELECT 또는 DELETE 명령문이 로깅됩니다:

set pgaudit.role = 'auditor';

grant select, delete
   on public.account
   to auditor;

예제

이 예제에서는 객체 감사 로깅을 사용하여 SELECT 및 DML 명령문의 로깅에 세분화된 접근 방식을 취하는 방법을 보여줍니다. account 테이블의 로깅은 열 수준 권한에 의해 제어되는 반면, account_role_map 테이블의 로깅은 테이블 수준입니다.

SQL:

set pgaudit.role = 'auditor';

create table account
(
    id int,
    name text,
    password text,
    description text
);

grant select (password)
   on public.account
   to auditor;

select id, name
  from account;

select password
  from account;

grant update (name, password)
   on public.account
   to auditor;

update account
   set description = 'yada, yada';

update account
   set password = 'HASH2';

create table account_role_map
(
    account_id int,
    role_id int
);

grant select
   on public.account_role_map
   to auditor;

select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id

로그 출력:

AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select password
  from account,<not logged>
AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account
   set password = 'HASH2',<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>

형식

감사 항목은 표준 로깅 기능에 기록되며 쉼표로 구분된 형식으로 다음 열을 포함합니다. 각 로그 항목의 로그 줄 접두사 부분을 제거한 경우에만 출력이 CSV 형식과 호환됩니다.

AUDIT_TYPE - SESSION 또는 OBJECT.
STATEMENT_ID - 이 세션에 대한 고유 명령문 ID입니다. 각 명령문 ID는 백엔드 호출을 나타냅니다. 일부 명령문이 로깅되지 않더라도 명령문 ID는 순차적입니다. 둘 이상의 관계가 로깅되는 경우 명령문 ID에 대해 여러 항목이 있을 수 있습니다.
SUBSTATEMENT_ID - 기본 명령문 내의 각 하위 명령문에 대한 순차적 ID입니다. 예를 들어, 쿼리에서 함수를 호출하는 경우. 일부 하위 명령문이 로깅되지 않더라도 하위 명령문 ID는 연속적입니다. 둘 이상의 관계가 로깅되는 경우 하위 명령문 ID에 대해 여러 항목이 있을 수 있습니다.
CLASS - 예: READ, ROLE (pgaudit.log 참조).
COMMAND - 예: ALTER TABLE, SELECT.
OBJECT_TYPE - TABLE, INDEX, VIEW 등. SELECT, DML 및 대부분의 DDL 명령문에 대해 사용 가능.
OBJECT_NAME - 정규화된 객체 이름(예: public.account). SELECT, DML 및 대부분의 DDL 명령문에 대해 사용 가능.
STATEMENT - 백엔드에서 실행된 명령문.
PARAMETER - pgaudit.log_parameter가 설정된 경우 이 필드에는 인용된 CSV로 명령문 매개변수 또는 매개변수가 없으면 <none>이 포함됩니다. 그렇지 않으면 필드는 <not logged>입니다.

감사 로그 요구 사항을 충족하는 데 필요한 다른 필드를 추가하려면 log_line_prefix를 사용하십시오. 일반적인 로그 줄 접두사는 '%m %u %d [%p]: '일 수 있으며, 이는 각 감사 로그에 대해 날짜/시간, 사용자 이름, 데이터베이스 이름 및 프로세스 ID를 제공합니다.

주의 사항

감사 로깅은 최선의 노력(best-effort)이며 트랜잭션이 아닙니다. pgAudit은 표준 PostgreSQL 로깅 기능을 통해 감사 항목을 기록하며, 이는 각 항목을 생성한 트랜잭션과 동기적으로 디스크에 플러시하지 않으며 쓰기 오류를 세션에 다시 전파하지 않습니다. 커밋된 트랜잭션에 해당하는 감사 로그 항목이 있다는 보장은 없습니다. 서버가 충돌하거나 전원이 꺼지거나 로그 대상(예: 로그 볼륨이 가득 참)을 사용할 수 없게 되는 경우, 트랜잭션이 커밋된 후 감사 항목이 영구적으로 기록되기 전에 해당 항목이 손실될 수 있습니다. 반대로 명령문은 실행될 때 로깅되므로 트랜잭션이 나중에 롤백되더라도 항목이 기록될 수 있습니다.

객체 이름 변경은 이름이 변경된 이름으로 로깅됩니다. 예를 들어, 테이블 이름을 바꾸면 다음과 같은 결과가 생성됩니다:

ALTER TABLE test RENAME TO test2;

AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,ALTER TABLE test RENAME TO test2,<not logged>

명령이 두 번 이상 로깅될 수 있습니다. 예를 들어, 생성 시 기본 키가 지정된 테이블이 생성되면 기본 키에 대한 인덱스가 독립적으로 로깅되고 생성 항목 아래에 인덱스에 대한 또 다른 감사 로그가 생성됩니다. 그러나 여러 항목은 하나의 명령문 ID 내에 포함됩니다.

Autovacuum 및 Autoanalyze는 로깅되지 않습니다.

트랜잭션이 중단 상태에 진입한 후 실행된 명령문은 감사 로깅되지 않습니다. 그러나 오류를 발생시킨 명령문과 중단된 트랜잭션에서 실행된 후속 명령문은 표준 로깅 기능에 의해 ERROR로 로깅됩니다.

pgAudit으로 슈퍼유저를 안정적으로 감사하는 것은 불가능합니다. 한 가지 해결책은 슈퍼유저 계정에 대한 액세스를 제한하고 필요할 때 set_user 확장을 사용하여 권한을 에스컬레이션하는 것입니다.

저자

PostgreSQL Audit Extension은 Simon Riggs, Abhijit Menon-Sen, Ian Barwick이 작성하고 PostgreSQL 코어에 확장으로 제출한 2ndQuadrant의 pgaudit 프로젝트를 기반으로 합니다. Crunchy Data의 David Steele에 의해 추가 개발이 이루어졌습니다.

pgaudit v19beta2

카테고리

pgaudit v19beta2

pgAudit 오픈 소스 PostgreSQL 감사 로깅

소개

pgAudit이 필요한 이유?

사용 고려 사항

PostgreSQL 버전 호환성

컴파일 및 설치

설정

pgaudit.log

pgaudit.log_catalog

pgaudit.log_client

pgaudit.log_level

pgaudit.log_parameter

pgaudit.log_parameter_max_size

pgaudit.log_relation

pgaudit.log_rows

pgaudit.log_statement

pgaudit.log_statement_once

pgaudit.role

세션 감사 로깅

구성

예제

객체 감사 로깅

구성

예제

형식

주의 사항

저자

카테고리

pgAudit
오픈 소스 PostgreSQL 감사 로깅