-- PostgreSQL RBAC Setup and Validation Script
--
-- Purpose:
--   Create and validate a production-shaped PostgreSQL role-based access control model.
--
-- What this script does:
--   1. Creates group roles and a login role placeholder
--   2. Grants role membership
--   3. Protects a target schema
--   4. Grants table and sequence privileges by function
--   5. Sets default privileges for future objects
--   6. Provides validation queries for review
--
-- Safety notes:
--   - Review all placeholders before executing.
--   - Run as a superuser or a role with equivalent privilege-management rights.
--   - This script does not drop roles, schemas, or objects.
--   - It uses placeholders instead of credentials and environment-specific names.
--
-- -----------------------------------------------------------------------------
-- Parameters / placeholders
-- -----------------------------------------------------------------------------
-- Replace the values below before running.
--
-- Target schema that contains protected application objects.
-- Example: app_data
\set target_schema 'app_data'

-- Login role used by the application or operator.
-- Example: app_service
\set login_role 'app_service'

-- Group roles for authorization.
-- Example: app_reader, app_writer, db_admins
\set reader_role 'app_reader'
\set writer_role 'app_writer'
\set admin_role  'db_admins'

-- Optional role that owns future objects created by migrations.
-- Example: app_owner
\set owner_role 'app_owner'

-- -----------------------------------------------------------------------------
-- 1) Create roles
-- -----------------------------------------------------------------------------
-- Group roles should usually not log in.
CREATE ROLE :reader_role NOLOGIN;
CREATE ROLE :writer_role NOLOGIN;
CREATE ROLE :admin_role  NOLOGIN;

-- Login role for the application or operator.
-- Password intentionally omitted.
CREATE ROLE :login_role LOGIN;

-- Optional ownership role for migrations and object creation.
-- If you already have a dedicated owner role, keep this as a placeholder.
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_roles
        WHERE rolname = :'owner_role'
    ) THEN
        EXECUTE format('CREATE ROLE %I NOLOGIN', :'owner_role');
    END IF;
END
$$;

-- -----------------------------------------------------------------------------
-- 2) Grant role membership
-- -----------------------------------------------------------------------------
-- Grant only the memberships that are intentionally required.
GRANT :reader_role TO :login_role;
-- Uncomment only if the login role must also write:
-- GRANT :writer_role TO :login_role;

-- -----------------------------------------------------------------------------
-- 3) Protect the schema
-- -----------------------------------------------------------------------------
-- Revoke broad access to public schema objects.
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- Ensure the protected schema exists before applying grants.
-- This is a validation-friendly check; it does not create the schema.
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.schemata
        WHERE schema_name = :'target_schema'
    ) THEN
        RAISE NOTICE 'Schema % does not exist yet. Create it before continuing.', :'target_schema';
    END IF;
END
$$;

-- Schema usage for the intended roles.
GRANT USAGE ON SCHEMA :target_schema TO :reader_role;
GRANT USAGE ON SCHEMA :target_schema TO :writer_role;

-- Grant CREATE only if the role must create objects in the schema.
-- Uncomment only when required:
-- GRANT CREATE ON SCHEMA :target_schema TO :owner_role;

-- -----------------------------------------------------------------------------
-- 4) Grant object privileges by function
-- -----------------------------------------------------------------------------
-- Read-only access.
GRANT SELECT ON ALL TABLES IN SCHEMA :target_schema TO :reader_role;

-- Read/write access.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA :target_schema TO :writer_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA :target_schema TO :writer_role;

-- If views are used for read access, grant SELECT on views as needed.
-- Note: views are included in ALL TABLES only for some privilege workflows; validate in your environment.
-- Example:
-- GRANT SELECT ON ALL TABLES IN SCHEMA :target_schema TO :reader_role;

-- -----------------------------------------------------------------------------
-- 5) Set default privileges for future objects
-- -----------------------------------------------------------------------------
-- IMPORTANT:
--   Default privileges must be set by the role that creates future objects.
--   Run these commands as the real object owner or migration role.

