+rule_postgresql_configure () {
+ # DOC: http://wiki.postgresql.org/wiki/Shared_Database_Hosting
+ rule apt_get_install postgresql-9.1
+ rule adduser postgres \
+ --disabled-login \
+ --disabled-password \
+ --group \
+ --home /home/postgresql \
+ --shell /bin/false \
+ --system
+ rule adduser postgres-data \
+ --disabled-login \
+ --disabled-password \
+ --group \
+ --home /home/postgresql/data \
+ --no-create-home \
+ --shell /bin/false \
+ --system
+ sudo usermod --home /home/postgresql postgres
+ sudo adduser postgres postgres-data
+ sudo rm -rf \
+ /etc/postgresql
+ sudo install -d -m 1751 -o postgres -g postgres-data \
+ /home/postgresql \
+ /home/postgresql/etc \
+ /etc/postgresql \
+ /etc/postgresql/9.1 \
+ /etc/postgresql/9.1/main
+ sudo ln -fns \
+ /etc/postgresql \
+ /home/postgresql/etc/postgresql
+ sudo install -d -m 2770 -o postgres -g log-postgres \
+ /home/postgresql/log \
+ /home/postgresql/log/9.1 \
+ /home/postgresql/log/9.1/main
+ sudo service tmpfs restart
+ if sudo test ! -d /home/postgresql/data
+ then
+ sudo install -d -m 750 -o postgres -g postgres \
+ /home/postgresql/data
+ (
+ cd /
+ sudo -u postgres pg_createcluster \
+ --datadir=/home/postgresql/data \
+ --logfile=/home/postgresql/log/9.1/main/cluster.log \
+ --socketdir=/run/postgresql \
+ 9.1 main
+ )
+ fi
+ sudo install -m 770 -o postgres -g postgres /dev/stdin \
+ /etc/postgresql/9.1/main/pg_hba.conf <<-EOF
+ local all postgres peer
+ local all all peer
+ EOF
+ sudo install -m 640 -o postgres -g postgres-data \
+ "$tool"/etc/postgresql/9.1/main/postgresql.conf \
+ /etc/postgresql/9.1/main/postgresql.conf
+ sudo insserv -r postgresql
+ sudo chmod ugo-x /etc/init.d/postgresql
+ case $(sudo sv status postgres || true) in
+ (''|run:*|*"s, normally up;"*)
+ sudo sv restart postgres
+ while case $(sudo inotifywait -e create -- /run/postgresql/) in
+ ("/run/postgresql/ CREATE .s.PGSQL.5432") true;;
+ (*) false;;
+ esac
+ do true; done
+ (
+ cd /
+ # NOTE: supprime l'accès au schéma public depuis public,
+ # de sorte à ce que les différents utilisateurices
+ # ne voient pas leurs bases de données entre-elleux ;
+ sudo -u postgres psql template1 -a -f - <<-EOF
+ \set ON_ERROR_STOP on
+ REVOKE ALL ON DATABASE template1 FROM public;
+ REVOKE ALL ON SCHEMA public FROM public;
+ GRANT ALL ON SCHEMA public TO postgres;
+ EOF
+ # NOTE: ajoute le support de PL/PGSQL s'il ne l'est pas déjà.
+ sudo -u postgres psql template1 -a -f - <<-EOF
+ \set ON_ERROR_STOP on
+ CREATE OR REPLACE FUNCTION create_language_plpgsql()
+ RETURNS BOOLEAN AS \$\$
+ CREATE LANGUAGE plpgsql;
+ SELECT TRUE;
+ \$\$ LANGUAGE SQL;
+ SELECT CASE WHEN NOT (
+ SELECT TRUE AS exists
+ FROM pg_language
+ WHERE lanname = 'plpgsql'
+ UNION
+ SELECT FALSE AS exists
+ ORDER BY exists DESC
+ LIMIT 1
+ )
+ THEN
+ create_language_plpgsql()
+ ELSE
+ FALSE
+ END AS plpgsql_created;
+ DROP FUNCTION create_language_plpgsql();
+ EOF
+ # NOTE: supprime l'accès à la liste des bases données
+ # et utilisateurices depuis public.
+ sudo -u postgres psql template1 -a -f - <<-EOF
+ \set ON_ERROR_STOP on
+ REVOKE ALL ON pg_auth_members FROM public;
+ REVOKE ALL ON pg_authid FROM public;
+ REVOKE ALL ON pg_database FROM public;
+ REVOKE ALL ON pg_group FROM public;
+ REVOKE ALL ON pg_roles FROM public;
+ REVOKE ALL ON pg_settings FROM public;
+ REVOKE ALL ON pg_tablespace FROM public;
+ REVOKE ALL ON pg_user FROM public;
+ EOF
+ )
+ ;;
+ esac
+ }
+rule_postgresql_db_add () { # SYNTAX: $db $db_user
+ local db="$1" db_user="$2"
+ sudo -u postgres psql template1 -a -f - <<-EOF
+ \set ON_ERROR_STOP on
+ DO LANGUAGE plpgsql \$\$
+ BEGIN
+ IF NOT EXISTS (
+ SELECT *
+ FROM pg_catalog.pg_roles
+ WHERE rolname = '${db}_role'
+ LIMIT 1
+ ) THEN
+ CREATE ROLE ${db}_role
+ NOCREATEDB
+ NOCREATEROLE
+ NOINHERIT
+ NOLOGIN
+ NOSUPERUSER;
+ END IF;
+ IF NOT EXISTS (
+ SELECT *
+ FROM pg_catalog.pg_user
+ WHERE usename = '$db_user'
+ LIMIT 1
+ ) THEN
+ CREATE ROLE $db_user
+ LOGIN
+ NOCREATEDB
+ NOCREATEROLE
+ NOINHERIT
+ NOSUPERUSER;
+ END IF;
+ END;
+ \$\$;
+ EOF
+ case $(sudo -u postgres psql template1 -t -c \
+ "SELECT datname FROM pg_catalog.pg_database WHERE datname = '$db' LIMIT 1") in
+ (" $db") true;;
+ (*)
+ sudo -u postgres psql template1 -a -f - <<-EOF
+ \set ON_ERROR_STOP on
+ CREATE DATABASE $db WITH OWNER=$db_user;
+ EOF
+ ;;
+ esac
+ sudo -u postgres psql template1 -a -f - <<-EOF
+ \set ON_ERROR_STOP on
+ GRANT ${db}_role TO $db_user;
+ REVOKE ALL ON DATABASE $db FROM public;
+ EOF
+ }
+rule_postgresql_db_user_add () { # SYNTAX: $db $user
+ local db="$1" user="$2"
+ sudo -u postgres psql template1 -a -f - <<-EOF
+ \set ON_ERROR_STOP on
+ DO LANGUAGE plpgsql \$\$
+ BEGIN
+ IF NOT EXISTS (
+ SELECT *
+ FROM pg_catalog.pg_user
+ WHERE usename = '$user'
+ LIMIT 1
+ ) THEN
+ CREATE ROLE $user
+ LOGIN
+ NOCREATEDB
+ NOCREATEROLE
+ NOINHERIT
+ NOSUPERUSER;
+ END IF;
+ END;
+ \$\$;
+ GRANT USAGE ON SCHEMA public TO $user;
+ GRANT CONNECT,TEMPORARY ON DATABASE $db TO $user;
+ GRANT $db TO $user;
+ EOF
+ }