-- Migration 003: Database security hardening
-- Run as starpost_admin

-- ── 1. Revoke public schema CREATE from public role ───────────────────────
-- Prevents any user from creating objects in public schema by default
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- ── 2. Grant only what starpost_app needs ────────────────────────────────
-- Explicit grants on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO starpost_app;
GRANT USAGE, SELECT, UPDATE         ON ALL SEQUENCES  IN SCHEMA public TO starpost_app;
GRANT EXECUTE                       ON ALL FUNCTIONS  IN SCHEMA public TO starpost_app;

-- Future objects created by starpost_admin are also accessible to app
ALTER DEFAULT PRIVILEGES FOR USER starpost_admin IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES    TO starpost_app;
ALTER DEFAULT PRIVILEGES FOR USER starpost_admin IN SCHEMA public
  GRANT USAGE, SELECT, UPDATE         ON SEQUENCES  TO starpost_app;
ALTER DEFAULT PRIVILEGES FOR USER starpost_admin IN SCHEMA public
  GRANT EXECUTE                       ON FUNCTIONS  TO starpost_app;

-- ── 3. Verify — show access privileges ───────────────────────────────────
-- Run \dp in psql to verify starpost_app appears in all table ACLs
