[PostgreSQL] 보안과 백업

보안

보안을 유지한다는 것은, 안전한 곳에 보관된 데이터에 접근할 수 있는 사람과 그렇지 않은 사람을 구분하고 제한한다는 것과 같습니다. 보안의 정도는 데이터의 중요도에 따라 그리고 누가 얼마나 데이터에 접근할 수 있는지에 따라 달라집니다. DBMS에서 보인이 이루어지는 구조는 사용자가 시스템에 접속할 기본적인 권리가 있다는 것을 확인해주는 인증 단계를 거쳐야합니다. 일반적으로 Id와 Password를 통해 홈페이지에 로그인 하는 방법으로 이 인증 단계를 통과합니다. 인증 단계를 통해 시스템에 접속했다면 시스템내에서 특정 기능에 접근할 수 있는지에 대한 여부를 결정하는 권한 설정 단계를 거쳐야 합니다. 이 단계에서는 사용자가 접근하는 것에 대한 승인이 필요합니다. 권한 설정 단계를 마쳤다면, 사용자는 데이터베이스에 접근하여 필요한 작업을 할 수 있는 상태가 됩니다.

PostgreSQL 보안은 세 가지 측면에서 생각해볼 수 있습니다. 첫 번째는 저장되는 데이터 파일들에 대한 보안입니다. 두 번쨰는 접속하는 클라이언트들의 접근에 대한 보안입니다. 마지막으로, 접근에 대한 검증이 끝난 사용자가 어느정도 수준까지 접근할 수 있는지에 대한 보안입니다. 즉, 데이터베이스에 접속한 클라이언트가 해당 데이터베이스의 테이블과 함수 등의 객체까지도 접근할 수 있는지에 대한 보안입니다.

롤

클라이언트 접근 보안과 데이터베이스 객체 보안에 대해 이해하기 위해서는 롤에 대해서 알아야 합니다. 왜냐하면 데이터베이스 접근 권한을 관리하는 역할은 롤이 맡고 있기 때문입니다. 롤은 특이하게도 개인이 될 수도 있고 집단이 될 수도 있습니다. 즉, 상황에 따라 PostgreSQL의 사용자가 될 수도, 그룹이 될 수도 있습니다.

롤은 데이터베이스를 이루는 테이블, 함수, 뷰, 시퀀스 등의 데이터베이스 객체들에 대한 소유권가 권한을 부여할 수 있는 특성을 가지고 있습니다. 롤을 생성하거나 삭제하려면 다음의 명령어를 사용합니다.

CREATE ROLE 롤이름;
DROP ROLE 롤이름;

기존의 롤 목록을 보고싶다면 다음과 같이 입력합니다.

postgres=# \du

롤의 속성

• 로그인 권한: 데이터베이스를 연결할 때 처음으로 해당 데이터베이스와 연결되는 롤이 있습니다. 데이터베이스와 처음으로 연결되는 롤은 로그인 속성, 즉 로그인을 할 수 있는 권한을 가진 롤이어야만 합니다.
• 슈퍼유저 권한: 거의 모든 권한을 다 가진 사용자를 의미합니다. 따라서 슈퍼유저는 데이터에 접근할 때 허가를 따로 받지 않아도 자유롭게 접근할 수가 있습니다. 슈퍼유저 롤로 작업을 하는 경우 보안상의 문제도 발생할 수 있기 때문에, 일반적인 작업을 할 때는 접근권한을 적절하게 제한된 DB보안의 롤로 작업하는 것이 좋습니다.
• 데이터베이스 생성 권한: 슈퍼유저가 아닌 다른 롤로 작업을 수행하는 경우, 데이터베이스를 생성할 권한을 해당 롤에 부여해야 합니다.
• 롤 생성 권한: 롤 생성의 경우도 데이터베이스 생성과 마찬가지로 롤을 생성할 권한을 따로 부여해야 합니다.

이밖에도 암호 권한, 복제 초기화 권한 등의 속성을 가지고 있습니다.

클라이언트 접근 보안

pg_hba.conf 파일

