Roles creation for dMZR postgreSQL- version batch
script pour la création d'une nouvelle base dans le cloud IBM avec les rôles dans Hashicorp Vault.
#!/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" == site de prod ]]
then
if (( $# != 1 ))
then
echo " "
echo "USAGE: $0 <asset> <a|b|c|...>"
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" == site de hors prod ]]
then
if (( $# != 3 ))
then
echo " "
echo "USAGE: $0 <asset> <dev|int|qual> <a|b|c|...>"
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 | grep -i $SOUSENV | 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}')
#SOUSENV=$(cat $ORA2PG_HOME/adm/sh/nonprod | grep -i $ASSET | awk '{print $2}')
FIRST=${SOUSENV:0:1}
DB=${FIRST}${APREFI}$3
ENVDIR="$ORA2PG_HOME/$ENV/$SOUSENV/$ASSET"
else
echo " Wrong environment "
exit 1
fi
fi
mkdir -p $ENVDIR
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}}';
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 'PWD';">$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 stat_own_12345_toto au groupe role_own_12345_toto
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/dba_pg | grep password | awk '{print $2}')
export PGPASSWORD=$secret;createdb ${DB} -O ${DBO} -h ${SERVER}${DOMAIN} -p ${PORT} -U dba_pg
cat <<EOF> /tmp/static-grant-statements.sql
DO
$do$
BEGIN
CREATE SCHEMA IF NOT EXISTS "$SCHE";
-- création du groupe role_app_12345_toto s'il n'existe pas déjà, avec droit de se connecter au schéma own_12345_toto
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" with schema ibm_extension;
END IF;
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_extension WHERE extname = 'pg_stat_statements') THEN CREATE EXTENSION "pg_stat_statements" with schema ibm_extension;
END IF;
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_extension WHERE extname = 'pg_freespacemap') THEN CREATE EXTENSION "pg_freespacemap" with schema ibm_extension;
END IF;
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_extension WHERE extname = 'pg_repack') THEN CREATE EXTENSION "pg_repack" with schema ibm_extension;
END IF;
IF NOT EXISTS (
SELECT FROM pg_catalog.pg_extension WHERE extname = 'pgstattuple') THEN CREATE EXTENSION "pgstattuple" with schema ibm_extension;
END IF;
--don des droits de manipulation des objets du schéma au groupe role_own_12345_toto
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";
GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA "$SCHE" TO "role_$DYN";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "$SCHE" TO "role_$DYN";
GRANT EXECUTE ON ALL FUNCTIONS in SCHEMA "$SCHE" TO "role_$DYN";
GRANT USAGE ON SCHEMA "$SCHE" 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 "$SCHE";
ALTER ROLE "$STATIC_ROLE_NAME" SET ROLE "$DBO";
END
$do$;
EOF
export PGPASSWORD=$secret;psql -h ${SERVER}${DOMAIN} -p ${PORT} -U dba_pg -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 stat_app_12345_toto au groupe role_app_12345_toto (qui a été créé en même temps que role_own_12345_toto)
GRANT "role_$DYN" TO "{{name}}";
ALTER ROLE "{{name}}" SET search_path TO "$SCHE";
RESET ROLE;
ALTER ROLE "{{name}}" SET ROLE "role_$DYN";
ALTER ROLE "$STATIC_ROLE_NAME" SET ROLE "role_$DYN";
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_12345_toto au groupe role_dyn_12345_toto (qui a été créé en même temps que app_12345_toto)
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 " "
#with the account dba_pg
RO="ro_${APREFI}_${ASSET}"
export PGPASSWORD=$secret;psql -h ${SERVER}${DOMAIN} -p ${PORT} -U dba_pg -d ${DB} <<EOF
GRANT "$GRP" TO stat_app_${APREFI}_${ASSET};
ALTER ROLE stat_app_${APREFI}_${ASSET} set search_path=${SCHE};
CREATE ROLE "$RO" NOLOGIN;
CREATE ROLE sysdba WITH LOGIN CREATEDB CREATEROLE PASSWORD 'PWD';
--grant privs for sysdba
GRANT CREATE ON DATABASE "$DB" TO sysdba;
GRANT USAGE,CREATE ON SCHEMA "$SCHE" TO sysdba;
GRANT admin,"ibm-cloud-base-user","$DBO" TO sysdba;
ALTER ROLE sysdba SET search_path TO "$SCHE";
EOF
#with the account stat_own
ownsecret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/stat_${SCHE} | grep password | awk '{print $2}')
export PGPASSWORD=$ownsecret;psql -h ${SERVER}${DOMAIN} -p ${PORT} -U stat_${SCHE} -d ${DB} <<EOF
GRANT USAGE ON SCHEMA "$SCHE" TO "$GRP";
GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA "$SCHE" TO "$GRP";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "$SCHE" TO "$GRP";
GRANT EXECUTE ON ALL FUNCTIONS in SCHEMA "$SCHE" TO "$GRP";
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT SELECT,UPDATE,INSERT,DELETE ON TABLES TO "$GRP";
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT USAGE ON SEQUENCES TO "$GRP";
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT EXECUTE ON FUNCTIONS TO "$GRP";
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT USAGE ON TYPES TO "$GRP";
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT SELECT ON TABLES TO "$RO";
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT USAGE ON TYPES TO "$RO";
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT USAGE ON SEQUENCES TO "$RO";
ALTER DEFAULT PRIVILEGES IN SCHEMA "$SCHE" GRANT EXECUTE ON FUNCTIONS TO "$RO";
EOF
exit 0