Database security

Sebastian Roth

We have left database security as a more or less untouched topic for too long and I hope to push that forward in the next weeks.

Good practice is to set a proper DB root password as well as add a less privileged account for the actual application. If we go that route we’d need to ask the user for two distinct new passwords or generate those and display to the user. We need a hint that those should be stored in the password manager of choice or written down. But as well I feel that we should prepare a detailed wiki article on how to reset the DB root password on all distros just in case people have locked themselves out. Who’s up for that?

I intend to use mysql_secure_installation command as well.

In case people choose to let the installer generate a password I’d prefer to set a semi complex password (numbers, lower case, upper case and a couple special characters) but only 8 characters in length. That way we have a chance that people actually note it down. -> Not sure if that’s a great idea, open for discussion.

I’ll probably need to setup a whole park of VMs to test the changes before we actually release this to the public. Otherwise we’ll have a massive storm of questions in the forums and installations to fix up later on I fear. This is because we have different versions of MySQL/MariaDB in the distros and some behave differently as far as I know. We have @Wayne-Workman’s awesome installer test park running but I have a feeling that we won’t cover it all with that. We need fresh install testing but even more we need testing on upgrade installations (one with empty root password and another one with a password already set). I think I can do a fair bit of that in my VM test setup I have on my working laptop using snapshots. But I am not sure I can do it for all distros. @Moderators @Testers Anyone around who’d do the tests for one or the other distro?

Please post here if you have more ideas on this or if I have left out something important!!

Issues that we might run into as setting the password has changed several times in different versions of MySQL and MariaDB:

MariaDB >10.4: https://www.reddit.com/r/sysadmin/comments/avx1u6/how_to_change_the_root_password_with_mariadb_104/ (“As a result of the above changes, the open-for-everyone all-powerful root account is finally gone. […] Using unix_socket means that if you are the system root user, you can login as root@locahost without a password.”)
MariaDB =10.3.15 (Debian 10): GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY "your-password"; works, tested 25.07.19 (ref)

Versions:

Ubuntu 18.04: mysql-5.7.24, php-7.2.10 oder mariadb-10.1.34
Arch: mariadb-10.1.37, php-7.3.0
Fedora 29: mariadb-10.3.11, 7.2.13
RHEL 7: mariadb-5.5.60, php-5.6.39
CentOS 7: mariadb-5.5.60, php-5.6.39
Debian 9: mariadb-10.1.37, php-7.0.33

Creating a DB user account seems to be just as problematic across different MySQL/MariaDB versions:

CREATE USER IF NOT EXISTS or DROP USER IF EXISTS are not available before MySQL 5.7
To get around this people propose to user GRANT ... TO which should create a user if it doesn’t exist - doesn’t work in MySQL 8.0 though.
So far the best I came up with is using a full blown SQL script to check if the user exists derived from scripts I found on the web (1, 2) - see below.
Tested the script on:
- CentOS 8/MySQL8.0.? (works)
- CentOS 7/MariaDB 5.5.64 (works after deleting anonymous account via DROP USER ''@'localhost';)
- Debian 9/MariaDB 10.1.38 (works)
- Debian 10/MariaDB 10.3.17 (works)
- Ubuntu 18.04.3 LTS/MariaDB 10.1.41 (works)
- Ubuntu 16.04.6 LTS and 18.04.3 LTS/MySQL 5.7.27 (works)
- Ubuntu 19.10/MySQL 8.0.17 (works)
- Ubuntu 19.10/MariaDB 10.3.17 (works)
Testing mysqladmin -u root -p password 'Passw0rd' command:
- CentOS 8/MySQL8.0.? (works)
- CentOS 7/MariaDB 5.5.64 (works)
- Debian 9/MariaDB 10.1.38 (works)
- Debian 10/MariaDB 10.3.17 (works)
- Ubuntu 18.04.3 LTS/MariaDB 10.1.41 (works)
- Ubuntu 16.04.6 LTS and 18.04.3 LTS/MySQL 5.7.27 (works)
- Ubuntu 19.10/MySQL 8.0.17 (works)
- Ubuntu 19.10/MariaDB 10.3.17 (works)

george1421

Just a couple of thoughts of the top of my head.

