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>
23 lines
1.3 KiB
SQL
23 lines
1.3 KiB
SQL
-- Remove anonymous read/listing access to the receipts storage bucket.
|
|
-- pg-test: covered-by tests/pg/db-advisor-lockdowns.pg.test.ts
|
|
--
|
|
-- Supabase advisor (public_bucket_allows_listing): the receipts storage
|
|
-- bucket is public and carried an anon SELECT policy on storage.objects
|
|
-- (receipts_public_read, USING bucket_id = 'receipts'), which let anonymous
|
|
-- clients LIST every object in the bucket through the storage API.
|
|
--
|
|
-- Investigation (2026-07-09): the bucket is unused. No code references it
|
|
-- (no storage.from('receipts'), no getPublicUrl or signed URL against it),
|
|
-- the public.receipts table has 0 rows in prod, and the bucket holds 2
|
|
-- orphan objects from 2026-02-26 (early development). Nothing depends on
|
|
-- anonymous reads or listing, so the policy can go. Authenticated own-folder
|
|
-- policies (receipts_select/insert/delete) are untouched, and direct
|
|
-- public-URL object reads are served without RLS while the bucket's public
|
|
-- flag stays on; whether to flip the bucket private or delete it outright is
|
|
-- a separate product decision, documented in the PR.
|
|
--
|
|
-- The bucket and its policies were created outside the migration history
|
|
-- (dashboard), hence IF EXISTS: fresh-from-migrations databases (pg-real CI,
|
|
-- preview branches) do not have the policy.
|
|
DROP POLICY IF EXISTS "receipts_public_read" ON storage.objects;
|