-
Notifications
You must be signed in to change notification settings - Fork 10
/
dropFastRefreshDataSchemas.sh
134 lines (99 loc) · 4.47 KB
/
dropFastRefreshDataSchemas.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
#! /bin/bash
#File: dropFastRefreshDataSchemas.sh
#Desc:
#
# Amendment History:
# Date: Who: Desc:
# 28/10/19 T.Mullen Initial;
#
. ./module_set_variables.sh
export LOG_FILE=/tmp/dropFastRefreshDataSchemas_`date +%Y%m%d-%H%M`.log
echo "INFO: Set variables" >> $LOG_FILE
echo "INFO: LOG_FILE parameter set to $LOG_FILE" >> $LOG_FILE
echo "INFO: MODULEOWNER parameter set to $MODULEOWNER" >> $LOG_FILE
echo "INFO: PGUSERNAME parameter set to $PGUSERNAME" >> $LOG_FILE
echo "INFO: SOURCEUSERNAME parameter set to $SOURCEUSERNAME" >> $LOG_FILE
echo "INFO: MVUSERNAME parameter set to $PGUSERNAME" >> $LOG_FILE
echo "INFO: HOSTNAME parameter set to $HOSTNAME" >> $LOG_FILE
echo "INFO: PORT parameter set to $PORT" >> $LOG_FILE
echo "INFO: DBNAME parameter set to $DBNAME" >> $LOG_FILE
echo "INFO: MODULE_HOME parameter set to $MODULE_HOME" >> $LOG_FILE
PGPASS=$PGPASSWORD
function dropmvschema
{
echo "INFO: Dropping MV user $MVUSERNAME " >> $LOG_FILE
PGPASSWORD=$PGPASS
echo "INFO: Run Cron revoke permissions from $MVUSERNAME user" >> $LOG_FILE
echo "INFO: Connect to postgres database via PSQL session" >> $LOG_FILE
psql --host=$HOSTNAME --port=$PORT --username=$PGUSERNAME --dbname=postgres -v MODULE_HOME=$MODULE_HOME -v MODULEOWNERPASS=$MODULEOWNERPASS -v MODULEOWNER=$MODULEOWNER << EOFC >> $LOG_FILE 2>&1
REVOKE USAGE ON SCHEMA cron FROM $MVUSERNAME;
REVOKE ALL PRIVILEGES ON SCHEMA cron FROM $MVUSERNAME;
REVOKE ALL ON ALL TABLES in schema cron FROM $MVUSERNAME;
REVOKE ALL ON ALL sequences in schema cron FROM $MVUSERNAME;
\q
EOFC
psql --host=$HOSTNAME --port=$PORT --username=$PGUSERNAME --dbname=$DBNAME << EOF3 >> $LOG_FILE 2>&1
REVOKE ALL PRIVILEGES ON DATABASE "$DBNAME" from $MVUSERNAME;
GRANT ALL PRIVILEGES ON SCHEMA $MVUSERNAME to $PGUSERNAME;
REVOKE USAGE ON FOREIGN SERVER pgmv\$_instance FROM $MVUSERNAME;
REVOKE USAGE ON FOREIGN SERVER pgmv\$cron_instance FROM $MVUSERNAME;
ALTER SCHEMA $MVUSERNAME OWNER TO $PGUSERNAME;
DROP SCHEMA $MVUSERNAME CASCADE;
REVOKE $SOURCEUSERNAME from $MVUSERNAME;
revoke pgmv$_role from $MVUSERNAME;
revoke $SOURCEUSERNAME from $MODULEOWNER;
revoke $MVUSERNAME from $MODULEOWNER;
revoke $MODULEOWNER from $MVUSERNAME ;
revoke $MVUSERNAME from $PGUSERNAME;
REVOKE ALL PRIVILEGES ON SCHEMA $MVUSERNAME from $MVUSERNAME;
revoke USAGE ON SCHEMA $MODULEOWNER from $MVUSERNAME ;
DROP USER $MVUSERNAME;
EOF3
}
function dropsourceschema
{
echo "INFO: Dropping Schema user $SOURCEUSERNAME " >> $LOG_FILE
PGPASSWORD=$PGPASS
echo "INFO: Run Cron revoke permissions from $SOURCEUSERNAME user" >> $LOG_FILE
echo "INFO: Connect to postgres database via PSQL session" >> $LOG_FILE
psql --host=$HOSTNAME --port=$PORT --username=$PGUSERNAME --dbname=postgres -v MODULE_HOME=$MODULE_HOME -v MODULEOWNERPASS=$MODULEOWNERPASS -v MODULEOWNER=$MODULEOWNER << EOFC >> $LOG_FILE 2>&1
REVOKE USAGE ON SCHEMA cron FROM $SOURCEUSERNAME;
REVOKE ALL PRIVILEGES ON SCHEMA cron FROM $SOURCEUSERNAME;
REVOKE ALL ON ALL TABLES in schema cron FROM $SOURCEUSERNAME;
REVOKE ALL ON ALL sequences in schema cron FROM $SOURCEUSERNAME;
\q
EOFC
psql --host=$HOSTNAME --port=$PORT --username=$PGUSERNAME --dbname=$DBNAME << EOF3 >> $LOG_FILE 2>&1
REVOKE ALL PRIVILEGES ON DATABASE "$DBNAME" from $SOURCEUSERNAME;
GRANT ALL PRIVILEGES ON SCHEMA $SOURCEUSERNAME to $PGUSERNAME;
REVOKE USAGE ON FOREIGN SERVER pgmv\$_instance FROM $SOURCEUSERNAME;
REVOKE USAGE ON FOREIGN SERVER pgmv\$cron_instance FROM $SOURCEUSERNAME;
ALTER SCHEMA $SOURCEUSERNAME OWNER TO $PGUSERNAME;
DROP SCHEMA $SOURCEUSERNAME CASCADE;
REVOKE $SOURCEUSERNAME from $PGUSERNAME;
REVOKE $SOURCEUSERNAME FROM $MODULEOWNER;
REVOKE USAGE ON SCHEMA $SOURCEUSERNAME FROM $MODULEOWNER;
REVOKE ALL ON SCHEMA $MODULEOWNER FROM $SOURCEUSERNAME;
DROP USER $SOURCEUSERNAME;
EOF3
}
function truncatemoduletbls
{
echo "INFO: Truncating modules tables" >> $LOG_FILE
PGPASSWORD=$MODULEOWNERPASS
psql --host=$HOSTNAME --port=$PORT --username=$MODULEOWNER --dbname=$DBNAME << EOF4 >> $LOG_FILE 2>&1
truncate table pgmview_logs;
truncate table pgmviews;
truncate table pgmviews_oj_details;
EOF4
}
read -p "Are you sure you want to remove the schemas - $MVUSERNAME and $SOURCEUSERNAME (y/n)?" choice
case "$choice" in
y|Y ) echo "yes selected the schemas - $MVUSERNAME and $SOURCEUSERNAME will be dropped"
dropmvschema
dropsourceschema
truncatemoduletbls;;
n|N ) echo "no selected so exiting";;
* ) echo "invalid choice exiting";;
esac
echo "INFO: Drop Complete check logfile for status - $LOG_FILE"