- case $(sudo inotifywait -e create -- /run/postgresql/sock/) in
- ("/run/postgresql/sock/ CREATE .s.PGSQL."*)
- # 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 ;
- # - ajoute le support de PL/PGSQL.
- sudo -u postgres psql template1 -f - <<-EOF
- REVOKE ALL ON DATABASE template1 FROM public;
- REVOKE ALL ON SCHEMA public FROM public;
- GRANT ALL ON SCHEMA public TO postgres;
- CREATE LANGUAGE plpgsql;
- EOF
- # NOTE:
- # - supprime l'accès à la liste des bases données
- # et utilisateurices depuis public.
- sudo -u postgres psql template1 -f - <<-EOF
- 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
+ # 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