Database security

  • Developer

    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:

    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)

  • Developer

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

  • Senior Developer

    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,

  • Developer

    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.

    To see the actual diff of todays work see here:

    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.

  • Developer

    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):

    USE fog ;
    DROP PROCEDURE IF EXISTS fog.drop_user_if_exists ;
    CREATE PROCEDURE fog.drop_user_if_exists()
      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 ;$$
    CALL fog.drop_user_if_exists() ;
    DROP PROCEDURE IF EXISTS fog.drop_users_if_exists ;

    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.

  • Developer

    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.

  • Developer

    @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!

  • 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.

  • Developer

    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:

  • @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.

  • 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.

  • 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.

  • Moderator

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

    1. 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.
    2. 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.
    3. 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.

Log in to reply