pgaudit 확장을 사용하여 PostgreSQL을 실행하는 Amazon RDS DB 인스턴스를 감사하려면 어떻게 해야 하나요?

해결 방법

PostgreSQL DB 인스턴스의 활동을 기록하기 위해 설정할 수 있는 다양한 파라미터가 있습니다. 다양한 데이터베이스, 역할, 테이블 또는 열을 감사하려면, pgaudit 확장을 사용할 수 있습니다. pgaudit 확장을 활성화한 후, 특정 데이터베이스, 역할, 테이블 및 열을 감사하도록 pgaudit.log 파라미터를 구성할 수 있습니다.

PostgreSQL을 실행하는 Amazon RDS 인스턴스에서 pgaudit 확장 활성화

1.    다음 명령을 실행하여 rds_pgaudit이라는 특정 데이터베이스 역할을 생성합니다.

CREATE ROLE rds_pgaudit;
CREATE ROLE

2.    DB 인스턴스와 연결된 사용자 지정 DB 파라미터 그룹에서 아래 파라미터를 수정합니다.
shared_preload_libraries에 pgaudit 추가 또는 첨부
pgaudit.role을 1단계에서 생성한 역할인 rds_pgaudit에 구성

3.    파라미터 그룹의 변경 사항이 인스턴스에 적용되도록 인스턴스를 재부팅합니다.

4.    다음 명령을 실행하여 pgaudit이 초기화되었는지 확인합니다.

show shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)

5.    다음 명령을 실행하여 pgaudit 확장을 생성합니다.

CREATE EXTENSION pgaudit;
CREATE EXTENSION

6.    다음 명령을 실행하여 pgaudit.role이 rds_pgaudit으로 설정되었는지 확인합니다.

show pgaudit.role;
pgaudit.role
------------------
rds_pgaudit

7.    다음 중 하나를 감사하도록 pgaudit.log 파라미터를 구성합니다.

• ALL은 다음 명령을 감사합니다.
• MISC는 DISCARD, FETCH, CHECKPOINT, VACUUM, SET와 같은 기타 명령을 감사합니다.
• DDL은 ROLE 클래스에 포함되지 않은 모든 데이터 설명 언어(DDL)를 감사합니다.
• ROLE은 GRANT, REVOKE, CREATE/ALTER/DROP ROLE와 같은 역할 및 권한과 관련된 설명을 감사합니다.
• FUNCTION은 함수 호출 및 DO 블록을 감사합니다.
• WRITE는 대상이 관계일 때 INSERT, UPDATE, DELETE, TRUNCATE, 및 COPY를 감사합니다.
• READ는 출처가 관계 또는 쿼리일 때 SELECT 및 COPY를 감사하고 복사합니다.

감사하려는 항목에 따라 데이터베이스, 역할 또는 테이블에 대한 pgaudit.log 파라미터의 값을 설정합니다.

pgaudit 확장을 사용하여 데이터베이스 감사

1.    데이터베이스, 역할 또는 테이블의 pgaudit.log 파라미터 값을 설정하려면, 파라미터 그룹 수준에서 파라미터 pgaudit.log를 none으로 설정합니다.

> show pgaudit.log;
+---------------+
| pgaudit.log   |
|---------------|
| none          |
+---------------+
SHOW

2. 다음 명령을 실행하여 이 데이터베이스에서만 이 파라미터에 대한 시스템 구성을 무효화합니다.

ALTER DATABASE test_database set pgaudit.log='All';

이렇게 하면 pgaudit.log 파라미터 값이 All로 변경되어 test_database가 RDS DB 인스턴스에서 감사되는 유일한 데이터베이스가 됩니다.

3.    test_database에 연결하고 다음 쿼리를 실행합니다.

select * from test_table;

오류 로그의 출력은 다음과 비슷합니다.

2019-06-25 19:21:35 UTC:192.0.2.7(39330):testpar@test_database:[21638]:LOG: AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not logged>

pgaudit 확장을 사용하여 역할 감사

데이터베이스 수준에서 pgaudit.log 파라미터를 구성하는 것과 마찬가지로 pgaudit.log 파라미터에 대해 다른 값을 갖도록 역할이 수정됩니다. 다음 예제 명령에서는 test1과 test2 역할이 서로 다른 pgaudit.log 구성을 갖도록 변경되었습니다.