PostgreSQL은 클라이언트 접근을 제어하기 위해서 pg_hba.conf 파일을 사용합니다. pg_hba.conf 파일은 기본적으로는 우리가 PostgreSQL을 설치한 경로의 data 파일 다음에 위치합니다. pg_hba.conf 파일은 텍스트 파일이기 때문에 경로만 안다면 텍스트 편집기로 파일을 열고 수정할 수 있습니다. pg_hba.conf에서 ‘hba’는 호스트 기반의 인증이라는 뜻의 약자입니다. pg_hba.conf 파일은 한 줄로 이루어져 있는 레코드들의 집합입니다.

필드특성예시

TYPE	연결 타입을 결정하는 필드	local, host, hostssl, hostnossl 등
DATABASE	해당 레코드와 일치하는 데이터베이스를 지정하는 필드	all, sameuser, samerole, replication, 특정 데이터베이스 이름 등
USER	해당 레코드의 일치하는 데이터베이스 사용자를 지정하는 필드	all, 특정 데이터베이스 사용자 이름 등
ADDRESS	해당 레코드와 일치하는 클라이언트 머신의 주소를 지정하는 필드	ip 주소/마스크 길이 ex) 192.168.0.0/16
METHOD	해당 레코드와 연결이 되었을 때 사용할 인증 수단을 지정하는 필드	trust, reject, password, ident, sspi, gss, bsd 등

인증 방법의 종류

• trust 인증: trust 인증은 가장 낮은 단계의 인증입니다. trust 인증을 사용한다는 것은 모든 사용자들을 받아들인다는 말과 같습니다. 즉 암호 없이 어떤 데이터든지 쉽게 접근할 수 있는 방법입니다. 또한 사용자 이름을 조작하더라고 들키지 않고 보안을 해제할 수 있습니다. 그러므로 추가적인 보안 작업이 따로 없는 환경에서는 trust 인증을 사용하면 안됩니다. 왜냐하면 이때 trust 인증을 사용하면 다른 사용자들에 대한 제한을 걸 수가 없기 때문입니다. 따라서 trust 인증은 집에서 혼자만 사용하는 데스크탑 환경같이 보안이 크게 중요하지 않은 환경에서만 사용해야 합니다.
• password 인증
  • password 인증: 암호화되어 있지 않은 암호를 입력받아 인증되는 구조
  • md5 인증: 위의 입력받은 암호를 128비트 길이의 해시값으로 암호화하여 인증하는 방법
  • scram-sha256: md5와 비슷하게 암호화하여 인증하는 방식이며, 현재 가장 안전하게 암호를 보호할 수 있다고 여기는 방법
  • pam 인증: pluggable authentication modules 인증만을 위한 프레임워크를 사용하여 기존의 애플리케이션으로부터 인증 과정을 떼어내는 방법
• ident, peer 인증: TCP/IP 연결 여부에 따라 구분할 수 있습니다. TCP/IP 연결이 되었을 때 사용하는 ident 인증은 사용자의 이름을 활용하여 인증하는 방법입니다. 간단히 말해서, ident 서버의 사용자 이름과 연결하려고 하는 사용자의 이름이 일치할 경우에 접속을 허용하는 방법입니다. ident 인증을 사용하면 특정 호스트의 사용자 이름과 ident의 사용자 이름이 일치할때에만 보안 인증이 가능합니다. 그러나 ident 인증 방법도 사용자 이름을 조작하여 보안을 쉽게 해제할 수 있는 단점이 있습니다. peer는 ident와 다르게 TCP/IP 연결을 하지 않은 상태에서 인증하는 방법입니다. 즉 peer 인증은 인증할 사용자 이름을 커널로부터 얻어 비교하는 인증 방법입니다.

인증 방법내용

reject	특정 클라이언트를 배제하고 싶을 때 무조건적으로 인증을 거부하는 방법
GSS	GSSAPI라는 보안 인증용 프로토콜을 이용하여 인증하는 방법
SSPI	윈도우 환경에서 SSPI를 사용하여 인증하는 방법
LDAP	LDAP를 사용하여 password를 검증하는 방법
RADIUS	RADIUS를 사용하여 password를 검증하는 방법
BSD	BSD를 사용하여 password를 검증하는 방법
인증서	SSL 인증서를 사용하여 인증하는 방법

테이블 보안

권한

