38 lines
1.7 KiB
SQL
38 lines
1.7 KiB
SQL
-- ============================================================
|
|
-- CS2 Skin Tracker — read-only reporting/BI role
|
|
-- Run as a superuser (e.g. postgres) OR as skintracker_app,
|
|
-- connected to the skintracker database. Safe to re-run.
|
|
--
|
|
-- Replace the password placeholder before running.
|
|
-- This role can SELECT every table in the skintracker schema
|
|
-- (existing and future) and nothing else: no INSERT/UPDATE/
|
|
-- DELETE, no DDL, no access to other schemas.
|
|
-- ============================================================
|
|
|
|
-- 1. Login role, guarded so the script is idempotent.
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'skintracker_readonly') THEN
|
|
CREATE ROLE skintracker_readonly WITH LOGIN PASSWORD 'change-me-readonly-password';
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
-- 2. Allow the role to connect to the database and resolve names
|
|
-- in the skintracker schema by default.
|
|
GRANT CONNECT ON DATABASE skintracker TO skintracker_readonly;
|
|
GRANT USAGE ON SCHEMA skintracker TO skintracker_readonly;
|
|
ALTER ROLE skintracker_readonly SET search_path = skintracker;
|
|
|
|
-- 3. Read access to every existing table/view in the schema.
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA skintracker TO skintracker_readonly;
|
|
|
|
-- 4. Read access to tables the app role creates LATER (e.g. new
|
|
-- migrations). Without this, future tables would not be visible.
|
|
-- Must name the role that OWNS new objects — that is skintracker_app.
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE skintracker_app IN SCHEMA skintracker
|
|
GRANT SELECT ON TABLES TO skintracker_readonly;
|
|
|
|
-- 5. Belt-and-braces: ensure the role can never write to public either.
|
|
REVOKE CREATE ON SCHEMA public FROM skintracker_readonly;
|