Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

Migration de base oracle vers postgreSQL

Script de déploiement en préparation d'une migration de base oracle vers postgreSQL (dMZR) avec roles hashicorp.

#!/bin/sh
#-ex
#set -x

while true
do

clear
        if [[ "$VAULT_ADDR" == <adresse de prod>]]
        then
        ENV="prod"
        DOMAIN="<domaine de prod>"
        cat  <chemin vers prod>/prod
        echo " "
        echo "Give the name of the ecosystem :"
        read ECOSYSTEM
        SERVER=$(cat <chemin vers prod>/prod | grep -i $ECOSYSTEM |  awk  '{print $4}')
        echo "Give the name of the instance (ex:$SERVER)"
        read SERVER
        echo " "
        ASSET=$(cat <chemin vers prod>/prod | grep -i $SERVER |  awk  '{print $1}')
        APREFI=$(cat <chemin vers prod>/prod | grep -i $SERVER |  awk  '{print $6}')
        echo " "
        echo " Give the type of account to create (own|app|dba) :"
        read TYPE
        echo " "
        echo " The database is : $ASSET"
        echo " You are goint to create the role : "
        echo " ${TYPE}_${APREFI}_${ASSET} "
        ROLE="${TYPE}_${APREFI}_${ASSET}"
        echo " "
        ENVDIR="${ORA2PG_HOME}/$ENV/$ASSET"
        else
           if [[ "$VAULT_ADDR" == <adresse de hors prod> ]]
           then
           ENV="nonprod"
           DOMAIN="<domaine hors prod>"
           cat  <chemin vers non prod>/nonprod
           echo " "
           echo "Give the name of the ecosystem :"
           read ECOSYSTEM
           SERVER=$(cat <chemin vers non prod>/nonprod | grep -i $ECOSYSTEM |  awk  '{print $4}')
           echo "Give the name of the instance (ex:$SERVER)"
           read SERVER
           ASSET=$(cat <chemin vers non prod>/nonprod | grep -i $SERVER |  awk  '{print $1}')
           APREFI=$(cat <chemin vers non prod>/nonprod | grep -i $SERVER |  awk  '{print $6}')
           SOUSENV=$(cat <chemin vers non prod>/nonprod | grep -i $SERVER |  awk  '{print $2}')
           echo " "
           echo " Give the type of account to create (own|app|dba) :"
           read TYPE
           echo " "
           echo " The environment of non prod is : $SOUSENV"
           echo " The database is : $ASSET"
           echo " You are goint to create the role : "
           echo " ${TYPE}_${APREFI}_${ASSET} "
           ROLE="${TYPE}_${APREFI}_${ASSET}"
           echo " "
           ENVDIR="${ORA2PG_HOME}/$ENV/$SOUSENV/$ASSET"
           else
           echo " Wrong environment "
           exit 1
           fi
        fi
mkdir -p $ENVDIR
if [[ "$TYPE" == dba ]]
then
STATIC_ROLE_NAME="dba_pg"
echo " You are going to create the role : "
echo " $STATIC_ROLE_NAME."
cat <<EOF> /tmp/dynamic-creation-statements.sql
DO
$do$
BEGIN
IF NOT EXISTS (
SELECT
FROM pg_roles
WHERE rolname = '$STATIC_ROLE_NAME') THEN
CREATE ROLE "$STATIC_ROLE_NAME" LOGIN CREATEDB CREATEROLE PASSWORD '{{password}}';
END IF;
create role "{{name}}" with login createdb createrole password '{{password}}' CONNECTION LIMIT 5 VALID UNTIL '{{expiration}}' in role pg_monitor,pg_signal_backend,"ibm-cloud-base-user" ;
grant "{{name}}" to admin ;
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_extension WHERE extname = 'pg_buffercache') THEN CREATE EXTENSION "pg_buffercache";
END IF;
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_extension WHERE extname = 'pg_stat_statements') THEN CREATE EXTENSION "pg_stat_statements";
END IF;
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_extension WHERE extname = 'pg_freespacemap') THEN CREATE EXTENSION "pg_freespacemap";
END IF;
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_extension WHERE extname = 'pg_repack') THEN CREATE EXTENSION "pg_repack";
END IF;
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_extension WHERE extname = 'pgstattuple') THEN CREATE EXTENSION "pgstattuple";
END IF;
END
$do$;
EOF