데이터베이스 객체들이 생성되면 자연스럽게 객체를 생성한 사용자가 객체 소유권을 가집니다. 아래의 권한은 소유권을 가진 사용자들은 가지고 있습니다.

권한역할

CREATE	데이터베이스에 새로운 스키마를, 스키마에 새로운 개체를, 테이블공간에 새로운 테이블과 인덱스 등을 만들 권한을 줌
SELECT	특정 테이블, 뷰, 시퀀스 등의 어떠한 컬럼에 대해서도 SELECT 할 권한을 줌
INSERT	특정 테이블, 뷰, 시퀀스 등에 새로운 로우를 INSERT 할 권한을 줌
UPDATE	특정 테이블, 뷰, 시퀀스 등의 어떠한 컬럼에 대해서도 UPDATE 권한을 줌
DELETE	특정 테이블, 뷰, 시퀀스 등의 어떠한 로우에 대해서도 DELETE 할 권한을 줌
REFERENCES	특정 테이블이나 컬럼을 참조하는 외래 키 제약을 만들 권한을 줌
TRUNCATE	특정 테이블을 TRUNCATE 할 수 있는 권한을 줌
TRIGGER	특정 테이블에 트리거를 만들 권한을 줌
CONNECT	특정 데이터베이스에 사용자가 접속할 수 있는 권한을 줌
EXECUTE	특정 함수나 연산자를 사용할 수 있는 권한을 줌
TEMPORARY	데이터베이스에 임시 테이블을 만들 수 있는 권한을 줌
USAGE	특정 스키마 내부의 개체에 접근할 수 있는 권한을 줌/주어진 절차적 언어로 함수를 만들 수 있는 권한을 줌

그런데 객체 소유권을 가진 사용자가 자신이 가진 권한을 다른 사용자에게도 나눠주고 싶다는 생각이 들었다면 GRANT 명령어를 통해 다른 사용자에게 권한을 부여할 수 있습니다.

GRANT <권한이름> ON <주는 사용자> TO <받는 사용자>

함수 보안

일반적으로 함수를 만들어 사용할 때 함수가 가지는 권한은 함수를 만든 role의 권한에 의해 결정됩니다. 그러므로 함수를 만드는 role의 권한을 조정하여 함수가 허가되지 않은 사용자에 의해 쓰이는 것을 막을 수 있습니다. PostgreSQL의 함수는 대부분 SECURITY INVOKER 명령어에 의해 만들어집니다. SECURITY INVOKER는 함수를 호출하는 role의 권한에 따라 함수도 권한을 가지는 것입니다. 반대로 SECURITY DEFINER는 함수를 만든 role의 권한에 따라 함수가 권한을 가지는 것입니다.

SECURITY DEFINER 함수의 보안 문제

SECURITY DEFINER로 함수를 만들면 함수를 호출하는 role이 권한을 갖고 있지 않아도 함수를 만든 role이 권한을 가지고 있다면 해당 함수를 사용할 수 있어서 보안 상의 문제가 발생할 수 있습니다.

SECURITY DEFINER 함수 보안 문제 해결

PostgreSQL에서는 search_path 변수를 사용하여 해당 문제를 해결할 수 있습니다. search_path란 탐색할 스키마의 순서를 지정하는 변수입니다. search_path를 조작해서 신뢰할 수 있는 스키마를 먼저 탐색하고, 보안상 취약한 스키마를 제일 뒤로 보낸다면 보안상의 문제를 해결할 수 있습니다. search_path를 조작할 수 있는 방법은 일반적으로 다음과 같습니다.

SECURITY DEFINER
  SET search_path = '신뢰할 수 있는 스키마', pg_temp;

신뢰할 수 있는 스키마 다음에 pg_temp가 온 이유는 pg_temp를 따로 지정하지 않으면 제일 먼저 탐색되는 스키마이기 때문입니다. 따라서 pg_temp를 탐색하는 것을 제일 마지막으로 보내버리면 문제를 해결할 수 있습니다.

특정 사용자만 함수를 사용할 수 있게끔 권한을 조정하기 전에 모두가 함수에 접근할 수 있었던 조건을 없애고, 필요한 role에 권한을 부여합니다. 권한을 없애고 다시 부여하는 중간에 보안상의 문제가 생길 수도 있으니 전체 작업을 하나의 트랜잭션으로 묶어서 수행합니다.

