Jakob Wennberg
aec81cb7ad
Three Supabase-advisor findings from the 2026-07-09 production log triage: 1. exchange_rates (rls_policy_always_true): the exchange_rates_insert policy was WITH CHECK (true) for authenticated, letting any signed-in user poison the shared FX cache that feeds money math (amount_sek on ingested transactions, invoice SEK conversion). Migration 20260710100000 drops the policy and revokes INSERT from anon/authenticated; only the service role writes the cache now (the 05:00 enable-banking sync cron and the v1 API-key paths both use the service client). writeCachedRate() in lib/currency/riksbanken.ts was already fail-soft and never inspects the upsert result, so user-client paths (bank file import, refresh-exchange-rate) keep returning the fetched rate unchanged when the cache write is rejected; documented and covered by a new unit test. 2. journal_entry_lines (duplicate_index): idx_journal_entry_lines_entry and idx_journal_entry_lines_entry_id are byte-identical btree indexes on (journal_entry_id), verified via pg_indexes on prod. Migration 20260710101000 drops idx_journal_entry_lines_entry (created outside the migration history); the repo-defined _entry_id stays. 3. receipts bucket (public_bucket_allows_listing): receipts_public_read gave anon SELECT over every object in the bucket, enabling anonymous listing. The bucket is unused: no code references it, public.receipts has 0 rows in prod, 2 orphan objects from 2026-02-26. Migration 20260710102000 drops the anon policy; authenticated own-folder policies stay untouched. New tests/pg/db-advisor-lockdowns.pg.test.ts covers all three (authenticated INSERT rejected, SELECT still works, privilege revoked, duplicate index gone, anon cannot list receipts). Co-authored-by: Claude Fable 5 <noreply@anthropic.com>
29 lines
1.6 KiB
SQL
29 lines
1.6 KiB
SQL
-- Lock down writes to the shared exchange-rate cache.
|
|
-- pg-test: covered-by tests/pg/db-advisor-lockdowns.pg.test.ts
|
|
--
|
|
-- Supabase advisor (rls_policy_always_true): exchange_rates_insert allowed
|
|
-- any authenticated user to INSERT arbitrary rows (WITH CHECK (true)) into a
|
|
-- cache that feeds money math (amount_sek on ingested transactions, invoice
|
|
-- SEK conversion). Because the table is shared across all tenants and reads
|
|
-- take the first row for a (currency, rate_date) pair (UNIQUE constraint +
|
|
-- ignoreDuplicates on the writer), one malicious or buggy client could
|
|
-- poison a rate for every company.
|
|
--
|
|
-- New design: only the service role writes the cache. The 05:00
|
|
-- enable-banking sync cron (service role, the primary cache filler) bypasses
|
|
-- RLS and keeps working. User-client paths (bank file import execute,
|
|
-- refresh-exchange-rate, manual bank sync) still attempt the write; it is
|
|
-- now rejected and deliberately ignored: lib/currency/riksbanken.ts
|
|
-- writeCachedRate() is fail-soft and never inspects the upsert result, so
|
|
-- the fetched rate is returned to the caller exactly as before. Reads
|
|
-- (exchange_rates_select) are unchanged: the data is public reference data.
|
|
DROP POLICY IF EXISTS "exchange_rates_insert" ON public.exchange_rates;
|
|
|
|
-- Defense in depth: revoke the table privilege as well, so a future
|
|
-- always-true policy cannot silently reopen the hole. With RLS enabled and
|
|
-- no INSERT policy this is already denied; the REVOKE makes the intent
|
|
-- explicit and the rejection deterministic (permission denied).
|
|
REVOKE INSERT ON public.exchange_rates FROM anon, authenticated;
|
|
|
|
NOTIFY pgrst, 'reload schema';
|