[HVAULT] Roles creation for dMZR postgreSQL

Roles : own , app,  devpg

script : creRole.sh

#!/bin/sh
#set -x
#bac a sable

while true
do

export ECOSYSTEM=EC002I000123;export SERVER=pg002i001234;export APPREF=10078; export ASSET=rmet

clear
cat ../adm/sh/perimeter.lst

echo " "
echo " "
echo  " ECOSYSTEM : ($ECOSYSTEM) "
read ECOSYSTEM
echo " "
echo " INSTANCE : ($SERVER)"
read SERVER
echo " "
echo " TYPE OF ACCOUNT : (own|app|dba|dba_dataop|dev)"
read TYPE
echo " "
echo " APP REFI : ($APPREF)"
read APPREF
echo " "
echo " ASSET : ($ASSET) "
read ASSET
echo " "
echo " You can create the role : "
echo " ${TYPE}_${APPREF}_${ASSET} "
echo " "
echo " Otherwise :"
echo " "
echo " ROLE : (dba_pg|dba_dataop_pg|dev_pg)"
read ROLE
echo " "
echo " ROTATION : (24h) "
read ROTATION
clear

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}_${APPREF}_${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/${TYPE}/create_dyna_role_${TYPE}_jojo.sql ${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql
while read ; do
sed -i.bak "s/54321_jojo/${APPREF}_${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

#add error conditions

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}

touch devpg_${ASSET}.sql
echo "create user devpg password 'tutu';">devpg_${ASSET}.sql
echo "GRANT CREATE ON DATABASE labase TO devpg;">devpg_${ASSET}.sql
echo "GRANT USAGE,CREATE ON SCHEMA own_${APPREF}_${ASSET} TO devpg;>>devpg_${ASSET}.sql
echo "GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA own_${APPREF}_${ASSET} TO devpg;">>devpg_${ASSET}.sql
echo "GRANT USAGE ON ALL SEQUENCES IN SCHEMA own_${APPREF}_${ASSET} TO devpg;">>devpg_${ASSET}.sql
echo "GRANT EXECUTE ON ALL FUNCTIONS in SCHEMA own_${APPREF}_${ASSET} TO devpg;;">>devpg_${ASSET}.sql
#echo "GRANT USAGE ON TYPE <type_name> TO devpg;">>devpg_${ASSET}.sql
echo "GRANT role_own_${APPREF}_${ASSET} to devpg;">>devpg_${ASSET}.sql
echo "ALTER ROLE devpg SET search_path TO own_${APPREF}_${ASSET};">>devpg_${ASSET}.sql

echo "!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo "!!! The role is created !!! "
echo "!!!!!!!!!!!!!!!!!!!!!!!!!!! "
echo " "
echo " Execute the script devpg_${ASSET}.sql to create the dev user."


#script : create_dyna_role_own_jojo.sql
DO
$do$
BEGIN

-- création du role dynamique
CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' INHERIT;
GRANT "{{name}}" TO admin;  -- pour permettre de faire le "SET ROLE "{{name}}";" plus bas

-- création du schéma s'il n'existe pas déjà
CREATE SCHEMA IF NOT EXISTS "own_54321_jojo";


-- 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;


-- création du groupe role_app_54321_jojo s'il n'existe pas déjà
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_roles WHERE rolname ='role_app_54321_jojo') THEN
CREATE ROLE "role_app_54321_jojo" NOLOGIN;
ALTER ROLE "role_app_54321_jojo" SET search_path TO "own_54321_jojo";
GRANT USAGE ON SCHEMA "own_54321_jojo" TO "role_app_54321_jojo";
END IF;

-- création du groupe role_own_54321_jojo s'il n'existe pas déjà, avec transfert de la propriété du schéma
IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname ='role_own_54321_jojo') THEN
CREATE ROLE "role_own_54321_jojo" NOLOGIN;
ALTER ROLE "role_own_54321_jojo" SET search_path TO "own_54321_jojo";
GRANT "role_own_54321_jojo" TO admin; -- permet de faire le changement de owner du schéma en dessous
ALTER SCHEMA "own_54321_jojo" OWNER TO "role_own_54321_jojo";
END IF;

-- rattachement du user own_25640_swift au groupe role_own_54321_jojo
GRANT "role_own_54321_jojo" TO "{{name}}";

-- don des droits de manipulation des objets du schéma au groupe role_app_54321_jojo
SET ROLE "role_own_54321_jojo"; -- Les ALTER DEFAULT PRIVILEGE doivent être exécutés par le créateur des objets
ALTER DEFAULT PRIVILEGES IN SCHEMA own_54321_jojo GRANT SELECT,UPDATE,INSERT,DELETE ON TABLES TO role_app_54321_jojo;
ALTER DEFAULT PRIVILEGES IN SCHEMA own_54321_jojo GRANT USAGE ON SEQUENCES TO role_app_54321_jojo;
ALTER DEFAULT PRIVILEGES IN SCHEMA own_54321_jojo GRANT EXECUTE ON FUNCTIONS TO role_app_54321_jojo;
ALTER DEFAULT PRIVILEGES IN SCHEMA own_54321_jojo GRANT USAGE ON TYPES TO role_app_54321_jojo;

RESET ROLE;

ALTER ROLE "{{name}}" SET search_path TO own_54321_jojo;

ALTER ROLE "{{name}}" SET ROLE "role_own_54321_jojo"; -- Permet de se connecter directement avec le rôle chapeau (qui sera donc propriétaire des objets que vous créerez)
END
$do$;


#script : create_dyna_role_app_jojo.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 app-ibmclouddb-schema1 au groupe role_app_12345_schema1 (qui a été créé en même temps que role_own_12345_schema1)
GRANT role_app_54321_jojo TO "{{name}}";
ALTER ROLE "{{name}}" SET search_path TO own_54321_jojo ;
RESET ROLE;
END
$do$;