[PostgreSql] pg_encrypto 데이터 암복호화 설정(feat. JPA)

3줄 요약

PostgreSql 데이터의 암복화 기능이 필요
PostgreSql의 pgcrypto extension으로 가능
JPA 엔티티에 암복호화 설정도 가능

 

 

들어가기 앞서

본 글은 pg_crypto 모듈 기반 PostgreSql 설정 및 JPA 설정을 정리하기 위한 목적으로 작성되었다. 

 

간단한 회원가입과 이름 조회 서비스 코드를 작성했고, 간단하게 통합테스트 코드를 통해 작동 방식을 확인했다.

(테스트 환경은 테스트 컨테이너를 통해 postgreSql 14 이미지로 컨테이너를 생성하고, flyway를 통해 데이터 스키마를 정의했다.)

 

실제 샘플 코드를 실행해보자 한다면 먼저 도커를 설치해 사용하기를 권장한다.

또한, 샘플 코드와 암호화 키 등은 이해를 돕기 위해 임의로 만든 것이니 실 사용 시 주의가 필요하다.

 

실제 프로젝트를 보고 싶다면 해당 레포에서 확인할 수 있다.

 

환경설정

1) build.gradle 설정

plugins {
    id 'java'
    id 'org.springframework.boot' version '3.4.5'
    id 'io.spring.dependency-management' version '1.1.7'
    id 'org.asciidoctor.jvm.convert' version '3.3.2'
}

group = 'com'
version = '0.0.1-SNAPSHOT'

java {
    toolchain {
        languageVersion = JavaLanguageVersion.of(17)
    }
}

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.springframework.boot:spring-boot-starter-validation'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.flywaydb:flyway-core'
    implementation 'org.flywaydb:flyway-database-postgresql'
    
    testImplementation 'org.testcontainers:junit-jupiter'
    testImplementation 'org.springframework.boot:spring-boot-testcontainers'
    testImplementation 'org.testcontainers:postgresql'
    
    compileOnly 'org.projectlombok:lombok'
    developmentOnly 'org.springframework.boot:spring-boot-docker-compose'
    runtimeOnly 'org.postgresql:postgresql'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

dependencyManagement {
    imports {
        mavenBom "org.testcontainers:testcontainers-bom:${testcontainersVersion}"
    }
}

tasks.named('test') {
    outputs.dir snippetsDir
    useJUnitPlatform()
}

 

2) flyway 문서(V1__create_members_table.sql

-- Create pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Create members table
DROP TABLE IF EXISTS members;

CREATE TABLE members (
    id BIGSERIAL PRIMARY KEY,
    name BYTEA NOT NULL,
    email BYTEA NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone_number BYTEA NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

comment on table members is '회원';
comment on column members.id is '회원 ID';
comment on column members.name is '회원명';
comment on column members.email is '이메일';
comment on column members.password is '비밀번호';
comment on column members.phone_number is '전화번호';
comment on column members.created_at is '생성일시';
comment on column members.updated_at is '수정일시';

 

 

 

pg_crypto 및 JPA 엔티티 설정

1) PostgreSql에 pgcrypto을 설치한다.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

 

2) 암호화할 테이블 칼럼 타입은 BYTEA로 한다.

CREATE TABLE members (
    id BIGSERIAL PRIMARY KEY,
    name BYTEA NOT NULL,
    email BYTEA NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone_number BYTEA NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

comment on table members is '회원';
comment on column members.id is '회원 ID';
comment on column members.name is '회원명';
comment on column members.email is '이메일';
comment on column members.password is '비밀번호';
comment on column members.phone_number is '전화번호';
comment on column members.created_at is '생성일시';
comment on column members.updated_at is '수정일시';

 

3) application.yml 설정

spring.datasource.hikari.connection-init.sql:

