모바일 & 앱/Flutter & Dart

[Flutter/SQLite] Drift 로컬 DB에서 삭제한 텍스트가 평문으로 남는 이유와 PRAGMA secure_delete·다계정 격리 설계

kugorang 2026. 11. 19. 09:32

TL;DR 1. SQLite의 DELETE는 행이 쓰던 자리에 "다시 써도 됨" 표시만 남기므로, 삭제한 텍스트의 바이트가 .db 파일에 남습니다.
2. Fillday는 Drift setup 콜백에서 PRAGMA secure_delete = ON을 실행하고, 2,030바이트 마커를 파일 바이트에서 직접 찾는 테스트로 덮어쓰기를 확인했습니다.
3. 로그아웃은 DB 파일을 지우지 않고 외부 캘린더 미러만 비우며, JSON 내보내기는 계약 플래그로 외부 사본과 연결 행을 제외합니다.

쿠고랑 (kugorang) 기술 블로그 85호는 Fillday에 병합된 코드와 테스트를 근거로 로컬 DB의 삭제, 로그아웃, 내보내기 설계를 정리합니다.

1. SQL DELETE를 실행했는데도 .db 파일에 평문이 남는 이유

원인은 SQLite가 삭제 속도를 우선하는 데 있습니다. SQLite는 DB 파일을 일정한 크기의 구역으로 나누고, 구역 하나를 페이지라고 부릅니다. 기본 크기는 4,096바이트입니다. DELETE를 실행하면 SQLite는 행이 차지하던 바이트를 지우지 않고 "다시 써도 되는 자리"라는 표시만 남깁니다. 페이지 전체가 비면 SQLite는 빈 페이지를 free page로 분류하고, free page 번호를 모은 목록인 free-list에 올립니다.

결과는 두 가지입니다. 파일 크기가 줄지 않고, 새 데이터가 같은 자리를 덮어쓰기 전까지 삭제한 텍스트가 평문 바이트로 남습니다. .db 파일을 바이트 단위로 읽는 도구는 삭제한 문장을 찾아낼 수 있습니다.

비교 항목 VACUUM PRAGMA secure_delete = ON
임시 공간 배수 DB 파일 크기의 최대 2배 여유 공간 필요 추가 공간 없음
배타 잠금 여부 실행 내내 필요, 다른 읽기와 쓰기는 대기 불필요, 일반 쓰기 잠금만 사용
기존 잔여 바이트 처리 범위 파일 전체를 새로 써서 과거 free page까지 제거 설정을 켠 뒤 삭제한 내용만 0으로 덮음
복구 대기 충돌 위험 긴 트랜잭션 도중 앱이 종료되면 다음 실행의 저널 복구와 잠금 대기가 겹칠 수 있음 삭제 트랜잭션 크기가 그대로라 위험 낮음

Fillday는 삭제할 때마다 쓰기 작업이 조금 늘어나는 secure_delete를 기본 설정으로 골랐습니다.

2. Drift 환경에서 PRAGMA secure_delete = ON을 setup 콜백에 거는 이유

PRAGMA secure_delete는 DB 파일에 저장되지 않습니다. 설정은 연결 하나에만 적용되고, 연결을 닫으면 사라집니다. 따라서 앱이 DB를 여는 모든 경로에서 매번 실행해야 합니다.

Fillday에는 실행 전 SQL을 검사하는 CoreDbAdmissionInterceptor가 있고, CoreDbAdmissionInterceptor는 일반 customStatement('PRAGMA ...')를 차단할 수 있습니다. 그래서 Drift를 거치지 않고 원시 sqlite3 핸들에 직접 실행합니다. setup 콜백은 Drift가 첫 쿼리를 보내기 전에 원시 핸들을 넘겨받는 자리입니다. 백그라운드 아이솔레이트로 전달해야 하므로 콜백은 정적 함수로 선언합니다. 아래는 핵심만 줄인 발췌입니다.

static void _configureConnection(sqlite.Database database) {
  database.execute('PRAGMA secure_delete = ON');
}

