1 # DOC: http://wiki.postgresql.org/wiki/Shared_Database_Hosting
3 "$tool"/local
/apt-get-install postgresql-9.1
4 "$tool"/local
/insserv-remove postgresql
5 "$tool"/local
/adduser postgres \
9 --home /home
/postgresql \
12 "$tool"/local
/adduser postgres-data \
16 --home /home
/postgresql
/data \
20 sudo usermod
--home /home
/postgresql postgres
21 sudo adduser postgres postgres-data
22 sudo
install -d -m 1751 -o postgres
-g postgres-data \
24 /home
/postgresql
/etc \
27 /etc
/postgresql
/9.1/main
30 /home
/postgresql
/etc
/postgresql
32 if sudo
test ! -d /home
/postgresql
/data
34 sudo
install -d -m 750 -o postgres
-g postgres \
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 \
43 sudo
install -m 640 -o postgres
-g postgres
/dev
/stdin \
44 /etc
/postgresql
/9.1/main
/pg_ctl.conf
<<-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
53 sudo
install -m 640 -o postgres
-g postgres
/dev
/stdin \
54 /etc
/postgresql
/9.1/main
/start.conf
<<-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
60 hostssl all postgres samehost cert
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
76 /etc
/postgresql
/9.1/main
/$f \
77 /home
/postgresql
/data
/$f
83 "$tool"/local
/runit-sv-start
"$sv"
84 while ! sudo
-u postgres psql
</dev
/null
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
92 REVOKE ALL ON DATABASE template1 FROM public;
93 REVOKE ALL ON SCHEMA public FROM public;
94 GRANT ALL ON SCHEMA public TO postgres;
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
99 CREATE OR REPLACE FUNCTION create_language_plpgsql()
100 RETURNS BOOLEAN AS \$\$
101 CREATE LANGUAGE plpgsql;
104 SELECT CASE WHEN NOT (
105 SELECT TRUE AS exists
107 WHERE lanname = 'plpgsql'
109 SELECT FALSE AS exists
114 create_language_plpgsql()
117 END AS plpgsql_created;
118 DROP FUNCTION create_language_plpgsql();
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;
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;
138 sudo adduser backup postgres-data
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 \
145 sudo
install -m 640 -o root
-g root \
146 "$tool"/etc
/cron.d
/postgresql-backup \
147 /etc
/cron.d
/postgresql-backup