Rocky Linux 9 PostgreSQL 18 설치와 계정 관리: 일반·컨테이너·앱 연결

MaruOps

2026.10.25

참고 사진 · 서버 저장 장치 교체용 하드디스크 두 개와 SSD 한 개 · 사진: Andy Melton / 원본 / CC BY-SA 2.0 · 목록용 크기 조정

PostgreSQL을 처음 설치할 때는 서버를 띄우는 일보다 “어떤 계정으로 어디에 접속하는가”에서 더 자주 막힙니다. 이 글은 새 Rocky Linux 9 서버에 PostgreSQL을 설치하고, 관리자와 앱 계정을 나눠 운영하려는 분을 위한 안내입니다.

먼저 질문의 답부터 짚을게요. 새 슈퍼유저를 만들어도 기존 postgres 계정이나 OS에서의 접속은 자동으로 차단되지 않습니다. psql -U postgres의 성공 여부는 현재 OS 사용자, 소켓·TCP 중 어떤 경로를 쓰는지, pg_hba.conf의 인증 규칙에 따라 달라집니다.

dba_admin은 설치 시 미리 만들어지는 시스템 계정이 아닙니다. 이 글에서 직접 생성할 관리자용 DB 역할의 예시 이름이에요. app_runtime도 뒤에서 만드는 일반 앱 계정입니다. Linux에 같은 이름의 사용자를 만들 필요는 없습니다.

설치가 끝났다면 슈퍼유저 만들기, 일반 앱 계정 만들기, 백업과 복구 순서로 진행하면 됩니다. 아래 명령은 OS 터미널에서 실행하는 Bash 명령과 psql 안에서 실행하는 SQL·역슬래시 명령을 구분해 표시합니다.

이 글의 목차

사용할 버전과 설치 방식

2026년 10월 25일 공식 릴리스 목록에서 확인한 최신 안정 버전은 PostgreSQL 18.6입니다. PostgreSQL 19 RC 1은 출시 후보이므로 이 글의 설치 대상으로 삼지 않습니다. RPM은 18 계열의 저장소 최신 패치를 설치하고, 컨테이너 예시는 재현하기 쉽도록 postgres:18.6을 지정합니다. 이후 설치할 때에는 공식 릴리스 목록에서 안정 버전을 다시 확인하세요.

구분	일반 설치: PGDG RPM	컨테이너: Docker Compose
실행·로그 관리	systemd·journalctl	docker compose
데이터 위치	기본 `/var/lib/pgsql/18/data`	명명 볼륨, 컨테이너의 `/var/lib/postgresql`에 연결
이 글의 호스트 포트	5432	15432 → 컨테이너 5432
처음 선택할 때	Linux 서비스 관리부터 익히고 싶을 때	앱과 DB를 Compose로 관리할 때

두 방법 중 하나를 선택하면 됩니다. 둘 다 실습한다면 포트와 데이터 저장소를 구분하세요. 기존 DB의 업그레이드 절차가 아니라 비어 있는 데이터 디렉터리에 새 DB 클러스터를 만드는 절차입니다. 여기서 클러스터는 한 PostgreSQL 서버가 관리하는 데이터베이스 묶음을 뜻합니다.

일반 설치: Rocky Linux 9에 PGDG 패키지 설치

Rocky 9 마이너 버전부터 확인

PGDG 공식 안내는 Rocky Linux를 지원하지만, 지원 대상 마이너 버전은 바뀝니다. 오래된 9.x에서 저장소가 404를 반환하면 GPG 검증을 끄거나 임의의 저장소로 바꾸기 전에 Rocky 버전부터 확인합니다. 이 글의 RPM 검증 환경은 Rocky Linux 9.8 x86_64입니다.

다음 명령은 새 서버에서 실행합니다. 기존 운영 서버의 전체 패키지 업데이트는 서비스 영향과 재부팅 일정을 먼저 확인하세요.

cat /etc/rocky-release
uname -m
sudo dnf upgrade --refresh -y

커널 등 재부팅이 필요한 패키지가 갱신되었다면 재부팅한 뒤 진행합니다. 아래 저장소 주소는 uname -m 결과로 아키텍처를 선택합니다. 해당 아키텍처·마이너 버전의 지원 여부는 공식 다운로드 선택기와 대조합니다.

저장소 추가와 초기화

sudo dnf install -y "https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-$(uname -m)/pgdg-redhat-repo-latest.noarch.rpm"
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql18 postgresql18-server

sudo env PGSETUP_INITDB_OPTIONS='--encoding=UTF8 --locale=C.UTF-8' /usr/pgsql-18/bin/postgresql-18-setup initdb
sudo systemctl enable --now postgresql-18
sudo systemctl status postgresql-18 --no-pager

배포판 기본 PostgreSQL 모듈과 PGDG 패키지가 섞이지 않도록 모듈을 비활성화합니다. 초기화 옵션은 한글 데이터를 위한 UTF-8과 C.UTF-8 로캘을 명시합니다. 정렬 규칙이 중요한 앱은 초기화 전에 필요한 로캘·콜레이션도 검토합니다.

initdb는 새 데이터 영역을 초기화하는 작업으로, 기존 데이터에 반복 실행하는 명령이 아닙니다. 데이터가 이미 있다는 메시지가 나오면 디렉터리를 지우지 말고 기존 설치 여부부터 확인합니다.

관리자 접속은 다음과 같습니다. 이후 일반 설치 예시에서는 버전이 다른 psql이 실행되지 않도록 전체 경로를 사용합니다.

sudo -u postgres /usr/pgsql-18/bin/psql -X -d postgres

postgres OS 사용자로 실행한 뒤 같은 이름의 DB 역할로 접속합니다. 설치 직후의 로컬 인증 방식은 직접 확인해야 합니다. PGDG 패키지 안내와 설치된 서비스 파일의 데이터 경로가 기준입니다.

컨테이너 설치: Docker Compose로 PostgreSQL 18 실행

