Root password policy for mysql

[Soren Hansen]

We want to ask as few questions as possible during installation, so we prefer not to ask the user for a root password for his mysql server during install. However, having a mysql server without a root password leaves the database wide open for various exploits in e..g webapps.

We need a solution that preferably does not involve asking questions during installation, but also does not leave us that open to exploits.

Martin Pitt suggested hacking mysql to allow the (system) root user to change the mysql root password without knowing the current password and setting it to a random/bogus value on installation. That way, any user with sudo privileges can set a proper password after installation if he wants to.

[Caspar Clemens Mierau (leitmedium)]

Actually you can do the sudo thing without hacking mysql or touching mysql code. A rather simple init/shell-script (re)starting mysqld with skip privileges lets you overwrite existing root passwords, that is also the way mysql suggests.

So a /etc/init.d/mysql setpass could interactively ask you wheter to restart mysql with skip privs listening only on a local socket, set a new password and restart mysql with normal my.cnf configuration afterwards.

That might sound a little freaky but actually it's fewer code, more maintainable and mysql administrators used to have a standard mysql server don't get puzzled about a patched server.

[Soren Hansen (soren)]

On Thu, Jun 07, 2007 at 11:32:14AM -0000, Caspar Clemens Mierau wrote:
> Actually you can do the sudo thing without hacking mysql or touching
> mysql code. A rather simple init/shell-script (re)starting mysqld with
> skip privileges lets you overwrite existing root passwords, that is also
> the way mysql suggests.

Do you have some sort of reference where they suggest that?

> So a /etc/init.d/mysql setpass could interactively ask you wheter to
> restart mysql with skip privs listening only on a local socket, set a
> new password and restart mysql with normal my.cnf configuration
> afterwards.

Hm. I thought about that, but hadn't thought about putting it in the
init script. Clever.

Suggestion:

1. Add a setpass option to /etc/init.d/mysql that will allow the
(system) root user to change the mysql root password.

2. Ask for a root password during installation (it's only on the server
CD and even then it's only if you choose the LAMP install) and also let
the user know about the shiny new way of resetting the password.

Question:

If the user doesn't give a password, should we:

a) just reprompt him until he caves in and sets one
b) accept the empty password
c) set it to a bogus(invalid) password and tell the user about the
setpass option of the init script.

?

[Mathias Gug (mathiaz)]

On Thu, Jun 07, 2007 at 01:06:24PM -0000, Soren Hansen wrote:
> On Thu, Jun 07, 2007 at 11:32:14AM -0000, Caspar Clemens Mierau wrote:
> > Actually you can do the sudo thing without hacking mysql or touching
> > mysql code. A rather simple init/shell-script (re)starting mysqld with
> > skip privileges lets you overwrite existing root passwords, that is also
> > the way mysql suggests.
>
> Do you have some sort of reference where they suggest that?

The official documentation from MySQL has a section on how to reset the
root password :
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

> > So a /etc/init.d/mysql setpass could interactively ask you wheter to
> > restart mysql with skip privs listening only on a local socket, set a
> > new password and restart mysql with normal my.cnf configuration
> > afterwards.

This solution (starting mysqld with "--skip-grant-tables --user=root") is
mentioned in the documentation, but not recommended.

The recommended way is to generate a file containing an SQL command to
set the password, and (re)start the server with the --init-file option.
That way you don't have to restart the mysql daemon once the password
has been reseted.

>
> Hm. I thought about that, but hadn't thought about putting it in the
> init script. Clever.
>

I like it too. But does this comply with the debian policy relating to
init scripts ? Can custom functions be added ?

> Suggestion:
>
> 1. Add a setpass option to /etc/init.d/mysql that will allow the
> (system) root user to change the mysql root password.
>
> 2. Ask for a root password during installation (it's only on the server
> CD and even then it's only if you choose the LAMP install) and also let
> the user know about the shiny new way of resetting the password.
I'd rather set a random password during installation.

May be for the LAMP case, a password could be asked, with a
pre-generated password pre-seeded.

> Question:
>
> If the user doesn't give a password, should we:
>
> a) just reprompt him until he caves in and sets one
> b) accept the empty password
> c) set it to a bogus(invalid) password and tell the user about the
> setpass option of the init script.
>
I would take b) in the case of installing a LAMP task. If the user wants
explicitly to have an empty password, we should let him, provided that
we offer a pre-generated random password by default.

--
Mathias

