Migrate PE roles and database tables

1. Save the following script to the PE primary and ensure it is executable:

#!/bin/bash

PG_BIN="/opt/puppetlabs/server/bin"
WORKDIR=$(runuser -u pe-postgres -- mktemp -d /tmp/pe-migrate-XXXXXXXX)

PE_ROLES=(
  pe-activity
  pe-activity-read
  pe-activity-write
  pe-ca
  pe-ca-read
  pe-ca-write
  pe-classifier
  pe-classifier-read
  pe-classifier-write
  pe-ha-replication
  pe-hac
  pe-hac-read
  pe-hac-write
  pe-infra-assistant
  pe-infra-assistant-read
  pe-infra-assistant-write
  pe-inventory
  pe-inventory-read
  pe-inventory-write
  pe-orchestrator
  pe-orchestrator-read
  pe-orchestrator-write
  pe-patching
  pe-patching-read
  pe-patching-write
  pe-postgres
  pe-puppetdb
  pe-puppetdb-migrator
  pe-puppetdb-read
  pe-rbac
  pe-rbac-read
  pe-rbac-write
  pe-workflow
  pe-workflow-read
  pe-workflow-write
)

PE_DATABASES=(
  pe-activity
  pe-ca
  pe-classifier
  pe-hac
  pe-infra-assistant
  pe-inventory
  pe-orchestrator
  pe-patching
  pe-puppetdb
  pe-rbac
  pe-workflow
)

# Migrate roles

ROLES_DUMPFILE="${WORKDIR}/roles.sql"

runuser -u pe-postgres -- \
  "${PG_BIN}/pg_dumpall" --roles-only \
  | sed \
    -e 's/ superuser / /gI' \
    -e 's/ nosuperuser / /gI' \
    -e 's/ granted by.*;/;/gI' \
  > "${ROLES_DUMPFILE}"

PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/psql" \
  --host="${PG_HOST}" \
  --username=postgres \
  postgres \
  < "${ROLES_DUMPFILE}"

for role in "${PE_ROLES[@]}"; do
  PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/psql" \
    --host="${PG_HOST}" \
    --username=postgres \
    postgres \
    -c "ALTER USER \"$role\" PASSWORD '$PG_PASSWORD'"
done

PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/psql" --host="${PG_HOST}" --username=postgres postgres <<EOF
  GRANT cloudsqlsuperuser TO "pe-postgres";
  GRANT cloudsqlsuperuser TO "pe-activity";
  GRANT cloudsqlsuperuser TO "pe-ca";
  GRANT cloudsqlsuperuser TO "pe-classifier";
  GRANT cloudsqlsuperuser TO "pe-ha-replication";
  GRANT cloudsqlsuperuser TO "pe-hac";
  GRANT cloudsqlsuperuser TO "pe-infra-assistant";
  GRANT cloudsqlsuperuser TO "pe-inventory";
  GRANT cloudsqlsuperuser TO "pe-orchestrator";
  GRANT cloudsqlsuperuser TO "pe-patching";
  GRANT cloudsqlsuperuser TO "pe-rbac";
  GRANT cloudsqlsuperuser TO "pe-workflow";

  GRANT "pe-activity" TO "pe-postgres";
  GRANT "pe-ca" TO "pe-postgres";
  GRANT "pe-classifier" TO "pe-postgres";
  GRANT "pe-ha-replication" TO "pe-postgres";
  GRANT "pe-hac" TO "pe-postgres";
  GRANT "pe-infra-assistant" TO "pe-postgres";
  GRANT "pe-inventory" TO "pe-postgres";
  GRANT "pe-orchestrator" TO "pe-postgres";
  GRANT "pe-patching" TO "pe-postgres";
  GRANT "pe-puppetdb" TO "pe-postgres";
  GRANT "pe-puppetdb" TO "pe-puppetdb-migrator";
  GRANT "pe-puppetdb-read" TO "pe-puppetdb";
  GRANT "pe-rbac" TO "pe-postgres";
  GRANT "pe-workflow" TO "pe-postgres";
  -- Added for PE-44633: lets pe-postgres reassign pe-puppetdb's restored objects
  -- to the migrator role in the ownership-reconciliation block below.
  GRANT "pe-puppetdb-migrator" TO "pe-postgres";
EOF

# Migrate databases

