-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathpgsql-9.5-debian-8-3-x-64.sh
250 lines (223 loc) · 8.01 KB
/
pgsql-9.5-debian-8-3-x-64.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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
#!/bin/bash -e
#Version: 0.6.1
apt-get install -y lsb-release
RELEASE=$(lsb_release -cs)
PG_VER='9.5'
UNPRIV_USER='pgmin'
touch /root/auth.txt
apt-get clean
apt-get update
function install_postgresql(){
#3. Install PostgreSQL
echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet --no-check-certificate -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get install -y postgresql-${PG_VER} postgresql-client-${PG_VER} postgresql-contrib-${PG_VER} \
python3-postgresql postgresql-plperl-${PG_VER} postgresql-plpython-${PG_VER} \
postgresql-pltcl-${PG_VER} odbc-postgresql libpostgresql-jdbc-java sudo
if [ ! -f /usr/lib/postgresql/${PG_VER}/bin/postgres ]; then
echo "Error: Get PostgreSQL version"; exit 1;
fi
ln -sf /usr/lib/postgresql/${PG_VER}/bin/pg_config /usr/bin
ln -sf /var/lib/postgresql/${PG_VER}/main/ /var/lib/postgresql
ln -sf /var/lib/postgresql/${PG_VER}/backups /var/lib/postgresql
service postgresql start
#5. Set postgres Password
if [ $(grep -m 1 -c 'pg pass' /root/auth.txt) -eq 0 ]; then
PG_PASS=$(< /dev/urandom tr -dc _A-Z-a-z-0-9 | head -c32);
sudo -u postgres psql 2>/dev/null -c "alter user postgres with password '${PG_PASS}'"
echo "pg pass: ${PG_PASS}" > /root/auth.txt
fi
#4. Add Postgre variables to environment
if [ $(grep -m 1 -c 'PGDATA' /etc/environment) -eq 0 ]; then
cat >>/etc/environment <<CMD_EOF
export PGDATA=/var/lib/postgresql/${PG_VER}/main
CMD_EOF
fi
#6. Configure ph_hba.conf
cat >/etc/postgresql/${PG_VER}/main/pg_hba.conf <<CMD_EOF
local all all md5
host all all 127.0.0.1 255.255.255.255 md5
host all all 0.0.0.0/0 md5
host all all ::1/128 md5
hostssl all all 127.0.0.1 255.255.255.255 md5
hostssl all all 0.0.0.0/0 md5
hostssl all all ::1/128 md5
CMD_EOF
sed -i.save "s/.*listen_addresses.*/listen_addresses = '*'/" /etc/postgresql/${PG_VER}/main/postgresql.conf
sed -i.save "s/.*ssl =.*/ssl = on/" /etc/postgresql/${PG_VER}/main/postgresql.conf
#10. Create Symlinks for Backward Compatibility from PostgreSQL 9 to PostgreSQL 8
#ln -sf /usr/pgsql-9.4/bin/pg_config /usr/bin
mkdir -p /var/lib/pgsql
ln -sf /var/lib/postgresql/${PG_VER}/main /var/lib/pgsql
ln -sf /var/lib/postgresql/${PG_VER}/backups /var/lib/pgsql
#create SSL certificates
if [ ! -f /var/lib/postgresql/${PG_VER}/main/server.key -o ! -f /var/lib/postgresql/${PG_VER}/main/server.crt ]; then
SSL_PASS=$(< /dev/urandom tr -dc _A-Z-a-z-0-9 | head -c32);
if [ $(grep -m 1 -c 'ssl pass' /root/auth.txt) -eq 0 ]; then
echo "ssl pass: ${SSL_PASS}" >> /root/auth.txt
else
sed -i.save "s/ssl pass:.*/ssl pass: ${SSL_PASS}/" /root/auth.txt
fi
openssl genrsa -des3 -passout pass:${SSL_PASS} -out server.key 1024
openssl rsa -in server.key -passin pass:${SSL_PASS} -out server.key
chmod 400 server.key
openssl req -new -key server.key -days 3650 -out server.crt -passin pass:${SSL_PASS} -x509 -subj '/C=CA/ST=Frankfurt/L=Frankfurt/O=acuciva-de.com/CN=acuciva-de.com/[email protected]'
chown postgres.postgres server.key server.crt
mv server.key server.crt /var/lib/postgresql/${PG_VER}/main
fi
service postgresql restart
}
function install_webmin(){
apt-get -y install libnet-ssleay-perl libauthen-pam-perl apt-show-versions libio-pty-perl libapt-pkg-perl
if [ ! -d /usr/share/webmin/ ]; then
wget http://www.webmin.com/download/deb/webmin-current.deb
dpkg -i webmin-current.deb
rm -rf webmin-current.deb
fi
cat >/etc/webmin/postgresql/config <<EOF
simple_sched=0
sameunix=1
date_subs=0
max_text=1000
perpage=25
stop_cmd=service postgresql stop
psql=/usr/bin/psql
pid_file=/var/run/postgresql/${PG_VER}-main.pid
hba_conf=/etc/postgresql/${PG_VER}/main/pg_hba.conf
setup_cmd=service postgresql start
user=postgres
nodbi=0
max_dbs=50
start_cmd=service postgresql start
repository=/var/lib/pgsql/${PG_VER}/backups
dump_cmd=/usr/bin/pg_dump
access=*: *
webmin_subs=0
style=0
rstr_cmd=/usr/bin/pg_restore
access_own=0
login=postgres
basedb=template1
add_mode=1
blob_mode=0
pass=${PG_PASS}
plib=
encoding=
port=
host=
EOF
service webmin restart
}
function secure_iptables(){
#Set firewall rules
cat >/etc/iptables.save <<EOF
# Generated by iptables-save v1.4.7 on Sun Dec 13 10:24:17 2015
*nat
:PREROUTING ACCEPT [0:0]
:POSTROUTING ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
COMMIT
# Completed on Sun Dec 13 10:24:17 2015
# Generated by iptables-save v1.4.7 on Sun Dec 13 10:24:17 2015
*mangle
:PREROUTING ACCEPT [0:0]
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:POSTROUTING ACCEPT [0:0]
COMMIT
# Completed on Sun Dec 13 10:24:17 2015
# Generated by iptables-save v1.4.7 on Sun Dec 13 10:24:17 2015
*filter
:FORWARD ACCEPT [0:0]
:INPUT DROP [0:0]
:OUTPUT ACCEPT [0:0]
# Accept traffic from internal interfaces
-A INPUT ! -i eth0 -j ACCEPT
# Accept traffic with the ACK flag set
-A INPUT -p tcp -m tcp --tcp-flags ACK ACK -j ACCEPT
# Allow incoming data that is part of a connection we established
-A INPUT -m state --state ESTABLISHED -j ACCEPT
# Allow data that is related to existing connections
-A INPUT -m state --state RELATED -j ACCEPT
# Accept responses to DNS queries
-A INPUT -p udp -m udp --dport 1024:65535 --sport 53 -j ACCEPT
# Accept responses to our pings
-A INPUT -p icmp -m icmp --icmp-type echo-reply -j ACCEPT
# Accept notifications of unreachable hosts
-A INPUT -p icmp -m icmp --icmp-type destination-unreachable -j ACCEPT
# Accept notifications to reduce sending speed
-A INPUT -p icmp -m icmp --icmp-type source-quench -j ACCEPT
# Accept notifications of lost packets
-A INPUT -p icmp -m icmp --icmp-type time-exceeded -j ACCEPT
# Accept notifications of protocol problems
-A INPUT -p icmp -m icmp --icmp-type parameter-problem -j ACCEPT
# Allow connections to our SSH server
-A INPUT -p tcp -m tcp --dport 3838 -j ACCEPT
# Allow connections to our IDENT server
-A INPUT -p tcp -m tcp --dport auth -j ACCEPT
# Respond to pings
-A INPUT -p icmp -m icmp --icmp-type echo-request -j ACCEPT
# Allow DNS zone transfers
-A INPUT -p tcp -m tcp --dport 53 -j ACCEPT
# Allow DNS queries
-A INPUT -p udp -m udp --dport 53 -j ACCEPT
# Allow connections to webserver
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
# Allow SSL connections to webserver
-A INPUT -p tcp -m tcp --dport 443 -j ACCEPT
# Allow connections to mail server
-A INPUT -p tcp -m tcp -m multiport -j ACCEPT --dports 25,587
# Allow connections to FTP server
-A INPUT -p tcp -m tcp --dport 20:21 -j ACCEPT
# Allow connections to POP3 server
-A INPUT -p tcp -m tcp -m multiport -j ACCEPT --dports 110,995
# Allow connections to IMAP server
-A INPUT -p tcp -m tcp -m multiport -j ACCEPT --dports 143,220,993
# Allow connections to Webmin
-A INPUT -p tcp -m tcp --dport 10000:10010 -j ACCEPT
# Allow connections to Usermin
-A INPUT -p tcp -m tcp --dport 20000 -j ACCEPT
# Postgres
-A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT
# pgbouncer
-A INPUT -p tcp -m tcp --dport 6432 -j ACCEPT
# SSH
-A INPUT -p tcp -m tcp --dport 3838 -j ACCEPT
COMMIT
EOF
cat >/etc/network/if-pre-up.d/iptablesload <<EOF
#!/bin/sh
iptables-restore < /etc/iptables.rules
exit 0
EOF
iptables-restore < /etc/iptables.save
}
function secure_ssh(){
if [ $(grep -m 1 -c ${UNPRIV_USER} /etc/passwd) -eq 0 ]; then
useradd -m ${UNPRIV_USER}
fi
if [ $(grep -m 1 -c "${UNPRIV_USER} pass" /root/auth.txt) -eq 0 ]; then
USER_PASS=$(< /dev/urandom tr -dc _A-Z-a-z-0-9 | head -c32);
echo "${UNPRIV_USER}:${USER_PASS}" | chpasswd
echo "${UNPRIV_USER} pass: ${USER_PASS}" >> /root/auth.txt
fi
sed -i.save 's/#\?Port [0-9]\+/Port 3838/' /etc/ssh/sshd_config
sed -i.save 's/#\?PermitRootLogin.*/PermitRootLogin no/' /etc/ssh/sshd_config
service ssh restart
}
install_postgresql;
install_webmin;
secure_iptables;
secure_ssh;
apt-get -y install pgbouncer
apt-get -y install libdbd-pg-perl
#change root password
if [ $(grep -m 1 -c 'root pass' /root/auth.txt) -eq 0 ]; then
ROOT_PASS=$(< /dev/urandom tr -dc _A-Z-a-z-0-9 | head -c32);
echo "root:${ROOT_PASS}" | chpasswd
echo "root pass: ${ROOT_PASS}" >> /root/auth.txt
fi
echo "Passwords saved in /root/auth.txt"
cat /root/auth.txt