Oracle & PostgreSQL, STMP 관련 가이드

Oracle vs PostgreSQL 가이드

시스템 운영 환경에서 데이터베이스가 직접 이메일을 발송해야 하는 요구사항은 꾸준히 존재합니다. 배치 작업 결과의 즉각적인 보고나 치명적인 데이터 오류 발생 시 관리자 알림을 위해, WAS(Web Application Server)를 거치지 않고 DB 레벨에서 SMTP 프로토콜을 직접 제어하는 것이 효율적일 때가 많습니다.

 

본 문서에서는 엔터프라이즈의 표준인 Oracle의 UTL_SMTP 방식과 오픈소스의 강자 PostgreSQL의 PL/Python 구현 방식을 비교 분석하고, 실무 적용 시 반드시 마주하게 되는 보안 설정 및 오류 해결 방법까지 상세히 기술합니다.

1. Oracle : UTL_SMTP 패키지 활용 및 ACL 보안

Technical Context

Oracle 11g R2 버전 이후부터는 데이터베이스의 보안 강화 정책에 따라, 외부 네트워크(TCP/IP)로 나가는 패킷을 엄격히 통제합니다. 따라서 SMTP 서버와 통신하기 위해서는 반드시 ACL(Access Control List)을 생성하여 특정 유저에게 Resolve 및 Connect 권한을 부여해야 합니다.

Step 1. ACL(Access Control List) 권한 부여 (SYSDBA)

가장 먼저 수행해야 할 작업은 네트워크 통신을 위한 화이트리스트 등록입니다. 이 작업은 SYS 계정에서 수행해야 합니다.

oracle_acl_setup.sql

BEGIN
    -- 1. ACL 파일 생성 및 권한 정의
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          => 'odinbox_smtp_acl.xml',
        description  => 'OdinBOX SMTP Access',
        principal    => 'ODIN_USER', -- 권한을 부여할 DB 스키마(유저)명
        is_grant     => TRUE,
        privilege    => 'connect'
    );
    
    -- 2. 도메인 해석을 위한 Resolve 권한 추가
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
        acl          => 'odinbox_smtp_acl.xml',
        principal    => 'ODIN_USER',
        is_grant     => TRUE,
        privilege    => 'resolve'
    );

    -- 3. 타겟 호스트 및 포트 매핑 (Gmail 예시)
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
        acl          => 'odinbox_smtp_acl.xml',
        host         => 'smtp.gmail.com',
        lower_port   => 587,
        upper_port   => 587
    );
    COMMIT;
END;
/

Step 2. 메일 발송 프로시저 구현

ACL 설정이 완료되면, UTL_SMTP 패키지를 사용하여 실제 발송 로직을 구현합니다. 한글 깨짐 방지를 위한 헤더 설정과 Base64 인증 처리가 핵심입니다.

oracle_mail_proc.sql

CREATE OR REPLACE PROCEDURE sp_send_mail_odinbox (
    p_recipient IN VARCHAR2,
    p_subject   IN VARCHAR2,
    p_body      IN VARCHAR2
) IS
    -- [OdinBOX] 변수 선언
    v_conn      UTL_SMTP.CONNECTION;
    v_host      VARCHAR2(50) := 'smtp.gmail.com';
    v_port      NUMBER       := 587; 
    v_user      VARCHAR2(50) := 'helpodinbox@gmail.com'; 
    v_pass      VARCHAR2(50) := 'app_password_key'; -- 앱 비밀번호 사용
