Script de création des roles HVAULT dans ibm cloud pour postgreSQL (dMZR);
#!/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