PostgreSQL 01006 오류 원인과 해결 방법 완벽 가이드

#postgres #database #dba #sql

01006 privilege not revoked 는?

PostgreSQL 에러 코드 01006은 SQLSTATE 기준으로 WARNING: privilege not revoked에 해당하는 경고성 메시지입니다. 이 에러는 REVOKE 명령을 실행했을 때, 해당 권한이 실제로 대상 사용자(또는 역할)에게 부여되어 있지 않아 권한 회수 작업이 실질적으로 수행되지 않은 경우 발생합니다. 엄밀히 말하면 쿼리 실행 자체가 실패하는 ERROR가 아니라 WARNING 수준의 알림이지만, 보안 감사나 권한 관리 자동화 스크립트 환경에서는 예상치 못한 동작을 유발할 수 있어 반드시 주의가 필요합니다.

주요 발생 원인

1. 대상 역할(Role)에 해당 권한이 애초에 부여되지 않은 경우

가장 흔한 원인으로, REVOKE 대상 역할이 실제로 해당 권한을 보유하고 있지 않을 때 발생합니다. 예를 들어 app_user에게 특정 테이블에 대한 INSERT 권한을 부여한 적이 없는데, 권한 정리 스크립트에서 일괄적으로 REVOKE를 시도하면 이 경고가 출력됩니다. 운영 중 권한 변경 이력이 명확하지 않거나 여러 DBA가 함께 작업하는 환경에서 특히 자주 발생합니다.

2. 잘못된 권한 부여 계층(GRANT OPTION) 또는 권한 경로 불일치

PostgreSQL의 권한 모델은 "누가 누구에게 부여했는가"라는 그랜터(Grantor) 정보를 함께 저장합니다. user_a가 부여한 권한을 user_b가 REVOKE하려 하면, 실제 권한 소유 관계가 맞지 않아 회수가 이루어지지 않고 경고가 발생할 수 있습니다. 특히 GRANT OPTION을 통해 위임된 권한 체계가 복잡하게 얽혀 있는 경우 이 문제가 두드러집니다.

3. PUBLIC 역할 또는 스키마 수준 권한 혼동

PostgreSQL에는 모든 사용자가 암묵적으로 속하는 PUBLIC 역할이 존재하며, 기본적으로 public 스키마에 대한 일부 권한이 PUBLIC에 부여되어 있습니다. 특정 사용자에게서 권한을 회수하려 할 때, 사실 해당 권한이 사용자 개인이 아닌 PUBLIC 역할을 통해 부여된 경우라면 개별 사용자를 대상으로 한 REVOKE는 효과가 없고 경고만 발생합니다. 이 경우 PUBLIC을 대상으로 권한을 회수해야 실질적인 효과가 나타납니다.

해결 방법

원인 1 해결: 권한 보유 여부 사전 확인 후 REVOKE

REVOKE 전에 information_schema 또는 pg_catalog를 통해 권한 보유 여부를 먼저 확인하세요.

-- 특정 테이블에 대한 권한 목록 확인
SELECT grantee, table_schema, table_name, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE table_name = 'orders'
  AND grantee = 'app_user';

-- 권한이 확인된 경우에만 REVOKE 실행
REVOKE INSERT ON TABLE public.orders FROM app_user;

-- 스키마 수준의 권한 확인
SELECT nspname, nspacl
FROM pg_catalog.pg_namespace
WHERE nspname = 'public';

-- 함수 권한 확인
SELECT routine_name, grantee, privilege_type
FROM information_schema.role_routine_grants
WHERE grantee = 'app_user';

자동화 스크립트에서는 아래처럼 권한 존재 여부를 체크하는 로직을 추가하면 경고 없이 안전하게 처리할 수 있습니다.