Docker 준비와 작업 디렉터리

이미 Docker Engine과 Compose 플러그인이 있다면 버전 확인부터 하면 됩니다. 새 Rocky 서버는 Rocky Linux의 Docker 설치 문서와 Docker RPM 설치 문서를 참고해 다음과 같이 설치할 수 있습니다. 기존 Podman·Docker 호환 패키지와 충돌하면 삭제 명령을 일괄 실행하지 말고 사용 중인 컨테이너부터 확인합니다.

sudo dnf install -y dnf-plugins-core
sudo dnf config-manager --add-repo https://download.docker.com/linux/rhel/docker-ce.repo
sudo dnf install -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin
sudo systemctl enable --now docker
sudo docker version
sudo docker compose version

아래 명령은 작업 디렉터리와 초기 관리자 비밀번호 파일을 만듭니다. 생성된 비밀번호는 안전한 비밀번호 관리 도구에도 보관하세요. Docker 관리 권한은 컨테이너와 데이터에 접근할 수 있는 강한 권한입니다.

sudo install -d -m 700 -o "$(id -un)" -g "$(id -gn)" /opt/postgresql18
cd /opt/postgresql18
mkdir -m 700 secrets
umask 077
openssl rand -hex 32 > secrets/pg_admin_password.txt

/opt/postgresql18/compose.yaml에 다음 내용을 저장합니다. 파일 기반 Compose secrets로 비밀번호를 전달하며, 호스트에서는 로컬 주소의 15432 포트로만 받습니다. 이 secrets 설정이 원본 파일을 자동으로 암호화해 주는 것은 아닙니다.

services:
  db:
    image: postgres:18.6
    restart: unless-stopped
    environment:
      POSTGRES_USER: postgres
      POSTGRES_DB: postgres
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_admin_password
      POSTGRES_INITDB_ARGS: --auth-host=scram-sha-256
      POSTGRES_HOST_AUTH_METHOD: scram-sha-256
    secrets:
      - pg_admin_password
    ports:
      - "127.0.0.1:15432:5432"
    volumes:
      - pgdata:/var/lib/postgresql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  pgdata:

secrets:
  pg_admin_password:
    file: ./secrets/pg_admin_password.txt

PostgreSQL 18 공식 이미지의 볼륨 연결 위치는 /var/lib/postgresql입니다. 기본 PGDATA는 /var/lib/postgresql/18/docker입니다. 17 이하 예제의 /var/lib/postgresql/data를 그대로 가져오지 않습니다. 이 변경과 초기화 환경변수의 동작은 공식 이미지 설명에 정리되어 있습니다.

기동과 관리자 접속

cd /opt/postgresql18
sudo docker compose config --quiet
sudo docker compose up -d --wait
sudo docker compose ps
sudo docker compose logs --tail=50 db
sudo docker compose exec db psql -X -U postgres -d postgres

pg_isready의 healthy 표시는 서버가 연결을 받을 준비가 됐다는 뜻입니다. 앱 비밀번호나 테이블 권한까지 검사한 결과는 아닙니다. 관리자 접속 후에는 아래 SQL로 실제 버전과 데이터 경로를 확인합니다.

SELECT version();
SHOW data_directory;
SHOW hba_file;

POSTGRES_USER는 최초 초기화 때 만드는 슈퍼유저입니다. 앱 이름을 여기에 넣으면 그 앱이 슈퍼유저가 될 수 있으므로 관리자용으로 유지합니다. POSTGRES_PASSWORD_FILE이나 초기화 SQL을 바꾸고 컨테이너를 재시작해도 기존 볼륨의 역할·비밀번호는 바뀌지 않습니다. 기존 비밀번호는 SQL이나 \password로 변경합니다.

docker compose down은 컨테이너와 네트워크를 내리고 명명 볼륨은 남깁니다. down -v는 DB 볼륨을 지우므로 일반 종료나 비밀번호 재설정에 사용하지 않습니다. Compose 종료 옵션에서 볼륨 삭제 조건을 확인할 수 있습니다.

OS 계정, DB 역할, psql 접속의 차이

psql은 PostgreSQL에 접속하는 클라이언트입니다. OS 계정과 DB 계정은 별개이며, PostgreSQL은 계정과 권한 묶음을 모두 역할(role)로 관리합니다. LOGIN이 있는 역할은 로그인 계정으로 쓸 수 있고, NOLOGIN 역할은 권한 묶음이나 객체 소유자로 쓸 수 있습니다. 역할 이름은 한 DB가 아니라 클러스터 전체에서 공유됩니다. 역할 속성과 DB·스키마 구조에 이 구분이 설명되어 있습니다.

항목	의미	예
OS 사용자	Linux 프로세스를 실행하는 사용자	root, postgres
DB 역할	DB 안에서 로그인·소유권·권한을 갖는 주체	postgres, dba_admin, app_runtime
데이터베이스	연결할 데이터 공간	postgres, appdb
스키마	DB 안의 테이블 등을 묶는 이름 공간	public, app

sudo -u와 psql -U의 차이

여기서 소문자 -u는 sudo의 옵션, 대문자 -U는 psql의 옵션입니다. sudo -u는 명령을 실행할 Linux 사용자를, psql -U는 접속할 DB 역할을 지정합니다. 옵션은 대소문자를 구분하므로 어느 프로그램 뒤에 붙었는지 함께 읽어야 합니다.

명령과 옵션	지정하는 대상	의미
`sudo -u postgres`	OS 사용자	Linux의 postgres 사용자로 뒤의 명령 실행
`psql -U postgres`	DB 역할	PostgreSQL의 postgres 역할로 접속 요청
`psql -d postgres`	데이터베이스	접속할 DB를 postgres로 지정

sudo -u의 정의는 sudo 매뉴얼에서 확인할 수 있습니다. psql에서 접속할 사용자는 대문자 -U로 지정하며, 소문자 -u로 대체할 수 없습니다.