cat <<EOF> /tmp/dynamic-revocation-statements.sql
REASSIGN OWNED BY "{{name}}" to "ibm-cloud-base-user";
DROP OWNED by "{{name}}" ;
DROP ROLE IF EXISTS "{{name}}" ;
EOF

DYNAMIC_ROLE_NAME="tmp-$(head /dev/urandom | tr -dc a-z0-9 | head -c 13 ; echo '')"

result=$(vault write -ns="$VAULT_NAMESPACE/$ECOSYSTEM"
  database/postgres/${SERVER}/roles/${DYNAMIC_ROLE_NAME}
  db_name=${SERVER}
  creation_statements=@/tmp/dynamic-creation-statements.sql
  revocation_statements=@/tmp/dynamic-revocation-statements.sql
  default_ttl=24h
  max_ttl=8d 2>&1)

if [[ $(echo "$result" | grep 'Success!' | wc -l) -lt 1  ]]; then
  echo "Can't create temporary dynamic role ${DYNAMIC_ROLE_NAME}: $result"
  exit 1
fi

result=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM"
  database/postgres/${SERVER}/creds/${DYNAMIC_ROLE_NAME} 2>&1)

if [[ $(echo "$result" | grep 'lease_id' | wc -l) -lt 1  ]]; then
  echo "Can't execute SQL query: $result"
  exit 2
fi

echo "Role $DYNAMIC_ROLE_NAME succefully created in DB. You can now create a Vault static role named $STATIC_ROLE_NAME."

result=$(vault delete -ns="$VAULT_NAMESPACE/$ECOSYSTEM"
  database/postgres/${SERVER}/roles/${DYNAMIC_ROLE_NAME} 2>&1)

if [[ $(echo "$result" | grep 'Success!' | wc -l) -lt 1  ]]; then
  echo "Can't delete temporary role ${DYNAMIC_ROLE_NAME}: $result"
fi

cat <<EOF> /tmp/static-rotation-statement.sql
ALTER ROLE "{{name}}" WITH PASSWORD '{{password}}';
EOF

vault write -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/${SERVER}/static-roles/${STATIC_ROLE_NAME}
 db_name=${SERVER}
 creation_statements=@/tmp/dynamic-creation-statements.sql
 rotation_statements=@/tmp/static-rotation-statement.sql
 default_ttl=24h
 max_ttl=8d
 username=${STATIC_ROLE_NAME}
 rotation_period=12h

echo "Role $STATIC_ROLE_NAME succefully created in DB."
echo " "
echo "Press enter to create a new role or ctrl C to quit"
read bidon


else
touch ${ORA2PG_HOME}/dynamic_role/${TYPE}/drop_dyna_role_${TYPE}_${ASSET}.sql

echo "revoke admin from "{{name}}" ;">${ORA2PG_HOME}/dynamic_role/${TYPE}/drop_dyna_role_${TYPE}_${ASSET}.sql
echo "grant "{{name}}" to admin ;">>${ORA2PG_HOME}/dynamic_role/${TYPE}/drop_dyna_role_${TYPE}_${ASSET}.sql
echo "REASSIGN OWNED BY "{{name}}" to role_${TYPE}_${APREFI}_${ASSET};">>${ORA2PG_HOME}/dynamic_role/${TYPE}/drop_dyna_role_${TYPE}_${ASSET}.sql
echo "DROP OWNED by "{{name}}" ;">>${ORA2PG_HOME}/dynamic_role/${TYPE}/drop_dyna_role_${TYPE}_${ASSET}.sql
echo "DROP ROLE IF EXISTS "{{name}}";">>${ORA2PG_HOME}/dynamic_role/${TYPE}/drop_dyna_role_${TYPE}_${ASSET}.sql

