# DELETE FROM mysql.user WHERE user = 'root' AND host NOT IN ('localhost', '127.0.0.1', '::1');
sudo mysql -u root --batch --verbose <<-EOF
DELETE FROM mysql.user WHERE user = 'root' and plugin = '';
- GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost' IDENTIFIED WITH auth_socket;
+ DROP PROCEDURE IF EXISTS mysql.create_user_mysql;
+ DELIMITER //
+ CREATE PROCEDURE mysql.create_user_mysql ()
+ BEGIN
+ IF NOT (EXISTS (SELECT User
+ FROM mysql.user
+ WHERE User='mysql'
+ AND Host='localhost'
+ LIMIT 1))
+ THEN GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost' IDENTIFIED WITH auth_socket;
+ END IF;
+ END;
+ //
+ CALL mysql.create_user_mysql();
+ DROP PROCEDURE mysql.create_user_mysql;
UPDATE mysql.user SET grant_priv='Y',super_priv='Y' WHERE user='mysql';
DELETE FROM mysql.db WHERE user = '';
DELETE FROM mysql.user WHERE user = '';
sudo postmap hash:/etc/postfix/$vm_domainname/virtual_alias
rule runit_configure postfix
}
-rule_postgresql_configure () {
- # DOC: http://wiki.postgresql.org/wiki/Shared_Database_Hosting
- rule apt_get_install postgresql-9.1
- rule insserv_remove postgresql
- 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
- if sudo test ! -d /home/postgresql/data
- then
- sudo install -d -m 750 -o postgres -g postgres \
- /home/postgresql/data
- 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 640 -o postgres -g postgres /dev/stdin \
- /etc/postgresql/9.1/main/pg_ctl.conf <<-EOF
- pg_ctl_options = ''
- EOF
- sudo install -m 640 -o postgres -g postgres /dev/stdin \
- /etc/postgresql/9.1/main/pg_ident.conf <<-EOF
- # MAPNAME SYSTEM-USERNAME PG-USERNAME
- EOF
- sudo install -m 640 -o postgres -g postgres /dev/stdin \
- /etc/postgresql/9.1/main/start.conf <<-EOF
- EOF
- sudo install -m 640 -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
- rule runit_configure postgres
- while ! sudo -u postgres psql </dev/null
- do sleep 1; done
- # 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
- }
-rule_postgresql_db_add () { # SYNTAX: $db $owner
- local db="$1"
- local owner="${2:-$db}"
- 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 = '$owner'
- LIMIT 1
- ) THEN
- CREATE ROLE $owner
- 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=$owner;
- EOF
- ;;
- esac
- sudo -u postgres psql template1 -a -f - <<-EOF
- \set ON_ERROR_STOP on
- REVOKE ALL ON DATABASE $db FROM public;
- EOF
- sudo -u postgres psql "$db" -a -f - <<-EOF
- \set ON_ERROR_STOP on
- GRANT ALL ON SCHEMA public TO $owner WITH GRANT OPTION;
- EOF
- }
-rule_postgresql_db_user_add () { # SYNTAX: $db $user
- local db="$1" user="$2"
- sudo -u postgres psql "$db" -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;
- EOF
- }
rule_postgrey_configure () {
rule apt_get_install postgrey
rule insserv_remove postgrey