[Ante Karamatić (ivoks)]

Soren Hansen wrote:

> We want to ask as few questions as possible during installation, so we
> prefer not to ask the user for a root password for his mysql server
> during install. However, having a mysql server without a root password
> leaves the database wide open for various exploits in e..g webapps.

What kind of exploits? User can access mysql (by default) only if he is
root or has sudo rights. So... If that user is compromised, securing
mysql is pointless since he can override mysql's root password.

Am I missing something?

[Ante Karamatić (ivoks)]

Ante Karamatić wrote:

> Am I missing something?

I am. Feel free to kick me :)

I vote for random root password as a quick measure. We don't even have
to show it to the user.

Changing random password would be easy, trough already mentioned acts,
or like this; /etc/mysql/root.cnf (chowned 600):

[client]
host = localhost
user = root
password = randompassword
socket = /var/run/mysqld/mysqld.sock

then running this as a wrapper or whatever:

mysqladmin --defaults-file=/etc/mysql/root.cnf password new_pass

This option is good cause it doesn't stop mysql and therefor doesn't
start it in --skip-grant-tables mode, which is insecure. Then again, we
already have /etc/mysql/debian.cnf with debian-sys-maint user, which is
equal to root user...

Good thing about using mentioned methods (--skip-grant-tables and
--init-file) is that is already used/tested solution for the problem we
are trying to solve. No need to invent new stuff when old stuff is good
and working :)

[Mathias Gug (mathiaz)]

On Thu, Jun 07, 2007 at 10:41:07PM -0000, Ante Karamatić wrote:
> What kind of exploits? User can access mysql (by default) only if he is
> root or has sudo rights.

I don't think so. Accounts in MySQL are different from system accounts.
After the package is installed, any local user to the system can connect
to the server using the command :
 mysql -u root

--
Mathias

[Caspar Clemens Mierau (leitmedium)]

I don't think setting a random password is a good idea.

1. Are there any other applications where random passwords are set (that you need)?

2. MySQL by default does not listen on a (remote) network interface.

3. I cannot name one distribution that sets a random password to mysql, this would lead to a lot of questions.

4. You need to store it somewhere on the disk.

I therefore still prefer offering a script using the skip-stuff, be it in an init script or not. It could of course also be a /usr/sbin or /usr/local/sbin script named "mysql_set_rootpass" or something like this. Such script could also be easily given back to the debian project. If you still want to warn the user you could check the mysql password on start (starting mysql ... mysql has no password set ... please do xyz) which could be stopped by setting a flag in a config script.

[Ante Karamatić (ivoks)]

Caspar Clemens Mierau wrote:

> 1. Are there any other applications where random passwords are set (that
> you need)?

Yes, mysql. People are unaware of this, but there is another root user
in mysql wich has random password. Check /etc/mysql/debian.cnf.

> 2. MySQL by default does not listen on a (remote) network interface.

Not directly, but that's not important. Local non-root user can destroy
databases and local user can set up a service wich would allow remote
users to connect to local mysql as root, again making possible to
destroy mysql databases.

> 3. I cannot name one distribution that sets a random password to mysql,
> this would lead to a lot of questions.

You can - Debian. I didn't check others.

> 4. You need to store it somewhere on the disk.

As said - /etc/mysql/debian.cnf

> I therefore still prefer offering a script using the skip-stuff, be it
> in an init script or not. It could of course also be a /usr/sbin or
> /usr/local/sbin script named "mysql_set_rootpass" or something like
> this. Such script could also be easily given back to the debian project.
> If you still want to warn the user you could check the mysql password on
> start (starting mysql ... mysql has no password set ... please do xyz)
> which could be stopped by setting a flag in a config script.

Script/program that overrides root password is something every mysql has
for years now. There is nothing special we have to develop. init script
already uses debian-sys-maint mysql account for checking status of
mysqld, stoping it and doing reload. Adding 'reset_password' should be
fairly easy.

We should take a look if it is possible to change root password with
mysqladmin, authenticated as debian-sys-maint user. If that's possible,
adding random password to root and changing it's pass with mysqladmin
trough init script should be trivial.

[Caspar Clemens Mierau (leitmedium)]

> Yes, mysql. People are unaware of this, but there is another root user
> in mysql wich has random password. Check /etc/mysql/debian.cnf.

Actually I thought debian-sys-maintainer is only able to ping the server but it seems I am wrong. At least under debian I see the user "Y" in all priv fields in the mysql user table.