For the root password in the db. By default pick a random password and then give the user the option to change it, akin to how the fog installer picks the network adapter, but then lets the user change it. The fog installer should warn the user to write this password down someplace because its important and would be needed for database repair.
The fogdb user’s password should be managed like the fogproject linux user’s password. Its owned and set by the fog installer, but is recorded in the .fogsettings file. If the fogdb user’s owns the fog db, then there really is never a reason to use the db’s root user any more.
For the db’s root user password resets, I don’t think we need to reinvent the wheel here. Maybe provide a wiki with examples for the big three centos, debian, and ubuntu (current minus 2 releases if there is any changes) and then say for other distros they will need to google the answer. Lets not kill our selves trying to be all things to everyone. If the fog admin has deviated from the recommended distros then they should have enough skills to reset the root password. Its not that complicated.

Wayne Workman

I’ve thought about testing upgrades, I don’t think it’d be too tough. Basically, I’d add 6 more instances - all the same OSs already being tested. But I’d install the last release of FOG on them - and then snapshot.
That way, the original 6 still have clean snapshots and would be labeled as ‘clean’, and the other 6 would have a fog installation on them and be labeled as ‘upgrade’. All the other commands remain the same I think.

Wayne Workman

It’s probably going to be two weeks or longer before I can adjust the daily tests - I’m super busy this weekend, next week, and vacation is the week after. But I’m pretty confident I can test upgrades. At least upgrading from the last release.

Wayne Workman

@developers @moderators I was able to get the daily installation tests to test upgrading from FOG 1.5.5. Mostly this is going good, but 1.5.5 didn’t install correctly on Fedora 30 or RHEL7. Those two are totally jacked, but everything else seems to be working right.

I need to write some new scripts to make the setup more easy though.

Sebastian Roth

I’ll definitely pick this up again in the next days and weeks. Just had so many other things around and couldn’t find the time for this.

As well on github we have someone reporting a related issue. The installer doesn’t care about you typing in a password. It just wouldn’t use this when setting up the database in a lot of cases. Will be working on this as well: https://github.com/FOGProject/fogproject/issues/319

hostjinni

Yes, very well said about DB root password. We must set very high security with database. There are some most trusted web hosting company in India but we should keep very strong DB security.

Sebastian Roth

@hostjinni What does the external link have to do with FOG? This seems to be a company and we don’t fancy linking to external pages that don’t actually relate to this project!

Sebastian Roth

Starting to collect information on password setting mechanisms for different versions of MySQL and MariaDB in the initial topic description. Please post here if you know of other things.

Sebastian Roth

Finally I got some hours to concentrate on this topic. Turns out it’s not just the root DB password that cannot be set using identical SQL syntax across different DB versions but also the process of creating new user account and granting access. Here is a first proposal of a script to use. So far only tested on MySQL 8.0 (CentOS 8):

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI';
USE fog ;
DROP PROCEDURE IF EXISTS fog.drop_user_if_exists ;
DELIMITER $$
CREATE PROCEDURE fog.drop_user_if_exists()
BEGIN
  DECLARE foo BIGINT DEFAULT 0 ;
  SELECT COUNT(*)
  INTO foo
    FROM mysql.user
      WHERE User = 'fogdbuser' and  Host = '%';
   IF foo = 0 THEN
         CREATE USER 'fogdbuser'@'%' IDENTIFIED BY 'Passw0rd';
         GRANT ALL PRIVILEGES ON fog.* TO 'fogdbuser'@'%';
         FLUSH PRIVILEGES;
  END IF;
END ;$$
DELIMITER ;
CALL fog.drop_user_if_exists() ;
DROP PROCEDURE IF EXISTS fog.drop_users_if_exists ;
SET SQL_MODE=@OLD_SQL_MODE ;

Anyone keen to test in their machines (shouldn’t cause any trouble to your DB!) is more than welcome to!! Use:

mysql -u root -p <script.sql
mysql -u fogdbuser -pPassw0rd fog -e "SELECT * FROM images"
mysql -u root -p -e "DROP USER 'fogdbuser'@'%'"

Note: I am constantly updating the initial post to show the state of testing we have done so far.

Sebastian Roth

After some intense work on this I pushed a commit to the db-security branch. Just looks terrible because I created a bit of a mess when trying to pull the latest changes from dev-branch into db-security as well and merge it all in. But should still be all fine I hope. https://github.com/FOGProject/fogproject/commit/2f70a7872c379ec3c6b4a433b02c48b2b7c03d81

