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-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-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-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-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";
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}"

  PGPASSWORD="${PG_PASSWORD}" "${PG_BIN}/pg_restore" \
    --host="${PG_HOST}" \
    --user=pe-postgres \
    -d postgres \
    --create "${DUMPFILE}"
done

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-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