> Script/program that overrides root password is something every mysql has
> for years now

Can name this? That would be the perfect solution as we only need to call this.

> We should take a look if it is possible to change root password with
> mysqladmin, authenticated as debian-sys-maint user.

If the above is right the user should be able to do this.

[Ante Karamatić (ivoks)]

Caspar Clemens Mierau wrote:

> Can name this? That would be the perfect solution as we only need to
> call this.

Check out Mathias's comments.

[Soren Hansen (soren)]

Alright then. I've added a "resetpasswd" option to /etc/init.d/mysql which asks for a new password (using whiptail) and sets it accordingly.

That's the technical bit. I'm still not convinced about the proper course of action with regard to the prompt during installation. The easiest is of course to just do whatever Debian does, but we have a policy about not asking superfluous questions during installation. However, we also aim for userfriendliness and security, so in my mind, out best bet is to ask for the password, but to accept an empty one if that's what the user really thinks he wants.

Setting it to a random value is not a good idea, I think. It renders the mysql server useless until the password has been dug out from the file where we put it or until the password has been reset. In my mind, this just sounds like a really unfriendly way of forcing the user to set a password anyhow, so we might as well reprompt him during installation until he sets a password, but that would be violating our don't-ask-too-many-questions-during-installation even more, I think.

[Ante Karamatić (ivoks)]

Soren Hansen wrote:

> Alright then. I've added a "resetpasswd" option to /etc/init.d/mysql
> which asks for a new password (using whiptail) and sets it accordingly.

Well, we have postfix which asks questions on installation, so adding
questions to mysql shouldn't be a big problem.

But, we should avoid that always, if we can. I think we can with mysql -
don't start it if password is not set.

For example, init script, start function:

if [ -e /etc/mysql/dont_start ]; then
 echo "Set MySQL root password or delete /etc/mysql/dont_start"
 exit 1
fi

Then provide easy to use tool to change password.

This gives an option of securing mysql or using it without password.

[Caspar Clemens Mierau (leitmedium)]

I guess /etc/defaults is also a good place for a variable to source.

[Ante Karamatić (ivoks)]

Caspar Clemens Mierau wrote:

> I guess /etc/defaults is also a good place for a variable to source.

Right:

# MySQL root password is empty. Enable MySQL service and set root's
# password with 'mysqladmin -u root password [your new password]'.
#
# If root's password isn left empty, all local users will be able
# to login as root to MySQL.
#
ENABLED=0

[Soren Hansen (soren)]

Ok, I now have a working version which:
  * Lower the priority of the debconf prompt for the root password to medium,
    to avoid asking for it during installation.
  * Add resetpasswd option to init script.
  * Set random root password on installation if no password given.
  * Warn about random password on each start and explain about resetpasswd.

However, after talking to Martin Pitt, we've decided to try to actually *fix* things rather than patch them. I'll be filling a bug against the debian package by the end of this week (or if someone else has the time, feel free to do so) and discuss it with them.

[Mathias Gug (mathiaz)]

On Mon, Jun 11, 2007 at 12:45:42PM -0000, Soren Hansen wrote:
> However, after talking to Martin Pitt, we've decided to try to actually
> *fix* things rather than patch them. I'll be filling a bug against the
> debian package by the end of this week (or if someone else has the time,
> feel free to do so) and discuss it with them.
>

There are two bugs in bugs.debian.org related to this issue:

* http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=418672:
  Creates root accounts without password on upgrade
* http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=418955:
  In default install all users are granted all access

It seems that the user is asked at installation time to enter a root
password.

[Martin Pitt (pitti)]

Needs more time to discuss with upstream and Debian, moving to tribe-3

[Soren Hansen (soren)]

After discussion with MySQL:
http://bugs.mysql.com/bug.php?id=29287

And with Debian, here's the result so far:

We don't like having cleartext passwords in files (regardless of the permissions of said file).
We want to be able to reset passwords etc. without a password if we're the user running mysqld.

Solution:
Patch (or wait until MySQL does it for us) mysqld to check who the connecting user is and grant full access if it matches the user it's currently running as.

[Caspar Clemens Mierau (leitmedium)]

Well actually this is a huge step back on the timetable, isn't it? I guess a patch for this feature will take some months for development and security/stabilty testing. So that's the reason I hoped for skip-priv-like solution fast to implement.

[Soren Hansen (soren)]