명령 순서는 psql -U 역할명 -d DB명입니다. -U는 사용자 선택이며 인증을 건너뛰는 옵션이 아닙니다. -h를 생략하면 일반적인 Linux 환경에서는 Unix 소켓을 사용하고, -h 127.0.0.1을 주면 TCP를 사용합니다. 환경변수로 기본값이 바뀔 수 있으므로 실습에서는 주소·사용자·DB를 명시하는 편이 편해요.

# RPM: local socket, OS user postgres
sudo -u postgres /usr/pgsql-18/bin/psql -X -U postgres -d postgres

# RPM: TCP password login, after configuring the role and HBA below
/usr/pgsql-18/bin/psql -X -h 127.0.0.1 -p 5432 -U dba_admin -d postgres -W

# Container: run psql inside the container
sudo docker compose exec db psql -X -U postgres -d postgres

# Container: TCP password login from the host, if the PG18 client is installed
/usr/pgsql-18/bin/psql -X -h 127.0.0.1 -p 15432 -U dba_admin -d postgres -W

첫 번째 명령의 postgres 세 개는 각각 Linux 사용자 → DB 역할 → 데이터베이스를 뜻합니다. OS의 postgres로 psql을 실행하고, DB의 postgres 역할로 postgres 데이터베이스에 접속한다는 의미입니다. 같은 이름을 사용해도 세 대상은 별개입니다.

-d를 생략하면 사용자 이름과 같은 DB를 찾다가 database "dba_admin" does not exist가 나올 수 있습니다. -W는 비밀번호 입력을 요청할 뿐, 서버 인증 방식 자체를 바꾸지 않습니다. 전체 옵션과 기본값은 psql 공식 문서를 기준으로 확인합니다.

슈퍼유저 직접 만들기: dba_admin 생성과 로그인

dba_admin은 원하는 다른 이름으로 바꿔도 됩니다. 새 슈퍼유저는 기존 슈퍼유저로 접속한 뒤 만듭니다. CREATEROLE만 있는 일반 역할은 다른 슈퍼유저를 만들 수 없습니다. CREATE ROLE의 SUPERUSER 조건을 따릅니다.

1. 기존 postgres 관리자로 psql 열기

아래 둘 중 설치 방식에 맞는 한 줄을 OS 터미널에서 실행합니다. 컨테이너 명령은 /opt/postgresql18의 Compose 작업 디렉터리에서 실행합니다.

# RPM installation
sudo -u postgres /usr/pgsql-18/bin/psql -X -U postgres -d postgres

# Container installation: run from the Compose directory
sudo docker compose exec db psql -X -U postgres -d postgres

2. 새 슈퍼유저 생성과 비밀번호 지정

일반 설치 또는 컨테이너의 postgres 관리자 세션에서 실행합니다. OS 셸이 아니라 psql 안에서 입력하는 명령입니다.

CREATE ROLE dba_admin LOGIN SUPERUSER;

이어서 비밀번호를 지정합니다. 아래 \password는 psql 전용 명령이므로 세미콜론을 붙이지 않습니다. 비밀번호를 SQL 문장에 직접 쓰는 대신 입력 창에서 두 번 입력합니다.

\password dba_admin
\du

dba_admin은 전체 DB를 관리할 수 있으므로 앱 설정에 넣지 않습니다. 비밀번호를 바꿀 때에도 관리자 세션에서 \password dba_admin 또는 \password app_runtime을 사용합니다. 일반 사용자는 \password로 자기 비밀번호를 바꿀 수 있습니다. OS 명령 passwd postgres는 Linux 암호만 바꾸며 DB 암호를 바꾸지 않습니다.

계정 생성 직후에는 DB 비밀번호가 없으므로 비밀번호 인증을 쓰려면 설정이 필요합니다. 반대로 비밀번호가 있어도 peer나 trust 인증 경로에서는 그 암호를 검사하지 않습니다. CREATE ROLE과 비밀번호 인증의 구분입니다.

3. 새 관리자 계정으로 다시 로그인

처음 연 postgres 세션은 유지하고 새 OS 터미널에서 접속합니다. dba_admin 생성과 비밀번호 설정을 먼저 마쳐야 합니다. RPM에서 접속이 거부되면 아래 pg_hba.conf 절의 루프백 SCRAM 규칙을 적용한 뒤 다시 시도합니다.

# RPM: TCP connection to the host service
/usr/pgsql-18/bin/psql -X -h 127.0.0.1 -p 5432 -U dba_admin -d postgres -W

# Container: TCP connection inside the DB container
sudo docker compose exec db psql -X -h 127.0.0.1 -p 5432 -U dba_admin -d postgres -W

컨테이너 안의 DB 포트는 5432입니다. 호스트에서 컨테이너로 접속할 때 쓰는 15432와 구분합니다. 새 psql 세션에서 현재 사용자와 슈퍼유저 속성을 확인합니다.

SELECT current_user, current_database();
SELECT rolname, rolcanlogin, rolsuper
FROM pg_roles
WHERE rolname IN ('postgres', 'dba_admin');

current_user는 dba_admin, 해당 역할의 rolcanlogin과 rolsuper는 모두 t여야 합니다. CREATE ROLE은 DB 역할을 만들며, Linux 사용자나 같은 이름의 데이터베이스를 만들지는 않습니다. 따라서 접속할 DB를 -d postgres로 지정했습니다.

새 슈퍼유저를 만들면 기존 postgres 접속이 막힐까

막히지 않습니다. CREATE ROLE dba_admin ...은 새로운 역할을 추가할 뿐입니다. 기존 postgres의 LOGIN, 비밀번호, 인증 규칙은 그대로 남습니다.

RPM: peer 인증은 OS 사용자를 확인

pg_hba.conf에 local all postgres peer가 있으면 OS의 postgres 사용자가 DB의 postgres 역할로 접속할 수 있습니다. 이때 DB 비밀번호를 묻지 않는 것은 peer 인증이 OS 사용자 이름을 확인하기 때문입니다. 별도 매핑이 없는 상태에서 OS 사용자 root가 psql -U postgres만 실행하면 이름이 달라 실패합니다.

