Managing databases users using nix

Posted on Tue 11 February 2025 in nix

Recently I had the following challenge: manage database (robotic) users using nix. More specifically I wanted to declare a list of mariadb/mysql users, alongside passwords, and permissions using nix. And while nix has some options for this (e.g. services.mysql.ensureUsers.*.name). Some aspects can’t be declared with nix options like the credentials or its permissions for given databases.

There is a way to solve this using nix using three steps: 1. Declaring the user configuration in an object 2. Creating nix packaged scripts that will apply a configuration to the database 3. Creating a systemd unit that runs the previous scripts when appropriate

In this article I will go into details on how this is done. Note: although I focus on MySQL/MariaDB, the underlying principles can be applied for PostgreSQL, Redis, …

Declaring the user configuration

The first step is to encapsulate the information required for configuring a user in a nix object (key-value dictionary). In my case it looks like this

dbUsecaseConfig = {
  database = "usecase_db";
  passwordFile = config.age.secrets.usecasePass.path;
  username = "usecase_username";
  hostname = "usecase_hostname";
};

Should be self explanatory. Note passwordFile is a path to a file, here I use agenix to manage secrets, which decrypts the password file with adequate permissions to prevent leaks (see article about agenix).

Set up script to apply configuration

The second step is to create a script that applies the configuration. This is done using pkgs.writeShellScript which generates a shell script and stores it as nix component (under /nix/store/).

The syntax of the function

writeShellScript "file-name"
  ''
  Content of the file
  '';

The file name here is not very important. The content of the script, however, is. To make this modular, I use a function that takes a configuration as input and produces a corresponding shell script:

applyMySQLConf = 
  {
    database,
    passwordFile,
    username,
    hostname
  }:
  pkgs.writeShellScript "applyMySQLConf"
    ''
      PASSWORD=$(cat ${passwordFile})
      ${pkgs.mariadb}/bin/mysql -u root -e "GRANT ALL PRIVILEGES ON ${database}.* TO '${username}'@'${hostname}' IDENTIFIED BY '$PASSWORD';"
    '';

The details will depends on the use case. For instance for a PostgreSQL database the actual commands used in the script will change.

To generate a script for a given configuration will look this:

dbUsecaseConfig = {
  database = "usecase_db";
  passwordFile = config.age.secrets.usecasePass.path;
  username = "usecase_username";
  hostname = "usecase_hostname";
};
usecaseConfScript = applyMySQLConf dbUsecaseConfig;

Automatically apply the configuration using systemd

Now, we have a script which applies a configuration, but nothing actually runs the script. This is solved by using systemd services :

systemd.services.apply-mysql-conf = {
  description = "Apply MySQL configurations";
  wants = [ "mysql.service" ];
  wantedBy = [ "multi-user.target" ];
  serviceConfig = {
    PremissionsStartOnly = true;
    RemainAfterExit = true;
    ExecStart = ''
      ${usecaseConfScript}
      ...
    '';
  };
};

I personally use one systemd service to apply all configurations for the database. You may prefer using multiple systemd unit files.

Creating a Nix module

It is good practice to create NixOS modules as it allows the code to be reusable and better organised.

A NixOS module is created by adding path/to/module.nix file.

{
  lib,
  config,
  pkgs,
  ...
}:
{
  # Content of the module
}

More specifically the module will define a NixOS option called mysqlInitialConfiguration so it can be used in the configuration.nix:

{
  lib,
  config,
  pkgs,
  ...
}:
{
  options.mysqlInitialConfiguration = {
    enable = lib.mkEnableOption "Activate initial MySQL configuration";
    configurations = lib.mkOption = {
      default = [ ];
      description = ''
        List of initial MySQL configurations
        For instance :
        [
          {
            database = "usecase_db";
            passwordFile = config.age.secrets.usecasePass.path;
            username = "usecase_username";
            hostname = "usecase_hostname";
          }
        ]
      '';
    };
  };
}

Now this defines the NixOS option but not what happens when it is enabled and configured. To do so we need to add the config part of the option which applies a configuration based on option.

{
  lib,
  config,
  pkgs,
  ...
}:
{
  options.mysqlInitialConfiguration = {
    enable = lib.mkEnableOption "Activate initial MySQL configuration";
    configurations = lib.mkOption = {
      default = [ ];
      description = ''
        List of initial MySQL configurations
        For instance :
        [
          {
            database = "usecase_db";
            passwordFile = config.age.secrets.usecasePass.path;
            username = "usecase_username";
            hostname = "usecase_hostname";
          }
        ]
      '';
    };
  };

  config = lib.mkIf config.mysqlInitialConfiguration {
    # Actual configuration
  };
}

Now we add the actual configuration (systemd files, scripts, …) and for practical reasons use the let ... in ... nix clause.

{
  lib,
  config,
  pkgs,
  ...
}:
let
  applyMySQLConf =
    {
      database,
      passwordFile,
      username,
      hostname,
    }:
    pkgs.writeShellScript "applyMySQLConf" ''
      PASSWORD=$(cat ${passwordFile})
      ${pkgs.mariadb}/bin/mysql -u root -e "GRANT ALL PRIVILEGES ON ${database}.* TO '${username}'@'${hostname}' IDENTIFIED BY '$PASSWORD';"
    '';

  # Function taking a list as input and returns a string
  genSystemdScript = list: lib.strings.concatStrings (map (conf: applyMySQLConf conf + "\n") list);
  systemdScript = pkgs.writeShellScript "mysqlInitialConfiguration" ''
    ${genSystemdScript cfg.configurations}
  '';

  cfg = config.mysqlInitialConfiguration;
in
{
  options.mysqlInitialConfiguration = {
    # ...
  };

  config = lib.mkIf config.mysqlInitialConfiguration {
    systemd.services.mysqlInitialConfiguration = {
      description = "Initial MySQL configuration";
      wants = [ "mysql.service" ];
      wantedBy = [ "multi-user.target" ];
      serviceConfig = {
        PermissionsStartOnly = true;
        RemainAfterExit = true;
        ExecStart = "${systemdScript}";
      };
    };
  };
}

Note that config.mysqlInitialConfiguration is replaced with cfg in the config part.

Now the new option can be used in the configuration.nix file as so:

{
  # ...
}:
{
  # ...
  imports = [
    # ...
    ./path/to/mysql/module.nix
    # ...
  ];
  # ...
  mysqlInitialConfiguration.enable = true;
  mysqlInitialConfiguration.configurations = [
    {
      database = "usecase_db";
      passwordFile = config.age.secrets.usecasePass.path;
      username = "usecase_username";
      hostname = "usecase_hostname";
    }
  ];
}

Summary

In this article I show to apply MySQL user configuration automatically using Nix and put all of this in a module.

For more articles about Nix and NixOS the following RSS feed is available over here.

pelican nix