On Mon, Jul 16, 2007 at 11:09:28AM -0000, Caspar Clemens Mierau wrote:
> Well actually this is a huge step back on the timetable, isn't it? I
> guess a patch for this feature will take some months for development and
> security/stabilty testing. So that's the reason I hoped for skip-priv-
> like solution fast to implement.

For someone who is C++ literate and vaguely familiar with the mysql
codebase, it ought to amount to max. two days of coding. Add to that
some testing (MySQL has extensive unit testing in place, so it should be
manageable) and we're rocking.

--
Soren Hansen
Ubuntu Server Team
http://www.ubuntu.com/

[Soren Hansen (soren)]

Just to record it: Our resident Debian maintainer person said he'd try to squeeze it into his otherwise busy schedule. I'll check up on it in a while and figure out how to handle this if he's too busy.

[Jonathan Riddell (jr)]

moving to beta, soren is away for tribe 5

[Steve Langasek (vorlon)]

moving the milestone

[Jamie Strandboge (jdstrand)]

The security team is getting bugs on this issue, and IMO it is not a good idea to setup a passwordless mysql root account. I understand about making things easy for the end user, but I don't see how this is much different than setting up a password for a login user. I think more people would be upset (if they knew) that the root mysql account is passwordless than being confronted with a dialog prompting for the password. I also realize that I am new to the discussion, but this is a real security issue going back to (at least) dapper.

Wouldn't it be better to prompt for the password until we figure out/have the time to implement the best way to not prompt safely?

[Martin Pitt (pitti)]

Hi,

Jamie Strandboge [2007-10-02 2:17 -0000]:
> Wouldn't it be better to prompt for the password until we figure
> out/have the time to implement the best way to not prompt safely?

Full ack.

[Steve Langasek (vorlon)]

Dropped from the RC milestone; the current behavior does prompt for a mysql root password at high priority on install, so the security issue is resolved.

It is still a bug that the package has to prompt the user for a password for a functional, secure-by-default installation, but this can be deferred.

[Jamie Strandboge (jdstrand)]

I am preparing patches for Dapper -> Feisty based on Soren Hansen's previous work. These patches are going to be part of a larger security upload. The real issue is that the root password prompt priority is medium, which is not normally seen in Ubuntu, therefore it is assumed there are *many* mysql installations with no root password. In light of this, the patches will:

a) let the user know on mysql startup that there is a blank root password (and how to reset it). This will be seen on stdout, as well as syslog, and therefore can also be seen on upgrade.
b) provide an easy means to reset the password

In talking with Soren and others, this seems the best course as there aren't any string changes to cause translation problems, no priority changes to cause automated install problems, no assumptions are made about what the user wants/needs (ie it won't break anything), and with the exception of an informational message, mysql will continue to operate as before even if the user does nothing.

I am going to leave Gutsy alone, as it has a high enough debconf priority for people to see it on install.

Attached is the diff of the changes for Feisty I am currently using (should work against Edgy too; Dapper has a slightly different patch). Preliminary testing shows it works as intended, but I am going to do more testing before uploading.

[Mathias Gug (mathiaz)]

Dropping importance to whishlist, as the default install will prompt for a root password.

[Rafael Gattringer (rafael.gattringer)]

I was surprised that the password at the installation is only set once. As the letters of the new password are (correctly) hidden a typo of users like me might be possible. ;)

Possible Solution: Confirm the new password in a second input field (like at the Ubuntu installation).

See the attached screenshot displaying the current password promt.

[Bram Daams (bram-daams)]

I was surprised to see the root password dialog. Even when using apt-get install with the "-y" option. The apt-get man page tells me that, besides answering 'yes' to all questions, the setup runs non-interactively.
It also doesn't tell anything about debconf priorities or how to prevent questions during automatic installation.

IMO the debconf priority should be lowered or the apt-get man page should document these kind of exceptions.

[Alex Ruddick (alexrudd0)]

Ugh, while upgrading Intrepid today Debconf blocked the upgrade 3 times asking me to set the root password.

Given that mysql is only installed for a DigiKam backend (I think), there's no reason why I would give a damn the first time around, much less the second or third.

[Sergio Zanchetta (primes2h)]

The 18 month support period for Gutsy Gibbon 7.10 has reached its end of life -
http://www.ubuntu.com/news/ubuntu-7.10-eol . As a result, we are closing the
Gutsy task.

[Soren Hansen (soren)]

As far as I can tell, this has been fixed since Gutsy.