Création d'une base postgresql avec role hvault en mode batch dans le cloud IBM..
#!/bin/sh
# Desc : $1 is the name of the ASSET you want to deploy
# $1 is also the name of the database
# $2 is the name of the non prod env like dev, int , qual
# the script create the own and app static and dynamic roles,
# the schema, the static dba role in a new database.
# Author : Josselin Joly .
# Date : 07-12-2022
set -ex
cd $ORA2PG_HOME/hvault
if [[ "$VAULT_ADDR" == url de prod ]]
then
if (( $# != 1 ))
then
echo " "
echo "USAGE: $0 <asset> "
echo " "
exit
fi
clear
echo " "
echo " PostgreSQL deployment of $1 in progress ..."
echo " "
ENV="prod"
#on récupère l'asset
ASSET=$1
#on récupère ecosystem, server, port, aprefin nom de la base
ECOSYSTEM=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $ASSET | awk '{print $3}')
SERVER=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $ASSET | awk '{print $4}')
PORT=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $SERVER | awk '{print $5}')
APREFI=$(cat $ORA2PG_HOME/adm/sh/prod | grep -i $SERVER | awk '{print $6}')
DB=p${APREFI}a
ENVDIR="$ORA2PG_HOME/$ENV/$ASSET"
else
if [[ "$VAULT_ADDR" == url de non prod ]]
then
if (( $# != 2 ))
then
echo " "
echo "USAGE: $0 <asset> <dev><int><qual> "
echo " "
exit
fi
clear
echo " "
echo " PostgreSQL deployment of $1 in progress ..."
echo " "
ENV="nonprod"
#on récupère l'asset et l'environnement de hors prod dev int qual
ASSET=$1
SOUSENV=$2
#on récupère ecosystem, server, port, aprefi, nom de la base
ECOSYSTEM=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $ASSET | awk '{print $3}')
SERVER=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $ASSET | grep -i $SOUSENV | awk '{print $4}')
PORT=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $5}')
APREFI=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $SERVER | awk '{print $6}')
FIRST=${SOUSENV:0:1}
DB=${FIRST}${APREFI}a
ENVDIR="$ORA2PG_HOME/$ENV/$SOUSENV/$ASSET"
else
echo " Wrong environment "
exit 1
fi
fi
mkdir -p $ENVDIR
STATIC_ROLE_NAME="dba"
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}}';
GRANT pg_monitor, pg_signal_backend TO "$STATIC_ROLE_NAME";
GRANT "$STATIC_ROLE_NAME" TO admin;
ALTER ROLE "$STATIC_ROLE_NAME" set search_path to "$STATIC_ROLE_NAME";
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 ;
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 'bnp@dminDMZR';">$ENVDIR/sysdba_${SERVER}_${ASSET}.sql
echo "GRANT CREATE ON DATABASE ${DB} 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 " "
TYPE=own
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 au groupe
GRANT "$DBO" TO "$STATIC_ROLE_NAME";
GRANT "$DBO" TO "dba_pg";
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 ${DB} -O ${DBO} -h ${SERVER}${DOMAIN} -p ${PORT} -U $STATIC_ROLE_NAME -T template0
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";
-- 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;
--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 ${DB} -f /tmp/static-grant-statements.sql
echo " "
echo "Database ${DB} succefully created."
echo " "
echo "Role ${STATIC_ROLE_NAME} and schema ${SCHE} succefully created in ${DB}."
echo " "
TYPE=app
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 app (qui a été créé en même temps que le groupe own)
GRANT "role_$DYN" TO "{{name}}";
ALTER ROLE "{{name}}" SET search_path TO "$SCHE";
RESET ROLE;
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 " "
echo "Role ${STATIC_ROLE_NAME} succefully created in ${DB}."
echo " "
TYPE=dyn
SCHE="own_${APREFI}_${ASSET}"
GRP="role_app_${APREFI}_${ASSET}"
DYN="dyn_app_${APREFI}_${ASSET}"
echo " "
echo " You are going to create the role : "
echo " ${DYN}."
echo " "
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 dyn_app_25640_wbfs au groupe role_dyn_25640_wbfs (qui a été créé en même temps que app_25640_wbfs)
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 ${DB}!!! "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo " "
exit 0