X-Git-Url: https://git.cyclocoop.org/?p=lhc%2Fateliers.git;a=blobdiff_plain;f=etc%2Fsv%2Fpostgres%2Flocal.sh;h=d8f47de54bb537ce2af8939e06a637232dd86a1c;hp=cb8eee67b4eecf13a996e754b23fe18de2263fb1;hb=f1aabff1ce5272bc0d54d3da0d21d7b07e66477e;hpb=3ad6118386977e346d81042e924e5db9c5f15b7d

diff --git a/etc/sv/postgres/local.sh b/etc/sv/postgres/local.sh
index cb8eee6..d8f47de 100644
--- a/etc/sv/postgres/local.sh
+++ b/etc/sv/postgres/local.sh
@@ -1,6 +1,6 @@
 # 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 \
@@ -19,15 +19,12 @@
  --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 \
- /home/postgresql/bin \
  /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
@@ -38,35 +35,41 @@ if sudo test ! -d /home/postgresql/data
 	 /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
-sudo find "$tool"/etc/postgresql/bin/ -type f -perm /+x -exec \
-	install -m 755 -o root -g root \
-	 -t /home/postgresql/bin/ {} +
+ "$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" \
@@ -108,18 +111,31 @@ sudo -u postgres psql template1 -a -f - <<-EOF
 	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