# DOC: http://wiki.postgresql.org/wiki/Shared_Database_Hosting
-#"$tool"/local/apt-get-install postgresql-9.1
+"$tool"/local/apt-get-install postgresql-9.4
"$tool"/local/insserv-remove postgresql
"$tool"/local/adduser postgres \
--disabled-login \
--system
sudo usermod --home /home/postgresql postgres
sudo adduser postgres postgres-data
-sudo rm -rf \
- /etc/postgresql
sudo install -d -m 1751 -o postgres -g postgres-data \
/home/postgresql \
/home/postgresql/etc \
/etc/postgresql \
- /etc/postgresql/9.1 \
- /etc/postgresql/9.1/main
+ /etc/postgresql/9.4 \
+ /etc/postgresql/9.4/main
sudo ln -fns \
/etc/postgresql \
/home/postgresql/etc/postgresql
/home/postgresql/data
sudo -u postgres pg_createcluster \
--datadir=/home/postgresql/data \
- --logfile=/home/postgresql/log/9.1/main/cluster.log \
+ --logfile=/home/postgresql/log/9.4/main/cluster.log \
--socketdir=/run/postgresql \
- 9.1 main
+ 9.4 main
fi
sudo install -m 640 -o postgres -g postgres /dev/stdin \
- /etc/postgresql/9.1/main/pg_ctl.conf <<-EOF
+ /etc/postgresql/9.4/main/pg_ctl.conf <<-EOF
pg_ctl_options = ''
EOF
sudo install -m 640 -o postgres -g postgres /dev/stdin \
- /etc/postgresql/9.1/main/pg_ident.conf <<-EOF
- # MAPNAME SYSTEM-USERNAME PG-USERNAME
- admin postgres postgres
- admin root postgres
- EOF
-sudo install -m 640 -o postgres -g postgres /dev/stdin \
- /etc/postgresql/9.1/main/start.conf <<-EOF
- EOF
-sudo install -m 640 -o postgres -g postgres /dev/stdin \
- /etc/postgresql/9.1/main/pg_hba.conf <<-EOF
- local all postgres peer map=admin
- local all all peer
+ /etc/postgresql/9.4/main/start.conf <<-EOF
EOF
+sudo install -m 640 -o postgres -g postgres \
+ "$tool"/etc/postgresql/9.4/main/pg_ident.conf \
+ /etc/postgresql/9.4/main/pg_ident.conf
+sudo install -m 640 -o postgres -g postgres \
+ "$tool"/etc/postgresql/9.4/main/pg_hba.conf \
+ /etc/postgresql/9.4/main/pg_hba.conf
sudo install -m 640 -o postgres -g postgres-data \
- "$tool"/etc/postgresql/9.1/main/postgresql.conf \
- /etc/postgresql/9.1/main/postgresql.conf
+ "$tool"/etc/postgresql/9.4/main/postgresql.conf \
+ /etc/postgresql/9.4/main/postgresql.conf
+sudo install -m 640 -o postgres -g postgres \
+ "$tool"/var/pub/x509/postgresql."$local_domainname"/crt+ca.pem \
+ /etc/postgresql/9.4/main/server.crt
+sudo install -m 640 -o postgres -g postgres \
+ "$tool"/var/pub/x509/postgresql."$local_domainname"/crt.self-signed.pem \
+ /etc/postgresql/9.4/main/root.crt
+sudo install -m 640 -o postgres -g postgres \
+ "$tool"/var/pub/x509/postgresql."$local_domainname"/crl.self-signed.pem \
+ /etc/postgresql/9.4/main/root.crl
+for f in server.crt server.key root.crt root.crl
+ do sudo ln -fns \
+ /etc/postgresql/9.4/main/$f \
+ /home/postgresql/data/$f
+ done
sudo ln -fns \
../sv/"$sv" \
END AS plpgsql_created;
DROP FUNCTION create_language_plpgsql();
EOF
-# NOTE: supprime l'accès à la liste des bases données
-# et utilisateurices depuis public.
sudo -u postgres psql template1 -a -f - <<-EOF
\set ON_ERROR_STOP on
REVOKE ALL ON ALL TABLES IN SCHEMA pg_catalog FROM public;
REVOKE ALL ON SCHEMA pg_catalog FROM public;
- -- REVOKE ALL ON pg_auth_members FROM public;
- -- REVOKE ALL ON pg_authid FROM public;
- -- REVOKE ALL ON pg_database FROM public;
- -- REVOKE ALL ON pg_group FROM public;
- -- REVOKE ALL ON pg_roles FROM public;
- -- REVOKE ALL ON pg_settings FROM public;
- -- REVOKE ALL ON pg_tablespace FROM public;
- -- REVOKE ALL ON pg_user FROM public;
EOF
+"$tool"/local/postgresql-user-create backup
+sudo -u postgres psql template1 -a -f - <<-EOF
+ \set ON_ERROR_STOP on
+ ALTER USER backup WITH SUPERUSER;
+ -- NOTE: permet VACUUM
+ GRANT USAGE ON SCHEMA pg_catalog TO backup;
+ GRANT USAGE ON SCHEMA public TO backup;
+ GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO backup;
+ GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
+ GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup;
+ GRANT CONNECT ON DATABASE template1 TO backup;
+ GRANT CONNECT ON DATABASE postgres TO backup;
+ EOF
+sudo adduser backup postgres-data
+
+sudo find "$tool"/local/backup \
+ -mindepth 1 -maxdepth 1 -type f -perm /+x \
+ -name 'postgresql-*' \
+ -exec install -m 750 -o backup -g backup \
+ -t ~backup/bin {} +
+sudo install -m 640 -o root -g root \
+ "$tool"/etc/cron.d/postgresql-backup \
+ /etc/cron.d/postgresql-backup