Sean kim의 생활 이야기

업무 진행 중 공유가 필요하다 보니 정리하게 된 내용을 블로그에 포스팅.

PostgreSQL 버전별 ALTER TABLE 구문 수행시 Table re-write 발생하는 경우 정리

** 정리 **

• ALTER TABLE ADD COLUMN... ; + ALTER TABLE ALTER COLUMN DEFAULT ... ;
  • 해당 구문 수행의 경우 기존 데이터는 두고 새롭게 생성되는 데이터에만 DEFAULT 적용
• ALTER TABLE ADD COLUMN ... DEFAULT ... ;
  • 해당 구문 수행시에는 기존 존재하던 null 데이터들도 DEFAULT 데이터 적용

• ALTER TABLE ADD COLUMN ... DEFAULT 관련
  • 11 버전에서는 기존 데이터 DEFAULT 값 설정 시에 table rewrite 발생
  • 12 버전부터는 기존 데이터 DEFAULT 값 설정 시에 table rewrite 발생하지 않음.

• 컬럼 데이터타입 변경시에는 케이스 별로 확인해야 함
  • 일반적으로 같은 종류의 더 큰 타입으로 갈 때에는 발생하지 않음.(varchar → text)
  • 다른 타입으로 변경 시에는 발생(varchar → integer)
• UNLOGGED 관련 변경 시에도 발생(해당 테이블에 대한 WAL 변경분 기록하지 않는 테이블)
  • 해당 부분 관련하여 속도를 위해 unlogged 테이블로 작업하는 경우 알아둬야 함.
  • 결국 unlogged 관련한 부분도 변경 일어나는 시점에서 WAL에 기록되어야 하기 때문
• 클러스터 명령어 수행시(특정 인덱스 기준으로 clustring factor를 위한 테이블 재작성)
• VACUUM FULL 시에는 발생(해당 명령어는 테이블을 재작성 하는 명령어)

출처: https://www.thatguyfromdelhi.com/

2022년 7월 AWS에서 Aurora PostgreSQL 14 버전을 릴리즈 하였다.

커뮤니티 버전이나 RDS는 꽤 오래 전에 릴리즈 되었는데,  이번에 redshift 관련하여 이슈가 발견되었다.

PostgreSQL은 오래 전부터 사용자 암호를 md5 방식으로 해시 처리를 하였는데, 이 방식은 최근 몇년 동안 취약점이 발견되어 권장하지 않는 방식이 되었고, PostgreSQL 10버전에서부터는 scram-sha-256 방식을 지원하여 왔다. 

PostgreSQL 14버전으로 기존 aurora 클러스터를 업그레이드 시키고 별 문제없이 잘 쓰고 있었는데

구성이 변경되며 amazon redshift가 추가되면서 이슈가 발견되었다.

이슈는 redshift에서 external table 기능을 사용하려고 Aurora 엔드포인트에 붙는데 기존에 계정들은 접근이 되는데

새로 해당 서비스 용도로 암호를 변경한 계정만 접근이 되지 않는 것이었는데, 원인을 찾고 보니 변경된 암호 hash 방식이었다.

비슷한 경우를 겪는 사람들을 위해 아래에 내용을 찾아 정리하였다.

PostgreSQL 10버전부터 지원하기 시작한 scram-sha-256 방식의 비밀번호 hashing 기법

이와 관련되어 에러 메시지는 아래와 같이 발생한다.

특정 클라이언트로 PostgreSQL에 접근해야 할 때 해당 클라이언트가 scram-sha-256을 지원하지 않는다면, 이와 같이 해당 이슈인지에 대해 확인이 가능하다.

위의 방식을 거쳐 암호를 변경하면, 해당 유저의 암호는 md5방식으로 hash되어 저장되므로, scram-sha-256 방식을 지원하지 않는 클라이언트에서도 정상적인 접근이 가능할 것이다.

다만 md5 방식의 암호 hashing을 이용함으로써 존재하게 되는 취약점에 대한 별도의 대안이 없지 않는 이상, 가급적 클라이언트를 scram-sha-256을 지원하는 버전으로 업그레이드 하는 것을 고려하는 것이 바람직할 것이다.

출처들:

To scram-sha-256 from MD5 in PostgreSQL- CYBERTEC (cybertec-postgresql.com)