BEGIN;
  CREATE FUNCTION '함수 설정'
  SECURITY DEFINER;
  REVOKE ALL ON FUNCTION '함수 이름' FROM PUBLIC;
  GRANT '권한 이름' ON FUNCTION '함수 이름' TO 'role 이름';
COMMIT;

백업

파일시스템 기반의 백업

파일시스템 기반의 백업은 파일시스템 전체를 통째로 복사해서 백업 파일로 만드는 것을 말합니다. PostgreSQL은 모든 데이터를 data 디렉토리에 저장하므로, data 디렉토리에서 파일들을 백업해야 합니다. 일반적으로 다음과 같은 명령어로 파일시스템 기반의 백업이 가능합니다.

tar -cf backup.tar 'data 디렉토리 경로'

파일시스템 기반의 백업은 있는 그대로 복사하는 방법이기 때문에 백업해야 하는 양이 많을수록 다른 방법들보다 빠른 백업이 가능할 수 있습니다. 그러나 일반적으로 파일시스템 기반의 백업이 가지는 한계로 인해 잘 사용하지 않습니다. 파일시스템 기반의 백업은 실행할 때 데이터베이스의 서버를 중지하고 진행해야 합니다. 또한 파일시스템 기반 백업은 클러스터 단위로 백업을 진행하기 때문에, 원하는 부분별 백업이 불가하고 전체 백업만 가능합니다. 이렇게 전체 단위로 백업을 진행하다보니 백업할 필요가 없는 파일들도 하게 되어 파일의 용량도 더 큽니다. 이러한 단점들로 인해 대부분 백업시에 파일시스템 기반 백업은 잘 쓰지 않습니다.

dump를 사용한 백업

dump를 이용한 백업은 dump라는 SQL 스크립트를 이용해서 백업을 하는 방법입니다. dump에는 백업한 순간으로 데이터를 재생산할 수 있는 방법이 적혀 있습니다. 따라서 빈 데이터베이스에 dump 파일을 넣으면 백업했던 상태 그대로 복구가 됩니다.

pg_dump

pg_dump '데이터베이스 이름' > '덤프 파일 이름'

해당 데이터베이스의 모든 것을 지정한 덤프 파일을 넣는다는 의미입니다. pg_dump를 사용하여 백업을 하면 두 가지 장점이 있습니다. 하나는 백업할 당시의 버전과 현재 PostgreSQL의 버전이 달라도 자동으로 복원이 된다는 것입니다. 다른 하나는 32비트에서 64비트로 이동하더라도 문제없이 복원됩니다. 이 두 가지 장점은 파일시스템 기반의 백업이나 아카이브 모드 백업과는 다른 점입니다.

pg_dump로 덤프 파일을 만들면 텍스트 형식으로 저장이 됩니다. 텍스트 형식으로 저장된 덤프 파일은 PostgreSQL 콘솔에서 바로 사용할 수 있습니다.

psql '데이터베이스 이름' < '덤프 파일 이름'

백업한 덤프 파일에서 데이터베이스로 데이터가 이동하는 것이므로, 백업할 때와 반대 방향의 화살표를 보입니다. 덤프 파일을 복원할 데이터베이스는 미리 지정해놓는 것이 좋습니다.

pg_dumpall

pg_dump는 특정 데이터베이스 내의 테이블들을 지정해 백업할 수 있지만, 다수의 데이터베이스를 백업할 수는 없습니다. 다수의 데이터베이스와 데이터베이스 객체 이외의 정보들까지도 백업하기 위해서 pg_dumpall을 사용합니다.

pg_dumpall > '덤프 파일 이름'

아카이브 모드 백업

백업하기

아카이브는 기록보존소라는 의미를 가진 단어입니다. 즉 dbms에서 아카이브는 데이터들을 보존하여 복구할 때나 참조할 때 쓸 수 있게 해주는 것입니다. PostgreSQL의 아카이브 모드 백업은 WAL 파일과 관련이 있습니다. WAL파일은 변경 내용을 적용하기 전에 변경할 내용을 로그에 기록하여 혹시 변경 후에 문제가 생기더라도 다시 복구할 수 있도록 하는 방법입니다.

