行レベルセキュリティポリシー

行レベルセキュリティとは

テーブルでの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;

特定のロールへの 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;

理解度チェック

レッスンのまとめ