Configuring remote access to mysql via puppet

Question

The problem: I want to connect to mysql from another host

I have a mysql instance that I configure with puppet. I want to be able to access the instance from host 1.2.3.4.

Part 1: I start by hand

In `/etc/mysql/my.cnf', I have

[mysqld]
bind-address = 0.0.0.0

[mine]
database = my-database-name
host = localhost

I restart mysqld, then grant permission

GRANT select,insert,delete,update \
  ON my-database-name.* \
  TO 'my-user-name'@'1.2.3.4'     \
  IDENTIFIED BY 'user-password';

and this works.

Part 2: I really want to do this with puppet, though...

I have the following puppet code:

mysql_grant {'my-user-name@1.2.3.4/my-database-name.*':
  ensure     => 'ensure',
  options    => ['GRANT'],
  privileges => ['SELECT', 'INSERT', 'DELETE', 'UPDATE'],
  table      => 'my-database-name.*',
  user       => 'my-user-name@1.2.3.4',
}

and that does grant the permissions I expect, but if I change my mind and say this

mysql_grant {'my-user-name@1.2.3.4/my-database-name.*':
  ensure     => 'absent',
  options    => ['GRANT'],
  privileges => ['SELECT', 'INSERT', 'DELETE', 'UPDATE'],
  table      => 'my-database-name.*',
  user       => 'my-user-name@1.2.3.4',
}

I note that it doesn't ungrant permission. Changing GRANT to REVOKE also does not have the desired effect. Any pointers much appreciated.

(A related puppet question: suppose I want to permit access from several hosts. My puppet-fu fails me on how to not repeat the block.)

Answer 1

For the repeat part I can think of two ways:

1. puppetDB
2. hiera

PuppetDB

Whenever you want the fact of a node to do something on a second node, use puppetDB. This is called exported resources. This is also explained in the puppet-mysql documentation.

Example1: Add the SSH Hostkeys of all machines to the known_keys of all other machines.

Example2: Add all machines to monitoring, creating their own host definition.

Example3: On a certain class of machine, allow them to connect to MySQL.

In each case, you first install puppetDB via the puppet-puppetdb module. You will need puppet4 for this. PuppetDB will only start if you have 8+ GB of memory.

You then have to write the resource export and the resource import. On all nodes that have a fact that you want (like ip / fqdn), you write the export:

@@mysql_grant {"my-user-name@${::ipaddress}/**my-database-name.*":
  ensure     => 'absent',
  options    => ['GRANT'],
  privileges => ['SELECT', 'INSERT', 'DELETE', 'UPDATE'],
  table      => 'my-database-name.*',
  user       => "my-user-name@${::ipaddress}",
}

The '@@' creates the export. Note that the exported resource is lower case. Also note the double quote instead of single quote whenever a variable is used.

What will happen whenever a node sees this, is that it will fill out this exported resource with its fact (in this case ::ipaddress), and send it to puppetDB. You can either add this part to all nodes you want to grant access, partially defeating its purpose, or you can have a manifest that is applied to all nodes and do something along the lines of:

if $::fqdn include? 'app'{
  @@mysql_grant {"my-user-name@${::ipaddress}/**my-database-name.*":
    ensure     => 'absent',
    options    => ['GRANT'],
    privileges => ['SELECT', 'INSERT', 'DELETE', 'UPDATE'],
    table      => 'my-database-name.*',
    user       => "my-user-name@${::ipaddress}",
  }
}

Then you need to write an import statement on the node that should apply this.

Mysql_grant <<| |>>

Please note the upper case.

Another quick example, which we apply to all our linux nodes:

  # collect all the public host RSA keys for known hosts
  @@sshkey { $hostname:
    ensure       => present,
    type         => 'rsa',
    host_aliases => [$::ipaddress, $::fqdn],
    key          => $sshrsakey,
  }
  # and populate known_hosts
  Sshkey <<| |>>
  #https://projects.puppetlabs.com/issues/21811
  file { '/etc/ssh/ssh_known_hosts':
    ensure => present,
    path   => '/etc/ssh/ssh_known_hosts',
    mode   => '0644',
  }

Hiera

Hiera is build for exactly this purpose, to seperate code from data. Please refer to the hiera documentation for how to set it up.

What you end up doing is that you will create a yaml file that has all your data in it:

mysql::grants:
  db1:
    username: my-user-name
    database: my-database-name
    ip: 1.2.3.4
    ensure: present
    options:
      - GRANT
    privileges:
      - SELECT
      - INSERT
      - DELETE
      - UPDATE
    table: my-database-name.*
  db2:
    username: my-user-name
    database: my-database-name
    ip: 1.2.3.5
    ensure: present
    options:
      - GRANT
    privileges:
      - SELECT
      - INSERT
      - DELETE
      - UPDATE
    table: my-database-name.*

Then you just go ahead and put this in your mysql node (although creating a small module would be cleaner):

$grants = hiera('mysql::grants', undef)
create_resources('mysql::grant', $grants) 

Puppet will parse all of hiera, then creating a grant for every db found.