Henrik Henrik - 7 months ago 63
SQL Question

Ansible MySQL User with REQUIRE SSL

I've just begun learning Ansible today, and I'm already making fast progress and on the edge of being able to automate our whole IT stack. That's nice! :)

I've however hit a roadblock. We've chosen to take the small performance hit and encrypt ALL MySQL connections using the SSL feature. This is to let our office IP's remotely manage it, and also inter-datacenter.

Using the mysql_user module, I can make sure an user is added, and set the password and so forth. But I can't seem to find anyway to require SSL on the user? According to a quick Google, and the lack of options in the documentation, I guess I can't do it with mysql_user.

But the real question is: Do you know a (preferably clean) work around?

If I could somehow execute raw queries with Ansible it would be perfect. To be specific, I need to replicate this SQL in Ansible, however possible:

GRANT ALL PRIVILEGES ON *.* TO ‘ssluser’@’%’ IDENTIFIED BY ‘pass’ REQUIRE SSL;

Answer

I believe you can do this with the mysql_user module:

- mysql_user: name=bob password=12345 append_privs=*.*:REQUIRESSL

By using "append_privs" you'd just be adding "REQUIRE SSL" to the existing privileges for bob.

If you want to do this manually it can get a little ugly, but you can always invoke the mysql client via the command module. Assuming you have variables defined for the mysql user & host:

- command: mysql -u {{ myslq_user }} -h {{ mysql_host }} -p{{ mysql_password }} "GRANT ALL PRIVILEGES ON *.* TO ‘ssluser’@’%’ IDENTIFIED BY ‘pass’ REQUIRE SSL;"

Edit: I just ran some tests and got this to work without any problems after ensuring that the proper Python libraries were installed:

- hosts: myhost
  name: test
  user: my-user
  tasks:
    - local_action: mysql_user
                    user=foo
                    host='%'
                    password=bar
                    state=present
                    append_privs=yes
                    login_host=<my_database_host>
                    login_user=<my_database_root_user>
                    login_password=<my_database_root_password>
                    priv='*.*:REQUIRESSL'

The above resulted in:

mysql> show grants for 'foo'@'%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for foo@%                                                                                               |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' REQUIRE SSL |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)