static void _configureWalConnection(sqlite.Database database) {
  _configureConnection(database);
  database.execute('PRAGMA journal_mode=WAL');
  database.execute('PRAGMA synchronous=FULL');
}

// 연결 경로 세 곳
NativeDatabase.memory(setup: _configureConnection);
NativeDatabase.createInBackground(file, setup: _configureWalConnection);
NativeDatabase.opened(_configured(opened));

WAL 모드 DB는 _configureConnection(database), PRAGMA journal_mode=WAL, PRAGMA synchronous=FULL 순서로 초기화합니다. 덮어쓰기 설정이 첫 쓰기보다 먼저 켜집니다.

검증은 설정 조회 값이 아니라 파일 바이트로 합니다. 29바이트 토큰을 70회 반복한 2,030바이트 문자열을 넣고, 지우고, 연결을 닫은 뒤 파일 전체에서 토큰을 찾습니다. 실제 테스트 코드에 들어 있는 검증 절차 그대로입니다.

const token = 'FILLDAY_SECURE_DELETE_MARKER_'; // 29바이트
final marker = List.filled(70, token).join(); // 2,030바이트
await db.customStatement('CREATE TABLE t(x TEXT)');
await db.customStatement('INSERT INTO t(x) VALUES (?)', [marker]);
await db.customStatement('DELETE FROM t');
await db.close();

final raw = latin1.decode(await File(path).readAsBytes());
expect(raw.contains(token), isFalse);

latin1은 0부터 255까지 모든 바이트를 글자 하나로 바꾸므로 이진 파일도 오류 없이 문자열로 읽습니다. 토큰을 70회 반복했기 때문에 일부 조각만 남아도 테스트가 잡아냅니다.

한계도 있습니다. secure_delete 적용 전에 이미 free page에 있던 과거 바이트는 해당 페이지가 재사용될 때까지 파일에 남습니다.

3. 로그아웃 시 DB 파일을 지우지 않고 외부 캘린더 미러만 비우는 이유

DB 파일에는 사용자가 직접 쓴 계정 기록이 들어 있습니다. 로그아웃할 때 파일을 삭제하면 사용자가 같은 계정으로 다시 로그인했을 때 기록을 되찾지 못할 위험이 생깁니다. 반대로 calsync 모듈의 미러는 기기 캘린더에서 복사한 파생 사본이라, 지워도 재동기화로 다시 만들 수 있습니다. 그래서 Fillday는 파일을 남기고 미러만 비웁니다.

각 모듈은 onRecordStoreLeave(Ref) 훅에 정리 작업을 등록하고, calsync 훅은 미러 행을 톰스톤(삭제 표시만 남긴 행)으로 정리합니다. 설계 세부는 세 가지입니다.

  1. 실행 시점: 세션 스코프를 회수하는 _mask() 호출 직전에 실행합니다. _mask() 뒤에는 회수된 스코프의 쓰기가 no-op으로 처리되어 정리 쓰기가 무효가 됩니다.
  2. 시간 한도: 로그아웃 흐름은 _storeLeaveBudget = Duration(seconds: 3)까지만 기다립니다. 정리 작업은 shutdown.track(work)에 등록되므로 3초가 지나도 계속 실행되고, DB close()는 등록된 작업이 끝난 뒤에 진행됩니다. 화면 대기와 DB 닫기 대기가 분리됩니다.
  3. 순회 대상: 활성 모듈이 아니라 moduleRegistryProvider.all의 전체 모듈을 순회합니다. 사용자가 끈 모듈의 과거 데이터도 같은 DB 파일에 남아 있기 때문입니다. 한 모듈의 훅이 실패하면 실패를 저널에 기록하고 다음 모듈로 넘어갑니다.

4. 사용자 JSON 내보내기에서 외부 사본과 연결 고리를 걸러내는 계약 설계

