PostgreSQL 자체에는 MySQL의 SET sql_safe_updates = 1;처럼 WHERE 절이 없는 UPDATE나 DELETE 문을 기본적으로 차단해 주는 내장 설정(GUC 파라미터)이 존재하지 않습니다. [1]
따라서 실수로 전체 데이터를 수정하거나 삭제하는 대형 사고를 막기 위해서는 DBeaver, DataGrip 같은 DB 툴의 설정을 이용하거나, Extension(확장 모듈) 또는 트리거(Trigger)를 활용해야 합니다.
가장 편리하고 널리 쓰이는 방법 순서대로 정리해 드립니다.

1. 사용 중인 DB 클라이언트 툴에서 설정하기 (가장 추천)

서버 설정을 바꾸지 않고 사용자의 실수만 방지하고 싶을 때 가장 안전하고 편리한 방법입니다.
  • DBeaver (디비버):
    1. 상단 메뉴의 윈도우 (Window) > 설정 (Preferences)으로 이동합니다.
    2. 데이터 편집기 (Data Editor) > 데이터 변경 (Data Changes) 메뉴를 선택합니다.
    3. "WHERE 절이 없는 UPDATE/DELETE 실행 시 경고" (Confirm data change / Check for missing WHERE clause) 옵션을 체크합니다. [2, 3]
  • DataGrip (데이터그립):
    • 기본적으로 WHERE 절이 없는 DELETE 또는 UPDATE 문을 실행하려고 하면 "Unsafe query: 'Delete' statement without 'where' clears all data in the table"라는 강력한 경고 창을 띄워 실행을 일차적으로 차단해 줍니다. [4, 5]

2. PostgreSQL 확장 모듈 pg-safeupdate 사용하기

만약 DB 서버 수준에서 WHERE 절이 없는 쿼리 자체를 에러를 내며 거부하도록 강제하고 싶다면, 오픈소스 확장 모듈인 pg-safeupdate를 사용할 수 있습니다. [6]
  1. 설치 및 설정 (postgresql.conf):
    postgresql.conf 파일의 shared_preload_libraries 항목에 추가합니다. 
    shared_preload_libraries = 'pg-safeupdate'
  2. 특정 세션에서만 켜기:
    서버 전체에 적용하지 않고, 특정 커넥션이 시작될 때마다 아래 명령어를 실행하도록 설정할 수도 있습니다. 
    LOAD 'pg-safeupdate';
    이 모듈이 활성화되면 WHERE 절 없이 UPDATE나 DELETE를 실행할 때 PostgreSQL이 자동으로 에러(ERROR)를 발생시키고 쿼리를 취소합니다. [6]

3. PL/pgSQL 트리거(Trigger) 활용하기 (특정 테이블 보호)

특정 중요 테이블에 대해 WHERE 절이 없으면 무조건 실행을 막고 싶을 때, 각 문장(Statement)이 실행되기 전 행의 개수를 검사하는 트리거를 만들 수 있습니다. 다만, 이 방법은 테이블 전체를 지우려고 의도한 쿼리(WHERE 1=1 등)까지 구분하기는 어렵다는 한계가 있습니다.
CREATE OR REPLACE FUNCTION block_unqualified_modifications()
RETURNS TRIGGER AS $$
BEGIN
    -- 실행 전 조건(WHERE)을 거치지 않아 영향받는 행의 예측 수가 
    -- 테이블 전체 행 수와 똑같거나 비정상적으로 많을 때 차단하는 로직을 구사하거나,
    -- 안전을 위해 전역적인 실수를 방지하는 용도로 커스텀 가이드를 적용할 수 있습니다.
    RAISE EXCEPTION 'WHERE 절이 없는 안전하지 않은 쿼리 요청이 차단되었습니다.';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

💡 실수를 방지하는 업무 습관 팁

  1. 트랜잭션 수동 시작하기: 변경 쿼리를 실행하기 전 항상 BEGIN;을 먼저 실행하세요. 실행 후 결과를 SELECT로 확인한 뒤, 잘못되었다면 ROLLBACK;, 잘 들어갔다면 COMMIT;을 수행하는 습관을 들이면 대형 사고를 예방할 수 있습니다.
  2. SELECT로 먼저 검증하기: UPDATE / DELETE 문을 작성하기 전, 동일한 WHERE 절을 가진 SELECT 문을 먼저 실행하여 내가 수정/삭제할 대상 행이 맞는지 눈으로 확인하는 것이 안전합니다.

 

티스토리툴바