for db in "${PE_DATABASES[@]}"; do
  DUMPFILE="${WORKDIR}/${db}.dump"

  runuser -u pe-postgres -- \
    "${PG_BIN}/pg_dump" -Fd -Z3 -j4 "${db}" --no-tablespaces -cC -f "${DUMPFILE}"

  # --no-owner: Cloud SQL's connecting role is a member of cloudsqlsuperuser but
  #   is NOT a real superuser, and SET SESSION AUTHORIZATION is superuser-only, so
  #   restoring objects under their original owners aborts (PE-44633 problem 3).
  #   Restoring everything as pe-postgres also avoids the PostgreSQL 15+
  #   "permission denied for schema public" failure (problem 2). Object ownership
  #   is reconciled explicitly after the loop.
  # --no-acl: skip the dump's GRANT/REVOKE, including "REVOKE CONNECT … FROM
  #   PUBLIC" (problem 4). PE re-applies database privileges via its catalog after
  #   restore.
  PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/pg_restore" \
    --host="${PG_HOST}" \
    --user=pe-postgres \
    -d postgres \
    --no-owner \
    --no-acl \
    --create "${DUMPFILE}"

  # pg_dump omits the default GRANT USAGE on the public schema, so non-owner
  # application roles cannot see objects in public after restore. Re-assert it.
  PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/psql" \
    --host="${PG_HOST}" --username=pe-postgres "${db}" \
    -c 'GRANT USAGE ON SCHEMA public TO PUBLIC'
done

# Reconcile object ownership (PE-44633). After --no-owner every restored object
# is owned by pe-postgres; PE's catalog does not reassign existing-object
# ownership, so each service connecting as its own role would hit
# "permission denied for table …". Reassign each database's objects from
# pe-postgres to the role that owns them on a fresh install. For these databases
# that is the service role itself (each holds cloudsqlsuperuser, so it already
# has CREATE on the public schema and REASSIGN succeeds).
for db in pe-activity pe-ca pe-classifier pe-hac pe-infra-assistant pe-inventory pe-orchestrator pe-patching pe-rbac pe-workflow; do
  PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/psql" \
    --host="${PG_HOST}" --username=pe-postgres "${db}" \
    -c "REASSIGN OWNED BY \"pe-postgres\" TO \"${db}\""
done

# pe-puppetdb is the exception: it is NOT a cloudsqlsuperuser, and on a fresh
# install the migrator role owns the schema objects while pe-puppetdb / -read
# hold only DML/SELECT grants. Reassign to the migrator, then reconstruct the
# app-role grants that --no-acl stripped (PuppetDB cannot self-grant until it
# starts, and it cannot start until these grants exist).
PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/psql" --host="${PG_HOST}" --username=pe-postgres pe-puppetdb <<EOF
  GRANT CREATE ON SCHEMA public TO "pe-puppetdb-migrator";
  REASSIGN OWNED BY "pe-postgres" TO "pe-puppetdb-migrator";
  ALTER SCHEMA public OWNER TO "pe-puppetdb-migrator";
EOF
PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/psql" --host="${PG_HOST}" --username=pe-puppetdb-migrator pe-puppetdb <<EOF
  GRANT USAGE, CREATE ON SCHEMA public TO "pe-puppetdb";
  GRANT USAGE ON SCHEMA public TO "pe-puppetdb-read";
  GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO "pe-puppetdb";
  GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO "pe-puppetdb";
  GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "pe-puppetdb";
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO "pe-puppetdb-read";
  GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO "pe-puppetdb-read";
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "pe-puppetdb";
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "pe-puppetdb-read";
EOF

PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/psql" --host="${PG_HOST}" --username=pe-postgres pe-puppetdb <<EOF
  REVOKE CONNECT,TEMPORARY ON DATABASE "pe-puppetdb" FROM PUBLIC;
  GRANT TEMPORARY ON DATABASE "pe-puppetdb" TO PUBLIC;
  GRANT CREATE,TEMPORARY ON DATABASE "pe-puppetdb" TO "pe-puppetdb";
  GRANT CONNECT ON DATABASE "pe-puppetdb" TO "pe-puppetdb-migrator" WITH GRANT OPTION;
EOF

PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/psql" --host="${PG_HOST}" --username=pe-puppetdb-migrator pe-puppetdb <<EOF
  GRANT CONNECT ON DATABASE "pe-puppetdb" TO "pe-puppetdb";
  GRANT CONNECT ON DATABASE "pe-puppetdb" TO "pe-puppetdb-read";
EOF

PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/psql" --host="${PG_HOST}" --username=postgres postgres <<EOF
  GRANT CONNECT ON DATABASE "pe-rbac" TO "pe-rbac";
  GRANT CONNECT ON DATABASE "pe-activity" TO "pe-activity";
  GRANT CONNECT ON DATABASE "pe-ca" TO "pe-ca";
  GRANT CONNECT ON DATABASE "pe-ca" TO "pe-ca-read";
  GRANT CONNECT ON DATABASE "pe-ca" TO "pe-ca-write";
  GRANT CONNECT ON DATABASE "pe-classifier" TO "pe-classifier";
  GRANT CONNECT ON DATABASE "pe-orchestrator" TO "pe-orchestrator";
  GRANT CONNECT ON DATABASE "pe-inventory" TO "pe-inventory";
EOF

2. Run the script, passing the password for the postgres user that was set when provisioning the database instance, and the IP address for the database instance:

PG_PASSWORD=<DB_PASSWORD> PG_HOST=<DB_HOST> ./migrate.sh