9.4 컬럼·뷰 권한

테이블 단위 권한(9.1)만으로는 모자란 경우가 있습니다. 어떤 사용자는 한 테이블의 일부 컬럼만 봐야 하거나, 가공·필터링한 결과만 보여야 합니다. 이럴 때 쓰는 것이 컬럼 단위 권한과 뷰(view) 권한, 그리고 함수 권한을 가르는 SECURITY DEFINER/INVOKER입니다.

컬럼 단위 권한

GRANT SELECT (col1, col2) ON table TO role 형식으로 특정 컬럼만 권한 부여.

-- users.email, users.phone 은 민감 — 일부 역할은 못 보게
GRANT SELECT (id, name) ON users TO app_readers;
-- email, phone에는 SELECT 권한 없음

확인:

SELECT column_name, privilege_type
  FROM information_schema.column_privileges
 WHERE table_name = 'users' AND grantee = 'app_readers';

INSERT·UPDATE도 컬럼 단위 가능:

GRANT INSERT (name, status) ON users TO app_writers;
-- email, phone에는 INSERT 권한 없음 (기본값 또는 NULL만)

함정

-- app_readers가 모든 컬럼을 SELECT 시도하면 ERROR
SELECT * FROM users;
-- ERROR: permission denied for column email

-- 명시적으로 허용된 컬럼만 SELECT 가능
SELECT id, name FROM users;
-- 정상

운영 코드의 SELECT *을 모두 명시 컬럼으로 바꿔야 하는 부담입니다. 그래서 컬럼 권한보다 뷰가 더 흔히 쓰입니다.

뷰로 컬럼 제한

CREATE VIEW users_public AS
  SELECT id, name, created_at
  FROM users;

GRANT SELECT ON users_public TO app_readers;
REVOKE ALL ON users FROM app_readers;

뷰는 재작성 단계에서 underlying 쿼리로 풀립니다(5.1). 어떤 권한으로 underlying 테이블에 접근하는지가 핵심이고, 여기에는 두 가지 모드가 있습니다.

기본은 뷰 소유자 권한으로 underlying 테이블에 접근하고, PostgreSQL 15+에서 WITH (security_invoker = true)를 주면 호출자 권한과 RLS가 적용됩니다.

security_barrier 뷰

기본 뷰는 옵티마이저가 뷰 안과 바깥의 WHERE를 자유롭게 섞을 수 있습니다. 보안에 쓰는 뷰는 이게 위험합니다.

-- 정의: salary가 1억 미만인 사람만 보이는 뷰
CREATE VIEW employees_public AS
  SELECT id, name, salary
  FROM employees
  WHERE salary < 100000000;

-- 사용자가:
SELECT * FROM employees_public
 WHERE name = pg_terminate_backend(1234);   -- 부작용 있는 함수

옵티마이저가 사용자 WHERE를 underlying 쿼리에 먼저 평가하면 salary 조건 통과 전에 pg_terminate_backend(1234)가 호출돼 row가 leak되거나 부작용이 일어납니다.

해결: security_barrier:

CREATE VIEW employees_public WITH (security_barrier = true) AS
  SELECT id, name, salary
  FROM employees
  WHERE salary < 100000000;

옵티마이저가 뷰의 WHERE를 먼저 평가하도록 강제합니다. 보안용 뷰에는 항상 security_barrier = true를 붙입니다.

updatable view

단순 뷰는 INSERT/UPDATE/DELETE 가능 (auto-updatable). 조건:

• 단일 테이블 FROM
• 집계·GROUP BY·DISTINCT·LIMIT 없음
• 모든 컬럼이 단순 컬럼 참조

CREATE VIEW active_users AS
  SELECT id, name, email FROM users WHERE deleted_at IS NULL;

-- 자동 updatable
INSERT INTO active_users (name, email) VALUES ('a','a@b.com');
-- users 에 deleted_at = NULL로 자동 삽입

복잡한 뷰는 INSTEAD OF 트리거로 직접 변환 로직 정의합니다.

WITH CHECK OPTION

뷰에 INSERT/UPDATE 시 뷰의 조건을 위반하는 row가 들어가지 않게 강제.

CREATE VIEW active_users AS
  SELECT id, name, email FROM users WHERE deleted_at IS NULL
  WITH CHECK OPTION;

