Création d'une base postgresql avec role hvault en mode batch dans le cloud IBM..
Cloud db
-
PostgreSQL in IBM Cloud.
-
Deploy new postgreSQL instance dMZR
Deploy new postgreSQL instance dMZR
-
[Cloud IBM PSQL] Role creation with HVAULT (static dba)
script : prepare_dba_static_role.sh
-
[Cloud IBM PSQL] Role creation with HVAULT
Roles creation using hvault :
- dynamic roles
The roles are created with this command :
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=${ROTATION}
max_ttl=${ROTATION} -
Connexions aux db du cloud
#!/bin/sh
#-------------------------------------------------------------------------------
#
# Script to connect to IBM Cloud DB, version 2.0
# Author : JJY
#
#-------------------------------------------------------------------------------
#set -vx
##############
#INSTANCIATION
##############
DOMAIN="<domain>"
DATABASE="postgres"
USERNAME="<user_dba>"
while true
do
DATABASE="postgres"
clear
echo "CONNEXION AUX BASES DU CLOUD "
echo " "
echo "CONNEXION A l ECOSYSTEM : <ecosystem>"
echo " "
echo "1 - DEV(dbname) - serveur : <instance> connexion DBA"
echo " "
echo "2 - DEV(dbname) - serveur : <instance> connexion dynamique applicative"
echo " "
echo "3 - QUAL(dbname) - serveur : <instance> connexion DBA"
echo " "
echo "4 - QUAL(dbname) - serveur : <instance> connexion dynamique applicative"
echo " "
echo "0 - TOOLBOX VAULT "
echo " "
echo " Faites votre choix (0 a 4) :"
echo " Taper Q pour quitter."
echo " "
read var
case $var in
1)
echo "Connexion a la base, veuillez patienter..."
export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;
vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME
secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME | grep password | awk '{print $2}')
export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $USERNAME -d $DATABASE
;;
2)
export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;export DATABASE=<dbname>
echo "Liste des roles:"
vault list -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles
echo " "
echo "A quel role souhaitez-vous vous connecter:"
read ROLE
clear
echo "Connexion a la base en mode dynamique, veuillez patienter..."
var=$(vault read -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/creds/$ROLE)
username=$(echo "$var" | grep username | awk '{print $2}');
password=$(echo "$var" | grep password | awk '{print $2}');
echo "User is : $username"
echo "Password is : $password"
echo " Tapez entrée pour continuer."
read bidon
export PGPASSWORD=$password;psql -h $SERVER$DOMAIN -p $PORT -U $username -d $DATABASE
;;
3)
echo "Connexion a la base, veuillez patienter..."
export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;
vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME
secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME | grep password | awk '{print $2}')
export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $USERNAME -d $DATABASE
;;
4)
export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;export DATABASE=<dbname>
echo "Liste des roles:"
vault list -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles
echo " "
echo "A quel role souhaitez-vous vous connecter:"
read ROLE
clear
echo "Connexion a la base en mode dynamique, veuillez patienter..."
var=$(vault read -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/creds/$ROLE)
username=$(echo "$var" | grep username | awk '{print $2}');
password=$(echo "$var" | grep password | awk '{print $2}');
echo "User is : $username"
echo "Password is : $password"
echo " Tapez entrée pour continuer."
read bidon
export PGPASSWORD=$password;psql -h $SERVER$DOMAIN -p $PORT -U $username -d $DATABASE
;;
0)
./toolbox.sh
echo " Tapez entrée pour continuer."
read bidon
;;
Q)
echo "Merci d'avoir utiliser ce programme."
exit
;;
*)
echo "Merci de renseignez un chiffre entre 0 et 4."
echo " Tapez entrée pour continuer."
read bidon
;;
esac
done
exit -
[Cloud db] liste des commandes vault
#!/bin/sh
DOMAIN="<nom_du_domaine>"
DATABASE="postgres"
USERNAME="<userpostgres>"
ECO=<nom_ecosystem>
SERVER=<instance_de_l_ecosystem>
clear
echo "Entrer le nom de l ecosystem (ex:$ECO)"
read $ECO
echo "Entrer le nom de l instance (ex:$SERVER)"
read $SERVER
echo "-- Infos sur l instance :"
echo " "
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/config/$SERVER "
echo " "
echo "-- Liste des rôle dynamiques : "
echo " "
echo "vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles "
echo " "
vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles
echo " "
echo "-- Liste des rôle statiques : "
echo " "
echo "vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/static-roles "
echo " "
vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/static-roles
echo "-- Informations sur un compte spécifique (dont les SQL de creation et révocation) : "
echo " "
echo "Entrez le nom du role:"
read ROLE
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles/$ROLE"
echo " "
echo "-- Récupérer les creds d un utilisateur dynamique: "
echo " "
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/creds/$ROLE"
echo " "
echo "-- Récupérer les creds d un utilisateur statique: "
echo " "
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/static-creds/$ROLE"
echo " "
echo "-- Créer les creds d un utilisateur: "
echo " "
echo "vault write -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles/${ROLE} db_name=${SERVER} creation_statements=@create_dyna_role_OWN.sql revocation_statements=@drop_dyna_role_OWN.sql default_ttl=${ROTATION} max_ttl=${ROTATION} "
echo " "
echo "-- Révoquer un compte avant son TTL : "
echo " "
echo "lease_id database/postgres/$SERVER/creds/OWN_IBMCLOUDDB_SCHEMA1/qdqsdqsdqsdsdqsdqsd.mxxxx"
echo " "
echo "vault lease revoke -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/creds/$ROLE/qdqsdqsdqsdsdqsdqsd.mxxx "
echo " "