다만 root나 sudo -u postgres가 허용된 운영자는 OS의 postgres로 전환할 수 있습니다. DB 암호 변경이나 슈퍼유저 이름 변경으로 서버 root를 DB에서 배제할 수는 없습니다. OS·sudo·Docker 관리 권한도 함께 관리해야 합니다.

컨테이너: 기본 로컬 trust와 TCP를 구분

위 Compose는 외부 TCP 접속에 SCRAM 비밀번호 인증을 요구합니다. 그러나 공식 이미지의 기본 컨테이너 내부 Unix 소켓은 trust입니다. 따라서 docker compose exec db psql -U postgres -d postgres는 DB 비밀번호 없이 접속할 수 있습니다. POSTGRES_HOST_AUTH_METHOD는 이 로컬 소켓 규칙까지 바꾸지 않습니다. 초기화 처리는 공식 이미지 진입 스크립트에서도 확인할 수 있습니다.

시도	이 글의 설정에서 판단 기준
RPM에서 `sudo -u postgres ... psql`	postgres용 peer 규칙이 있으면 암호 없이 가능
RPM에서 OS root가 `psql -U postgres`	peer의 사용자 이름이 달라 실패; sudo 전환은 별개
컨테이너 안에서 `psql -U postgres`	기본 소켓 trust면 암호 없이 가능
`psql -h 127.0.0.1 ... -U dba_admin`	TCP HBA 규칙과 dba_admin의 암호로 결정
`ALTER ROLE postgres NOLOGIN` 후 새 연결	postgres 역할로 직접 새 로그인 불가; 기존 세션은 유지

NOLOGIN 역할도 권한 있는 다른 역할이 SET ROLE로 전환할 수 있으므로 OS 관리자 차단 수단이 아닙니다. 초기 슈퍼유저는 삭제하지 말고, 초보 단계에서는 로컬 복구용 postgres 경로를 유지하면서 사람용 관리자와 앱 계정을 분리하는 편이 관리하기 쉽습니다. NOLOGIN으로 바꾸려면 별도 관리자 재접속과 백업·복구 도구 설정을 먼저 검증해야 합니다. ALTER ROLE에서 NOLOGIN의 의미를 확인할 수 있습니다.

pg_hba.conf로 로그인 규칙 정리

RPM: postgres는 peer, 나머지는 SCRAM

관리자 psql 세션을 열린 상태로 두고 실제 파일 경로부터 확인합니다. 다음 파일 경로는 PGDG 기본값이며 SHOW 결과가 우선입니다.

SHOW hba_file;
SHOW config_file;
SHOW password_encryption;

별도 OS 터미널에서 원본을 백업한 뒤 편집합니다.

sudo cp -a /var/lib/pgsql/18/data/pg_hba.conf /var/lib/pgsql/18/data/pg_hba.conf.before-app
sudoedit /var/lib/pgsql/18/data/pg_hba.conf

다음은 이 글의 로컬 접속용 규칙 예시입니다. 기존 일반 접속용 local·루프백 host 규칙을 확인해 정리하고, 더 위에 포괄적인 trust나 ident가 남지 않도록 합니다. 복제·백업 도구용 규칙이 있다면 용도를 확인한 뒤 보존합니다.

# TYPE  DATABASE  USER       ADDRESS         METHOD
local   all       postgres                   peer
local   all       all                        scram-sha-256
host    all       all        127.0.0.1/32     scram-sha-256
host    all       all        ::1/128          scram-sha-256

HBA는 위에서 처음 일치한 규칙 한 개로 인증합니다. 그 규칙에서 실패했다고 다음 규칙으로 넘어가지 않습니다. 기존 암호가 오래된 MD5 형식이면 SCRAM 규칙만 바꾸는 것으로는 부족하며, password_encryption을 확인하고 해당 계정의 암호를 다시 설정해야 합니다. HBA 규칙에 순서와 적용 조건이 설명되어 있습니다.

관리자 psql에서 파일의 문법 오류를 확인한 뒤 다시 읽게 합니다.

SELECT line_number, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

SELECT pg_reload_conf();

오류 조회가 0행인지 확인합니다. pg_reload_conf()의 true는 재읽기 신호를 보냈다는 뜻이지 모든 설정이 정상이라는 보장은 아닙니다. 서버 로그를 확인하고 기존 관리자 세션을 닫기 전에 다른 터미널에서 dba_admin과 앱 계정의 새 접속을 시험합니다. pg_hba_file_rules는 현재 파일을 보여주며 마지막으로 적용된 규칙을 증명하는 뷰는 아닙니다. 뷰 설명을 참고하세요.

컨테이너의 로컬 접속도 제한하려면

기본 trust를 바꾸려면 컨테이너에서 SHOW hba_file로 확인한 실제 HBA 파일을 먼저 백업하고 수정해야 합니다. 위 RPM 예시의 local 두 줄만 참고해 Unix 소켓 규칙을 postgres peer, 그 밖의 역할은 SCRAM으로 정리합니다. 컨테이너의 HBA 전체를 RPM용 예시로 덮어쓰지 않습니다. 비상 관리자 접속도 다음처럼 OS 사용자를 지정해야 합니다.

sudo docker compose exec --user postgres db psql -X -U postgres -d postgres

다른 컨테이너와 호스트에서 들어오는 기존 TCP 규칙은 별도로 유지·제한해야 합니다. 호스트의 127.0.0.1 포트로 접속해도 DB가 보는 클라이언트 주소는 Docker 브리지 주소일 수 있습니다. HBA를 제한할 때에는 SELECT inet_client_addr();로 실제 주소를 확인합니다. 아래와 같은 파일 재읽기 후 새 접속을 검증하세요. POSTGRES_PASSWORD_FILE을 수정하는 것으로 HBA 변경을 대신할 수는 없습니다.

