#!/bin/bash
#set -x


DB="template1"
echo "INGRESE EL NOMBRE DE LA BASE:"
read BASE
echo "INGRESE EL USUARIO DE LA APP:"
read USER
touch dcl-postgres-$BASE.sql


echo ""

##
## BAJO TODA LA INFO DE LOS ROLES
##
pg_dumpall -g -f role.txt

##
## SACO LA CREACION DEL USER
##
grep "CREATE ROLE $USER" role.txt >> dcl-postgres-$BASE.sql

echo "" >> dcl-postgres-$BASE.sql

##
## SACO LOS ALTER DEL USER
##
grep "ALTER ROLE $USER" role.txt >> dcl-postgres-$BASE.sql

echo "" >> dcl-postgres-$BASE.sql

##
## SACO LOS GRUPOS A LOS QUE PERTENECE EL USER
##
GRUPOS=`/usr/bin/psql $DB -c "SELECT ur.rolname AS GRUPOS FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid LEFT JOIN pg_authid um on um.oid = a.member LEFT JOIN pg_authid ug on ug.oid = a.grantor WHERE um.rolname = '$USER';" -t 2>/dev/null | awk '{print $1}' | egrep -v "^$"`

  for i in `/usr/bin/psql $DB -c "SELECT ur.rolname AS GRUPOS FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid LEFT JOIN pg_authid um on um.oid = a.member LEFT JOIN pg_authid ug on ug.oid = a.grantor WHERE um.rolname = '$USER';" -t 2>/dev/null | awk '{print $1}' | egrep -v "^$";`
  
  do
        grep "CREATE ROLE $i;" role.txt >> dcl-postgres-$BASE.sql
        grep "ALTER ROLE $i" role.txt >> dcl-postgres-$BASE.sql
        grep "GRANT $i TO $USER" role.txt >> dcl-postgres-$BASE.sql
        echo "" >> dcl-postgres-$BASE.sql
  done

rm role.txt


##
## BAJO EL ESQUEMA
##
pg_dump -C -s $BASE -f ddl-postgres-$BASE.sql


##
## TENGO QUE BAJAR DATOS DE ALGUNA TABLA
##

echo "QUIERE SACAR DATOS DE ALGUNA TABLA:?(si/no)"
read DATA

while [ "si" == $DATA ]
    do
    
    echo "QUE TABLA DESEA BAJAR:?"
    read TABLA
    
    pg_dump -d $BASE -a -t $TABLA --column-inserts  -f dml-postgres-$BASE-$TABLA.sql

    echo "QUIERE SEGUIR SACANDO DATOS DE OTRA TABLA:?(si/no)"
    read DATA

done


