1 # DOC: http://wiki.postgresql.org/wiki/Shared_Database_Hosting
3 rule apt_get_install postgresql-9.1
4 rule insserv_remove postgresql
5 rule adduser postgres \
9 --home /home
/postgresql \
12 rule adduser postgres-data \
16 --home /home
/postgresql
/data \
20 sudo usermod
--home /home
/postgresql postgres
21 sudo adduser postgres postgres-data
24 sudo
install -d -m 1751 -o postgres
-g postgres-data \
26 /home
/postgresql
/etc \
27 /home
/postgresql
/bin \
30 /etc
/postgresql
/9.1/main
33 /home
/postgresql
/etc
/postgresql
35 if sudo
test ! -d /home
/postgresql
/data
37 sudo
install -d -m 750 -o postgres
-g postgres \
39 sudo
-u postgres pg_createcluster \
40 --datadir=/home
/postgresql
/data \
41 --logfile=/home
/postgresql
/log
/9.1/main
/cluster.log \
42 --socketdir=/run
/postgresql \
46 sudo
install -m 640 -o postgres
-g postgres
/dev
/stdin \
47 /etc
/postgresql
/9.1/main
/pg_ctl.conf
<<-EOF
50 sudo
install -m 640 -o postgres
-g postgres
/dev
/stdin \
51 /etc
/postgresql
/9.1/main
/pg_ident.conf
<<-EOF
52 # MAPNAME SYSTEM-USERNAME PG-USERNAME
54 sudo
install -m 640 -o postgres
-g postgres
/dev
/stdin \
55 /etc
/postgresql
/9.1/main
/start.conf
<<-EOF
57 sudo
install -m 640 -o postgres
-g postgres
/dev
/stdin \
58 /etc
/postgresql
/9.1/main
/pg_hba.conf
<<-EOF
59 local all postgres peer
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
find "$tool"/etc
/postgresql
/bin
/ -type f
-perm /+x
-exec \
66 install -m 755 -o root
-g root \
67 -t /home
/postgresql
/bin
/ {} +
72 rule runit_sv_start
"$sv"
73 while ! sudo
-u postgres psql
</dev
/null
76 # NOTE: supprime l'accès au schéma public depuis public,
77 # de sorte à ce que les différents utilisateurices
78 # ne voient pas leurs bases de données entre-elleux ;
79 sudo
-u postgres psql template1
-a -f - <<-EOF
81 REVOKE ALL ON DATABASE template1 FROM public;
82 REVOKE ALL ON SCHEMA public FROM public;
83 GRANT ALL ON SCHEMA public TO postgres;
85 # NOTE: ajoute le support de PL/PGSQL s'il ne l'est pas déjà.
86 sudo
-u postgres psql template1
-a -f - <<-EOF
88 CREATE OR REPLACE FUNCTION create_language_plpgsql()
89 RETURNS BOOLEAN AS \$\$
90 CREATE LANGUAGE plpgsql;
93 SELECT CASE WHEN NOT (
96 WHERE lanname = 'plpgsql'
98 SELECT FALSE AS exists
103 create_language_plpgsql()
106 END AS plpgsql_created;
107 DROP FUNCTION create_language_plpgsql();
109 # NOTE: supprime l'accès à la liste des bases données
110 # et utilisateurices depuis public.
111 sudo
-u postgres psql template1
-a -f - <<-EOF
112 \set ON_ERROR_STOP on
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;