sudo docker compose exec --user postgres db psql -X -U postgres -d postgres -c 'SELECT pg_reload_conf();'

일반 App 계정 만들기: app_runtime과 권한 설정

앱에 입력할 일반 로그인 계정은 app_runtime입니다. 아래 CREATE ROLE app_runtime LOGIN NOSUPERUSER ... 줄이 계정을 만드는 명령이고, \password app_runtime이 비밀번호를 지정하는 명령입니다. 이 이름도 예시이며 설치 시 자동 생성되지 않습니다.

이 절은 앞에서 만든 dba_admin 또는 기존 postgres로 접속한 관리자 psql에서 순서대로 실행합니다. 계정만 생성하면 앱의 DB·스키마·테이블 권한까지 생기는 것은 아니므로 아래 권한 설정도 함께 진행합니다.

처음부터 앱에 슈퍼유저를 주면 권한 오류는 줄어도 앱 오류나 유출의 영향이 커집니다. 아래 구성은 appdb의 전용 app 스키마에서 테이블 소유권과 평상시 실행 권한을 나눕니다.

역할	로그인	담당
app_owner	불가	DB·스키마·테이블 소유
app_migrator	가능	배포 시 app_owner로 전환해 DDL 실행
app_runtime	가능	앱의 SELECT·INSERT·UPDATE·DELETE
app_reader	가능	조회 전용

DDL은 CREATE TABLE, ALTER TABLE처럼 구조를 바꾸는 명령입니다. 앱 실행 중 자동 마이그레이션이 필요한 제품은 별도 배포 단계로 분리할 수 있는지 먼저 확인하세요. 아래 예시는 새 역할·DB 이름을 쓰며 반복 실행용 스크립트는 아닙니다.

1. 일반 로그인 계정 생성과 비밀번호 지정

postgres 또는 dba_admin 관리자 psql에서 실행합니다. \password에는 각기 다른 비밀번호를 지정합니다.

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_migrator LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
CREATE ROLE app_runtime LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
CREATE ROLE app_reader LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
GRANT app_owner TO app_migrator;

\password app_migrator
\password app_runtime
\password app_reader

app_runtime에는 app_owner 멤버십을 주지 않습니다. 배포 계정만 소유자로 전환할 수 있게 합니다. CREATEDB, CREATEROLE도 평상시 앱에는 필요하지 않습니다.

2. 앱 전용 DB와 접속 권한 지정

로그인 계정 생성과 비밀번호 지정을 마쳤다면 같은 관리자 세션에서 DB를 만듭니다. app_owner는 소유권만 갖고, 실제 앱은 app_runtime으로 접속하게 구성합니다.

CREATE DATABASE appdb OWNER app_owner;
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_migrator, app_runtime, app_reader;
ALTER ROLE app_runtime IN DATABASE appdb SET search_path = app;
ALTER ROLE app_reader IN DATABASE appdb SET search_path = app;

PUBLIC은 모든 DB 역할에 공통으로 적용되는 권한 대상입니다. REVOKE로 기본 DB 접근을 거두고 필요한 계정에 CONNECT를 줍니다. DB 접속 권한과 테이블 조회 권한은 별개이므로 GRANT ALL ON DATABASE만으로 테이블을 쓸 수 있게 되는 것은 아닙니다. GRANT 문서를 참고하세요.

3. 스키마와 테이블 권한 설정

같은 관리자 세션에서 appdb로 바꿉니다. 아래 설정은 appdb 안에서 실행해야 합니다.

\connect appdb

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_runtime, app_reader;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_reader;

기본 권한은 이 설정 이후 app_owner가 만드는 객체에 적용됩니다. 이미 존재하는 테이블에 소급 적용되지 않고, 다른 역할이 만든 테이블에도 자동 적용되지 않습니다. 배포 작업에서 SET ROLE app_owner를 명시하는 이유입니다. ALTER DEFAULT PRIVILEGES의 적용 범위를 확인하세요.

관리자 세션 또는 app_migrator로 접속한 appdb 세션에서 예제 테이블을 만듭니다.

SET ROLE app_owner;
CREATE TABLE app.notes (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body text NOT NULL
);
RESET ROLE;

기존 테이블과 시퀀스에도 권한이 필요하다면 관리자가 appdb에서 다음을 추가 실행합니다. app 스키마 전체를 공유하려는 경우의 예시이며, 일부 테이블만 허용할 때에는 객체 이름을 개별 지정합니다.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_runtime;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_runtime;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_reader;

4. 앱 계정으로 로그인하고 권한 확인

일반 설치는 포트 5432, 컨테이너는 호스트에서 15432로 접속합니다. 아래는 일반 설치 예시입니다.

/usr/pgsql-18/bin/psql -X -h 127.0.0.1 -p 5432 -U app_runtime -d appdb -W

컨테이너 설치는 위 Bash 명령 대신 Compose 작업 디렉터리에서 다음을 실행할 수 있습니다. 컨테이너 내부에서 TCP로 연결하므로 호스트에 별도 psql을 설치할 필요가 없습니다.

sudo docker compose exec db psql -X -h 127.0.0.1 -p 5432 -U app_runtime -d appdb -W

\password app_runtime으로 지정한 비밀번호를 입력한 다음, 아래 SQL을 새 앱 세션에서 실행합니다.

SELECT current_user, session_user, current_database();
INSERT INTO app.notes (body) VALUES ('connection test') RETURNING id, body;
SELECT id, body FROM app.notes;

위 작업은 성공해야 합니다. 반면 아래 명령은 app_runtime에서 permission denied가 나오는 것이 의도한 결과입니다. 오류를 없애기 위해 슈퍼유저를 주지 않습니다.

CREATE TABLE app.should_fail (id integer);

app_reader로 접속하면 조회는 되고 INSERT는 거부되어야 합니다. 스키마 사용 권한, 테이블 권한, 앞으로 생성할 객체의 기본 권한을 각각 확인하면 불필요한 권한을 주는 일을 줄일 수 있어요.

