database Role

This role installs and configures a database server and prepares an application database with user and privileges.

Description

The database role manages relational databases for web applications.
It supports MariaDB and PostgreSQL, installs the appropriate PHP database extensions, creates databases and users, assigns fine‑grained privileges, and optionally applies database server configuration parameters in a declarative, idempotent, and ansible‑lint‑clean way.

The role is explicitly separated from the webserver role to ensure clean responsibility boundaries.

Installation

1. Ensure that Ansible is installed on your system. The recommended version is at least 2.17.
2. Install the collection that contains this role.
3. Ensure the following dependencies are available:
   • community.mysql collection (for MariaDB)
   • community.postgresql collection (for PostgreSQL)
   • Python database drivers (PyMySQL, psycopg2)

Variables

The role uses the following variables:

Database Engine

• database_type: Database engine to install.
  Supported values:
  • mariadb
  • postgresql

Base Database Setup

• database_name: Name of the database to create.
  Example: appdb.

• database_user: Database user name.
  Example: appuser.

• database_password: Password for the database user.

• database_host: Host from which the user may connect (MariaDB only).
  Example: localhost.

MariaDB Privileges

• database_mariadb_privileges:
  List of additional MariaDB privilege grants.

  Default: empty list ([])
  → No additional privileges are granted unless explicitly defined.

  This supports both database‑scoped and global privileges, including backup and monitoring use cases.

  Example:

  database_mariadb_privileges:
    - user: backup
      host: localhost
      scope: "*.*"
      priv: "RELOAD,LOCK TABLES,PROCESS"
  
    - user: appuser
      host: localhost
      scope: "appdb.*"
      priv: "SELECT,INSERT,UPDATE,DELETE"
  

  This corresponds to SQL statements like:

  GRANT RELOAD, LOCK TABLES, PROCESS ON *.* TO 'backup'@'localhost';
  

PostgreSQL Privileges

• database_postgresql_privileges:
  List of PostgreSQL privilege definitions.

  Default: empty list ([])
  → No additional privileges are granted unless explicitly defined.

  Supports privileges on:

  • databases
  • schemas
  • tables

  Example (database‑level privileges):

  database_postgresql_privileges:
    - role: backup
      database: postgres
      type: database
      privs: CONNECT
  
    - role: appuser
      database: appdb
      type: database
      privs: ALL
  

  Example (table privileges in a schema):

  database_postgresql_privileges:
    - role: appuser
      database: appdb
      type: table
      schema: public
      objs: ALL_IN_SCHEMA
      privs: SELECT,INSERT,UPDATE,DELETE
  

Database Server Configuration

The role optionally allows direct configuration of database server parameters, similar to PHP configuration handling.

Defaults are neutral:
→ No database configuration is changed unless explicitly defined.

MariaDB Server Configuration

• database_mariadb_config:
  List of MariaDB server configuration options written to
  /etc/mysql/mariadb.conf.d/50-server.cnf.

  Default: empty list ([])

  Example:

  database_mariadb_config:
    - section: mysqld
      option: innodb_buffer_pool_size
      value: 5G
  
    - section: mysqld
      option: innodb_log_file_size
      value: 512M
  
    - section: mysqld
      option: max_connections
      value: 100
  
    - section: mysqld
      option: innodb_flush_log_at_trx_commit
      value: 2
  
    - section: mysqld
      option: innodb_flush_method
      value: O_DIRECT
  
    - section: mysqld
      option: skip_name_resolve
      value: 1
  
    - section: mysqld
      option: thread_cache_size
      value: 16
  

  Changes trigger a MariaDB reload.

PostgreSQL Server Configuration

• database_postgresql_config:
  List of PostgreSQL server parameters written to
  postgresql.conf of the detected PostgreSQL major version.

  Default: empty list ([])

  Example:

  database_postgresql_config:
    - option: max_connections
      value: 100
  
    - option: shared_buffers
      value: 5GB
  
    - option: work_mem
      value: 64MB
  
    - option: maintenance_work_mem
      value: 512MB
  
    - option: effective_cache_size
      value: 8GB
  

  PostgreSQL version is automatically detected using community.postgresql.postgresql_info.

  Changes trigger a PostgreSQL reload.

Output Behavior

• Database server is installed and enabled.
• Required PHP database module is installed (version‑aware if configured).
• Databases are created if missing.
• Users are created with defined passwords.
• Privileges are granted explicitly and idempotently.
• Database server configuration is applied only when defined.
• No implicit configuration or hidden tuning is performed.

Usage

Example Playbook

- hosts: db
  tasks:
    - name: Start database role
      ansible.builtin.include_role:
        name: database
      vars:
        database_type: mariadb
        database_name: appdb
        database_user: appuser
        database_password: secret
        database_mariadb_privileges:
          - user: backup
            host: localhost
            scope: "*.*"
            priv: "RELOAD,LOCK TABLES,PROCESS"
        database_mariadb_config:
          - section: mysqld
            option: innodb_buffer_pool_size
            value: 5G

Features

• MariaDB and PostgreSQL Support
• Canonical community modules
• Explicit and Fine‑Grained Privilege Management
• Global, Schema, and Default Privileges
• Optional Database Server Tuning
• PHP Extension Installation
• Strict ansible‑lint Compliance
• Neutral Defaults (no implicit changes)
• Clear Separation of Concerns

Contributing

Contributions are welcome!

1. Fork the repository.
2. Create a new branch.
3. Submit a pull request describing your changes.

License

This project is licensed under the AGPL License.
Any modifications must be disclosed when distributed or provided as a network service.