9.4 컬럼·뷰 권한

-- 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';

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;
-- 정상

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;

-- 정의: 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);   -- 부작용 있는 함수

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

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로 자동 삽입

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"

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

-- 함수로만 추가 가능
CREATE FUNCTION log_event(event text) RETURNS void AS $$
  INSERT INTO audit_log(event, created_at, user_name)
    VALUES (event, now(), session_user);
$$ LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, public;   -- 보안 베스트 프랙티스

GRANT EXECUTE ON FUNCTION log_event(text) TO app_writers;

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

CREATE FUNCTION ...
  SET search_path = pg_catalog, public, app
  AS $$ ... $$;