이 로그를 이용하여 백업이 가능하고, 또 원하는 시점으로 복구할 수 있는 point-in-time recovery(pitr)이 가능합니다. 우선 WAL의 아카이브 파일을 만들기 위해서는 postgresql.conf 파일을 수정해야 합니다. postgres.conf 파일은 보통 PostgreSQL을 설치한 경로의 data 파일 내에 위치합니다.

• wal_level: WAL에 기록되는 정보의 양을 지정하는 요소입니다. wal_level는 enum형으로 minimal, replica, logical 등이 있습니다. 아카이브 설정에는 archive를 사용합니다.
• archive_command: archive_command는 WAL 파일을 아카이브 시키는 명령어입니다. archive_command에는 %p와 %f 인자를 사용할 수 있습니다. %p는 WAL 파일의 절대 경로를 나타내고 %f는 저장할 로그 파일의 이름을 나타냅니다.
• archive_mode: archive_mode는 on/off로 설정할 수 있는 boolean형입니다. on으로 설정하려면 wal_level을 archive로 설정해놓아야 합니다. archive_mode를 on으로 설정하면 WAL 파일이 archive_command 설정에 따라 아카이브를 저장하는 저장소에 전달됩니다.
• achive_timeout: achive_timeout은 int형이며, 아카이브 되지 않고 데이터가 남아있는 경우를 방지하기 위해 지정한 시간마다 새로운 WAL 파일을 아카이브하도록 지정하는 명령어입니다. achive_timeout은 보통 60초에서 120초 사이로 설정합니다.

#------------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------------

# - Settings -

#wal_level = replica    # minimal, replica, or logical
                # (change requries restart)
...
# - Archiving -

# archive_mode = off    # enables archiving; off, on, or always
                # (change requries restart)
# archive_command = ''  # command to use to archive a logfile               segment
                # placeholders: %p = path of file to archive
                #         %f = file name only
                # e.g.  'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/
server/arhivedir/%f'

아카이브 파일을 만들기 위해서는 위의 요소들을 적절히 설정해야 합니다. 우선 wal_level은 replica나 그 이상의 단계로 설정하고, archive_mode는 on으로 설정해야 합니다. archive_command는 자신의 os 환겨엥 맞게 설정하고 일반적으로 archive_command의 형태는 다음과 같습니다.

archive_command = 'copy "%p" "아카이브 파일을 저장할 곳%f"'

그런 다음 백업한 복제된 replication 데이터베이스에 접근할 수 있도록 pg_hba.conf 파일을 설정해줍니다. pg_hba.conf 파일 설정을 마쳤다면 데이터베이스 서버를 재시작해야 반영이 됩니다. 서버를 시작하고 중지, 제어하는 역할은 pg_ctl 명령어로 수행할 수 있습니다.

베이스 백업 만들기

서버를 재시작하면 이제 베이스 백업을 만들어야 합니다. PostgreSQL은 베이스 백업을 만들 수 있는 pg_basebackup 도구를 지원합니다. pg_basebackup을 사용하면 백업한 파일들을 tar 파일 같이 다수의 파일을 하나로 묶어서 저장할 수 있습니다. pg_basebackup 명령어로 백업 파일을 저장할 경로와 저장 형식 등을 설정하고, 저장할 경로는 -D 명령어로, 호스트는 -h 명령어로 지정할 수 있습니다.

pg_basebackup -h 실행중인 서버의 이름 -D 베이스백업을 저장할 디렉토리

백업 파일 복구하기

1. 서버가 실행중이라면 서버를 중지합니다.
2. 기존에 있던 서버용 디렉토리의 데이터를 모두 지우거나 옮깁니다.
3. 백업 해놨던 파일을 데이터를 지웠던 원래의 디렉토리로 옮깁니다.
4. 데이터베이스 클러스터 디렉토리 안에 recovery.conf 파일 내용을 수정합니다. restore_command와 recovery_target_time을 설정합니다.
5. restore_command = 'copy "백업 디렉토리 경로 %f" "%p"' recovery_target_time = '원하는 날짜, 시간'
6. 서버를 재실행하여 복구되었는지 확인합니다. 제대로 복구 모드가 실행되었다면, 서버가 recovery.conf 파일을 recovery.done으로 바꿉니다.