앱에서 DB 계정을 사용하는 방법

앱이 DB 서버에 “계정을 가져오는” 작업은 필요하지 않습니다. 호스트·포트·DB명·사용자·비밀번호를 연결 설정에 전달하면 됩니다. 앱용 비밀번호는 app_runtime의 값이며, postgres나 app_migrator의 값이 아닙니다.

앱 실행 위치	PGHOST	PGPORT
RPM DB와 같은 호스트	127.0.0.1	5432
위 컨테이너 DB의 호스트	127.0.0.1	15432
DB와 같은 Compose 네트워크의 앱	db	5432
별도 서버	DB의 사설 주소·DNS	실제 허용한 포트

앱 컨테이너의 localhost는 앱 컨테이너 자신입니다. 같은 Compose의 DB에는 서비스 이름 db로 접속합니다. 다른 Compose 프로젝트에서는 기본 네트워크가 다르므로 공용 네트워크 등 별도 연결 구성이 필요합니다.

Python 앱의 실제 연결 예시

새 앱 디렉터리에서 가상환경과 드라이버를 준비합니다. Rocky 9에 Python이 없다면 먼저 sudo dnf install -y python3 python3-pip로 설치합니다. 운영 프로젝트에서는 검증한 의존성 버전을 프로젝트의 잠금 파일로 고정합니다.

python3 -m venv .venv
.venv/bin/python -m pip install 'psycopg[binary]'
mkdir -m 700 secrets
umask 077
read -r -s -p 'app_runtime DB password: ' app_db_password
printf '\n'
printf '%s' "$app_db_password" > secrets/app_db_password.txt
unset app_db_password

입력하는 값은 앞에서 \password app_runtime으로 설정한 동일한 DB 비밀번호입니다. secrets/는 Git에 커밋하지 않습니다. 컨테이너 안에서 앱을 실행한다면 앱 서비스에 해당 secret을 허용하고 파일 경로를 전달해야 합니다.

db_check.py를 다음과 같이 저장합니다. 이 예제의 APP_DB_PASSWORD_FILE은 코드가 직접 읽는 설정입니다. 모든 앱이 _FILE 이름을 자동 지원하는 것은 아닙니다.

import os
from pathlib import Path
import psycopg

password = Path(os.environ['APP_DB_PASSWORD_FILE']).read_text().rstrip('\n')

with psycopg.connect(
    host=os.environ['PGHOST'],
    port=int(os.environ['PGPORT']),
    dbname=os.environ['PGDATABASE'],
    user=os.environ['PGUSER'],
    password=password,
    connect_timeout=5,
) as conn:
    with conn.cursor() as cur:
        cur.execute(
            'INSERT INTO app.notes (body) VALUES (%s) RETURNING id',
            ('hello from app',),
        )
        print('created note:', cur.fetchone()[0])

호스트에서 컨테이너 DB에 접속하는 실행 예시입니다. RPM DB라면 포트를 5432로 바꿉니다.

export PGHOST=127.0.0.1
export PGPORT=15432
export PGDATABASE=appdb
export PGUSER=app_runtime
export APP_DB_PASSWORD_FILE="$PWD/secrets/app_db_password.txt"
.venv/bin/python db_check.py

with 블록이 정상 종료되면 이 예제의 INSERT는 커밋됩니다. %s에는 드라이버가 값을 바인딩하므로 문자열을 이어 붙여 SQL을 만들지 않습니다. Psycopg 연결·트랜잭션 예제를 참고하세요.

비밀 값을 소스 코드·로그·공유 문서·연결 URL에 직접 넣지 않는 편이 좋습니다. libpq 환경변수 안내는 PGPASSWORD 노출 가능성도 설명합니다. 환경변수와 secret 파일의 접근 권한은 배포 방식에 맞춰 관리합니다.

다른 서버의 앱을 연결할 때

이 글의 기본 구성은 외부 서버에서 바로 접속할 수 있게 열어 놓지 않습니다. 원격 앱이 필요하면 DB의 수신 주소, 클라이언트 IP별 HBA, 방화벽, 전송 암호화를 함께 구성합니다. 예를 들어 DB 사설 IP가 10.20.30.10, 앱 IP가 10.20.30.40이라면 RPM의 listen_addresses에 DB 사설 IP를 추가하고 재시작해야 합니다. HBA에는 앱 역할·DB·단일 앱 IP를 제한한 규칙을 사용합니다.

TLS 인증서와 서버의 ssl = on을 별도로 구성한 뒤 적용할 HBA 예시는 다음과 같습니다. TLS를 구성하지 않은 서버에 이 줄만 추가해도 접속되지는 않습니다.

hostssl appdb app_runtime 10.20.30.40/32 scram-sha-256

클라이언트는 CA 파일과 인증서 이름에 맞는 DNS를 사용하고 sslmode=verify-full로 서버 신원을 확인합니다. 컨테이너의 호스트 포트도 현재 127.0.0.1 바인딩을 사설 주소로 바꾸는 별도 작업이 필요하며, 포트 공개 후에는 실제 외부 접근 범위를 확인합니다. SCRAM은 비밀번호 인증 방식이고 전체 SQL 트래픽 암호화는 TLS가 담당합니다. 수신 주소 설정과 TLS 검증이 기준입니다.

계정 잠금·회수와 접속 문제 해결

앱 비밀번호를 바꾸면 DB의 \password app_runtime과 앱의 secret을 함께 갱신합니다. 이미 연결된 세션은 비밀번호 변경만으로 종료되지 않습니다. 새 연결을 확인한 뒤 앱의 연결 풀이나 서비스를 순서대로 재시작합니다.

퇴사자·폐기 앱 등 로그인 자체를 중지하려면 관리자 세션에서 실행합니다.

ALTER ROLE app_runtime NOLOGIN;

