MySQL/MariaDB root password with Ansible

[Damien Buttler]

For those that are using Ansible that is installing a database such as MySQL or MariaDB, do you have a preferred method to configure the root password for the database. I've tried different examples found on the web but they haven't worked for me.

I am using MariaDB but from what I can work out it should be the same as MySQL.

Damien

--

• Damien Buttler
• Email: dam...@doublehops.com
• Web: www.doublehops.com
• Twitter: @doublehops
• LinkedIn: http://au.linkedin.com/in/damienbuttler
• Phone: 0418 998 283

[Gary Pope]

Good question,. Damien. I've been concerned that the method of putting
the root password into /etc/mysql/my.cnf as plain text, protected only
by the internal security of that file being accessible by root password
access in the first place, is less than desirable. I can only assume
your ansible question relates to the disclosure of the root password in
the ansible recipe that needs to modify /etc/mysql/my.cnf to deploy
mysql in such a fashion. I'm in the middle of introducing ansible and
this has been a stumbling block for me, security wise too.

FYI: I'm doing this in a Ubuntu v12.04LTS virtual machine withing a
Debian v7.2 Host.
--
*Gary *

On 14/02/2014 12:24 AM, Damien Buttler wrote:
> For those that are using Ansible that is installing a database such as
> MySQL or MariaDB, do you have a preferred method to configure the root
> password for the database. I've tried different examples found on the
> web but they haven't worked for me.
>
> I am using MariaDB but from what I can work out it should be the same
> as MySQL.
>
> Damien
>
>
> --
>

> * Damien Buttler
> * Email: dam...@doublehops.com <mailto:dam...@doublehops.com>
> * Web: www.doublehops.com <http://www.doublehops.com?via_email>
> * Twitter: @doublehops <http://twitter.com/doublehops>
> * LinkedIn: http://au.linkedin.com/in/damienbuttler
> * Phone: 0418 998 283
>
> --
> You received this message because you are subscribed to the Google
> Groups "mlug-au" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to mlug-au+u...@googlegroups.com.
> To post to this group, send email to mlu...@googlegroups.com.
> Visit this group at http://groups.google.com/group/mlug-au.
> For more options, visit https://groups.google.com/groups/opt_out.

[Chris Berkhout]

I'm not really familiar with MariaDB/MySQL, but I suspect that a password can be set in the database itself, probably by giving an encrypted version of the password. Maybe the password in the file is supposed to be temporary, just to let you in to set a proper password?

Also, I see that it has pluggable authentication:
    https://mariadb.com/kb/en/pluggable-authentication/

So maybe you can have authentication done by another method, which doesn't use plain text passwords.

As for Ansible, there are several ways to handle sensitive data.

If you're happy to run it interactively you can have it prompt you for passwords. Read this page:

    http://docs.ansible.com/playbooks_prompts.html

In the past I have dealt with sensitive data by putting it all in a vars files that I track in a separate, private repository. I suppose the next level would be to have that vars file encrypted on disk and require users running the playbook to decrypt it into memory first.

Cheers,

Chris

On Fri, Feb 14, 2014 at 7:46 AM, Gary Pope <g...@alchester.com.au> wrote:

Good question,. Damien.   I've been concerned that the method of putting the root password into /etc/mysql/my.cnf as plain text,  protected only by the internal security of that file being accessible by root password access in the first place,  is less than desirable.    I can only assume your ansible question relates to the disclosure of the root password in the ansible recipe that needs to modify /etc/mysql/my.cnf  to deploy mysql in such a fashion.    I'm in the middle of introducing ansible and this has been a stumbling block for me, security wise too.

FYI:   I'm doing this in a Ubuntu v12.04LTS  virtual machine withing a Debian v7.2 Host.
--
*Gary *

On 14/02/2014 12:24 AM, Damien Buttler wrote:

For those that are using Ansible that is installing a database such as MySQL or MariaDB, do you have a preferred method to configure the root password for the database. I've tried different examples found on the web but they haven't worked for me.

I am using MariaDB but from what I can work out it should be the same as MySQL.

Damien


--

  * Damien Buttler
  * Email: dam...@doublehops.com <mailto:dam...@doublehops.com>
  * Web: www.doublehops.com <http://www.doublehops.com?via_email>
  * Twitter: @doublehops <http://twitter.com/doublehops>
  * LinkedIn: http://au.linkedin.com/in/damienbuttler
  * Phone: 0418 998 283

--
You received this message because you are subscribed to the Google Groups "mlug-au" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+unsubscribe@googlegroups.com.

To post to this group, send email to mlu...@googlegroups.com.
Visit this group at http://groups.google.com/group/mlug-au.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "mlug-au" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+unsubscribe@googlegroups.com.

[Chris Berkhout]

PS. There's an Ansible newsletter: https://devopsu.com/newsletters/ansible-weekly-newsletter.html

[Daniel X]

I have set user passwords via (non-interactive) ansible, and instead of having plaintext passwords, have used salted hashes instead, which can be inserted directly.

