DB 날려먹은 사람의 외양간 고치기

이지호·2025년 1월 8일
데이터베이스
8

Ask-It 프로젝트 문제해결 경험

목록 보기
3/4
post-thumbnail

이 글에서는 PostgreSQL과 Prisma ORM을 사용하는 프로젝트를 진행하면서 겪은 실수와 그로부터 배운 교훈을 공유하고자 한다.

왜 날려먹었나?

리팩토링 과정에서 DB 테이블 스키마를 수정해야 했다. Prisma를 사용하고 있었는데, schema.prisma 파일에 변경 사항을 반영하고, 이를 실제 DB에 적용하려고 했다.

그리고 prisma migrate dev --name 'RBAC' 명령어로 데이터베이스에 반영을 시도했다. 이 때에 경고문이 나오면서 데이터를 날리고 계속할지를 물었다.

  • prisma migrate dev: 개발 환경에서만 쓰는 명령어로, 스키마 변경이 어려운 경우 DB를 초기화(드롭)하고 재생성한다.
  • prisma migrate deploy: 운영 환경에서 쓰는 명령어로, ALTER TABLE만 수행하며 실패하면 롤백한다.

무심코 prisma migrate dev를 쳤고, “DB를 날릴 것 같은데 계속할래?” 같은 경고문을 대충 읽고 ENTER를 눌렀다. 그러면서 운영 DB를 통째로 날려버렸다...ㅋ 조금 더 신중했어야 했는데, 지금 생각해도 너무 후회스럽다.

다시 보니 다음과 같은 문제가 있었다:

/*추가된 모델*/
model Role {
  name         String   @id
  permissions  Int[]
  tokens       UserSessionToken[] @relation("RoleTokens")
}

model UserSessionToken {
  token     String  @id @default(uuid())
  userId    Int?    @map("user_id")
  sessionId String  @map("session_id")
  roleType  String  @map("role_type") /*추가*/
  /*중략*/
  role  Role  @relation("RoleTokens", fields: [roleType], references: [name])
}

Added the required column roleType to the UserSessionToken table without a default value. This is not possible if the table is not empty

이때 FK 제약조건 때문에(이미 Role테이블에는 데이터가 들어있고, UserSessionToken에도 roleType이 비어있는 상태) 테이블을 ALTER 할 수가 없었다고 한다. Prisma가 이를 자동으로 해결하려다가 “디폴트값 줄 수 없으면 그냥 테이블 갈아엎을래?”라고 물어본 거고, 나는 무심코 허락해버렸다.

복구 못하나?

처음엔 현실을 부정했다. PosgreSQL이 MySQL처럼 '바이너리 로그' 같은 걸 어디엔가 켜두었을 거라고 막연히 기대했다.

  • MySQL: 바이너리 로그가 기본적으로 활성화된 경우가 많아서, mysqlbinlog 등으로 특정 시점까지 복구가 가능하다.
  • PostgreSQL: WAL(Write-Ahead Log)을 사용하지만, “archive_mode=on” 등 별도 설정이 없으면, 과거 로그가 자동으로 계속 덮어쓰기된다.

문제는 우리 DB 설정을 확인해보니 이랬다:

SHOW archive_mode;     -- off
SHOW wal_level;        -- replica
SHOW archive_command;  -- (disabled)

즉, 아카이빙 설정이 전혀 안 되어 있었다는 의미다. WAL 파일은 꾸준히 덮어쓰여서 과거 상태로 돌아갈 수 있는 ‘과거 로그’가 이미 사라져버렸다. 백업도 없으니, 남은 방법은 사실상 없다.

외양간이라도 고쳐보자

앞으로라도 날리지 않기 위해 외양간을 고쳐보려 한다. 알고 있었음에도 실천하지 않았던 나의 과거를 반성하며 대책을 세워본다.

1. Role을 부여할 때 DROP TABLE을 못 하게 막았더라면..

PostgreSQL는 역할(Role)과 권한(Privilege)을 비교적 세밀하게 설정할 수 있다. 운영 DB에서 ‘DDL(테이블 생성·수정·삭제)’ 권한을 제한한다면, Prisma 같은 툴이 함부로 DROP TABLE 등을 실행하지 못한다.

1.1 Role을 어떻게 부여할까?

1)슈퍼유저(Superuser)를 최소화한다

  • PostgreSQL에서 슈퍼유저(SUPERUSER) 권한이 있는 역할은 거의 모든 작업을 할 수 있다. 운영 환경에서는 슈퍼유저 계정 한두 개만 남기고, 나머지는 모두 슈퍼유저가 아니도록 제한한다.
  • 예:
    ALTER ROLE developer_role WITH NOSUPERUSER;

2) 개발용 계정은 CREATE / DROP 권한을 부여하지 않는다

  • PostgreSQL에서 스키마 단위, 테이블 단위로 권한을 부여·회수할 수 있다.
  • 예: ‘public’ 스키마 안에서 CREATE(테이블 생성 권한)와 DROP(테이블 삭제 권한)을 막으려면:
    REVOKE CREATE ON SCHEMA public FROM developer_role;