-- 정상
INSERT INTO active_users (name, email) VALUES ('a','a@b.com');

-- ERROR — 조건을 우회하려는 시도
UPDATE active_users SET deleted_at = now() WHERE id = 1;
-- ERROR:  new row violates check option for view "active_users"

WITH CASCADED CHECK OPTION은 뷰 위에 또 뷰가 있을 때 모든 조건 체크.

SECURITY DEFINER vs INVOKER

함수 권한 컨텍스트의 두 모드.

옵션	의미
SECURITY INVOKER (기본)	호출자(현재 사용자)의 권한으로 함수 본문 실행
SECURITY DEFINER	함수 생성자의 권한으로 본문 실행

DEFINER는 사용자가 직접 만질 수 없는 데이터를 정해진 함수로만 접근하게 하는 패턴입니다.

-- 일반 사용자는 audit_log 직접 INSERT 불가
REVOKE ALL ON app.audit_log FROM app_writers;

-- 함수로만 추가 가능
CREATE FUNCTION log_event(event text) RETURNS void AS $$
  INSERT INTO app.audit_log(event, created_at, user_name)
    VALUES (event, now(), session_user);
$$ LANGUAGE sql
SECURITY DEFINER
SET search_path = app, pg_temp;   -- 쓰기 권한이 통제된 스키마만, pg_temp는 마지막

GRANT EXECUTE ON FUNCTION log_event(text) TO app_writers;

SECURITY DEFINER의 함정

함정	대응
search_path가 사용자 제어 가능	SET search_path = app, pg_temp처럼 쓰기 권한이 통제된 스키마만 두고 pg_temp는 마지막에
함수 본문이 사용자 입력을 SQL로 연결	SQL injection 위험 — 파라미터화
잊혀진 SECURITY DEFINER 함수가 슈퍼유저 권한 노출	정기 감사

-- 위험한 예
CREATE FUNCTION bad_lookup(t text) RETURNS setof record AS $$
  EXECUTE 'SELECT * FROM ' || t;   -- t를 그대로 SQL에 — SQL injection
$$ LANGUAGE plpgsql SECURITY DEFINER;

search_path와 보안

PostgreSQL은 객체 이름을 search_path 순서로 해석. 사용자가 search_path를 조작해 다른 스키마의 동명 객체로 함수가 흘러가게 만들 수 있습니다.

운영 베스트:

CREATE FUNCTION ...
  SET search_path = app, pg_temp
  AS $$ ... $$;

함수 안에서 search_path를 명시적으로 고정. SECURITY DEFINER에서는 필수입니다.

컬럼·뷰 vs RLS 선택

요구사항	권장
일부 컬럼 숨김	뷰 (security_barrier=true) 또는 컬럼 권한
일부 row 숨김	RLS
일부 row + 일부 컬럼	뷰 (둘 다 포함)
사용자별 다른 데이터	RLS
복잡한 가공·변환	뷰 + WITH CHECK OPTION
정해진 함수로만 접근	SECURITY DEFINER 함수

운영 안티패턴

안티패턴	문제
보안용 뷰에 security_barrier 빠뜨림	우회 가능
SECURITY DEFINER 함수가 search_path 미고정	권한 escalation 가능
컬럼 권한과 뷰를 섞어 씀	권한 모델 불분명
INSTEAD OF 트리거에 비즈니스 로직 폭주	디버그·추적 어려움

정리

일부 컬럼만 노출할 때는 컬럼 권한(GRANT SELECT (col1, col2))이나 뷰를 쓰는데, SELECT * 부담이 적은 뷰 쪽이 실무에서 더 흔합니다. 보안용 뷰에는 security_barrier = true를 반드시 붙이고, INSERT/UPDATE가 뷰 조건을 벗어나지 못하게 하려면 WITH CHECK OPTION을 더합니다. 정해진 인터페이스로만 보호 데이터에 닿게 하려면 SECURITY DEFINER 함수를 쓰고, RLS와 결합하면 더 촘촘한 권한 모델이 됩니다.

• 보안용 뷰: security_barrier = true 필수
• SECURITY DEFINER 함수: 항상 search_path 명시 (권한 escalation 방어)