-- ============================================================
-- CS2 Skin Tracker — schema & role hardening
-- Run ONCE as a superuser (e.g. postgres), connected to the
-- skintracker database, before the app's first migration.
--
-- Replace the password placeholders before running.
-- The application/migration connection should authenticate as
-- the skintracker_app role.
-- ============================================================

-- 1. Application login role (least-privilege; not a superuser).
--    Skip the CREATE if the role already exists.
CREATE ROLE skintracker_app WITH LOGIN PASSWORD 'change-me-strong-password';

-- 2. Create the schema and make the app role its owner.
--    Because the app owns it, EF's `EnsureSchema` (CREATE SCHEMA IF
--    NOT EXISTS) becomes a no-op and the app can create/alter tables
--    here during `database update` without any rights on `public`.
CREATE SCHEMA IF NOT EXISTS skintracker AUTHORIZATION skintracker_app;

-- 3. Lock down the default `public` schema.
--    Historically every role had CREATE on public; revoke it so no
--    objects can be created there by accident. (PG15+ already removed
--    this by default, but being explicit is harmless and portable.)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 4. Make the app role use its own schema by default (so unqualified
--    object names resolve to skintracker, not public).
ALTER ROLE skintracker_app SET search_path = skintracker;

-- ------------------------------------------------------------
-- Optional: a read-only role for reporting / BI.
-- Uncomment if you need separate read-only access.
-- ------------------------------------------------------------
-- CREATE ROLE skintracker_readonly WITH LOGIN PASSWORD 'change-me-too';
-- GRANT USAGE ON SCHEMA skintracker TO skintracker_readonly;
-- GRANT SELECT ON ALL TABLES IN SCHEMA skintracker TO skintracker_readonly;
-- -- Apply automatically to tables created LATER by the app role:
-- ALTER DEFAULT PRIVILEGES FOR ROLE skintracker_app IN SCHEMA skintracker
--     GRANT SELECT ON TABLES TO skintracker_readonly;