REVOKE ALL ON SCHEMA public FROM developer_role;
  • 이미 developer_role이 특정 테이블의 Owner라면, Owner는 테이블 삭제가 가능하므로, Owner를 다른 역할(예: admin_role)로 바꾸고 developer_role에는 SELECT/INSERT/UPDATE 정도만 부여한다.
    ALTER TABLE some_table OWNER TO admin_role;
REVOKE ALL ON TABLE some_table FROM developer_role;
GRANT SELECT,INSERT,UPDATE ON TABLE some_table TO developer_role;

3) DB 접근 권한(Account)과 실행 권한(Privilege)을 분리한다

  • 운영 DB에 접근 가능한 계정이 곧바로 테이블 드롭 같은 DDL 명령을 실행할 수 없다면, 실수로 DROP TABLE을 때리더라도 에러가 발생한다.
  • 개발자는 운영 DB에 “데이터 SELECT, INSERT” 정도까지만 허용해도 되는 경우가 많다. 마이그레이션 등 스키마 변경은 DBA나 별도의 관리자 역할(admin_role) 만이 하도록 규정하면 안전하다.

1.2 Role 실습

우리의 DB에서는 app_server라는 계정을 만들어 사용하려고 한다. 이 계정에는 DML만 허용하고 DDL은 불가능하게 하고자 한다.

-- 1) 'app_server'라는 계정(ROLE) 생성
CREATE ROLE app_server
    WITH LOGIN
    PASSWORD '비밀번호'  -- 예시 비밀번호
    NOSUPERUSER     -- 슈퍼유저 권한 X
    NOCREATEDB      -- DB 생성 권한 X
    NOCREATEROLE    -- ROLE 생성 권한 X
    NOINHERIT;      -- 다른 ROLE 권한 상속 X

-- 2) 'mydb'에 대한 기본 접근(접속) 권한 부여
GRANT CONNECT ON DATABASE mydb TO app_server;

-- 3) public 스키마에서의 CREATE 권한(DDL)과 기타 권한 회수
REVOKE ALL ON SCHEMA public FROM app_server;
REVOKE CREATE ON SCHEMA public FROM app_server;

-- 4) public 스키마 내 테이블들에 대해서 DML(SELECT, INSERT, UPDATE, DELETE)만 허용
GRANT USAGE ON SCHEMA public TO app_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_server;

-- 5) 앞으로 생성되는 테이블에도 자동으로 DML 권한을 부여하고 싶다면:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_server;

-- 6) 이미 존재하는 테이블의 Owner가 'app_server'라면, DROP TABLE을 막기 위해 Owner를 바꿔야 함
-- Owner는 Drop table이 가능하기 때문
-- 최소한의 권한만 부여
ALTER TABLE user OWNER TO admin_role;
REVOKE ALL ON TABLE user FROM app_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE user TO app_server;

이런 식으로 운영 환경에서 DROP 권한을 아예 막아두면, Prisma가 DROP TABLE 같은 명령을 시도해도 권한 에러로 막힌다. 결과적으로 개발자가 실수로 치명적인 명령을 내리지 못하게 통제할 수 있다.

정리하자면: 운영 DB에는 슈퍼유저를 최소화하고, 개발자 계정에는 필요한 최소 권한만 부여한다. 특히 CREATE/DROP 등 DDL 권한은 원칙적으로 막고, 실제 스키마 변경은 승인 절차를 거친 관리자만 수행하도록 한다.

2. 날아가도 복구할 수 있게 설정해둘걸..

내가 날린 뒤에 복구하지 못했던 이유는 이전까지 아무런 백업 설정을 해두지 않았기 때문이다. 이제부터는 복구할 수 있는 방법들을 알아보고, 직접 복구 시뮬레이션을 해보고자 한다. 복구 실험은 Ubuntu 24.04.1에 설치된 PostgreSQL 16.6 버전에서 진행했다.

2.1 논리 백업

논리 백업은 데이터베이스의 내용을 SQL 문장 형태로 추출하는 백업 방식이다. PostgreSQL에서는 pg_dump를 사용하여 논리 백업을 수행할 수 있다.

논리 백업의 특징은 다음과 같다.

  • 호환성
    • SQL 문으로 저장되어 다른 버전의 PostgreSQL이나 심지어 다른 DBMS로도 마이그레이션이 가능하다.
    • 예를 들어 PostgreSQL 13에서 백업한 내용을 PostgreSQL 16에 복원할 수 있다.
  • 선택적 백업 가능
    • 특정 테이블이나 스키마만 선택하여 백업할 수 있다.
    • 데이터만 백업하거나, 스키마만 백업하는 등 유연한 백업이 가능하다.
  • 백업 파일 내용 확인/수정 가능
    • 백업 파일이 텍스트 형태이므로 내용을 직접 확인하고 필요한 경우 수정도 가능하다.

pg_dump 명령을 정기적으로 실행되도록 Cron Job을 걸어두면, 사고 발생 시 최소한 최근 백업본으로는 되돌릴 수 있다. 다만 사고 직전 시점까지 복원하기는 어렵다.