• 애플리케이션이 뜨면서 HikariCP가 최소 유휴 커넥션 수(minimumIdle) 만큼 새 JDBC 커넥션을 “물리적으로” 연다.
• 각 커넥션을 열자마자 바로 다음 순서로 SQL을 실행
  • 커넥션 열기 → select set_config('encryption.key', '${security-properties.encryptionSecretKey}', false) 실행 → 커넥션 유효성 검사 → 풀에 보관
• 빌려 쓰는 순간에는 connection-init-sql이 다시 실행되지 않는다.(한 커넥션당 딱 한 번)

security-properties.encryptionSecretKey:

• 임의의 암호화 키를 설정, 명명은 각자 커스텀해서 사용해도 무관

 

 

application.yml(예시)

spring:
  datasource:
    driver-class-name: org.testcontainers.jdbc.ContainerDatabaseDriver
    url: jdbc:tc:postgresql:14:///test
    hikari:
      connection-init-sql: select set_config('encryption.key', '${security-properties.encryptionSecretKey}', false)

  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        dialect: org.hibernate.dialect.PostgreSQLDialect

  flyway:
    enabled: true
    baseline-on-migrate: true
    locations: classpath:db/migration

security-properties:
  encryptionSecretKey: test-encryption-secret-key

 

 

JPA 엔티티 설정

• ColumnTransformer(read = "...", write = "..."): DB 데이터를 READ, WRITE 시 정의한 쿼리를 수행

options	description
read	DB → 엔티티 값을 읽어 올 때 적용할 SQL 식
write	엔티티 → DB 값을 저장·수정할 때 적용할 SQL 식 (?가 필드 값 자리)
forColumn	(선택) 테이블에 같은 필드명이 여러 컬럼으로 맵핑될 때 어떤 컬럼에 적용할지 지정

 

 

1) Members(Entity) 설정

package com.sandbox.member;

import com.sandbox.BaseEntity;
import jakarta.persistence.*;
import jakarta.validation.constraints.NotNull;
import lombok.AccessLevel;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.ColumnTransformer;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.nio.charset.StandardCharsets;

@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@Entity
public class Members extends BaseEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ColumnTransformer(
        read = "pgp_sym_decrypt(" + "name, " + "current_setting('encryption.key')" + ")",
        write = "pgp_sym_encrypt(" + "?::text," + "current_setting('encryption.key')" + ")")
    @NotNull
    @Column(name = "name", nullable = false, columnDefinition = "bytea")
    private String name;
    
    private String password;

    @ColumnTransformer(
        read = "pgp_sym_decrypt(" + "email, " + "current_setting('encryption.key')" + ")",
        write = "pgp_sym_encrypt(" + "?::text," + "current_setting('encryption.key')" + ")")
    @NotNull
    @Column(name = "email", nullable = false, columnDefinition = "bytea")
    private String email;

    @ColumnTransformer(
        read = "pgp_sym_decrypt(" + "phone_number, " + "current_setting('encryption.key')" + ")",
        write = "pgp_sym_encrypt(" + "?::text," + "current_setting('encryption.key')" + ")")
    @NotNull
    @Column(name = "phone_number", nullable = false, columnDefinition = "bytea")
    private String phoneNumber;

    @Builder
    private Members(String name, String email, String password, String phoneNumber) {
        if (name == null || name.isEmpty()) {
            throw new IllegalArgumentException("이름을 입력해주세요.");
        }
        this.name = name;

        if (email == null || email.isEmpty()) {
            throw new IllegalArgumentException("이메일을 입력해주세요.");
        }
        this.email = email;

        if (password == null || password.isEmpty()) {
            throw new IllegalArgumentException("비밀번호를 입력해주세요.");
        }
        this.password = hashPassword(password);

        if (phoneNumber == null || phoneNumber.isEmpty()) {
            throw new IllegalArgumentException("전화번호를 입력해주세요.");
        }
        this.phoneNumber = phoneNumber;
    }

    private String hashPassword(String password) {
        try {
            MessageDigest digest = MessageDigest.getInstance("SHA-256");
            byte[] encodedhash = digest.digest(password.getBytes(StandardCharsets.UTF_8));
            
            StringBuilder hexString = new StringBuilder();
            for (byte b : encodedhash) {
                String hex = Integer.toHexString(0xff & b);
                if (hex.length() == 1) hexString.append('0');
                hexString.append(hex);
            }
            return hexString.toString();
        } catch (NoSuchAlgorithmException e) {
            throw new RuntimeException("Hash algorithm not found", e);
        }
    }
}

 

 

 