1.    다음 명령을 실행하여 test1과 test2에 대해 pgaudit.log 파라미터의 값을 다르게 설정합니다.

ALTER ROLE test1 set pgaudit.log='All';

ALTER ROLE test2 set pgaudit.log='DDL';

2.    다음 쿼리를 실행하여 역할 수준에서 수정이 이루어졌는지 확인합니다.

> select rolname,rolconfig from pg_roles where rolname in ('test1','
                  test2');
+-----------+----------------------+
| rolname   | rolconfig            |
|-----------+----------------------|
| test1     | [u'pgaudit.log=All'] |
| test2     | [u'pgaudit.log=DDL'] |
+-----------+----------------------+
SELECT 2
Time: 0.010s

3.    test1과 test2 모두에 대해 다음 쿼리를 실행합니다.

CREATE TABLE test_table (id int);
CREATE TABLE

select * from test_table;
id
----
(0 rows)

test1의 로그 출력은 다음과 비슷합니다.

...
2019-06-26 14:51:12 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id
int);,<not logged>

2019-06-26 14:51:18 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not
logged>
...

동일한 쿼리를 실행한 후 test2에 대한 로그 출력은 다음과 비슷합니다.

...
2019-06-26 14:53:54 UTC:192.0.2.7(44772):test2@postgres:[5517]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id
int);,<not logged>
...

참고: test2의 pgaudit.log 파라미터가 DDL로만 구성되어 있기 때문에 SELECT 쿼리에 대한 감사 항목이 없습니다.

pgaudit 확장을 사용하여 테이블 감사

pgaudit.log 파라미터를 구성하면 특정 관계에 영향을 주는 명령문을 감사하고 기록합니다. pgaudit 확장에서는 SELECT, INSERT, UPDATE, 및 DELETE 명령만 기록할 수 있습니다. TRUNCATE는 객체 감사 로깅에 포함되지 않습니다. 감사하려는 테이블의 작업(예: SELECT, DELETE, INSERT, 또는 UPDATE)에 대한 액세스 권한을 rds_pgaudit 역할에 부여하면 모든 권한 부여 감사가 해당 명령문을 기록합니다. 다음 예에서는 rds_pgaudit 역할에 SELECT 및 DELETE에 대한 액세스 권한을 부여하여 test_table의 모든 SELECT 및 DELETE 문을 감사할 수 있도록 합니다.

1.    다음 명령을 실행하여 rds_pgaudit 역할에 SELECT 및 DELETE에 대한 액세스 권한을 부여합니다.

grant select, delete on test_table to rds_pgaudit;

2.    test_table에서 DELETE 문을 실행하여 감사 로깅이 올바르게 구성되었는지 테스트합니다.

Time: 0.008s
DELETE 1
>delete from test_table where pid=5050;

DELETE 문의 출력은 다음과 비슷합니다.

2019-06-25 17:13:02
UTC:192.0.2.7(41810):postgresql104saz@postgresql104saz:[24976]:LOG:
AUDIT: OBJECT,3,1,WRITE,DELETE,TABLE,public.t1,delete from test_table where
pid=5050,<not logged>

pgaudit 확장을 사용하여 열 감사

특정 테이블의 열 수준에서 감사를 설정할 수도 있습니다. 민감한 데이터가 한 열에만 있는 경우를 예로 들 수 있습니다. 다음 예제 명령에서는 급여 테이블이 생성되고 테이블에는 감사해야 하는 급여 데이터가 포함된 민감한 열이 있습니다.

create table payroll
(    
    name text,
    salary text
);

1.    rds_pgaudit 역할에 급여 열의 SELECT에 대한 액세스 권한을 부여하여 이 열의 모든 SELECT가 감사를 받도록 합니다.

grant select (salary)  on payroll to rds_pgaudit;

2.    급여 열을 포함하여 테이블의 모든 열을 선택합니다.

select * from payroll;

다음 예제 출력에서는 급여 열을 포함하는 모든 SELECT가 감사됩니다. 하지만 급여 열을 포함하지 않는 SELECT는 감사되지 않습니다.

2019-06-25
 18:25:02
UTC:192.0.2.7(42056):postgresql104saz@postgresql104saz:[4118]:LOG:
AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.payroll,select * from
payroll,<not logged>

---

관련 정보

PostgreSQL의 일반적인 DBA 작업