2.2 물리 백업

논리 백업은 사고 시점의 최근 백업본까지만 복원할 수 있다는 한계가 있다. 하지만 물리 백업과 WAL 아카이빙을 함께 사용하면 특정 시점으로 정밀하게 복원할 수 있다.

WAL(Write-Ahead Logging)이란?

PostgreSQL은 데이터 변경사항을 먼저 WAL에 기록한 후에 실제 데이터 파일을 수정한다. 이는 시스템 충돌이 발생했을 때 WAL을 통해 일관성 있는 상태로 복구할 수 있게 해주는 메커니즘이다.

기본적으로 WAL 파일은 덮어씌워진다. 그러나 WAL 파일 아카이빙을 활성화하면 이 로그들을 영구적으로 보관할 수 있다.

PITR(Point-In-Time Recovery)

WAL 아카이빙을 하면 백업 시점을 지정하여 복구할 수 있다. 이렇게 시점을 지정하는 것을 PITR(Point In Time Recover)라고 한다.

다만 WAL 아카이빙은 DB의 모든 변경사항을 기록하므로, 디스크 공간을 충분히 확보해야 한다. 오래된 WAL 파일은 주기적으로 다른 저장소로 이동하거나 삭제하는 정책이 필요하다.

2.3 백업 실습

두 백업 중 논리 백업을 도전해보았다.
우리의 프로젝트는 세밀한 시점 관리를 할 정도로 백업이 중요하다고 느껴지진 않았기 때문이다. 대신 이렇게 실수로 DB를 날릴 경우만을 대비하여, 상대적으로 간단해보이는 논리 백업을 선택하게 되었다.

pg_dump 명령어를 사용하면 SQL 스크립트 형식 등으로 DB 백업을 할 수 있다. pg_dump를 사용하면 특정 테이블만을 선별하여 백업할 수 있고, pg_dumpall을 사용하면 DB 전체를 백업할 수 있다.
(이번 시뮬레이션에서는 pg_dumpall을 사용해보았다.)

테스트를 위해 다음과 같이 임의의 user 테이블을 만들었다.

testdb=# CREATE TABLE user(
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
username VARCHAR(50) NOT NULL
);

testdb=# INSERT INTO user (username) VALUES ('user1'), ('user2'), ('user3');

그리고 pg_dumpall을 사용하여 덤프를 생성했다. 먼저 백업 디렉토리를 만들고 적절한 권한을 설정했다:

# 백업 디렉토리 생성
sudo mkdir /var/backups/postgresql
# PostgreSQL 사용자(postgres)에게 소유권 부여
sudo chown postgres:postgres /var/backups/postgresql
# 디렉토리 권한 설정
sudo chmod 755 /var/backups/postgresql
  • 백업을 실행하는 주체가 postgres이므로 postgres에게 소유권을 부여했다. postgresql은 보안 상 postgres 사용자로만 백업 파일을 생성할 수 있기 때문이다.
  • 755에서는, 소유자(postgres)가 읽기 쓰기 실행 모두 할 수 있도록 하였으며, 다른 사용자들은 읽을 수는 있되 수정은 못하도록 권한을 부여해주었다.

그 다음 postgres 사용자로 전체 DB를 백업했다:

sudo -u postgres bash -c 'pg_dumpall > /var/backups/postgresql/db_backup_$(date +%Y%m%d).sql'

이제 복원이 잘 되는지 확인해보자.
DROP TABLE로 user 테이블을 날려보았다.

testdb=# drop table user;
DROP TABLE
testdb=# select * from user;
ERROR:  relation "user" does not exist
LINE 1: select * from user;
                      ^

이제 이 user 테이블을 복원해보자.

sudo -u postgres psql -f /var/backups/postgresql/db_backup_20250112.sql

다음과 같이 데이터가 성공적으로 복원된 것을 확인할 수 있다!

testdb=# select * from user;
 id | username
----+----------
  1 | user1
  2 | user2
  3 | user3
(3 rows)

마치며

실수로 운영 DB를 날려버린 순간의 충격과 팀원들에 대한 미안함은 아직도 생생하다. 경고 문구를 한 번만 더 꼼꼼히 읽었다면 막을 수 있었던 실수였다. 그래도 팀원들이 책망하지 않아주시고 오히려 '속시원히 리팩토링 할 수 있게 되었다'고 해주셔서 감사했다.

이번 실수를 통해 두 가지 중요한 교훈을 얻었다.
1. 미연에 사고를 방지해둘 것
2. 사고가 터져도 복구할 수 있는 체계를 갖춰둘 것

사람은 누구나 실수할 수 있다. 이 글을 보는 분들 모두 운영 DB에 백업과 보안 설정을 꼼꼼히 해두길 바란다.

참고

profile
이지호
이전 포스트

PM2 환경 설정 트러블슈팅 (Windows 환경 문제)

다음 포스트

RBAC 도입을 통한 권한 관리 로직 개선기

0개의 댓글

관련 채용 정보