etc/sv/postgres/local.sh

   1 # DOC: http://wiki.postgresql.org/wiki/Shared_Database_Hosting
   2
   3 "$tool"/local/apt-get-install postgresql-9.1
   4 "$tool"/local/insserv-remove  postgresql
   5 "$tool"/local/adduser postgres \
   6  --disabled-login \
   7  --disabled-password \
   8  --group \
   9  --home /home/postgresql \
  10  --shell /bin/false \
  11  --system
  12 "$tool"/local/adduser postgres-data \
  13  --disabled-login \
  14  --disabled-password \
  15  --group \
  16  --home /home/postgresql/data \
  17  --no-create-home \
  18  --shell /bin/false \
  19  --system
  20 sudo usermod --home /home/postgresql postgres
  21 sudo adduser postgres postgres-data
  22 sudo install -d -m 1751 -o postgres -g postgres-data \
  23  /home/postgresql \
  24  /home/postgresql/etc \
  25  /etc/postgresql \
  26  /etc/postgresql/9.1 \
  27  /etc/postgresql/9.1/main
  28 sudo ln -fns \
  29                  /etc/postgresql \
  30  /home/postgresql/etc/postgresql
  31
  32 if sudo test ! -d /home/postgresql/data
  33  then
  34         sudo install -d -m 750 -o postgres -g postgres \
  35          /home/postgresql/data
  36         sudo -u postgres pg_createcluster \
  37          --datadir=/home/postgresql/data \
  38          --logfile=/home/postgresql/log/9.1/main/cluster.log  \
  39          --socketdir=/run/postgresql \
  40          9.1 main
  41  fi
  42
  43 sudo install -m 640 -o postgres -g postgres /dev/stdin \
  44  /etc/postgresql/9.1/main/pg_ctl.conf <<-EOF
  45         pg_ctl_options = ''
  46         EOF
  47 sudo install -m 640 -o postgres -g postgres /dev/stdin \
  48  /etc/postgresql/9.1/main/pg_ident.conf <<-EOF
  49         # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
  50         admin           postgres                postgres
  51         admin           root                    postgres
  52         EOF
  53 sudo install -m 640 -o postgres -g postgres /dev/stdin \
  54  /etc/postgresql/9.1/main/start.conf <<-EOF
  55         EOF
  56 sudo install -m 640 -o postgres -g postgres /dev/stdin \
  57  /etc/postgresql/9.1/main/pg_hba.conf <<-EOF
  58         local all postgres peer map=admin
  59         local all all      peer
  60         hostssl all postgres samehost cert
  61         EOF
  62 sudo install -m 640 -o postgres -g postgres-data \
  63  "$tool"/etc/postgresql/9.1/main/postgresql.conf \
  64         /etc/postgresql/9.1/main/postgresql.conf
  65 sudo install -m 640 -o postgres -g postgres \
  66  "$tool"/var/pub/x509/postgresql."$local_domainname"/crt+ca.pem \
  67  /etc/postgresql/9.1/main/server.crt
  68 sudo install -m 640 -o postgres -g postgres \
  69  "$tool"/var/pub/x509/postgresql."$local_domainname"/crt.self-signed.pem \
  70  /etc/postgresql/9.1/main/root.crt
  71 sudo install -m 640 -o postgres -g postgres \
  72  "$tool"/var/pub/x509/postgresql."$local_domainname"/crl.self-signed.pem \
  73  /etc/postgresql/9.1/main/root.crl
  74 for f in server.crt server.key root.crt root.crl
  75  do sudo ln -fns \
  76          /etc/postgresql/9.1/main/$f \
  77          /home/postgresql/data/$f
  78  done
  79
  80 sudo ln -fns \
  81         ../sv/"$sv" \
  82  /etc/service/"$sv"
  83 "$tool"/local/runit-sv-start "$sv"
  84 while ! sudo -u postgres psql </dev/null
  85 do sleep 1; done
  86
  87 # NOTE: supprime l'accès au schéma public depuis public,
  88 #       de sorte à ce que les différents utilisateurices
  89 #       ne voient pas leurs bases de données entre-elleux ;
  90 sudo -u postgres psql template1 -a -f - <<-EOF
  91         \set ON_ERROR_STOP on
  92         REVOKE ALL ON DATABASE template1 FROM public;
  93         REVOKE ALL ON SCHEMA   public    FROM public;
  94         GRANT  ALL ON SCHEMA   public    TO   postgres;
  95         EOF
  96 # NOTE: ajoute le support de PL/PGSQL s'il ne l'est pas déjà.
  97 sudo -u postgres psql template1 -a -f - <<-EOF
  98         \set ON_ERROR_STOP on
  99         CREATE OR REPLACE FUNCTION create_language_plpgsql()
 100                 RETURNS BOOLEAN AS \$\$
 101                         CREATE LANGUAGE plpgsql;
 102                         SELECT TRUE;
 103                 \$\$ LANGUAGE SQL;
 104         SELECT CASE WHEN NOT (
 105                 SELECT  TRUE AS exists
 106                 FROM    pg_language
 107                 WHERE   lanname = 'plpgsql'
 108                 UNION
 109                 SELECT  FALSE AS exists
 110                 ORDER BY exists DESC
 111                 LIMIT 1
 112          )
 113         THEN
 114                 create_language_plpgsql()
 115         ELSE
 116                 FALSE
 117         END AS plpgsql_created;
 118         DROP FUNCTION create_language_plpgsql();
 119         EOF
 120 sudo -u postgres psql template1 -a -f - <<-EOF
 121         \set ON_ERROR_STOP on
 122         REVOKE ALL ON ALL TABLES IN SCHEMA pg_catalog FROM public;
 123         REVOKE ALL ON               SCHEMA pg_catalog FROM public;
 124         EOF
 125 "$tool"/local/postgresql-user-create backup
 126 sudo -u postgres psql template1 -a -f - <<-EOF
 127         \set ON_ERROR_STOP on
 128         ALTER USER backup WITH SUPERUSER;
 129                 -- NOTE: permet VACUUM
 130         GRANT USAGE  ON                  SCHEMA pg_catalog TO backup;
 131         GRANT USAGE  ON                  SCHEMA public     TO backup;
 132         GRANT SELECT ON ALL TABLES    IN SCHEMA pg_catalog TO backup;
 133         GRANT SELECT ON ALL TABLES    IN SCHEMA public     TO backup;
 134         GRANT SELECT ON ALL SEQUENCES IN SCHEMA public     TO backup;
 135         GRANT CONNECT ON DATABASE template1 TO backup;
 136         GRANT CONNECT ON DATABASE postgres  TO backup;
 137         EOF
 138 sudo adduser backup postgres-data
 139
 140 sudo find "$tool"/local/backup \
 141  -mindepth 1 -maxdepth 1 -type f -perm /+x \
 142  -name 'postgresql-*' \
 143  -exec install -m 750 -o backup -g backup \
 144  -t ~backup/bin {} +
 145 sudo install -m 640 -o root -g root \
 146  "$tool"/etc/cron.d/postgresql-backup \
 147         /etc/cron.d/postgresql-backup