Deploy new postgreSQL instance dMZR
#!/bin/sh
#set -ex
cd $ORA2PG_HOME/hvault
while true
do
if [ "$#" -eq "0" ]
then
clear
echo " "
echo " Welcome to the program of a new deployment in <cloudDBname> - dMZR"
echo " "
echo " "
echo " 1 - Create a new role"
echo " 2 - Deploy the script of connection"
echo " q - Quit."
echo " "
read var
case $var in
1)
clear
echo " "
if [[ "$VAULT_ADDR" == <url_prod> ]]
then
ENV="prod"
cat $ORA2PG_HOME/adm/sh/prod
echo " "
echo "Give the name of the ecosystem :"
read ECOSYSTEM
SERVER=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $ECOSYSTEM | awk '{print $4}')
echo "Give the name of the instance :"
echo "$SERVER"
echo " "
read SERVER
echo " "
ASSET=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $SERVER | awk '{print $1}')
APREFI=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $SERVER | awk '{print $6}')
echo " "
echo " Give the type of account to create (dba|own|app) :"
read TYPE
echo " "
echo " The database is : $ASSET"
echo " "
ENVDIR="$ORA2PG_HOME/$ENV/$ASSET"
else
if [[ "$VAULT_ADDR" == <url_hors_prod> ]]
then
ENV="nonprod"
cat $ORA2PG_HOME/adm/sh/nonprod
echo " "
echo "Give the name of the ecosystem :"
read ECOSYSTEM
SERVER=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $ECOSYSTEM | awk '{print $4}')
echo "Give the name of the instance :"
echo "$SERVER"
echo " "
read SERVER
ASSET=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $1}')
APREFI=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $6}')
SOUSENV=$(cat $ORA2PG_HOME/adm/sh/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 " "
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
#creation of a sysdba role inside the database
touch $ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "CREATE ROLE sysdba WITH LOGIN CREATEDB CREATEROLE PASSWORD 'xxxxxxxxxxxxx';">$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT CREATE ON DATABASE <cloudDBname> TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT USAGE,CREATE ON SCHEMA own_${APREFI}_${ASSET} TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA own_${APREFI}_${ASSET} TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT USAGE ON ALL SEQUENCES IN SCHEMA own_${APREFI}_${ASSET} TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT EXECUTE ON ALL FUNCTIONS in SCHEMA own_${APREFI}_${ASSET} TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT admin,"ibm-cloud-base-user",role_own_${APREFI}_${ASSET} TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "ALTER ROLE sysdba SET search_path TO own_${APREFI}_${ASSET};">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "Role $STATIC_ROLE_NAME succefully created in DB."
echo " "
echo "Role sysdba can be created from $ENVDIR/sysdba_${SERVER}_${ASSET}.sql."
echo " "
echo "Press enter to create a new role or ctrl C to quit"
read bidon
else
echo " You are going to create the role : "
echo " ${TYPE}_${APREFI}_${ASSET} "
ROLE="${TYPE}_${APREFI}_${ASSET}"
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
#copy of the template to create the dynamic role
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/<nom_applicatif>/${APREFI}_${ASSET}/g" ${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql
sed -i.bak "s/<schema>/own_${APREFI}_${ASSET}/g" ${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql
done < ${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql
#creation of the dynamic role in hashicorp vault
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
echo " "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo "!!! The role ${TYPE}_${APREFI}_${ASSET} is created !!! "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo " "
echo "Press enter to create a new role or ctrl C to quit"
read bidon
fi
;;
2)
clear
echo " "
if [[ "$VAULT_ADDR" == <url_prod> ]]
then
ENV="prod"
cat $ORA2PG_HOME/adm/sh/prod
echo " "
echo "Give the name of the ecosystem :"
read ECOSYSTEM
SERVER=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $ECOSYSTEM | awk '{print $4}')
echo " "
echo "Give the name of the instance:"
echo "$SERVER"
echo " "
read SERVER
echo " "
ASSET=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $SERVER | awk '{print $1}')
echo " "
echo " The database is : $ASSET"
echo " "
echo " The Env is : $ENV"
echo " "
echo " The deployment is on progress..."
#creation of a new directory to store the connection script
ENVDIR="$ORA2PG_HOME/$ENV/$ASSET"
mkdir -p $ENVDIR
cp ${ORA2PG_HOME}/adm/sh/template.sh ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
while read ; do
sed -i.bak "s/EC002I001576/${ECOSYSTEM}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
sed -i.bak "s/<instance>/${SERVER}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
sed -i.bak "s/ENV/${ENV}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
sed -i.bak "s/ASSET/${ASSET}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
done < ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
cp ${ORA2PG_HOME}/adm/sh/template_launcher.sql ${ORA2PG_HOME}/${ENV}/${ASSET}/launcher${ENV}.sql
while read ; do
sed -i.bak "s/ENV/${ENV}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/launcher${ENV}.sql
done < ${ORA2PG_HOME}/${ENV}/${ASSET}/launcher${ENV}.sql
fi
if [[ "$VAULT_ADDR" == <url_hors_prod> ]]
then
ENV="nonprod"
cat $ORA2PG_HOME/adm/sh/nonprod
echo " "
echo "Give the name of the ecosystem :"
read ECOSYSTEM
SERVER=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $ECOSYSTEM | awk '{print $4}')
echo " "
echo "Give the name of the instance :"
echo " "
echo "$SERVER"
echo " "
read SERVER.
ASSET=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $1}')
APREFI=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $6}')
SOUSENV=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $2}')
echo " "
echo " The database is : $ASSET"
echo " "
echo " The Env is : $SOUSENV"
echo " "
echo " The deployment is on progress..."
ENVDIR="$ORA2PG_HOME/$ENV/$SOUSENV/$ASSET"
mkdir -p $ENVDIR
cp ${ORA2PG_HOME}/adm/sh/template.sh ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
while read ; do
sed -i.bak "s/<ecosystem>/${ECOSYSTEM}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
sed -i.bak "s/<instance>/${SERVER}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
sed -i.bak "s/ENV/${SOUSENV}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
sed -i.bak "s/ASSET/${ASSET}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
done < ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
fi
cp ${ORA2PG_HOME}/adm/sh/template_launcher.sql ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/launcher${SOUSENV}.sql
while read ; do
sed -i.bak "s/ENV/${SOUSENV}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/launcher${SOUSENV}.sql
done < ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/launcher${SOUSENV}.sql
#creation of the specific privs for dev users
if [[ "$SOUSENV" == dev ]]
then
touch devpg/devpg_${ASSET}.sql
echo "create user devpg password 'xxxxxxxxxx';">devpg/devpg_${ASSET}.sql
echo "GRANT CREATE ON DATABASE <cloudDBname> TO devpg;">>devpg/devpg_${ASSET}.sql
echo "GRANT USAGE,CREATE ON SCHEMA own_${APREFI}_${ASSET} TO devpg;">>devpg/devpg_${ASSET}.sql
echo "GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA own_${APREFI}_${ASSET} TO devpg;">>devpg/devpg_${ASSET}.sql
echo "GRANT USAGE ON ALL SEQUENCES IN SCHEMA own_${APREFI}_${ASSET} TO devpg;">>devpg/devpg_${ASSET}.sql
echo "GRANT EXECUTE ON ALL FUNCTIONS in SCHEMA own_${APREFI}_${ASSET} 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 own_${APREFI}_${ASSET};">>devpg/devpg_${ASSET}.sql
echo " "
echo " Execute the script ${ORA2PG_HOME}/hvault/devpg/devpg_${ASSET}.sql to create the dev user."
echo " "
fi
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo "!!! The script ${ASSET}.sh !!! "
echo "!!! is deployed under $ENVDIR !!! "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo " The log is : log/${ASSET}.log "
echo " To read the role :">>log/${ASSET}.log
echo " vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/${SERVER}/creds/${ROLE}">>log/${ASSET}.log
echo " To delete the role execute : ">>log/${ASSET}.log
echo "vault delete -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles/${ROLE}">>log/${ASSET}.log
echo " "
echo "Press enter to continue or ctrl C to quit"
read bidon
#rm -f $.bak to do
;;
q)
echo "Thanks, bye."
exit
;;
*)
echo "Wrong value."
echo "Press enter to continue."
read bidon
;;
esac
else
clear
echo " "
echo " Welcome to the program of the deployment of $1 - dMZR"
echo " "
echo " "
echo " 1 - Create a new role"
echo " 2 - Deploy the script of connection"
echo " q - Quit."
echo " "
read var
case $var in
1)
clear
echo " "
if [[ "$VAULT_ADDR" == <url_prod> ]]
then
ENV="prod"
DOMAIN=".svc.paas.echonet"
cat $ORA2PG_HOME/adm/sh/prod
echo " "
echo "Give the name of the ecosystem :"
read ECOSYSTEM
SERVER=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $ECOSYSTEM | awk '{print $4}')
echo "Give the name of the instance :"
echo "$SERVER"
echo " "
read SERVER
echo " "
ASSET=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $SERVER | awk '{print $1}')
APREFI=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $SERVER | awk '{print $6}')
PORT=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $SERVER | awk '{print $5}')
echo " "
echo " Give the type of account to create (dba|own|app|dyn) :"
read TYPE
echo " "
echo " The database is : $ASSET"
echo " "
ENVDIR="$ORA2PG_HOME/$ENV/$ASSET"
else
if [[ "$VAULT_ADDR" == <url_hors_prod> ]]
then
ENV="nonprod"
DOMAIN=".svc-np.paas.echonet"
cat $ORA2PG_HOME/adm/sh/nonprod
echo " "
echo "Give the name of the ecosystem :"
read ECOSYSTEM
SERVER=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $ECOSYSTEM | awk '{print $4}')
echo "Give the name of the instance :"
echo "$SERVER"
echo " "
read SERVER
ASSET=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $1}')
APREFI=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $6}')
SOUSENV=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $2}')
PORT=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $5}')
echo " "
echo " Give the type of account to create (dba|own|app|dyn) :"
read TYPE
echo " "
echo " The environment of non prod is : $SOUSENV"
echo " The database is : $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
#creation of the revocation script for the temporary dynamic role
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
#temporary dynamic role name used to transform the dynamic role to a static role
DYNAMIC_ROLE_NAME="tmp-$(head /dev/urandom | tr -dc a-z0-9 | head -c 13 ; echo '')"
#creation of the temporary dynamic role
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)
#test if the creation of the dynamic role is successful
if [[ $(echo "$result" | grep 'Success!' | wc -l) -lt 1 ]]; then
echo "Can't create temporary dynamic role ${DYNAMIC_ROLE_NAME}: $result"
exit 1
fi
#read the creds of the dynamic role
result=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM"
database/postgres/${SERVER}/creds/${DYNAMIC_ROLE_NAME} 2>&1)
#test if the creds can be read
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."
#delete the dynamic role
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
#creation of a sysdba role inside the database
touch $ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "CREATE ROLE sysdba WITH LOGIN CREATEDB CREATEROLE PASSWORD 'xxxxxxxxxxxxx';">$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT CREATE ON DATABASE $1 TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT USAGE,CREATE ON SCHEMA own_${APREFI}_${ASSET} TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA own_${APREFI}_${ASSET} TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT USAGE ON ALL SEQUENCES IN SCHEMA own_${APREFI}_${ASSET} TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT EXECUTE ON ALL FUNCTIONS in SCHEMA own_${APREFI}_${ASSET} TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT admin,"ibm-cloud-base-user",role_own_${APREFI}_${ASSET} TO sysdba;">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "ALTER ROLE sysdba SET search_path TO own_${APREFI}_${ASSET};">>$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "Role $STATIC_ROLE_NAME succefully created in $1."
echo " "
echo "Role sysdba can be created from $ENVDIR/sysdba_${SERVER}_${ASSET}.sql."
echo " "
echo "Press enter to create a new role or ctrl C to quit"
read bidon
fi
if [[ "$TYPE" == own ]]
then
STATIC_ROLE_NAME="stat_${TYPE}_${APREFI}_${ASSET}"
SCHE="own_${APREFI}_${ASSET}"
DYN="app_${APREFI}_${ASSET}"
DBO="role_${TYPE}_${APREFI}_${ASSET}"
echo " You are going to create the role : "
echo " $STATIC_ROLE_NAME."
cat <<EOF> /tmp/static-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}}' INHERIT;
grant "{{name}}" to admin ;
ALTER ROLE "{{name}}" set search_path to "$STATIC_ROLE_NAME";
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_roles WHERE rolname ='$DBO') THEN
CREATE ROLE "$DBO" NOLOGIN;
GRANT "$DBO" TO admin;
-- rattachement du user stat_own_25640_wbfs au groupe role_own_25640_wbfs
GRANT "$DBO" TO "$STATIC_ROLE_NAME";
END IF;
-- rajouter la section suivante pour les instances sur l’offre PostgreSQL mais pas pour les instances sur l’offre PostgreSQL EE car pour l’instant, une limitation sur cette offre ne permet pas de créer les extensions
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/static-revocation-statements.sql
revoke admin from "{{name}}" ;
grant "{{name}}" to admin ;
REASSIGN OWNED BY "{{name}}" to "$DBO";
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/static-creation-statements.sql
revocation_statements=@/tmp/static-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/static-creation-statements.sql
rotation_statements=@/tmp/static-rotation-statement.sql
default_ttl=24h
max_ttl=8d
username=${STATIC_ROLE_NAME}
rotation_period=12h
secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$STATIC_ROLE_NAME | grep password | awk '{print $2}')
export PGPASSWORD=$secret;createdb $1 -O $DBO -h $SERVER$DOMAIN -p $PORT -U $STATIC_ROLE_NAME
cat <<EOF> /tmp/static-grant-statements.sql
DO
$do$
BEGIN
CREATE SCHEMA IF NOT EXISTS "$SCHE";
-- création du groupe s'il n'existe pas déjà, avec droit de se connecter au schéma
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_roles WHERE rolname ='role_$DYN') THEN
CREATE ROLE "role_$DYN" NOLOGIN;
ALTER ROLE "role_$DYN" SET search_path TO "$SCHE";
GRANT USAGE ON SCHEMA "$SCHE" TO "role_$DYN";
END IF;
ALTER SCHEMA "$SCHE" OWNER TO "$DBO";
--don des droits de manipulation des objets du schéma au groupe
SET ROLE "$DBO";
-- Les ALTER DEFAULT PRIVILEGE doivent être exécutés par le créateur des objets
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT SELECT,UPDATE,INSERT,DELETE ON TABLES TO role_$DYN;
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT USAGE ON SEQUENCES TO role_$DYN;
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT EXECUTE ON FUNCTIONS TO role_$DYN;
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT USAGE ON TYPES TO role_$DYN;
RESET ROLE;
-- Permet de se connecter directement avec le rôle chapeau (qui sera donc propriétaire des objets que vous créerez)
ALTER ROLE "$STATIC_ROLE_NAME" SET search_path TO "$STATIC_ROLE_NAME";
ALTER ROLE "$STATIC_ROLE_NAME" SET ROLE "$DBO";
END
$do$;
EOF
export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $STATIC_ROLE_NAME -d $1 -f /tmp/static-grant-statements.sql
echo " "
echo "Database $1 succefully created."
echo " "
echo "Role $STATIC_ROLE_NAME and schema $SCHE succefully created in $1."
echo " "
echo "Press enter to create a new role or ctrl C to quit"
read bidon
fi
if [[ "$TYPE" == app ]]
then
STATIC_ROLE_NAME="stat_${TYPE}_${APREFI}_${ASSET}"
SCHE="own_${APREFI}_${ASSET}"
DYN="app_${APREFI}_${ASSET}"
echo " You are going to create the role : "
echo " $STATIC_ROLE_NAME."
cat <<EOF> /tmp/statics-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 PASSWORD '{{password}}';
END IF;
-- création du role dynamique
CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' INHERIT;
GRANT "{{name}}" TO admin;
-- rattachement du user au groupe (qui a été créé en même temps )
GRANT "role_$DYN" TO "{{name}}";
ALTER ROLE "{{name}}" SET search_path TO "$SCHE";
RESET ROLE;
-- rajouter la section suivante pour les instances sur l’offre PostgreSQL mais pas pour les instances sur l’offre PostgreSQL EE car pour l’instant, une limitation sur cette offre ne permet pas de créer les extensions
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/statics-revocation-statements.sql
revoke admin from "{{name}}" ;
grant "{{name}}" to admin ;
REASSIGN OWNED BY "{{name}}" to role_$DYN;
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/statics-creation-statements.sql
revocation_statements=@/tmp/statics-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/statics-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/statics-creation-statements.sql
rotation_statements=@/tmp/statics-rotation-statement.sql
default_ttl=24h
max_ttl=8d
username=${STATIC_ROLE_NAME}
rotation_period=12h
echo "Role $STATIC_ROLE_NAME succefully created in $1."
echo " "
echo "Press enter to create a new role or ctrl C to quit"
read bidon
fi
if [[ "$TYPE" == dyn ]]
then
SCHE="own_${APREFI}_${ASSET}"
GRP="role_app_${APREFI}_${ASSET}"
DYN="dyn_app_${APREFI}_${ASSET}"
echo " You are going to create the role : "
echo " $DYN."
cat <<EOF> /tmp/dyn-creation-statements.sql
DO
$do$
BEGIN
-- création du role dynamique
CREATE ROLE "{{name}}" LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' INHERIT;
GRANT "{{name}}" TO admin;
-- rattachement du user au groupe (qui a été créé en même temps )
GRANT $GRP TO "{{name}}";
ALTER ROLE "{{name}}" SET search_path TO "$SCHE";
RESET ROLE;
ALTER ROLE "{{name}}" SET ROLE "$GRP";
--Permet de se connecter directement avec le rôle chapeau (qui sera donc propriétaire des objets que vous créerez)
END
$do$;
EOF
cat <<EOF> /tmp/dyn-revocation-statements.sql
revoke admin from "{{name}}" ;
grant "{{name}}" to admin ;
REASSIGN OWNED BY "{{name}}" to $GRP;
DROP OWNED by "{{name}}" ;
DROP ROLE IF EXISTS "{{name}}";
EOF
vault write -ns="$VAULT_NAMESPACE/$ECOSYSTEM"
database/postgres/${SERVER}/roles/${DYN}
db_name=${SERVER}
creation_statements=@/tmp/dyn-creation-statements.sql
revocation_statements=@/tmp/dyn-revocation-statements.sql
default_ttl=24h
max_ttl=8d 2>&1
echo " "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo "!!! The role ${DYN} is created in $1!!! "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo " "
echo "Press enter to create a new role or ctrl C to quit"
read bidon
fi
;;
2)
clear
echo " "
if [[ "$VAULT_ADDR" == <url_prod> ]]
then
ENV="prod"
cat $ORA2PG_HOME/adm/sh/prod
echo " "
echo "Give the name of the ecosystem :"
read ECOSYSTEM
SERVER=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $ECOSYSTEM | awk '{print $4}')
echo " "
echo "Give the name of the instance:"
echo "$SERVER"
echo " "
read SERVER
echo " "
ASSET=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $SERVER | awk '{print $1}')
echo " "
echo " The database is : $ASSET"
echo " "
echo " The Env is : $ENV"
echo " "
echo " The deployment is on progress..."
ENVDIR="$ORA2PG_HOME/$ENV/$ASSET"
mkdir -p $ENVDIR
cp ${ORA2PG_HOME}/adm/sh/template-static.sh ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
while read ; do
sed -i.bak "s/<ecosystem>/${ECOSYSTEM}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
sed -i.bak "s/<instance>/${SERVER}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
sed -i.bak "s/mydb/$1/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
sed -i.bak "s/ENV/${ENV}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
sed -i.bak "s/ASSET/${ASSET}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
done < ${ORA2PG_HOME}/${ENV}/${ASSET}/${ASSET}${ENV}.sh
cp ${ORA2PG_HOME}/adm/sh/template_launcher.sql ${ORA2PG_HOME}/${ENV}/${ASSET}/launcher${ENV}.sql
while read ; do
sed -i.bak "s/ENV/${ENV}/g" ${ORA2PG_HOME}/${ENV}/${ASSET}/launcher${ENV}.sql
done < ${ORA2PG_HOME}/${ENV}/${ASSET}/launcher${ENV}.sql
fi
if [[ "$VAULT_ADDR" == <url_hors_prod> ]]
then
ENV="nonprod"
cat $ORA2PG_HOME/adm/sh/nonprod
echo " "
echo "Give the name of the ecosystem :"
read ECOSYSTEM
SERVER=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $ECOSYSTEM | awk '{print $4}')
echo " "
echo "Give the name of the instance :"
echo " "
echo "$SERVER"
echo " "
read SERVER
ASSET=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $1}')
APREFI=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $6}')
SOUSENV=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $2}')
echo " "
echo " The database is : $ASSET"
echo " "
echo " The Env is : $SOUSENV"
echo " "
echo " The deployment is on progress..."
ENVDIR="$ORA2PG_HOME/$ENV/$SOUSENV/$ASSET"
mkdir -p $ENVDIR
cp ${ORA2PG_HOME}/adm/sh/template-static.sh ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
while read ; do
sed -i.bak "s/<ecosystem>/${ECOSYSTEM}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
sed -i.bak "s/<instance>/${SERVER}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
sed -i.bak "s/mydb/$1/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
sed -i.bak "s/ENV/${SOUSENV}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
sed -i.bak "s/ASSET/${ASSET}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
done < ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/${ASSET}${SOUSENV}.sh
fi
cp ${ORA2PG_HOME}/adm/sh/template_launcher.sql ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/launcher${SOUSENV}.sql
while read ; do
sed -i.bak "s/ENV/${SOUSENV}/g" ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/launcher${SOUSENV}.sql
done < ${ORA2PG_HOME}/${ENV}/${SOUSENV}/${ASSET}/launcher${SOUSENV}.sql
if [[ "$SOUSENV" == dev ]]
then
touch <userdev>/<userdev>_${ASSET}.sql
echo "create user <userdev> password 'xxxxxxxxxx';"><userdev>/<userdev>_${ASSET}.sql
echo "GRANT CREATE ON DATABASE $1 TO <userdev>;">><userdev>/<userdev>_${ASSET}.sql
echo "GRANT USAGE,CREATE ON SCHEMA own_${APREFI}_${ASSET} TO <userdev>;">><userdev>/<userdev>_${ASSET}.sql
echo "GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA own_${APREFI}_${ASSET} TO <userdev>;">><userdev>/<userdev>_${ASSET}.sql
echo "GRANT USAGE ON ALL SEQUENCES IN SCHEMA own_${APREFI}_${ASSET} TO <userdev>;">><userdev>/<userdev>_${ASSET}.sql
echo "GRANT EXECUTE ON ALL FUNCTIONS in SCHEMA own_${APREFI}_${ASSET} TO <userdev>;">><userdev>/<userdev>_${ASSET}.sql
echo "GRANT role_own_${APREFI}_${ASSET} to <userdev>;">><userdev>/<userdev>_${ASSET}.sql
echo "ALTER ROLE <userdev> SET search_path TO own_${APREFI}_${ASSET};">><userdev>/<userdev>_${ASSET}.sql
echo " "
echo " Execute the script ${ORA2PG_HOME}/hvault/<userdev>/<userdev>_${ASSET}.sql to create the dev user."
echo " "
fi
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo "!!! The script ${ASSET}.sh !!! "
echo "!!! is deployed under $ENVDIR !!! "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo " The log is : log/${ASSET}.log "
echo " To read the role :">>log/${ASSET}.log
echo " vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/${SERVER}/creds/${ROLE}">>log/${ASSET}.log
echo " To delete the role execute : ">>log/${ASSET}.log
echo "vault delete -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles/${ROLE}">>log/${ASSET}.log
echo " "
echo "Press enter to continue or ctrl C to quit"
read bidon
#rm -f les .bak
;;
q)
echo "Thanks, bye."
exit
;;
*)
echo "Wrong value."
echo "Press enter to continue."
read bidon
;;
esac
fi
done
exit 0