일전에 유투브에서 본 내용 중 이런 글을 소개하는 글을 본 적이 있다.
Is Aurora PostgreSQL really faster and cheaper than RDS PostgreSQL - Benchmarking - MigOps

 이 글의 내용은 결국 AWS가 자랑하는 Auroa가 정말 같은 가격이라고 가정하고 RDS PostgreSQL 대비 성능이 좋은지에 대한 부분이다. 위 글의 결론은 IOPS를 높인 PostgreSQL RDS 인스턴스가 가격대비 성능이 더 좋을 수 있다는 결론이다.
이런 이유는 결국 Aurora의 스토리지 성능에 관련된 내용인데, 이 내용은 아래에 조금 더 다룰 예정이다.
 
 
- Aurora의 경우, Storage IOPS를 별도로 조정할 수 있는 방법이 존재하지 않는다.
  이로 인해 이관이나 이행,성능 이슈 해결 등을 위해 스토리지 성능을 올려야 하는 경우, Aurora의 경우는 인스턴스 타입을 변경하여야 네트워크 대역폭이 올라 결과적으로 스토리지 IOPS를 올리게 될 수 있다
  이에 반해 RDS PostgreSQL는 스토리지 성능만 별도로 조정이 가능하다.
 
 
- Aurora의 경우 스토리지에서 발생한 IOPS에 요금이 발생한다.
   RDS PostgreSQL의 경우는 사용량에 따른 요금은 설정한 인스턴스 타입에 대한 비용, 스토리지 사용량에 대한 비용인데 비해 Aurora RDS의 경우 사용한 IOPS에 따라 요금이 별도로 발생하는 방식이다.
  이로 인해 AWS 관리팀이 예상하지 못한 금액이 나오는 경우가 발생할 수 있으니, 가급적 이 부분은 염두에 두고 선정을 하여야 할 것이다.
 
 
- 그 외에 PostgreSQL 마이너/메이저 버전 릴리즈 기간이나 이런 부분들이 RDS PostgreSQL에 좀 더 마음이 가게 하는 부분들이다. Aurora의 경우 버그가 발생하여도 AWS에서 별도 패치가 나오지 않으면 PostgreSQL 커뮤니티 버전에서 이미 패치가 나온 경우여도 해당 패치를 적용할 수 없다.
 
 
다만 이런 부분들에도 불구하고 Aurora가 매력적인 이유는 완전 관리형이라고 할 수 있는 기능들을 제공하기 때문이고, 아직 사용하지 못한 플랜관리 기능이나 빠르게 구성할 수 있는 DB클론 기능 등이 사용자 입장에서 편리하기 때문일 것이라고 생각한다.(Aurora 스토리지 엔진도 빼놓을 수 없는 부분이긴 하다)

PostgreSQL 가 의존성(dependency tracking) 추적을 제공함에도 불구하고 PostgreSQL 14 버전 이전에서는 function body에서의 의존성을 추적하지는 않았다.

즉, function body에서 정의된 부분에서는 다른 drop이 가능한 오브젝트를 참조하여도, 기존 오브젝트가 drop이 되는 것을 PostgreSQL에서 막지 않아 추후에 기존 오브젝트를 참조하는 function을 실행하려고 할 때 이미 drop된 오브젝트를 참조하여 오류가 발생하는 경우가 발생할 수 있다는 이야기다.

PostgreSQL 14 이전 버전에서는 PL/pgsql에서 BODY 파트는 일반 텍스트로 취급되었지만, PostgreSQL 14버전부터는 BEGIN ATOMIC이라고 명시함으로써 BODY 파트가 저장시에 파싱되어, 펑션 의존성을 검증할 수 있게 되었다.

[PostgreSQL: Re: Feature matrix updates]

PostgreSQL 14버전 이전: function body부분이 string으로 저장됨(function 실행될 때 검증), 의존성 체크 불가능.
PostgreSQL 14버전(ATOMIC 사용시): function 내용은 body부분이 저장시에 parsing되어 검증 후 parse tree로 저장됨,의존성 체크 가능

-- PostgreSQL 14이전에 사용되던 방식
CREATE FUNCTION subtraction(int, int)
RETURNS int
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
  ....
$$;

-- PostgreSQL 14에서 소개된 ATOMIC 방식
CREATE FUNCTION subtraction(int, int)
RETURNS int
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
BEGIN ATOMIC
  ....
END;

PostgreSQL 14 Release 내용

• Allow SQL-language functions and procedures to use SQL-standard function bodies (Peter Eisentraut)
• Previously only string-literal function bodies were supported. When writing a function or procedure in SQL-standard syntax, the body is parsed immediately and stored as a parse tree. This allows better tracking of function dependencies, and can have security benefits.

