diff options
| -rw-r--r-- | roles/mariadb/defaults/main.yml | 4 | ||||
| -rw-r--r-- | roles/mariadb/files/50-server.cnf | 134 | ||||
| -rw-r--r-- | roles/mariadb/handlers/main.yml | 6 | ||||
| -rw-r--r-- | roles/mariadb/tasks/main.yml | 84 | ||||
| -rw-r--r-- | roles/mariadb/tasks/prune_users.yml | 11 |
5 files changed, 239 insertions, 0 deletions
diff --git a/roles/mariadb/defaults/main.yml b/roles/mariadb/defaults/main.yml new file mode 100644 index 0000000..155ecf7 --- /dev/null +++ b/roles/mariadb/defaults/main.yml @@ -0,0 +1,4 @@ +--- +dbs: [] + +db_users: [] diff --git a/roles/mariadb/files/50-server.cnf b/roles/mariadb/files/50-server.cnf new file mode 100644 index 0000000..7ef47b3 --- /dev/null +++ b/roles/mariadb/files/50-server.cnf @@ -0,0 +1,134 @@ +# +# These groups are read by MariaDB server. +# Use it for options that only the server (but not clients) should see +# +# See the examples of server my.cnf files in /usr/share/mysql + +# this is read by the standalone daemon and embedded servers +[server] + +# this is only for the mysqld standalone daemon +[mysqld] + +# +# * Basic Settings +# +user = mysql +pid-file = /run/mysqld/mysqld.pid +socket = /run/mysqld/mysqld.sock +#port = 3306 +basedir = /usr +datadir = /var/lib/mysql +tmpdir = /tmp +lc-messages-dir = /usr/share/mysql +#skip-external-locking + +# Instead of skip-networking the default is now to listen only on +# localhost which is more compatible and is not less secure. +bind-address = 0.0.0.0 + +# +# * Fine Tuning +# +#key_buffer_size = 16M +#max_allowed_packet = 16M +#thread_stack = 192K +#thread_cache_size = 8 +# This replaces the startup script and checks MyISAM tables if needed +# the first time they are touched +#myisam_recover_options = BACKUP +#max_connections = 100 +#table_cache = 64 +#thread_concurrency = 10 + +# +# * Query Cache Configuration +# +#query_cache_limit = 1M +query_cache_size = 16M + +# +# * Logging and Replication +# +# Both location gets rotated by the cronjob. +# Be aware that this log type is a performance killer. +# As of 5.1 you can enable the log at runtime! +#general_log_file = /var/log/mysql/mysql.log +#general_log = 1 +# +# Error log - should be very few entries. +# +log_warnings = 4 +log_error = /var/log/mysql/error.log +# +# Enable the slow query log to see queries with especially long duration +#slow_query_log_file = /var/log/mysql/mariadb-slow.log +#long_query_time = 10 +#log_slow_rate_limit = 1000 +#log_slow_verbosity = query_plan +#log-queries-not-using-indexes +# +# The following can be used as easy to replay backup logs or for replication. +# note: if you are setting up a replication slave, see README.Debian about +# other settings you may need to change. +#server-id = 1 +#log_bin = /var/log/mysql/mysql-bin.log +expire_logs_days = 10 +#max_binlog_size = 100M +#binlog_do_db = include_database_name +#binlog_ignore_db = exclude_database_name + +# +# * Security Features +# +# Read the manual, too, if you want chroot! +#chroot = /var/lib/mysql/ +# +# For generating SSL certificates you can use for example the GUI tool "tinyca". +# +ssl-ca = /etc/ssl/certs/ca-certificates.crt +ssl-cert = /etc/mysql/mysql.pem +ssl-key = /etc/mysql/mysql.key +# +# Accept only connections using the latest and most secure TLS protocol version. +# ..when MariaDB is compiled with OpenSSL: +ssl-cipher = TLSv1.2 +# ..when MariaDB is compiled with YaSSL (default in Debian): +#ssl = on + +# +# * Character sets +# +# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full +# utf8 4-byte character set. See also client.cnf +# +character-set-server = utf8mb4 +collation-server = utf8mb4_general_ci + +# +# * InnoDB +# +# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. +# Read the manual for more InnoDB related options. There are many! + +# +# * Unix socket authentication plugin is built-in since 10.0.22-6 +# +# Needed so the root database user can authenticate without a password but +# only when running as the unix root user. +# +# Also available for other users if required. +# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/ + +# this is only for embedded server +[embedded] + +# This group is only read by MariaDB servers, not by MySQL. +# If you use the same .cnf file for MySQL and MariaDB, +# you can put MariaDB-only options here +[mariadb] + +# This group is only read by MariaDB-10.3 servers. +# If you use the same .cnf file for MariaDB of different versions, +# use this group for options that older servers don't understand +[mariadb-10.3] diff --git a/roles/mariadb/handlers/main.yml b/roles/mariadb/handlers/main.yml new file mode 100644 index 0000000..9c9e4d0 --- /dev/null +++ b/roles/mariadb/handlers/main.yml @@ -0,0 +1,6 @@ +- name: Restart MariaDB + systemd: + name: mariadb.service + enabled: yes + state: restarted + become: yes diff --git a/roles/mariadb/tasks/main.yml b/roles/mariadb/tasks/main.yml new file mode 100644 index 0000000..f1dc10f --- /dev/null +++ b/roles/mariadb/tasks/main.yml @@ -0,0 +1,84 @@ +--- +- name: install Packages + apt: + name: + - mariadb-client + - mariadb-server + - python3-pymysql + update_cache: yes + become: yes + +- name: Config File + copy: + src: 50-server.cnf + dest: /etc/mysql/mariadb.conf.d/50-server.cnf + become: yes + notify: + - Restart MariaDB + +- name: Generate SSL Certificates + include_role: + name: signed_certificate + vars: + cert_name: mysql + ca_path: /etc/mysql + key_path: /etc/mysql + cert_path: /etc/mysql + owner: mysql + group: mysql + +- name: Check for changed cert + command: /bin/true + when: + - cert_changed + notify: + - Restart MariaDB + +- name: Flush handlers + meta: flush_handlers + +- name: Securing the installation + community.mysql.mysql_query: + query: + - "DELETE FROM mysql.user WHERE User=''" + - "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')" + - "DROP DATABASE IF EXISTS test" + - "DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'" + - "FLUSH PRIVILEGES" + login_unix_socket: /var/run/mysqld/mysqld.sock + become: yes + +- name: Create Databases + community.mysql.mysql_db: + name: '{{ item }}' + state: present + encoding: utf8 + login_unix_socket: /var/run/mysqld/mysqld.sock + loop: '{{ dbs }}' + become: yes + +- name: Create Users + community.mysql.mysql_user: + name: '{{ item.key }}' + password: '{{ vault_db_users_pw[ ansible_facts.fqdn ][ item.key ] }}' + login_unix_socket: /var/run/mysqld/mysqld.sock + args: '{{ item.value }}' + with_dict: '{{ db_users }}' + become: yes + +# Not great, but the only way to do custom nested loops + +- name: get to prune users + community.mysql.mysql_query: + query: + - "SELECT User,Host FROM mysql.user WHERE User='{{ item.key }}' AND Host!='{{ item.value.host }}'" + login_unix_socket: /var/run/mysqld/mysqld.sock + with_dict: '{{ db_users }}' + register: sql_prune_users + become: yes + +- name: Prune users + include_tasks: prune_users.yml + with_subelements: + - '{{ sql_prune_users.results }}' + - query_result diff --git a/roles/mariadb/tasks/prune_users.yml b/roles/mariadb/tasks/prune_users.yml new file mode 100644 index 0000000..b2d3da7 --- /dev/null +++ b/roles/mariadb/tasks/prune_users.yml @@ -0,0 +1,11 @@ +--- +- name: Prune users + community.mysql.mysql_user: + name: '{{ inner_item.User }}' + host: '{{ inner_item.Host }}' + state: absent + login_unix_socket: /var/run/mysqld/mysqld.sock + loop: '{{ item.1 }}' + loop_control: + loop_var: inner_item + become: yes |