Connect Postgres

-- ============================================================
-- INPUTS - edit these before running
-- ============================================================
DO $$
DECLARE
  v_username   TEXT    := 'skoot_readonly_user';   -- login username
  v_password   TEXT    := 'replace_with_strong_password';   -- login password
  v_role_name  TEXT    := 'skoot_readonly_role';   -- role to create/grant
  v_schemas    TEXT[]  := ARRAY['my_schema_1', 'my_schema_2'];      -- schemas to grant access to
  v_database   TEXT    := 'postgres';              -- or hardcode: 'postgres'
BEGIN
 
  -- ============================================================
  -- 1. Create the role (no login, just holds privileges)
  -- ============================================================
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = v_role_name) THEN
    EXECUTE format('CREATE ROLE %I NOLOGIN', v_role_name);
    RAISE NOTICE 'Created role: %', v_role_name;
  ELSE
    RAISE NOTICE 'Role already exists, skipping: %', v_role_name;
  END IF;
 
  -- ============================================================
  -- 2. Create the login user
  -- ============================================================
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = v_username) THEN
    EXECUTE format(
      'CREATE USER %I WITH ENCRYPTED PASSWORD %L',
      v_username, v_password
    );
    RAISE NOTICE 'Created user: %', v_username;
  ELSE
    RAISE NOTICE 'User already exists, skipping: %', v_username;
  END IF;
 
  -- ============================================================
  -- 3. Grant CONNECT on the database
  -- ============================================================
  EXECUTE format('GRANT CONNECT ON DATABASE %I TO %I', v_database, v_role_name);
 
  -- ============================================================
  -- 4. Grant USAGE + SELECT on each schema
  -- ============================================================
  FOR i IN 1 .. array_length(v_schemas, 1) LOOP
    -- Usage on schema
    EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', v_schemas[i], v_role_name);
 
    -- Select on all existing tables
    EXECUTE format(
      'GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I',
      v_schemas[i], v_role_name
    );
 
    -- Select on all existing sequences (needed for serial/bigserial columns)
    EXECUTE format(
      'GRANT SELECT ON ALL SEQUENCES IN SCHEMA %I TO %I',
      v_schemas[i], v_role_name
    );
 
    -- Default privileges for FUTURE tables
    EXECUTE format(
      'ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO %I',
      v_schemas[i], v_role_name
    );
 
    -- Default privileges for FUTURE sequences
    EXECUTE format(
      'ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON SEQUENCES TO %I',
      v_schemas[i], v_role_name
    );
 
    RAISE NOTICE 'Granted read-only access on schema: %', v_schemas[i];
  END LOOP;
 
  -- ============================================================
  -- 5. Assign the role to the user
  -- ============================================================
  EXECUTE format('GRANT %I TO %I', v_role_name, v_username);
  RAISE NOTICE 'Granted role % to user %', v_role_name, v_username;
 
END $$;