새로운 직장에서 AWS RDS 환경을 사용하며 재미있게 업무를 하고 있는 와중, ORACLE to PostgreSQL 이행을 위한 준비작업으로 암호화에 대한 내용을 정리하며 수행한 작업들을 정리하였다.
.
AWS RDS를 사용하며 느끼는 점은 될 것 같은건 찾아서 하면 거의 되는데 의외의 부분에서 안되는 부분이 튀어나오고, 사용한지 5달 가량 되었는데 커뮤니티 버전 PostgreSQL에서는 상상도 못한 버그가 툭 튀어나오고는 AWS에서 해결해주는 것만을 기다려야 할 수 있다는 점이다.(단기간에 해결할 것 같지 않지만..) 다만 SCALE OUT,CLONE 등의 기능들이 편하다는 부분과 시간을 많이 아낄 수 있다는 점은 큰 장점이다.
.
암호화 얘기로 돌아오면, 기존 ASIS 시스템은 현재는 사용하면 안전하다고 할 수 없는 AES128 방식의 암호화를 진행하였고 BASE64가 아닌 HEX 방식으로 값을 저장해두었다. 그래서 TOBE에서는 AES256 방식을 사용하며 암호화가 되는 방식도 변경하기로 결정하였다.
.
실제 찾아가는 과정은 며칠 걸렸지만, 결론만 말하자면 기존 ASIS 시스템이 ORACLE에서 제공하는 DBMS_CRYPTO 패키지의 함수들을 사용하였기 때문에 간단하게 RDS에서 제공하는 PG_CRYPTO 확장 프로그램으로 대체가 가능함을 확인하였다.(구성을 돌이켜 보면 ASIS 기준 보안적인 측면으로는 처참했지만.. 덕분에 편하게 작업했다.)
.
다만 생각치 못했던 부분은 기존 ASIS에서 정확하게 어떤 컬럼들이 암호화된 값을 담고 있는지에 대한 정보를 관리하고 있지 않았다는 점이었다.
.
MAX,LENGTH 등의 함수들을 이용하여 컬럼들의 목록을 취합하였는데,AWS DMS로 이관을 테스트하던 도중 추가적으로 암호화된 컬럼이 있다는 것을 발견하였다.
.
그 이후 하나하나 조사하는 것이 현실적으로 불가능함을 깨닳고(컬럼 개수 2만개 넘음) 최대한 빠르게 암호화컬럼을 찾을 방법을 간구하였고, 그에 필요하다고 생각하는 부분들을 완료된 시점에서 정리해두었다. 굳이 암호화 컬럼을 찾는 작업이 아니어도 응용할 부분이 있어 보인다.

.
.
.

본 내용을 설명하기 전에 그 기반이 되는 조건들에 대한 조사를 하였다.
1.ASIS 오라클 암호화가 된 후에 담기는 방식이 base64가 아닌 hex 방식이었다.
이 부분에 대해서는 암호화 컬럼을 1차로 찾아서 대상을 줄일때 사용하였다. 즉 hex 방식으로 데이터를 인코딩한다고 하면 0~F 까지의 문자로 이루어지면서 길이가 32의 배수일 수 있는 것이기 때문에 정규표현식으로 해당 컬럼의 데이터를 확인하면 1차적으로 암호화된 값이 담긴 컬럼을 걸러낼 수 있다는 이야기이다.<[0-9a-fA-F]{32}>
.
2.암호화된 값이 담기는 컬럼의 데이터 타입은 문자 기반이어야 한다.
PostgreSQL 기준 데이터 타입이 TEXT인 컬럼이나 varchar(32) 이상인 컬럼인 경우 이 기준을 만족한다.
.
3.이렇게 해서 선별된 컬럼의 리스트에 ASIS의 복호화 함수를 PGCRYPTO 확장 프로그램의 함수를 사용하여 동일하게 구현한 복호화 함수를 만드는데, 이 복호화 함수에는 기본적으로 암호화 알고리즘, IV값, 암호화에 사용되었던 키 등이 필요하다. 그리고 이 복호화 함수를 호출하기 전에 기존 암호화 함수에서 어떤 방식의 인코딩을 사용하여 암호화된 값을 담고 있는지를 알아야 DECODE 함수 등을 통해 해당 암호화된 값을 복호화 함수를 호출할 때 암호화된 값을 디코딩해주는 작업이 필요하다. 그 이후에도 다시 CONVERT_FROM 함수를 사용하여 다시 UTF-8 등의 읽을 수 있는 문자로 변환하는 작업이 요구된다. 예시는 다음과 같다.
.

