--------

The ``database`` role can be used for creating a MariaDB database and

accompanying user on destination machine.

The role implements the following:

* Creates MariaDB database.

* Creates a dedicated user capable of performing any operation on the created

  database. Username is set to be same as the name of database.

* Sets-up pre-backup task that creates database dump in location

  ``/srv/backup/mariadb/{{ db_name }}.sql``.

Role dependencies

~~~~~~~~~~~~~~~~~

Depends on the following roles:

* **database_server**

* **backup_client**

Backups

~~~~~~~

If the backup for this role has been enabled, the following paths are backed-up:

**/srv/backup/maraidb/{{ db_name }}.sql**

  Dump of the database. Database dump is created every day at 01:45 in the

  morning.

Parameters

~~~~~~~~~~

**db_name** (string, mandatory)

  Name of the database that should be created.

**db_password** (string, mandatory)

  Password for the database user.

Distribution compatibility

~~~~~~~~~~~~~~~~~~~~~~~~~~

Role is compatible with the following distributions:

- Debian 10 (Buster)

Examples

~~~~~~~~

Here is an example configuration for creating a single database (for some

website):

.. code-block:: yaml

  - role: database

    db_name: phpinfo_example_com

    db_password: phpinfo_example_com

Backup Server

-------------

The ``backup_server`` role can be used for setting-up a server to act as backup

storage for the backup clients. Storage is made available to the clients

exclusively via SFTP on a dedicated port and dedicated OpenSSH server

instance. This instance is specifically configured and tailored for this

purpose.

The role is primarily aimed for use with `Duplicity

<http://duplicity.nongnu.org/>`_, but should be also usable for generic SFTP

uploads.

The role implements the following:

* Installs backup software (Duplicity, Duply).

* Creates a dedicated directory structure for backups with the following structure:

  * ``/srv/backups/`` - main directory under which all the backups reside.

  * ``/srv/backups/SERVER_NAME/`` - home directory for the backup user, name

    after the server. Backup users are confined to their respective home

    directory via chroot. Backup users can't write to their own home directory,

    though.

  * ``/srv/backups/SERVER_NAME/duplicity/`` - directory where the Duplicity

    backups are stored at. This directory is writable by the respective backup

    user.

  * ``SERVER_NAME/.ssh/`` - directory where authorized keys are stored. Backup

    user is not allowed to make modifications to this directory and files

    contained within (i.e. backup users can't add more keys to the

    ``authorized_keys`` file).

* Creates dedicated operating system users for backup clients. These users will

  be made members of the ``backup`` group as well (as an additional group).

---

allow_duplicates: true

dependencies:

  - database_server

  - role: backup

    when: enable_backup

    backup_patterns_filename: "database_{{ db_name }}"

    backup_patterns:

      - "/srv/backup/mariadb/{{ db_name }}.sql"

galaxy_info:

  author: Branko Majic

  description: Creates MariaDB database and accompanying user to access it

  license: BSD

  min_ansible_version: 2.9

  platforms:

    - name: Debian

      versions:

        - 10

---

dependency: {}

driver:

  name: vagrant

  provider:

    name: virtualbox

lint:

  name: yamllint

  options:

    config-file: ../../.yamllint.yml

platforms:

  - name: parameters-mandatory-buster64

    groups:

      - parameters-mandatory

    box: debian/contrib-buster64

    cpus: 1

  - name: parameters-optional-buster64

    groups:

      - parameters-optional

      - backup-server

    box: debian/contrib-buster64

    memory: 512

    cpus: 1

provisioner:

  name: ansible

  config_options:

    defaults:

      force_valid_group_names: "ignore"

      interpreter_python: "/usr/bin/python3"

    ssh_connection:

      pipelining: "True"

  lint:

    name: ansible-lint

scenario:

  name: default

verifier:

  name: testinfra

  lint:

    name: flake8

import os

import testinfra.utils.ansible_runner

testinfra_hosts = testinfra.utils.ansible_runner.AnsibleRunner(

    os.environ['MOLECULE_INVENTORY_FILE']).get_hosts('parameters-*')

def test_database_created(host):

    """

    Tests if database has been created.

    """

    with host.sudo():

        show_databases = host.run("mysql -BNe \"show databases like 'testdb'\"")

        assert show_databases.rc == 0

        assert show_databases.stdout == "testdb\n"

def test_database_user_login(host):

    """

    Tests database user login.

    """

    login = host.run("mysql -utestdb -ptestdbpassword -BNe 'show databases'")

    assert login.rc == 0

def test_database_user_permissions(host):

    """

    Tests if database user has been granted correct permissions on the database.

    """

    ansible_facts = host.ansible("setup")["ansible_facts"]

    ansible_distribution_release = ansible_facts['ansible_distribution_release']

    # Small difference in usage of backtick (`) instead of single

    # quote (') when displaying grants for user.

        expected_usage = "GRANT USAGE ON *.* TO `testdb`@`localhost` IDENTIFIED BY PASSWORD '*676852B7FAE972722AD20D6E74781D6B1A100544'"

        expected_privileges = "GRANT ALL PRIVILEGES ON `testdb`.* TO `testdb`@`localhost`"

    else:

        raise Exception("Tried running test on unsupported distribution: %s" % ansible_distribution_release)

    visible_databases = host.run("mysql -utestdb -ptestdbpassword -BNe 'show databases'")

    assert visible_databases.rc == 0

    assert visible_databases.stdout == "information_schema\ntestdb\n"

    with host.sudo():

        permissions_command = host.run("mysql -BNe 'show grants for testdb@localhost'")

        permissions = permissions_command.stdout.rstrip().split("\n")

        assert len(permissions) == 2

        assert expected_usage in permissions

        assert expected_privileges in permissions