기기 캘린더에서 복사한 일정에는 다른 사람이 쓴 제목과 계정 라벨이 들어 있으므로 내보내기 파일에 넣지 않습니다. 내보내기 코드에 모듈 ID를 직접 적으면 외부 사본을 다루는 모듈이 늘 때마다 내보내기 코드를 고쳐야 하고 누락이 생깁니다. Fillday는 ModuleDescriptor.excludeFromUserExport 계약 플래그를 두었고, 모듈이 플래그를 켜면 내보내기 서비스가 세 단계를 적용합니다.

  1. 소유 테이블 제외: calsync_accounts, calsync_calendars, calsync_links를 통째로 뺍니다.
  2. 공용 테이블 행 제외: timeline_entries에서 module_id NOT IN (?) 조건으로 뺍니다. 모듈 ID는 SQL 문자열에 붙이지 않고 바인드 값으로 전달합니다.
  3. 연결 행 제외: entry_links와 entry_reminders 중 미러 행을 가리키는 행을 NOT EXISTS 서브쿼리로 뺍니다.

서브쿼리는 연결 행이 가리키는 timeline_entries 행이 제외 모듈 소유인지만 확인하고, 톰스톤 여부는 조건에 넣지 않습니다. 따라서 살아 있는 미러 행과 톰스톤된 미러 행을 가리키는 연결이 모두 빠집니다. entry_uuid IS NULL인 날짜 단위 알림은 가리키는 행이 없어 서브쿼리 결과가 0건이고, NOT EXISTS가 참이 되어 내보내기에 남습니다.

5. 실측 검증 결과와 엔지니어링 경계

검증 대상 내용 결과
core/core_db PRAGMA secure_delete = ON, 2,030바이트 마커 바이트 스캔 flutter analyze 오류 0건, flutter test 전체 통과
app/test/record_store_leave_hooks_test.dart 전 모듈 순서 및 실패 저널 후 계속, 회수된 스코프 무호출, 부트 전 무호출 3개 시나리오 통과
packages/feature_calsync/test/repository_test.dart OS 권한 조회 0회 멱등 purge 및 재동기화 복원, 디스크립터 훅 경유 purge 2개 시나리오 통과
app/test/export_test.dart, export_service_test.dart 제3자 일정 텍스트, 계정 라벨, 외부 ID 노출 노출 0건 검증 통과

쿠고랑 (kugorang) 블로그의 엔지니어링 원칙은 "코드 수정 ≠ 모의 시험 ≠ 실기기 검증 ≠ 운영 반영"입니다. 표의 수치는 모의 시험 단계까지만 증명합니다. 실제 기기에서 꺼낸 DB 파일의 바이트 스캔과 운영 환경 반영은 별도 단계에서 확인합니다.

자주 묻는 질문 (FAQ)

Q1. VACUUM을 주기적으로 돌리면 secure_delete가 필요 없지 않나요?

VACUUM과 VACUUM 사이의 기간에는 삭제한 바이트가 파일에 남습니다. VACUUM은 최대 2배의 여유 공간과 배타 잠금도 요구합니다. 다만 secure_delete 적용 전의 과거 잔여 바이트는 VACUUM만 한 번에 지울 수 있으므로, 두 방식은 맡는 범위가 다릅니다.

Q2. Drift의 customStatement('PRAGMA secure_delete = ON')으로 실행해도 되는데 왜 setup 콜백을 쓰나요?

이유는 두 가지입니다. 첫째, CoreDbAdmissionInterceptor가 customStatement로 보낸 PRAGMA를 차단할 수 있습니다. 둘째, secure_delete는 연결 단위 설정이라 첫 쿼리보다 먼저, 모든 연결 경로에서 실행되어야 합니다. setup 콜백은 두 조건을 모두 만족합니다.

Q3. 로그아웃 시 3초 안에 정리가 안 끝나면 데이터가 남지 않나요?

3초는 로그아웃 흐름이 기다리는 시간의 상한이고, 작업을 취소하는 시점이 아닙니다. shutdown.track(work)에 등록된 정리 작업은 계속 실행되고, DB close()가 작업 종료를 기다립니다. purge는 멱등이라 여러 번 실행해도 결과가 같습니다. 정리 도중 앱 프로세스가 강제 종료되는 경우는 위 테스트의 검증 범위 밖입니다.

728x90
728x90