행 수준 보안 정책

행 수준 보안이란 무엇인가요

테이블에서 RLS 활성화

-- Create a sample table
CREATE TABLE orders (
  id        SERIAL PRIMARY KEY,
  owner     TEXT NOT NULL,
  amount    NUMERIC(10,2)
);

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

첫 번째 정책 만들기

-- Allow each user to see only their own orders
CREATE POLICY orders_owner_policy
  ON orders
  FOR SELECT
  USING (owner = current_user);

USING과 WITH CHECK 절

-- Allow users to select and insert only their own rows
CREATE POLICY orders_isolation
  ON orders
  FOR ALL
  USING       (owner = current_user)
  WITH CHECK  (owner = current_user);

정책 범위: FOR SELECT, INSERT, UPDATE, DELETE

-- Everyone can read all orders
CREATE POLICY read_all_orders
  ON orders
  FOR SELECT
  USING (true);

-- But each user can only update their own orders
CREATE POLICY update_own_orders
  ON orders
  FOR UPDATE
  USING       (owner = current_user)
  WITH CHECK  (owner = current_user);

session_user와 current_user 사용

-- Inspect the current identity inside a query
SELECT current_user, session_user;

특정 ROLE에 RLS 적용하기

-- Policy only for the 'app_user' role
CREATE POLICY app_user_policy
  ON orders
  FOR SELECT
  TO app_user
  USING (owner = current_user);

-- Separate permissive policy for 'admin' role
CREATE POLICY admin_full_access
  ON orders
  FOR ALL
  TO admin
  USING (true)
  WITH CHECK (true);

PERMISSIVE 정책과 RESTRICTIVE 정책

-- Restrictive policy: block access to archived orders for everyone
CREATE POLICY no_archived_rows
  ON orders
  AS RESTRICTIVE
  FOR SELECT
  USING (amount > 0);

RLS 우회: BYPASSRLS와 테이블 소유자

-- Force the table owner to also obey RLS policies
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Grant BYPASSRLS to a trusted service account
ALTER ROLE service_account BYPASSRLS;

정책 수정 및 삭제

-- Rename a policy
ALTER POLICY orders_owner_policy ON orders
  RENAME TO user_isolation_policy;

-- Update the USING expression
ALTER POLICY user_isolation_policy ON orders
  USING (owner = current_user AND amount >= 0);

-- Remove a policy
DROP POLICY admin_full_access ON orders;

-- Disable RLS entirely on the table
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

실제 패턴: 멀티 테넌트 데이터 격리

-- Table with tenant isolation column
CREATE TABLE documents (
  id         SERIAL PRIMARY KEY,
  tenant_id  TEXT NOT NULL,
  title      TEXT
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy reads the tenant from a session variable
CREATE POLICY tenant_isolation
  ON documents
  FOR ALL
  USING       (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK  (tenant_id = current_setting('app.tenant_id'));

-- Application sets the variable before running queries
SELECT set_config('app.tenant_id', 'tenant_42', true);

-- Now only tenant_42 documents are visible
SELECT * FROM documents;

이해도 확인

레슨 요약