Alternate MySQL backend for Hiera with json support

This is a backend for Hiera based on hiera-mysql-backend.

This backend differs from hiera-mysql and hiera-mysql-backend in that it expects queries to return json. This json is then parsed and the resulting data structure is given back to the lookup.

Puppet client:

/opt/puppetlabs/puppet/bin/gem install hiera-mysql-json-backend mysql2

Puppet server:

/opt/puppetlabs/bin/puppetserver gem install hiera-mysql-json-backend-jruby jdbc-mysql

The backend is configured like any other in the hiera.conf. Here is the simplest possible example:

---
:backends:
  - yaml
  - mysql_json

:yaml:
  :datadir: /etc/puppet/hieradata

:mysql_json:
  :datadir: /etc/puppet/hieradata

:hierarchy:
  - "%{::clientcert}"
  - "%{::custom_location}"
  - common

:logger: console

This will cause it to try to connect on localhost:3306 with no username and password. This will probably not work. The following options can be set:

• host: mysql host (string)
• port: mysql port (int)
• user: mysql user (string)
• pass: mysql password (string)
• database: mysql database name (string)
• datadir: root of your mysql_json hierarchy (string)
• only_for: only perform queries if conditions in this section are met (hash)
• ignore_json_parse_errors: Do not raise an exception when the database contains invalid json (boolean, defaults to false)

A more complete example might contain:

:mysql_json:
  :datadir: /etc/puppet/hierasql
  :host: db042.example.com
  :user: puppetserver
  :pass: secret123
  :port: 3306
  :ignore_json_parse_errors: true # why why why...
  :only_for:
    :fqdn:
      - '^vagrant.+'
      - '^node\d+\.someservice\.example\.com$'
    :domain:
      - '^vagrant.+

This will perform lookups if any of the given conditions are met:

• The nodes fqdn fact starts with 'vagrant'
• The nodes fqdn fact looks like node01.someservice.example.com
• The nodes domain fact starts with 'vagrant'

Any node fact may be used to build these conditions.

Note that putting something in the list that always matches makes the whole only_for block useless.

Queries are defined in he poorly named sql files. These are really yaml files where the key is the lookup key and the value is the SQL statement (it accepts interpolation)

As of version 0.0.4 you can also add connection information to these sql files, this allows you to connect to different databases. This is optional if no connection information is found it will use the default defined in your hiera.yaml config.

Lets assume your datadir is /etc/puppet/hieradata/ and your hierarchy for hiera just have a common. hiera-mysql-backend would look for /etc/puppet/hieradata/common.sql the common.sql would look like:

---
# This is optional, if not present it will use the default connection info from hiera.yaml
:dbconfig:
  :host: database.example.com
  :user: hieratest
  :pass: sekret
  :database: testhieradb
  :port: 44445

applications: SELECT value FROM applications WHERE host='%{fqdn}';

If host is not defined it will use localhost as default.

If port is not defined it will use the default 3306 mysql port

Running hiera applications would run the query against the configured database, parse the result as json and return the resulting data structure. If all you want is a string, strings are valid json, too.

When encountering invalid json, it will raise an exception, which would in turn cause catalog compilation to fail. You can disable this behaviour by setting ignore_json_parse_errors.

When no results are returned by your query, it will return nil.

When multiple results are returned by your query, it will return nil. A future version will introduce ignore_multiple_results, defaulting to true, to make it possible to trigger a catalog compilation in this case.

1. Multiple results are currently silently ignored.
2. It always return an Array of hashes regardless of the number of items returned. (I did this on purpose because it is what I needed but I may be persuaded to do otherwise)
3. This README is poorly written.

1. Fork it
2. Create your feature branch (git checkout -b my-new-feature)
3. Commit your changes (git commit -am 'Add some feature')
4. Push to the branch (git push origin my-new-feature)
5. Create new Pull Request