[Cloud IBM PSQL] Role creation with HVAULT (static dba)

script : prepare_dba_static_role.sh

 

#!/bin/bash
clear
cat ../adm/sh/perimeter.lst
# Vault namespace
echo " "
echo " "
echo 'Vault namespace (example: xxx/xxx/<ecosystem_name>)?'
read VAULT_NS
# Vault secret engine
echo 'Instance name (example: instance name)?'
read INSTANCE_NAME
# Static role name
echo 'Static role name (example: dba)?'
read 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 ;
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_NS}" 
  database/postgres/${INSTANCE_NAME}/roles/${DYNAMIC_ROLE_NAME} 
  db_name=${INSTANCE_NAME} 
  creation_statements=@/tmp/dynamic-creation-statements.sql 
  revocation_statements=@/tmp/dynamic-revocation-statements.sql 
  default_ttl=24h 
  max_ttl=192h 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_NS}" 
  database/postgres/${INSTANCE_NAME}/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 "Temporary dynamic role ${DYNAMIC_ROLE_NAME} succefully created in DB."

echo " You can now create a Vault static role named $STATIC_ROLE_NAME."

result=$(vault delete -ns="${VAULT_NS}" 
  database/postgres/${INSTANCE_NAME}/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

echo " "
echo "Press enter to continue."
read bidon
vault write -ns=FORTIS/FBBE/$ECOSYSTEM database/postgres/$SERVER/static-roles/$ROLE 
 db_name=$SERVER 
 creation_statements=@/tmp/dynamic-creation-statements.sql 
 rotation_statements=@/tmp/static-rotation-statement.sql 
 default_ttl=24h 
 max_ttl=192h 
 username=$ROLE 
 rotation_period=24h