Ansible MySQL User with REQUIRE SSL

Question

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;

I know SSL is still possible without the REQUIRE SSL statement, but I would just like to enforce it, so no "new guy" (or tired me) ever connects to the server without SSL, and if it's done somewhere I would like an immediate connection error, so I know something is wrong. That's why I want to enforce it. — Henrik, Oct 23 '14 at 01:43
This does not directly answer your question, but instead of exposing MySQL to the internet(even if it's ssl'ed), consider having it listen only on `127.0.0.1` and then use SSH tunnel for remote administration. — Mxx, Oct 23 '14 at 05:16
Thanks @Mxx I've thought about that. I honestly just liked the simplicity while still retaining security. The server is firewalled to office IP only, so I don't see it as a big concern. I just wanted to protect data while in transit from casual sys-admin observers and such on our ISP. I might do it in the future tho as it feels "dirty" to have an exposed MySQL server even through only to a specific IP. — Henrik, Oct 24 '14 at 15:10
With SSH those casual observers won't even know that you have mysql traffic going there since everything will be encapsulated in SSH. To setup an SSH tunnel is just a single command. — Mxx, Oct 24 '14 at 15:23

score 5 · Accepted Answer · answered Apr 19 '16 at 12:19

5

This works for me, without the append_privs option (ansible 2+)

priv='some_db.*:ALL,GRANT,REQUIRESSL'

answered Apr 19 '16 at 12:19

bas

628
9
22

Bruce P · Answer 2 · 2014-11-03T19:03:20.350

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)

Hmm I tried the append_privs way, but get an error "msg: Boolean *.*:require ssl not in either boolean list" so I don't think it works since it is not a mysql "priv_type" — codercake, Oct 31 '14 at 18:22
Interesting. I took a look at the source of the mysql module and there are references to "require ssl" that seems to imply this would have worked. If I have time next week I'll see if I can figure it out... — Bruce P, Oct 31 '14 at 21:29

Ansible MySQL User with REQUIRE SSL

2 Answers2