[postgresql] 양방향 암호화를 위한 암호화 키 생성 및 암호화 수행

Postgresql 암호화 #1

 

PostgreSQL에서 외부에 저장되어 있는 암호화 키를 사용하여 양방향 암호화를 구현하려면, 암호화 키를 불러와서 쿼리에서 사용해야 합니다. 
이를 위해서는 암호화 키를 어떤 형태로 저장할지 정하고, PostgreSQL에서 이를 불러오는 방법을 구현해야 합니다.

다음은 PostgreSQL에서 파일로부터 암호화 키를 불러와서 사용하는 방법입니다.

1. OpenSSL 라이브러리를 사용하여 암호화 키를 생성

OpenSSL은 암호화와 해시, 그리고 기타 보안 관련 작업을 수행할 수 있는 라이브러리입니다.
OpenSSL을 사용하여 암호화 키를 생성하는 방법은 OpenSSL의 명령어 중 하나인 openssl rand를 사용하여 무작위 바이트 값을 생성하는 방법입니다.

## Linux
bash
Copy code
$ openssl rand -base64 32

## Windows
PS C:\Windows\system32> openssl rand -base64 32 > C:\test\mykeyfile.key

위 명령어는 32바이트 길이의 무작위 바이트 값을 생성하고, 이 값을 암호화 키로 사용할 수 있습니다.
이러한 방법을 사용하여 PostgreSQL에서 사용할 수 있는 암호화 키를 생성할 수 있습니다. 암호화 키의 보안성을 유지하기 위해서는, 생성된 암호화 키를 안전한 장소에 보관하고, 암호화 키를 사용하는 애플리케이션 또는 사용자에게만 알려주어야 합니다.

2. pg_read_binary_file 수행 권한 부여

사용하려는 DB에 postgres(Superuser) 계정으로 암호화를 사용하고자 하는 DB에 접속하여 pg_read_binary_file 함수 수행 권한을 필요한 계정에 부여

GRANT EXECUTE ON FUNCTION pg_catalog.pg_read_binary_file(text) TO testuser;

pg_read_binary_file 함수는 PGDATA 경로 하단에 위치한 파일의 상대경로를 읽을 수 있음

3. 암호화 키파일을 PGDATA 디렉토리 하단에 위치시키고, 암호화 키를 불러오는 함수를 작성

이 함수는 파일에서 암호화 키를 읽어와서 반환합니다. 예를 들어, 다음과 같이 작성할 수 있습니다.

CREATE OR REPLACE FUNCTION testuser.get_encryption_key()
RETURNS bytea AS $$
DECLARE
    encryption_key bytea;
BEGIN
    SELECT DECODE(replace(cast(pg_read_binary_file('postgresql_enc\mykeyfile.key') AS TEXT),'\x',''), 'hex') INTO encryption_key;
    -- 실제 Key 위치는 $PGDATA/postgresql_enc\mykeyfile.key
    RETURN encryption_key;
END;
$$ LANGUAGE plpgsql;

위의 함수는 pg_read_binary_file() 함수를 사용하여 파일에서 암호화 키를 읽어옵니다. 이때, 
암호화 키는 16진수 문자열로 저장되어 있으므로 decode() 함수를 사용하여 바이트 형식으로 변환합니다.

암호화 키를 사용하여 데이터를 암호화하거나 복호화합니다. 이때, get_encryption_key() 함수를 사용하여 암호화 키를 불러옵니다.

4. 암호화 수행

-- 암호화
SELECT encode(encrypt('테스트를 위한 텍스트', get_encryption_key(), 'aes'), 'hex'); 
-- 테이블에 있는 데이터를 읽어와서 암호화를 하려면 convert_to('','utf8') 함수를 써야 함
select encode(encrypt(convert_to('email','utf8'),testuser.get_encryption_key(),'aes'),'hex') as email from emp;
-- 복호화
SELECT convert_from(decrypt(DECODE(email,'hex'),testuser.get_encryption_key(),'aes'),'utf8') from emp;