즉시 연결을 끊어야 한다면 대상 계정을 다시 확인하고 아래 명령을 별도로 실행합니다. 진행 중인 요청이 실패하고 미커밋 트랜잭션은 롤백될 수 있습니다.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_runtime'
  AND pid <> pg_backend_pid();

재개가 필요하면 ALTER ROLE app_runtime LOGIN;으로 허용합니다. 세션 종료 함수의 권한과 반환값은 관리 함수 문서에 설명되어 있습니다. 역할 삭제는 소유 객체·부여 권한·기본 권한의 의존성을 확인한 뒤 진행합니다. 특히 DROP OWNED는 객체를 지울 수 있으므로 계정 정리용 명령을 의미 없이 복사하지 않습니다. 역할 삭제 절차를 참고하세요.

메시지·증상	먼저 확인할 항목
role “root” does not exist	`-U`를 생략해 OS 사용자 이름으로 접속했는지
database “계정명” does not exist	`-d appdb` 또는 `-d postgres`를 지정했는지
Peer authentication failed	소켓 접속인지, 현재 OS 사용자와 DB 역할이 일치하는지
password authentication failed	DB 역할·실제 암호·HBA 방식, 다른 인스턴스에 접속했는지
no pg_hba.conf entry	클라이언트 주소·DB·역할·TLS 조건에 일치하는 규칙이 있는지
connection refused	서비스 기동, 호스트·포트, 수신 주소·포트 바인딩
연결 시간 초과	라우팅·방화벽·보안 그룹·주소 오류
permission denied for schema/table/sequence	해당 DB의 스키마 USAGE·객체 권한·생성 역할

기본 상태 확인에는 다음 psql 명령이면 충분한 경우가 많습니다. SQL은 세미콜론으로 끝내고, 아래 역슬래시 명령에는 세미콜론을 붙이지 않습니다.

\conninfo
\du
\l
\dn
\dt app.*
\dp app.*
\ddp
\q

일반 설치 로그는 sudo journalctl -u postgresql-18 -n 100 --no-pager, 컨테이너 로그는 sudo docker compose logs --tail=100 db로 확인합니다. 설정에 따라 별도 PostgreSQL 로그 파일로 기록될 수 있으므로 필요하면 SHOW logging_collector;와 SHOW log_directory;도 확인합니다.

PostgreSQL 백업과 복구: 파일 생성부터 새 서버 복원까지

계정을 나눴다면 다음은 백업입니다. 명명 볼륨은 컨테이너 재생성과 데이터를 분리하지만 백업은 아닙니다. 아래 명령은 관리자 접근이 가능한 호스트의 쓰기 가능한 백업 디렉터리에서 실행하며, 파일에는 민감한 데이터와 역할 암호 해시가 포함될 수 있습니다.

백업 파일은 두 종류입니다. appdb.dump는 DB 안의 구조·데이터·객체 소유권과 권한을 담는 custom 형식이고, globals.sql은 역할·역할 멤버십 등 클러스터 공통 설정을 담는 SQL 파일입니다. .dump는 pg_restore, globals.sql은 psql로 복구합니다. 이 구분은 SQL 백업 안내에 설명되어 있습니다.

1. DB와 역할을 파일로 백업

쓰기 가능한 백업 디렉터리를 먼저 정하고 그 안에서 실행합니다. 아래 파일명은 고정되어 있어 재실행하면 기존 파일을 덮어쓰므로, 매번 날짜가 다른 디렉터리나 파일명을 사용하세요. 두 설치 방식 중 자신에게 맞는 명령 묶음만 실행합니다.

일반 설치는 OS postgres로 소켓 접속해 DB와 전역 역할을 각각 백업합니다.

umask 077
sudo -u postgres /usr/pgsql-18/bin/pg_dump -Fc -d appdb > appdb.dump
sudo -u postgres /usr/pgsql-18/bin/pg_dumpall --globals-only > globals.sql

컨테이너는 Compose 작업 디렉터리에서 실행합니다. 로컬 HBA를 peer로 강화한 경우에도 동작하도록 OS 사용자를 명시했습니다. 파일 리다이렉션은 호스트에서 처리합니다.

umask 077
sudo docker compose exec -T --user postgres db pg_dump -U postgres -Fc -d appdb > appdb.dump
sudo docker compose exec -T --user postgres db pg_dumpall -U postgres --globals-only > globals.sql

2. 같은 서버의 새 DB에 복원 시험

이 시험은 역할이 이미 있는 원래 클러스터에서 진행하므로 globals.sql을 다시 실행하지 않습니다. 다음 단계에서 appdb_restore_check라는 새 DB를 만들고 데이터를 복원합니다.

pg_dump만으로는 클러스터의 역할이 저장되지 않으므로 globals.sql도 별도로 보관합니다. 아래는 같은 클러스터 안에 새 검증 DB를 만드는 간단한 복원 시험입니다. 실제 appdb를 덮어쓰지 않습니다.

sudo -u postgres /usr/pgsql-18/bin/createdb -O app_owner appdb_restore_check
sudo -u postgres /usr/pgsql-18/bin/pg_restore --exit-on-error -d appdb_restore_check < appdb.dump
sudo -u postgres /usr/pgsql-18/bin/psql -d appdb_restore_check -c 'TABLE app.notes;'

컨테이너를 선택했다면 위 세 줄 대신 다음을 실행합니다.

sudo docker compose exec -T --user postgres db createdb -U postgres -O app_owner appdb_restore_check
sudo docker compose exec -T --user postgres db pg_restore -U postgres --exit-on-error -d appdb_restore_check < appdb.dump
sudo docker compose exec -T --user postgres db psql -U postgres -d appdb_restore_check -c 'TABLE app.notes;'

검증 DB명은 비어 있어야 합니다. 복원에 실패한 경우 오류를 해결한 뒤 새로운 검증 DB명으로 다시 시험합니다. 새 서버로 복구하는 절차는 다음과 같습니다.

3. 새 서버에 역할부터 복구하고 원래 DB 복원