-- Default privileges for tables created by the owner role in the target schema.
ALTER DEFAULT PRIVILEGES FOR ROLE :owner_role IN SCHEMA :target_schema
GRANT SELECT ON TABLES TO :reader_role;

ALTER DEFAULT PRIVILEGES FOR ROLE :owner_role IN SCHEMA :target_schema
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO :writer_role;

-- Default privileges for sequences created by the owner role in the target schema.
ALTER DEFAULT PRIVILEGES FOR ROLE :owner_role IN SCHEMA :target_schema
GRANT USAGE, SELECT ON SEQUENCES TO :writer_role;

-- -----------------------------------------------------------------------------
-- 6) Optional hardening checks
-- -----------------------------------------------------------------------------
-- Review whether the public schema should remain accessible.
-- You can also revoke create privileges on public if your policy requires it.
-- Example:
-- REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- -----------------------------------------------------------------------------
-- 7) Validation queries
-- -----------------------------------------------------------------------------
-- List roles and memberships.
SELECT
    r.rolname AS role_name,
    r.rolcanlogin AS can_login,
    ARRAY_REMOVE(ARRAY_AGG(m.rolname), NULL) AS member_of
FROM pg_roles r
LEFT JOIN pg_auth_members am ON am.member = r.oid
LEFT JOIN pg_roles m ON m.oid = am.roleid
WHERE r.rolname IN (:'login_role', :'reader_role', :'writer_role', :'admin_role', :'owner_role')
GROUP BY r.rolname, r.rolcanlogin
ORDER BY r.rolname;

-- Schema privileges summary.
SELECT
    n.nspname AS schema_name,
    pg_get_userbyid(n.nspowner) AS owner,
    n.nspacl AS acl
FROM pg_namespace n
WHERE n.nspname IN ('public', :'target_schema')
ORDER BY n.nspname;

-- Table privileges in the target schema.
SELECT
    table_schema,
    table_name,
    privilege_type,
    grantee
FROM information_schema.table_privileges
WHERE table_schema = :'target_schema'
ORDER BY table_name, grantee, privilege_type;

-- Sequence privileges in the target schema.
SELECT
    sequence_schema,
    sequence_name,
    privilege_type,
    grantee
FROM information_schema.sequence_privileges
WHERE sequence_schema = :'target_schema'
ORDER BY sequence_name, grantee, privilege_type;

-- Default privileges for the target schema.
SELECT
    defaclrole::regrole AS owner_role,
    defaclnamespace::regnamespace AS schema_name,
    defaclobjtype AS object_type,
    defaclacl AS acl
FROM pg_default_acl
WHERE defaclnamespace = (SELECT oid FROM pg_namespace WHERE nspname = :'target_schema')
ORDER BY owner_role, object_type;

-- -----------------------------------------------------------------------------
-- 8) Manual access tests to run from separate sessions
-- -----------------------------------------------------------------------------
-- Run these from a non-privileged session connected as the intended login role.
--
-- As reader role:
--   SELECT * FROM :target_schema.some_table LIMIT 1;
--
-- As writer role:
--   INSERT INTO :target_schema.some_table (column1, column2) VALUES ('example', 'example');
--   UPDATE :target_schema.some_table SET column1 = 'updated' WHERE ...;
--   DELETE FROM :target_schema.some_table WHERE ...;
--
-- Sequence-backed insert test:
--   INSERT INTO :target_schema.some_table DEFAULT VALUES;
--
-- Expected result:
--   - Reader can read but cannot write.
--   - Writer can read and write as intended.
--   - Unauthorized schemas remain inaccessible.
--   - Sequence-backed inserts succeed only if sequence privileges are correct.

-- -----------------------------------------------------------------------------
-- 9) Notes for production use
-- -----------------------------------------------------------------------------
-- - Keep login roles and group roles separate.
-- - Grant privileges to group roles, not individual users, whenever possible.
-- - Verify the object owner before relying on default privileges.
-- - Re-test whenever new tables, sequences, or schemas are added.
-- - Avoid granting CREATE on public unless there is a clear operational need.