9.3 행 수준 보안(RLS)

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  tenant_id int NOT NULL,
  user_id int,
  amount numeric,
  ...
);

-- RLS 활성
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 자기 테넌트만 보이게 하는 정책
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::int);

-- 애플리케이션이 접속 직후 설정
SET app.tenant_id = '42';

-- 이제 모든 SELECT는 tenant_id = 42만 자동
SELECT * FROM orders;
-- 옵티마이저가 자동으로 WHERE tenant_id = 42 추가

CREATE POLICY <name> ON <table>
  [ AS { PERMISSIVE | RESTRICTIVE } ]
  [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
  [ TO { role | PUBLIC | CURRENT_USER } ]
  USING ( <visibility_expression> )
  [ WITH CHECK ( <write_expression> ) ];

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE  ROW LEVEL SECURITY;   -- 소유자도 정책 적용

CREATE ROLE auditor BYPASSRLS;
GRANT pg_read_all_data TO auditor;

-- 사용자가 자기 row만 + 활성 row만 (두 정책 모두 PERMISSIVE)
CREATE POLICY p_own ON profiles
  USING (user_id = current_setting('app.user_id')::int);

CREATE POLICY p_active ON profiles
  AS RESTRICTIVE
  USING (deleted_at IS NULL);

CREATE POLICY p_tenant_insert ON orders
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

CREATE POLICY p_tenant_update ON orders
  FOR UPDATE
  USING      (tenant_id = current_setting('app.tenant_id')::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

SELECT schemaname, tablename, policyname, permissive,
       roles, cmd, qual, with_check
  FROM pg_policies
 WHERE schemaname = 'public';

CREATE POLICY p_self ON notes
  USING (owner_id = current_user::regrole::oid::int);

CREATE POLICY p_no_update ON audit_log
  AS RESTRICTIVE
  FOR UPDATE
  USING (false);

CREATE POLICY p_business_hours ON salaries
  USING (current_time BETWEEN '09:00' AND '18:00');

-- tenant_id에 인덱스 필수 (또는 파티션 키)
CREATE INDEX idx_orders_tenant_id ON orders(tenant_id);

 Seq Scan on orders
   Filter: (tenant_id = 42)    <- RLS 정책에서 추가

CREATE FUNCTION get_user(p_id int) RETURNS users AS $$
  SELECT * FROM users WHERE id = p_id;
$$ LANGUAGE sql SECURITY DEFINER;
-- 호출하는 누구든 모든 user를 볼 수 있게 됨