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/start.conf <<-EOF
  49         EOF
  50 sudo install -m 640 -o postgres -g postgres \
  51  "$tool"/etc/postgresql/9.1/main/pg_ident.conf \
  52         /etc/postgresql/9.1/main/pg_ident.conf
  53 sudo install -m 640 -o postgres -g postgres \
  54  "$tool"/etc/postgresql/9.1/main/pg_hba.conf \
  55         /etc/postgresql/9.1/main/pg_hba.conf
  56 sudo install -m 640 -o postgres -g postgres-data \
  57  "$tool"/etc/postgresql/9.1/main/postgresql.conf \
  58         /etc/postgresql/9.1/main/postgresql.conf
  59 sudo install -m 640 -o postgres -g postgres \
  60  "$tool"/var/pub/x509/postgresql."$local_domainname"/crt+ca.pem \
  61  /etc/postgresql/9.1/main/server.crt
  62 sudo install -m 640 -o postgres -g postgres \
  63  "$tool"/var/pub/x509/postgresql."$local_domainname"/crt.self-signed.pem \
  64  /etc/postgresql/9.1/main/root.crt
  65 sudo install -m 640 -o postgres -g postgres \
  66  "$tool"/var/pub/x509/postgresql."$local_domainname"/crl.self-signed.pem \
  67  /etc/postgresql/9.1/main/root.crl
  68 for f in server.crt server.key root.crt root.crl
  69  do sudo ln -fns \
  70          /etc/postgresql/9.1/main/$f \
  71          /home/postgresql/data/$f
  72  done
  73
  74 sudo ln -fns \
  75         ../sv/"$sv" \
  76  /etc/service/"$sv"
  77 "$tool"/local/runit-sv-start "$sv"
  78 while ! sudo -u postgres psql </dev/null
  79 do sleep 1; done
  80
  81 # NOTE: supprime l'accès au schéma public depuis public,
  82 #       de sorte à ce que les différents utilisateurices
  83 #       ne voient pas leurs bases de données entre-elleux ;
  84 sudo -u postgres psql template1 -a -f - <<-EOF
  85         \set ON_ERROR_STOP on
  86         REVOKE ALL ON DATABASE template1 FROM public;
  87         REVOKE ALL ON SCHEMA   public    FROM public;
  88         GRANT  ALL ON SCHEMA   public    TO   postgres;
  89         EOF
  90 # NOTE: ajoute le support de PL/PGSQL s'il ne l'est pas déjà.
  91 sudo -u postgres psql template1 -a -f - <<-EOF
  92         \set ON_ERROR_STOP on
  93         CREATE OR REPLACE FUNCTION create_language_plpgsql()
  94                 RETURNS BOOLEAN AS \$\$
  95                         CREATE LANGUAGE plpgsql;
  96                         SELECT TRUE;
  97                 \$\$ LANGUAGE SQL;
  98         SELECT CASE WHEN NOT (
  99                 SELECT  TRUE AS exists
 100                 FROM    pg_language
 101                 WHERE   lanname = 'plpgsql'
 102                 UNION
 103                 SELECT  FALSE AS exists
 104                 ORDER BY exists DESC
 105                 LIMIT 1
 106          )
 107         THEN
 108                 create_language_plpgsql()
 109         ELSE
 110                 FALSE
 111         END AS plpgsql_created;
 112         DROP FUNCTION create_language_plpgsql();
 113         EOF
 114 sudo -u postgres psql template1 -a -f - <<-EOF
 115         \set ON_ERROR_STOP on
 116         REVOKE ALL ON ALL TABLES IN SCHEMA pg_catalog FROM public;
 117         REVOKE ALL ON               SCHEMA pg_catalog FROM public;
 118         EOF
 119 "$tool"/local/postgresql-user-create backup
 120 sudo -u postgres psql template1 -a -f - <<-EOF
 121         \set ON_ERROR_STOP on
 122         ALTER USER backup WITH SUPERUSER;
 123                 -- NOTE: permet VACUUM
 124         GRANT USAGE  ON                  SCHEMA pg_catalog TO backup;
 125         GRANT USAGE  ON                  SCHEMA public     TO backup;
 126         GRANT SELECT ON ALL TABLES    IN SCHEMA pg_catalog TO backup;
 127         GRANT SELECT ON ALL TABLES    IN SCHEMA public     TO backup;
 128         GRANT SELECT ON ALL SEQUENCES IN SCHEMA public     TO backup;
 129         GRANT CONNECT ON DATABASE template1 TO backup;
 130         GRANT CONNECT ON DATABASE postgres  TO backup;
 131         EOF
 132 sudo adduser backup postgres-data
 133
 134 sudo find "$tool"/local/backup \
 135  -mindepth 1 -maxdepth 1 -type f -perm /+x \
 136  -name 'postgresql-*' \
 137  -exec install -m 750 -o backup -g backup \
 138  -t ~backup/bin {} +
 139 sudo install -m 640 -o root -g root \
 140  "$tool"/etc/cron.d/postgresql-backup \
 141         /etc/cron.d/postgresql-backup