I haven't had any experience with MariaDB, but can see from their docs that you can pass a hashed value directly when creating users (https://mariadb.com/kb/en/create-user/).  I will presume that this value is either stored in some sort of config, or db file.

Not sure what their hash mechanism would be, but for linux SHA512 salted shadow files I use (you can make up any value as a salt):

#!/usr/bin/env python
import crypt

import sys

password = raw_input("Password: ")

salt = raw_input("Salt: ")

salt = "$6$" + salt + "$"

print crypt.crypt(password, salt)

Not sure if that helps your case much,

Daniel

To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+u...@googlegroups.com.

[Damien Buttler]

No, my question is more about how to set the password with Ansible.

I am getting errors when trying to set the root password. I was using the following code (code?, playbook rules?)

- name: MariaDB | Update root password for all root accounts
  mysql_user: name=root host={{ item }} password={{ mariadb_root_password }} priv=*.*:ALL,GRANT
  with_items:
    - "{{ ansible_hostname }}"
    - 127.0.0.1
    - ::1
    - localhost

The error I see is:

failed: [webserver1] => (item=debian-web) => {"failed": true, "item": "debian-web"} msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials

Basically, after the initial install where there is not root password, the playbook with loop through the hosts and successfully set the password for root. However running the playbook again will fail here because I guess it's trying to run mysqladmin without a password. I've tried hard coding values into my.cnf but this hasn't helped.

I am not concerned about password security at this time as this is just for development environments. Therefore I can work around this for now.

Damien

On 14/02/14 07:46, Gary Pope wrote:

Good question,. Damien.   I've been concerned that the method of putting the root password into /etc/mysql/my.cnf as plain text,  protected only by the internal security of that file being accessible by root password access in the first place,  is less than desirable.    I can only assume your ansible question relates to the disclosure of the root password in the ansible recipe that needs to modify /etc/mysql/my.cnf  to deploy mysql in such a fashion.    I'm in the middle of introducing ansible and this has been a stumbling block for me, security wise too.

FYI:   I'm doing this in a Ubuntu v12.04LTS  virtual machine withing a Debian v7.2 Host.

--

[Damien Buttler]

Great, thanks for sharing.

To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+u...@googlegroups.com.

To post to this group, send email to mlu...@googlegroups.com.
Visit this group at http://groups.google.com/group/mlug-au.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "mlug-au" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+u...@googlegroups.com.

To post to this group, send email to mlu...@googlegroups.com.
Visit this group at http://groups.google.com/group/mlug-au.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "mlug-au" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+u...@googlegroups.com.

To post to this group, send email to mlu...@googlegroups.com.
Visit this group at http://groups.google.com/group/mlug-au.
For more options, visit https://groups.google.com/groups/opt_out.

--

[Damien Buttler]

Thanks, that's an interesting approach. I might try that also.

To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+u...@googlegroups.com.
To post to this group, send email to mlu...@googlegroups.com.
Visit this group at http://groups.google.com/group/mlug-au.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "mlug-au" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+u...@googlegroups.com.
To post to this group, send email to mlu...@googlegroups.com.
Visit this group at http://groups.google.com/group/mlug-au.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "mlug-au" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+u...@googlegroups.com.
To post to this group, send email to mlu...@googlegroups.com.
Visit this group at http://groups.google.com/group/mlug-au.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "mlug-au" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+u...@googlegroups.com.
To post to this group, send email to mlu...@googlegroups.com.
Visit this group at http://groups.google.com/group/mlug-au.
For more options, visit https://groups.google.com/groups/opt_out.

[Damien Buttler]

OK, I have a working Ansible script that installs a Debian vm running as a webserver. You can read about it and download to test/try-out at https://github.com/doublehops/debian-webdev-environment.

Constructive criticism welcome.

Damien

[Duncan Roe]

It's saying it can't connect to the database, and then suggesting user /
password may be wrong. But that may not be the problem at all. Usually with
Mariadb if you want local use only then you get it to use a Unix socket. In that
case it does not listen on 127.0.0.1.

Worth checking out maybe?

Cheers ... Duncan.

> * Damien Buttler
> * Email: dam...@doublehops.com <mailto:dam...@doublehops.com>
> * Web: www.doublehops.com <http://www.doublehops.com?via_email>
> * Twitter: @doublehops <http://twitter.com/doublehops>
> * LinkedIn: http://au.linkedin.com/in/damienbuttler
> * Phone: 0418 998 283
>
> --
> You received this message because you are subscribed to the Google Groups "mlug-au" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+u...@googlegroups.com.
> To post to this group, send email to mlu...@googlegroups.com.
> Visit this group at http://groups.google.com/group/mlug-au.
> For more options, visit https://groups.google.com/groups/opt_out.

--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

[Chris Berkhout]

You can probably look at the mysql module code,

or turn up logging in ansible and/or mariadb to see how its actually trying to do it and why it's failing.