Ajout : sauvegardes automatiques des bases postgresql.

[lhc/ateliers.git] / etc / sv / postgres / local.sh

diff --git a/etc/sv/postgres/local.sh b/etc/sv/postgres/local.sh
index 1bcd0f6..8b784c1 100644 (file)
--- 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
 
 # DOC: http://wiki.postgresql.org/wiki/Shared_Database_Hosting
 

-#"$tool"/local/apt-get-install postgresql-9.1
+"$tool"/local/apt-get-install postgresql-9.1

 "$tool"/local/insserv-remove  postgresql
 "$tool"/local/adduser postgres \
  --disabled-login \
 "$tool"/local/insserv-remove  postgresql
 "$tool"/local/adduser postgres \
  --disabled-login \


@@ -19,8 +19,6 @@
  --system
 sudo usermod --home /home/postgresql postgres
 sudo adduser postgres postgres-data
  --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 \
 sudo install -d -m 1751 -o postgres -g postgres-data \
  /home/postgresql \
  /home/postgresql/etc \


@@ -59,10 +57,25 @@ 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.1/main/pg_hba.conf <<-EOF
        local all postgres peer map=admin
        local all all      peer

+       hostssl all postgres samehost cert

        EOF
 sudo install -m 640 -o postgres -g postgres-data \
  "$tool"/etc/postgresql/9.1/main/postgresql.conf \
         /etc/postgresql/9.1/main/postgresql.conf
        EOF
 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 install -m 640 -o postgres -g postgres \
+ "$tool"/var/pub/x509/postgresql."$local_domainname"/crt+ca.pem \
+ /etc/postgresql/9.1/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.1/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.1/main/root.crl
+for f in server.crt server.key root.crt root.crl
+ do sudo ln -fns \
+        /etc/postgresql/9.1/main/$f \
+        /home/postgresql/data/$f
+ done

 
 sudo ln -fns \
         ../sv/"$sv" \
 
 sudo ln -fns \
         ../sv/"$sv" \


@@ -104,18 +117,31 @@ sudo -u postgres psql template1 -a -f - <<-EOF
        END AS plpgsql_created;
        DROP FUNCTION create_language_plpgsql();
        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;
 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
        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