To see the actual diff of todays work see here: https://github.com/FOGProject/fogproject/compare/dev-branch...db-security

Just posting this here as I feel we are on a good track with this but it takes some very rough changes as well. @Tom-Elliott would you take a look at this. If you feel like it you are more than welcome to comment on things. May I ask you to not add this to working-1.6 before I have some more testing done!

So far only tested on CentOS 7 - looking forward to the results on Wayne’s great test environment.

Tom Elliott

I haven’t added anything in working-1.6 but I have added a few comments for review if you’d be so kind and when you have enough time.

Thank you,

Sebastian Roth

@Tom-Elliott Great comments, Tom! Thanks! Just pushed new commits and answered as well.

Sebastian Roth

@george1421 @Tom-Elliott Further tested and improved db-security branch. Also testing upgrades from 1.4.4 and 1.5.7 now. Could you think of more tests we should do? Maybe in a storage node setup? So far I have not touched the fogstorage DB user password. So it shouldn’t cause any trouble with storage node setups but you never know.

EDIT: Now that I think more about it… the fogstorage DB user password is generated as fs[0-9]* and therefore fairly easy to brute force.

Current diff: https://github.com/FOGProject/fogproject/compare/dev-branch...db-security

george1421

@Sebastian-Roth said in Database security:

EDIT: Now that I think more about it… the fogstorage DB user password is generated as fs[0-9]* and therefore fairly easy to brute force.

Could you run the generated password through md5sum or sha1sum to create a hash value. That hash value would then become the password. It would do a good job of scrambling of the password and make it over 15 characters (guess) long.

Tom Elliott

@george1421 md5 would at least be 32 characters but would do a good amount of scrambling. I don’t remember sha1-128-256-512 lengths off top of my head but pretty sure they have specific character lengths as well

Sebastian Roth

@george1421 I’d like to keep at least one special character in the password. Just pushed a new implementation a few minutes ago: https://github.com/FOGProject/fogproject/blob/4caa9f0e2f98a95c95057aaac9021f13ca2d9128/lib/common/functions.sh#L2471

This will generate passwords of adjustable length with at least but no more than one special character.

george1421

[[ $length -ge 8 && $length -le 128 ]] || length=16

For password length NIST recommends at least 12 characters min for normal users and 20 characters min for Admin accounts (yes this is my life). I know FOG is not going towards any certification, but using a standard min recommendation is always a good start. FOG admins should never need to key this password in so a longer one is a bit better. I’m wondering if its a good idea to store this password in the .fogsettings file in some kind of reversible encrypted form?

Sebastian Roth

@george1421 said:

For password length NIST recommends at least 12 characters min for normal users and 20 characters min for Admin accounts (yes this is my life).

I can see what you mean. I’ll make it 12 as minimum and 20 default.

I’m wondering if its a good idea to store this password in the .fogsettings file in some kind of reversible encrypted form?

For any kind of reversible encryption you need to have a secret. That needs to be stored somewhere. If a person is able to grab /opt/fog/.fogsettings the encryption key is also seen to be compromised. So I don’t see any better security with this.

@Tom-Elliott I have done a fair amount of testing now and I have a good feeling about the changes not causing too much of trouble for users installing or upgrading FOG. You are welcome to look through the current change set again and comment. I plan to merge this into dev-branch tomorrow.

Tom Elliott

@Sebastian-Roth while agree we need a more secure password for database and all, I don’t understand why we wouldn’t store the password with fogsettings. I say this because anybody who can get this file can just as easily get the config.class.php file which would have the password stored in plain text.

So removing it from this file doesn’t make it any more secure.

Similarly storing it in a reversible encrypted form wouldn’t matter either. Why waste time trying to encrypt or decrypt if it has to be stored in plain text on the system anyway? Just go to the file that has it stored.

Unfortunately there really isn’t a better way to access the database with a password than to have it called out in plain text to begin with.

Essentially it boils down to:

If the person can access /opt/fog/.fogsettings, they just as easily have access to the rest of the system and any files therein that may be accessible. It doesn’t matter how much we make the password secure, if they are on the system, you’re toast anyway.

Removing the password from .fogsettings just makes it harder for us and others to troubleshoot an issue with the database in general.

Database security

184

11.6k

17.1k

154.6k