BEGIN
    -- 1. TCP 연결 및 Handshake
    v_conn := UTL_SMTP.OPEN_CONNECTION(v_host, v_port);
    UTL_SMTP.HELO(v_conn, v_host);
    UTL_SMTP.STARTTLS(v_conn); -- TLS 보안 세션 시작

    -- 2. SMTP 인증 (AUTH LOGIN 방식 - Base64 인코딩 필수)
    UTL_SMTP.COMMAND(v_conn, 'AUTH LOGIN');
    UTL_SMTP.COMMAND(v_conn, 
        UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(v_user))));
    UTL_SMTP.COMMAND(v_conn, 
        UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(v_pass))));

    -- 3. 데이터 전송 섹션
    UTL_SMTP.MAIL(v_conn, v_user);
    UTL_SMTP.RCPT(v_conn, p_recipient);
    UTL_SMTP.OPEN_DATA(v_conn);
    
    -- [OdinBOX] MIME 헤더 구성 (Subject 한글 처리 중요)
    UTL_SMTP.WRITE_DATA(v_conn, 'Subject: =?UTF-8?B?' || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(p_subject))) || '?=' || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(v_conn, 'To: ' || p_recipient || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(v_conn, 'Content-Type: text/plain; charset=UTF-8' || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(v_conn, UTL_TCP.CRLF || p_body);
    
    UTL_SMTP.CLOSE_DATA(v_conn);
    UTL_SMTP.QUIT(v_conn);
EXCEPTION
    WHEN OTHERS THEN
        UTL_SMTP.QUIT(v_conn);
        RAISE;
END;
/

Troubleshooting : Oracle Critical Errors

ORA-24247 네트워크 접근 권한 거부 (Network Access Denied)
ACL 설정이 누락되었거나, DBMS_NETWORK_ACL_ADMIN을 통해 부여된 권한이 현재 접속한 유저와 매핑되지 않았을 때 발생합니다. Step 1의 ACL 스크립트를 재점검하십시오.

ORA-29024 인증서 유효성 검사 실패 (Certificate Validation Failure)
Oracle Wallet에 메일 서버(Gmail 등)의 Root CA 인증서가 등록되지 않았을 때 발생합니다. UTL_SMTP는 기본적으로 SSL/TLS 핸드쉐이크 시 인증서를 검증합니다. 이를 해결하려면 Wallet을 구성하거나, 내부망에 인증서 검증이 없는 SMTP Relay 서버를 구축하여 경유해야 합니다.

2. PostgreSQL : PL/Python 확장과 유연성

Technical Context

PostgreSQL은 모놀리식 구조를 지양하고 Extension(확장) 생태계를 권장합니다. plpython3u(Python 3 Untrusted Language) 확장을 설치하면, DB 내부에서 Python의 표준 라이브러리인 smtplib을 직접 호출할 수 있습니다. 이를 통해 복잡한 인코딩 로직 없이 매우 간결한 코드로 메일 발송이 가능합니다.

Step 1. PL/Python 확장 설치

'Untrusted' 언어는 파일 시스템 접근 등 OS 레벨의 제어가 가능하므로, 오직 Superuser(관리자) 계정만이 해당 확장을 활성화하고 함수를 생성할 수 있습니다.

postgres_ext_setup.sql

-- 사전 조건: DB 서버 OS에 postgresql-contrib 및 python3 패키지가 설치되어 있어야 함
CREATE EXTENSION IF NOT EXISTS plpython3u;

Step 2. Python smtplib 기반 함수 작성

postgres_mail_func.sql

CREATE OR REPLACE FUNCTION fn_send_mail_odinbox (
    smtp_to text,
    smtp_subject text,
    smtp_content text
)
RETURNS text
AS $$
    import smtplib
    from email.mime.text import MIMEText
    from email.mime.multipart import MIMEMultipart

    # [OdinBOX] SMTP 설정
    smtp_host = "smtp.gmail.com"
    smtp_port = 587
    smtp_user = "helpodinbox@gmail.com"
    smtp_pass = "app_password_key"

    # 메시지 컨테이너 생성
    msg = MIMEMultipart()
    msg['From']    = smtp_user
    msg['To']      = smtp_to
    msg['Subject'] = smtp_subject

    # 본문 내용 UTF-8 인코딩 명시
    msg.attach(MIMEText(smtp_content, 'plain', 'utf-8'))

    try:
        # 서버 연결 및 TLS 승격
        server = smtplib.SMTP(smtp_host, smtp_port)
        server.ehlo()
        server.starttls() # 보안 연결 시작
        server.ehlo()
        
        # 로그인 및 발송
        server.login(smtp_user, smtp_pass)
        server.sendmail(smtp_user, smtp_to, msg.as_string())
        server.quit()
        return "Success: Email Sent via OdinBOX"
    except Exception as e:
        # 상세 에러 메시지 반환
        return f"Error: {str(e)}"
$$ LANGUAGE plpython3u;

Troubleshooting : PostgreSQL Critical Errors

ImportError 모듈 미설치 오류 (No module named...)
PostgreSQL의 PL/Python은 DB 서버 OS의 Python 환경을 공유합니다. 만약 라이브러리가 없다는 에러가 발생하면, DB 서버의 쉘(Shell)에서 pip3 install [모듈명] 명령어를 통해 필요한 패키지를 설치해야 합니다.

SMTPAuthenticationError 인증 실패 (Authentication Failed)
Google 등 최신 메일 서비스는 보안 수준이 낮은 앱에서의 직접 로그인을 차단합니다. 계정의 일반 비밀번호를 사용하면 이 에러가 발생합니다. 반드시 해당 메일 서비스 설정에서 [2단계 인증]을 활성화한 후, [앱 비밀번호(App Password)]를 별도로 생성하여 코드 내 smtp_pass 변수에 적용해야 합니다.

Permission Denied 권한 부족 (Superuser Required)
plpython3u 언어로 작성된 함수를 생성하거나 수정할 때는 반드시 Superuser 권한이 필요합니다. 일반 유저가 사용해야 한다면, 관리자가 함수를 생성한 뒤 GRANT EXECUTE ON FUNCTION... 명령어로 실행 권한만 부여하는 방식을 사용하십시오.

3. 마무리 (Conclusion)

Oracle과 PostgreSQL은 메일 발송이라는 동일한 목적을 달성하기 위해 서로 다른 접근 방식을 취합니다. Oracle은 UTL_SMTP와 ACL 설정을 통해 DB 자체의 완결성 있는 기능을 제공하지만, SSL 인증서(Wallet) 관리와 복잡한 인코딩 처리가 진입 장벽이 될 수 있습니다.

 

반면, PostgreSQL은 Python 생태계를 적극 활용하여 구현 난이도를 대폭 낮추고 코드의 가독성을 높였습니다. 다만, OS 레벨의 패키지 의존성과 Superuser 권한 관리가 필수적이라는 점을 고려해야 합니다. 운영 중인 시스템의 보안 정책과 인프라 환경을 고려하여 가장 적합한 방식을 선택하시기 바랍니다.