PostgreSQL: password 안전하게 사용하기 # PGPASSWORD # .pgpass # .env

Python에서 프레임워크가 아닌 Script를 통해 sql문을 사용하는 상황에서

create table sql을 작성할 때 DB 접근을 위해 PASSWORD가 필요하다.

 

.env에 기록을 해두고 load_dotenv로 사용할 수 있는데,

psql command에 .env에서 불러온 password를 넣는 방식은 보안에 취약할 수 있다.

비밀번호 등이 명령어 히스토리에 남을 수 있기 때문이다.

 

예제코드를 통해 살펴보자

import subprocess
import os
from pathlib import Path
from dotenv import load_dotenv


load_dotenv()

def create_table():
    db_user = os.getenv('DB_USER')
    db_name = os.getenv('DB_NAME')
    db_password = os.getenv('DB_PASSWORD')
    db_host = os.getenv('DB_HOST')
    db_port = os.getenv('DB_PORT')
    
    # Get the path to the SQL file relative to this script
    current_dir = Path(__file__).parent
    sql_file = current_dir / 'sql' / 'create_table.sql'

    # Set PGPASSWORD environment variable for the subprocess
    env = os.environ.copy()
    env['PGPASSWORD'] = db_password

    command = f'psql -h {db_host} -U {db_user} -d {db_name} -p {db_port} -f "{sql_file}"'

    try:
        subprocess.run(command, shell=True, check=True, env=env)
        print("Table created successfully.")
    except subprocess.CalledProcessError as e:
        print(f"An error occurred: {e}")

if __name__ == "__main__":
    create_table()

 

command에 아래와 같이 비밀번호를 직접 입력하는 것은 명령어 히스토리에 기록되므로 보안에 취약하다.

psql -h ... -U user -p 5432 -W 'password'

 

이를 대체하기 위한 위 코드의 로직을 설명하면 다음과 같다.

 

1. PGPASSWORD 환경 변수의 역할

PostgreSQL 클라이언트인 psql은 데이터베이스 연결 시 사용자 인증을 처리해야 합니다. 인증 방법 중 하나는 비밀번호 기반 인증으로, 이 비밀번호를 전달하는 방법 중 하나가 PGPASSWORD 환경 변수를 설정하는 것입니다.

• PGPASSWORD 환경 변수에 비밀번호를 설정하면, psql 실행 시 이 변수를 읽어 비밀번호를 전달합니다.
• 즉, 명령어 내부에서 비밀번호를 입력할 필요 없이, 클라이언트는 이 변수를 사용하여 자동으로 인증을 시도합니다.

 

2. os.environ.copy() 및 환경 변수 설정

env = os.environ.copy()
env['PGPASSWORD'] = db_password

• os.environ.copy()는 현재 프로세스의 환경 변수를 복사한 딕셔너리를 반환합니다.
• 복사본에 PGPASSWORD 키를 추가하여 비밀번호 값을 설정합니다.
• 이 환경 변수는 이후 subprocess.run으로 실행되는 psql 명령어에 전달됩니다.

 

3. psql 명령어 실행 및 PGPASSWORD 사용

command = f'psql -h {db_host} -U {db_user} -d {db_name} -p {db_port} -f "{sql_file}"'
subprocess.run(command, shell=True, check=True, env=env)

 

• 명령어 구성:
  • -h {db_host}: 데이터베이스 호스트 주소.
  • -U {db_user}: 데이터베이스 사용자명.
  • -d {db_name}: 데이터베이스 이름.
  • -p {db_port}: 데이터베이스 포트.
  • -f "{sql_file}": 실행할 SQL 파일 경로.
• env=env 전달:
  • subprocess.run은 env 매개변수를 통해 실행될 프로세스에 환경 변수를 전달합니다.
  • 여기서 env는 이전에 설정한 PGPASSWORD 값을 포함하고 있으므로, psql은 이 변수를 읽어 비밀번호를 자동으로 처리합니다.

 

4. psql의 동작 원리

1. 환경 변수 확인:
   • psql 실행 시, 먼저 인증 정보를 확인합니다.
   • 이때, PGPASSWORD 환경 변수가 설정되어 있다면 이를 비밀번호로 사용합니다.
2. PostgreSQL 서버와 통신:
   • psql은 서버에 접속한 후 사용자가 입력한 비밀번호(PGPASSWORD 값)를 전달해 인증을 시도합니다.
3. 비밀번호 인증 성공 시 연결:
   • 인증에 성공하면 지정된 SQL 파일을 실행합니다.

 

5. 왜 PGPASSWORD가 유효한가?

PostgreSQL 클라이언트는 비밀번호 인증을 위해 다음 순서로 비밀번호를 찾습니다:

1. 환경 변수 PGPASSWORD: 가장 먼저 확인합니다.
2. .pgpass 파일: 사용자의 홈 디렉토리에 있는 .pgpass 파일에 저장된 비밀번호를 확인합니다.
3. 수동 입력: 위 옵션을 제공하지 않으면 실행 시 프롬프트에서 비밀번호 입력을 요구합니다.

PGPASSWORD가 설정된 경우에는 프롬프트를 생략하고 이를 자동으로 사용하기 때문에 명령 실행이 편리해집니다.

---

6. 보안 고려 사항

PGPASSWORD는 일시적으로만 설정되며, 명령 실행 후 자동으로 소멸하므로 보안성이 비교적 높습니다. 하지만 환경 변수를 통해 비밀번호를 설정하는 것은 여전히 일부 보안 리스크를 가질 수 있습니다:

• 시스템 프로세스 목록에서 잠깐 동안 노출될 가능성.
• 환경 변수 설정이 코드나 로그에서 유출될 가능성.

더 안전한 방법:

• .pgpass 파일 사용: ~/.pgpass에 호스트, 포트, 데이터베이스, 사용자, 비밀번호를 설정.
• .pgpass는 파일 권한을 제한(예: chmod 600)하여 비밀번호를 보호합니다.