-- 45_row_level_security_per_tenant.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: RLS: tenant/customer isolation.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.
SET search_path = bank, public;

-- RLS per tenant/customer: demo by limiting notes to owner
CREATE TABLE IF NOT EXISTS bank.customer_notes (
  note_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES bank.customers(customer_id),
  author TEXT NOT NULL,
  note TEXT
);
ALTER TABLE bank.customer_notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY notes_by_author ON bank.customer_notes USING (author = current_user);