ENCODE(ENCRYPT(SOCIAL_SECURITY_NUMBER::bytea, 'ENCRYPTKEYWORD'::bytea, 'aes-cbc/pad:pkcs'::text), 'hex')

.
물론 이 부분을 별도의 함수로 만들었고, 해당 함수는 암호화 컬럼 파라미터 부분에 암호화되지 않은 값이 들어오면 받은 값을 그냥 리턴하도록 작성하여, 기존의 데이터가 암호화가 제대로 되어있는 데이터와 되어있지 않은 데이터가 한 컬럼에 있더라도, 복호화된 값만 추후에 만들 컬럼에는 담기도록 하였다.
.
.
.
1.PostgreSQL에서 모든 컬럼에 대한 정보는 INFORMATION_SCHEMA.COLUMNS 뷰에 담겨 있다.
.

SELECT * FROM
  INFORMATION_SCHEMA.COLUMNS
  WHERE 1=1
    AND DATA_TYPE IN ('TEXT','CHARACTER VARYING')
    AND CHARACTER_MAXIMUM_LENGTH >= 32
    AND TABLE_SCHEMA = 'etisiare';

.
.
2.PostgreSQL에서 특정 테이블의 랜덤한 ROW를 읽어오기 위해서 TABLESAMPLE + SYSTEM() 구문을 사용하였다.

.

• 처음에는 SYSTEM() 구문과 limit절을 사용하였지만 나중에는 SYSTEM_ROWS(100) 구문으로 대체하였다
  (tsm_system_rows 확장 프로그램(extension) 설치 필요)
• SYSTEM() 기반으로 만든 익스텐션이라고 하며 더 랜덤한 값을 빠르게 리턴하게 해준다(1~10ms)
  출처: [Getting random rows faster. Very much faster. – /techblog (redpill-linpro.com)]
  .
  해당 테이블의 랜덤한 블록(페이지)을 시작점으로 100줄을 가져옴.
  SELECT ... from ... TABLESAMPLE SYSTEM_ROWS(100);
  .
  .

3.해당 TABLESAMPLE + SYSTEM_ROWS() 구문을 사용하면 가져오는 ROW들 중 정규표현식 만족 여부 확인을 위해 인라인뷰를 활용하였다.

• REGEXP_MATCH() 함수는 두번째 파라미터에서 정의한 정규표현식을 만족하면 파라미터로 받은 값을 리턴하고 그렇지 않은 경우 NULL을 리턴한다.
  .

  SELECT a.cc FROM (
  SELECT 
    (regexp_match(column_name,'[0-9a-fA-F]{32}'))[1] AS cc
  FROM table_name
    TABLESAMPLE SYSTEM_ROWS(100) a 
    WHERE a.cc IS NOT NULL 

  .
  .

4.이렇게 한 값들을 FOR 문으로 먼저 언급한 복호화 함수를 수행하여 값이 복호화 함수 수행 전 값과 다른 경우 해당 컬럼과 테이블명을 기록하도록 하였다. 이를 위해 테이블을 하나 생성하였으며, FOR문에서 1000번씩 반복 수행하게 하여 최대한 확률을 높이는 방식으로 작업하였다.
.
5.이런 일련의 작업들에 대한 결과로 기존에 조사하였던 컬럼을 모두 포함하고 대략 30개 가량의 추가적인 암호화 컬럼을 찾을 수 있었다.

• TABLESAMPLE 구문을 적극 활용한 결과 1TB가 넘고 2만개가 넘는 컬럼을 모두 검사하는 함수를 호출하는데 50분 가량의 시간이 걸렸다.

이렇게 수동으로 찾아야 했던 이유는 다음과 같다.
.
1.ASIS 암호화 컬럼에 암호화되지 않은 데이터와 암호화된 데이터가 혼재되어 있다는 점
2.과거 기존 운영 인력들이 생성한 방식을 파악하지 못하는 암호화 데이터 존재(암호화된 값이지만 ASIS 복호화 함수로 복호화가 불가능)
3.추후에 이행 시점에서 암호화 컬럼이 현재와 변경될 여지가 남아 있을 수 있는 부분이 존재
4.아무도 어떤 컬럼이 암호화 컬럼인지에 대한 신뢰할 수 있는 목록을 관리하지 않고 있다는 점