테스트 코드

package com.sandbox.member;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.testcontainers.junit.jupiter.Testcontainers;

import static org.assertj.core.api.Assertions.*;
import static org.junit.jupiter.api.Assertions.*;

@Testcontainers
@ActiveProfiles("test")
@SpringBootTest
class MemberServiceTest {

    @Autowired
    private MemberRepository memberRepository;

    @Autowired
    private MemberService memberService;


    @AfterEach
    void tearDown() {
        memberRepository.deleteAllInBatch();
    }

    @DisplayName("회원가입을 하면 ID를 반환한다")
    @Test
    void register() {
        // given
        MemberRequestDto requestDto = new MemberRequestDto("name", "email", "password", "phoneNumber");

        // when
        Long savedMemberId = memberService.register(requestDto);

        // then
        assertAll(
            () -> assertThat(savedMemberId).isNotNull(),
            () -> assertThat(savedMemberId).isEqualTo(1L)
        );
    }

    @DisplayName("회원가입시 이미 동일한 이메일로 가입된 회원이 있다면 예외가 발생한다.")
    @Test
    void registerDuplicateEmail() {
        // given
        MemberRequestDto requestDto = new MemberRequestDto("name", "email", "password", "phoneNumber");
        memberRepository.save(requestDto.toEntity());

        // when, then
        Throwable throwable = assertThrows(IllegalArgumentException.class, () -> memberService.register(requestDto));
        assertThat(throwable.getMessage()).isEqualTo("이미 해당 이메일로 가입된 회원이 존재합니다.");
    }

    @DisplayName("이메일로 회원정보를 조회할 수 있다.")
    @Test
    void findByEmail() {
        // given
        String targetEmail = "email";
        MemberRequestDto requestDto = new MemberRequestDto("name", targetEmail, "password", "phoneNumber");
        memberService.register(requestDto);

        // when
        Members foundMember = memberService.findByEmail(targetEmail);

        // then
        assertAll(
            () -> assertThat(foundMember).isNotNull(),
            () -> assertThat(foundMember.getEmail()).isEqualTo(targetEmail)
        );
    }
}

 

 

쿼리 결과

Hibernate: 
    select
        m1_0.id 
    from
        members m1_0 
    where
        pgp_sym_decrypt(m1_0.email, current_setting('encryption.key'))=? 
    fetch
        first ? rows only
Hibernate: 
    insert 
    into
        members
        (created_at, email, name, password, phone_number, updated_at) 
    values
        (?, pgp_sym_encrypt(?::text, current_setting('encryption.key')), pgp_sym_encrypt(?::text, current_setting('encryption.key')), ?, pgp_sym_encrypt(?::text, current_setting('encryption.key')), ?)
Hibernate: 
    select
        m1_0.id,
        m1_0.created_at,
        pgp_sym_decrypt(m1_0.email, current_setting('encryption.key')),
        pgp_sym_decrypt(m1_0.name, current_setting('encryption.key')),
        m1_0.password,
        pgp_sym_decrypt(m1_0.phone_number, current_setting('encryption.key')),
        m1_0.updated_at 
    from
        members m1_0 
    where
        pgp_sym_decrypt(m1_0.email, current_setting('encryption.key'))=?

 

 

 

 

Reference

https://dev.to/cristiancfm/encrypting-postgresql-database-columns-in-spring-boot-jpa-entities-4915

Encrypting PostgreSQL database columns in Spring Boot JPA entities