-- 22_security_definer_read_model.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: SECURITY DEFINER function for limited reads.
-- 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.

-- SECURITY DEFINER function to expose safe subset to analytics role
CREATE OR REPLACE FUNCTION bank.get_public_accounts()
RETURNS TABLE(account_id bigint, currency char(3), status text)
LANGUAGE sql SECURITY DEFINER AS $$
  SELECT account_id, currency, status FROM bank.accounts WHERE status='active';
$$;

REVOKE ALL ON bank.accounts FROM public;
GRANT EXECUTE ON FUNCTION bank.get_public_accounts() TO public;