cp ${ORA2PG_HOME}/dynamic_role/template/create_dyna_role_${TYPE}_apro.sql ${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql
while read ; do
sed -i.bak "s/24269_apro/${APREFI}_${ASSET}/g" ${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql
sed -i.bak "s/apro_own/${ASSET}_own/g" ${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql
done < ${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql

vault write -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/${SERVER}/roles/${ROLE}
db_name=${SERVER}
creation_statements=@${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql
revocation_statements=@${ORA2PG_HOME}/dynamic_role/${TYPE}/drop_dyna_role_${TYPE}_${ASSET}.sql
default_ttl=24h
max_ttl=8d

if [[ "$VAULT_ADDR" == <adresse de prod> ]]
then
cp ${ORA2PG_HOME}/adm/sh/template.sh ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}.sh
while read ; do
sed -i.bak "s/EC002I001576/${ECOSYSTEM}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}.sh
sed -i.bak "s/pg002i002102/${SERVER}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}.sh
sed -i.bak "s/<ENV><ASSET>/<${ENV}><${ASSET}>g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}.sh
done < ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}.sh
fi
if [[ "$VAULT_ADDR" == <adresse de hors prod> ]]
then
cp ${ORA2PG_HOME}/adm/sh/template.sh ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}.sh
while read ; do
sed -i.bak "s/EC002I001576/${ECOSYSTEM}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}.sh
sed -i.bak "s/pg002i002102/${SERVER}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}.sh
sed -i.bak "s/<ENV><ASSET>/<${SOUSENV}><${ASSET}>/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}.sh
done < ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}.sh
fi

touch devpg/devpg_${ASSET}.sql
echo "create user devpg password 'mot de passe de dev';">devpg/devpg_${ASSET}.sql
echo "GRANT CREATE ON DATABASE ibmclouddb TO devpg;">>devpg/devpg_${ASSET}.sql
echo "GRANT USAGE,CREATE ON SCHEMA ${ASSET}_own TO devpg;">>devpg/devpg_${ASSET}.sql
echo "GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA ${ASSET}_own TO devpg;">>devpg/devpg_${ASSET}.sql
echo "GRANT USAGE ON ALL SEQUENCES IN SCHEMA ${ASSET}_own TO devpg;">>devpg/devpg_${ASSET}.sql
echo "GRANT EXECUTE ON ALL FUNCTIONS in SCHEMA ${ASSET}_own TO devpg;">>devpg/devpg_${ASSET}.sql
echo "GRANT role_own_${APREFI}_${ASSET} to devpg;">>devpg/devpg_${ASSET}.sql
echo "ALTER ROLE devpg SET search_path TO ${ASSET}_own;">>devpg/devpg_${ASSET}.sql

echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo "!!! The role ${TYPE}_${APREFI}_${ASSET} is created !!! "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo " "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo "!!! The script ${ASSET}.sh                                           !!! "
echo "!!! is deployed under $ENVDIR !!! "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo " Execute the script devpg_${ASSET}.sql to create the dev user."
echo " The log is : log/${TYPE}_${APREFI}_${ASSET}.log              "
echo " To read the role :">log/${TYPE}_${APREFI}_${ASSET}.log
echo " vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/${SERVER}/creds/${ROLE}">>log/${TYPE}_${APREFI}_${ASSET}.log
echo " To delete the role execute : ">>log/${TYPE}_${APREFI}_${ASSET}.log
echo "vault delete -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles/${ROLE}">>log/${TYPE}_${APREFI}_${ASSET}.log

echo " "
echo "Press enter to create a new role or ctrl C to quit"
read bidon
fi
#rm -f
done

exit 0

Les commentaires sont fermés.