다음 명령은 PostgreSQL 18을 새로 초기화한 별도 복구 서버에서 실행합니다. 그 서버에 appdb가 없어야 하며, 원본의 appdb.dump와 globals.sql을 안전하게 옮겨 두어야 합니다. 원본이 쓰던 확장 모듈이 있다면 복구 서버에도 먼저 설치합니다. 아래 절차는 이 글의 기본 구성처럼 사용자 정의 테이블스페이스가 없는 경우를 기준으로 합니다.

새 서버에도 초기 관리자 postgres는 있으므로 전역 백업을 그대로 실행하면 role "postgres" already exists로 중단될 수 있습니다. 원본은 보관하고 검토용 복사본을 편집합니다.

umask 077
cp globals.sql globals.reviewed.sql
chmod 600 globals.reviewed.sql
vi globals.reviewed.sql

새 서버의 역할 목록을 \du로 확인한 뒤, 복사본에서 이미 존재하는 초기 역할을 만드는 CREATE ROLE postgres; 한 문장만 제거합니다. 그 역할의 ALTER ROLE postgres ...와 나머지 새 역할·멤버십 문장은 유지합니다. 다른 이름도 충돌한다면 무시하고 계속하지 말고 원인부터 확인합니다. 암호 해시가 있으므로 파일 내용을 공개하거나 로그에 남기지 않습니다.

일반 설치 복구 서버에서는 다음을 실행합니다.

sudo -u postgres /usr/pgsql-18/bin/psql -X -v ON_ERROR_STOP=1 -d postgres < globals.reviewed.sql
sudo -u postgres /usr/pgsql-18/bin/pg_restore --exit-on-error --create -d postgres < appdb.dump
sudo -u postgres /usr/pgsql-18/bin/psql -X -d appdb -c 'ANALYZE;'

컨테이너 복구 서버라면 위 세 줄 대신 새 서버의 Compose 작업 디렉터리에서 다음을 실행합니다.

sudo docker compose exec -T --user postgres db psql -X -U postgres -v ON_ERROR_STOP=1 -d postgres < globals.reviewed.sql
sudo docker compose exec -T --user postgres db pg_restore -U postgres --exit-on-error --create -d postgres < appdb.dump
sudo docker compose exec -T --user postgres db psql -X -U postgres -d appdb -c 'ANALYZE;'

--create를 쓰면 -d postgres는 DB 생성에 사용할 최초 접속 대상이며, 실제 복구 대상은 백업에 기록된 원래 이름 appdb입니다. 이 방식은 DB 자체의 권한과 DB별 역할 설정도 복원합니다. 오류가 나면 즉시 멈추고 원인을 확인합니다. 같은 명령을 계속 재실행하거나 기존 DB에 --clean을 붙여 덮어쓰지 않습니다. pg_restore의 –create 옵션을 참고하세요.

4. 데이터와 앱 권한을 함께 확인

같은 서버의 시험 복구는 appdb_restore_check, 새 서버 복구는 appdb에 접속해 다음을 관리자 psql에서 확인합니다.

SELECT count(*) FROM app.notes;
SELECT pg_get_userbyid(relowner) AS table_owner
FROM pg_class
WHERE oid = 'app.notes'::regclass;
SELECT
  has_table_privilege('app_runtime', 'app.notes', 'SELECT') AS can_select,
  has_table_privilege('app_runtime', 'app.notes', 'INSERT') AS can_insert,
  has_schema_privilege('app_runtime', 'app', 'CREATE') AS can_create;

행 수와 실제 데이터를 백업 시점의 기록과 대조합니다. 소유자는 app_owner, 앱의 조회·입력 권한은 t, 스키마에 테이블을 만드는 권한은 f여야 합니다. 새 서버에서는 app_runtime으로 새로 로그인해 앞 절의 INSERT·SELECT와 테이블 생성 거부도 확인합니다. 복구된 비밀번호는 백업 시점의 값이므로 앱의 secret과 일치해야 합니다.

백업 두 파일은 DB 서버와 다른 저장소에도 보관하고, 실행 날짜·종료 상태·복구 확인 결과를 남깁니다. postgresql.conf, pg_hba.conf, TLS 인증서, Compose 파일과 앱의 secret은 이 논리 백업에 모두 포함되는 것이 아니므로 별도로 관리합니다. 이 절차는 WAL을 이용한 특정 시점 복구(PITR)를 다루지 않습니다. PostgreSQL 백업 방식에서 논리 백업과 다른 복구 방식의 범위를 확인할 수 있습니다.

평상시에는 앱을 app_runtime으로 실행하고, 구조 변경 때만 app_migrator를 사용합니다. 관리자 새 접속, 앱의 정상 작업과 권한 거부, 다른 저장소에 보관한 백업의 복원까지 확인되면 처음 운영할 때 필요한 경계가 훨씬 분명해집니다.

이 글의 검증 범위

확인 기준일은 2026년 10월 25일입니다. 격리된 Rocky Linux 9.8 x86_64 컨테이너에서 PGDG PostgreSQL 18.6 패키지 설치·초기화·프로세스 기동을 확인했습니다. Docker 공식 18.6 이미지에서도 역할·SCRAM 접속·앱 권한·Python 연결·백업과 별도 DB 복원을 검증했습니다. 이번 보완에서는 별도 신규 클러스터에 전역 역할과 원래 DB를 복원하고 앱 계정의 권한을 다시 확인했습니다. systemd가 동작하는 전체 VM의 재부팅, 원격 TLS·방화벽 구성은 이 검증에 포함하지 않습니다.

대표 이미지는 서버 저장 장치의 일반 참고 사진입니다. 본문의 DB 서버나 검증 장비를 촬영한 사진이 아닙니다. 사진: Andy Melton · 원본과 사용 조건 · CC BY-SA 2.0. 원본 비율을 유지하며 웹 표시 크기와 목록 썸네일이 조정될 수 있습니다.