-- 권한 보유 여부를 확인하는 DO 블록 예시
DO $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM information_schema.role_table_grants
    WHERE grantee = 'app_user'
      AND table_name = 'orders'
      AND privilege_type = 'INSERT';

    IF v_count > 0 THEN
        EXECUTE 'REVOKE INSERT ON TABLE public.orders FROM app_user';
        RAISE NOTICE '권한이 성공적으로 회수되었습니다.';
    ELSE
        RAISE NOTICE '해당 권한이 존재하지 않아 REVOKE를 건너뜁니다.';
    END IF;
END;
$$;

원인 2 해결: 그랜터(Grantor) 기반 권한 확인 및 정확한 회수

권한 부여 체계를 pg_catalog.pg_class와 aclexplode 함수를 통해 상세히 확인합니다.

-- 테이블 ACL 상세 분석 (grantor 포함)
SELECT 
    c.relname AS table_name,
    (aclexplode(c.relacl)).grantor::regrole AS grantor,
    (aclexplode(c.relacl)).grantee::regrole AS grantee,
    (aclexplode(c.relacl)).privilege_type,
    (aclexplode(c.relacl)).is_grantable
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'orders'
  AND n.nspname = 'public';

-- GRANT OPTION 포함 권한 회수 예시
-- app_manager가 부여한 권한을 회수할 때
SET ROLE app_manager;
REVOKE INSERT ON TABLE public.orders FROM app_user;
RESET ROLE;

-- CASCADE 옵션으로 위임된 권한까지 일괄 회수
REVOKE INSERT ON TABLE public.orders FROM app_manager CASCADE;

원인 3 해결: PUBLIC 역할 권한 분리 처리

-- PUBLIC 역할에 부여된 권한 확인
SELECT 
    nspname,
    (aclexplode(nspacl)).grantor::regrole AS grantor,
    (aclexplode(nspacl)).grantee::regrole AS grantee,
    (aclexplode(nspacl)).privilege_type
FROM pg_catalog.pg_namespace
WHERE nspname = 'public';

-- PUBLIC에서 스키마 접근 권한 회수 (PostgreSQL 15 이후 기본값 변경됨)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- PUBLIC에 부여된 테이블 권한 회수
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM PUBLIC;

-- 이후 필요한 사용자에게만 선택적으로 부여
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT ON TABLE public.orders TO app_user;

-- 현재 PUBLIC 권한 전체 점검
SELECT 
    table_schema,
    table_name,
    privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'PUBLIC'
ORDER BY table_schema, table_name;

예방 방법

1. 권한 관리 대장(Privilege Inventory)을 주기적으로 유지하고 IaC(Infrastructure as Code) 방식으로 관리하세요

권한 부여 및 회수 내역을 Git 저장소에서 SQL 파일로 버전 관리하면, 현재 DB 상태와 코드 상태 간의 불일치를 빠르게 파악할 수 있습니다. 배포 시마다 아래와 같은 권한 현황 스냅샷 쿼리를 실행하여 기록을 남기면 감사(Audit) 대응에도 유리합니다.

-- 전체 권한 현황 스냅샷 (정기 실행 권장)
SELECT 
    grantee,
    table_catalog,
    table_schema,
    table_name,
    privilege_type,
    is_grantable,
    now() AS snapshot_time
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('postgres', 'PUBLIC')
ORDER BY grantee, table_schema, table_name;

2. REVOKE 스크립트에 IF EXISTS 패턴에 준하는 방어 코드를 항상 포함하세요

PostgreSQL의 REVOKE 문은 SQL 표준과 달리 IF EXISTS 옵션을 지원하지 않기 때문에, 앞서 소개한 DO $$ ... $$ 블록 패턴이나 애플리케이션 레벨에서 권한 존재 여부를 사전 검증하는 루틴을 표준화해야 합니다. CI/CD 파이프라인에서 DB 마이그레이션 도구(Flyway, Liquibase 등)를 사용할 때도 권한 관련 스크립트는 반드시 멱등성(idempotency)을 보장하도록 작성하여, 동일 스크립트가 여러 번 실행되어도 오류나 경고가 발생하지 않도록 설계하세요.