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 rm -rf \
  23  /etc/postgresql
  24 sudo install -d -m 1751 -o postgres -g postgres-data \
  25  /home/postgresql \
  26  /home/postgresql/etc \
  27  /etc/postgresql \
  28  /etc/postgresql/9.1 \
  29  /etc/postgresql/9.1/main
  30 sudo ln -fns \
  31                  /etc/postgresql \
  32  /home/postgresql/etc/postgresql
  33
  34 if sudo test ! -d /home/postgresql/data
  35  then
  36         sudo install -d -m 750 -o postgres -g postgres \
  37          /home/postgresql/data
  38         sudo -u postgres pg_createcluster \
  39          --datadir=/home/postgresql/data \
  40          --logfile=/home/postgresql/log/9.1/main/cluster.log  \
  41          --socketdir=/run/postgresql \
  42          9.1 main
  43  fi
  44
  45 sudo install -m 640 -o postgres -g postgres /dev/stdin \
  46  /etc/postgresql/9.1/main/pg_ctl.conf <<-EOF
  47         pg_ctl_options = ''
  48         EOF
  49 sudo install -m 640 -o postgres -g postgres /dev/stdin \
  50  /etc/postgresql/9.1/main/pg_ident.conf <<-EOF
  51         # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
  52         admin           postgres                postgres
  53         admin           root                    postgres
  54         EOF
  55 sudo install -m 640 -o postgres -g postgres /dev/stdin \
  56  /etc/postgresql/9.1/main/start.conf <<-EOF
  57         EOF
  58 sudo install -m 640 -o postgres -g postgres /dev/stdin \
  59  /etc/postgresql/9.1/main/pg_hba.conf <<-EOF
  60         local all postgres peer map=admin
  61         local all all      peer
  62         EOF
  63 sudo install -m 640 -o postgres -g postgres-data \
  64  "$tool"/etc/postgresql/9.1/main/postgresql.conf \
  65         /etc/postgresql/9.1/main/postgresql.conf
  66
  67 sudo ln -fns \
  68         ../sv/"$sv" \
  69  /etc/service/"$sv"
  70 "$tool"/local/runit-sv-start "$sv"
  71 while ! sudo -u postgres psql </dev/null
  72 do sleep 1; done
  73
  74 # NOTE: supprime l'accès au schéma public depuis public,
  75 #       de sorte à ce que les différents utilisateurices
  76 #       ne voient pas leurs bases de données entre-elleux ;
  77 sudo -u postgres psql template1 -a -f - <<-EOF
  78         \set ON_ERROR_STOP on
  79         REVOKE ALL ON DATABASE template1 FROM public;
  80         REVOKE ALL ON SCHEMA   public    FROM public;
  81         GRANT  ALL ON SCHEMA   public    TO   postgres;
  82         EOF
  83 # NOTE: ajoute le support de PL/PGSQL s'il ne l'est pas déjà.
  84 sudo -u postgres psql template1 -a -f - <<-EOF
  85         \set ON_ERROR_STOP on
  86         CREATE OR REPLACE FUNCTION create_language_plpgsql()
  87                 RETURNS BOOLEAN AS \$\$
  88                         CREATE LANGUAGE plpgsql;
  89                         SELECT TRUE;
  90                 \$\$ LANGUAGE SQL;
  91         SELECT CASE WHEN NOT (
  92                 SELECT  TRUE AS exists
  93                 FROM    pg_language
  94                 WHERE   lanname = 'plpgsql'
  95                 UNION
  96                 SELECT  FALSE AS exists
  97                 ORDER BY exists DESC
  98                 LIMIT 1
  99          )
 100         THEN
 101                 create_language_plpgsql()
 102         ELSE
 103                 FALSE
 104         END AS plpgsql_created;
 105         DROP FUNCTION create_language_plpgsql();
 106         EOF
 107 # NOTE: supprime l'accès à la liste des bases données
 108 #       et utilisateurices depuis public.
 109 sudo -u postgres psql template1 -a -f - <<-EOF
 110         \set ON_ERROR_STOP on
 111         REVOKE ALL ON ALL TABLES IN SCHEMA pg_catalog FROM public;
 112         REVOKE ALL ON               SCHEMA pg_catalog FROM public;
 113         -- REVOKE ALL ON pg_auth_members FROM public;
 114         -- REVOKE ALL ON pg_authid       FROM public;
 115         -- REVOKE ALL ON pg_database     FROM public;
 116         -- REVOKE ALL ON pg_group        FROM public;
 117         -- REVOKE ALL ON pg_roles        FROM public;
 118         -- REVOKE ALL ON pg_settings     FROM public;
 119         -- REVOKE ALL ON pg_tablespace   FROM public;
 120         -- REVOKE ALL ON pg_user         FROM public;
 121         EOF