Raspbian Stretch

I’ve been through the process again (more thoroughly this time) and have a few more observations. :slight_smile:

This line:
sudo pecl install channel://pecl.php.net/dio-0.1.0 swift/swift
produced this warning:
WARNING: channel "pecl.php.net" has updated its protocols, use "pecl channel-update pecl.php.net" to update

I suggest you add that as well, so the section becomes:

That was the only thing in the doc that didn’t work cleanly, everything else worked as expected.

The only other exception was that I used my raspi-config method to disable the serial console instead of the commands in your doc. Once powering off, plugging my emonbase into the GPIO header and powering up, EmonHub is happily reading data via the serial port from my emonbase.

Other observations
These are the Modules listed on the Administration page on the stretch install completed above:
Administration | EventProcesses | Feed | Input | CoreProcess | Schedule | Time | User | Visualisation

These are the Modules listed on the Administration page from the SD Card Image install:
Administration | App v1.0.0 | app-original | Backup v1.0.0 | EmonHub Config v1.0.0 | dashboard | EventProcesses | Feed | Graph v1.0.0 | Input | postprocess | CoreProcess | Schedule | setup | Time | User | Visualisation | WiFi v1.0.0

Many of those Modules are not found in any of the links at the end of the documentation, EmonHub Config v1.0.0 was one that I noticed immediately. I assume there’s also a module in there that enables the “Update” section of the “Administration” page, which is missing in the new stretch install.

My next step is to go through the MQTT install guide, but that will have to wait for another night.

Thanks heaps for putting the guide together!

EDIT: For completeness, this test was carried out on a Raspberry Pi Model B+ V1.2 (2014)
The Image I started with was 2017-08-16-raspbian-stretch-lite.img

For future builds has anybody got experience of automating this install using Puppet or Chef for instance?

Sorry guys I haven’t played with the Stretch version of Raspbian yet so any comment from me would be based on expectation and assumption until I get a chance to try it for myself, hopefully soon(ish).

@Greebo - guide updated - thank you, but I encountered problems using raspi-config instead of editing cmdline.txt, so have not made those changes at the moment.
It’s something we can revisit though.

Paul

I started looking at this over the weekend, but I got a bit distracted by the way mariadb seems to authorise the root user, I became aware there were differences when I tried to install phpmyadmin and could not log in using the root password that I thought I had set using sudo mysql_secure_installation whilst stepping through the Stretch guide.

Seems no matter how many times I loop through the sudo mysql_secure_installation steps (as per the guide) the password change doesn’t stick. This is confirmed in several ways

  1. Still unable to log in to phpmyadmin as the root user.
  2. The next time I run “sudo mysql_secure_installation” it still allows me to “enter for none” when it prompts me for the current password, replying “OK, successfully used password, moving on…” and allowing me to go through the motions of setting a new root password or not depending on whether I reply “Y” or “n”. In fact I can enter any old text as the current password and it will happily move on to the next Q with the same “OK, successfully used password, moving on…” reply.
  3. Any attempt to start the mysql shell using mysql -u root -p will ask for a password and any response, including correct password, incorrect password or just ‘enter for none’ results in the same “ERROR 1698 (28000): Access denied for user ‘root’@‘localhost’” error message.
  4. If I use 'sudo mysql -u root -p` I can give any password or random text I like, it still lets me continue as the root user.

Further investigation has led me to understand the root password is not and possibly can not be set for mariadb due to the default root user being authenticated by the unix_socket authentication plugin. Which effectively allows mariadb to trust the OS security and any user on the sudoers list can execute “root user” level commands by simply using sudo. For example running mysql -e 'SHOW DATABASES' will result in an error whereas sudo mysql -e 'SHOW DATABASES' will work perfectly without using -u root -p.

This explains

quite literally using sudo IS logging you in as the root mysql user, any use of a password seems to be ignored.
Seems the options here come in 2 flavours

  1. Remove the unix_socket authentication plugin for the root mysql user, this will allow a root password to be set and return the usage to what we are used to.
  2. Accept the new authorisation method (using sudo) and if needed create another user with root level permissions for phpmyadmin and for using mysql in a “traditional” way.

Although I read many pages to arrive at this point, this page sums up those options very succinctly.

SO either we need to add a step to remove the unix_socket authentication plugin for the root mysql user prior to running sudo mysql_secure_installation

OR when the guide walks us through using sudo mysql_secure_installation the responses perhaps should be “n” to changing the (unused) root password and “Y” to all that follows (I haven’t yet checked to see if these other options stick) to avoid the confusion of thinking you’ve set a password when you haven’t.

Personally I like the new way of using the mysql at the command line without needing the mysql root password, so I opt for embracing the new method (no better time than along side the change to mariadb) and adding an additional user with root privileges if/when remote access or phpmyadmin is required. What do you think?

Please try the tests I laid out above on your own systems to check I haven’t introduced something unique to my setup and confirm if this is the same for you.

Confirmed that following the install guide instructions doesn’t in fact set the new MariaDB root password. It does appear to remove the “test” DB, and running “mysql” from the shell results in an Access denied error implying that the anonymous user was deleted.

I vote for this option, with perhaps a short description of why.

Further to the above I have just checked and phpmyadmin can be used with the emoncms user:password which is probably a more secure option than allowing login as root, so only if a user wants full root mysql access via phpmyadmin would he need to create another mysql user with full root privileges

In my (incomplete) notes I have changed that section to the following, it explains the above and it also uses the new “sudo” method to create the emoncms table and emoncms user without invoking the mariadb command line.

Setup the Mariadb server (MYSQL)

Firstly we should secure the database server, and then create a database and database user for emoncms to use;

sudo mysql_secure_installation

This command starts the database wizard, which guides you to through some security options. Setting the root password is not required as mariadb’s default configuration in Stretch gives sudoers root privileges. Therefore at the first prompt we “enter for none” and then “n” to changing the root password, it is advised to answer “Y” to all the remaining questions.

Create the emoncms database using utf8 character decoding:

sudo mysql -e "CREATE DATABASE emoncms DEFAULT CHARACTER SET utf8;"

Add an emoncms database user and set that user’s permissions. In the command below, we’re creating the database ‘user’ named ‘emoncms’, and you should create a new secure password of your choice for that user. Make a note of both the database ‘username’ (‘emoncms’) & the ‘new_secure_password’. They will be inserted into the settings.php file in a later step:

sudo mysql -e "CREATE USER 'emoncms'@'localhost' IDENTIFIED BY 'new_secure_password'; GRANT ALL ON emoncms.* TO 'emoncms'@'localhost'; flush privileges;"
1 Like

…but isn’t the point of setting the root password to protect the system from unauthorized access?
If the root password is not set, it surely means leaving the system open to being hacked, because others could gain access without using any authorisation, in just the same way as we initially created the emoncms user.
Having a root password stops that from happening.
@Greebo the root password is saved, but you need to prefix your MYSQL login with sudo, to authorize access.

Paul

No, I don’t think that is correct.

By answering “Yes” to all of the subsequent questions, you are preventing root access to the DB from anywhere else except the localhost, so you have to be logged into your Pi as someone who has sudo access to get root access to the DB.

That same logon is going to be able to reset your MariaDB root password anyway, so it is no less secure.

I disagree. if you run sudo mysql without the -u root -p, it works exactly as if you had used the -u root -p
Also, if you run sudo mysql -u root -p and then put in any old rubbish for the password, you get logged in too!

1 Like

Anyone that can log in as a sudoer can change the mysql root password so there is no weakness added by automatically granting a sudoer access and by not having a root password to breach it reduces the chance of a non-sudoer getting in by finding out said password. It also reduces the chance of losing your mysql root password.

If using rsa key to login via ssh (with root login and password login are disabled in sshd) this is a very secure method. Especially if you go on to install phpmyadmin and expose said root password to possible brute force attacks.

1 Like

Another welcome advance this method brings is when automating mysql stuff from within a script, you no longer need to hardcode and potentially expose your password to perform mysql commands. Just run the script as root or using sudo.

For a good on-topic example a emoncms installer has always been awkward to write due to the fact everyone should be using a different mysql root password and the best case scenario was the user had to edit the script to add their password (a security breach) after manually setting one (undermines using an installer) or mysql had to be installed with a blank password (even bigger breach), but not any more, we could now easily wrap these steps up into a simple script that worked out of the box for everyone.

The guide could be reduced to just “download and unzip emoncms.zip, then run stretch_installer.sh” or something very similar.

job done! ready to log-in. No mysql root password to remember/lose and an auto generated secure password used for the emoncms user and added to the settings.php by the script.

Although it has always been possible to automate the emoncms install it was a bit messy due to the mysql root password.

When installing phpmyadmin, we can just advise users to log in as the emoncms user by using the password retrieved from the settings.php file.

Plus! this means that emonPi and emonSD could now ship without a common mysql root password (huge breach) as most users will change their “pi” user password but few (if any) will change the mysql root password so any WAN accesible emonPi/emonSD’s with phpmyadmin installed are not easily accessed with just the url or IP address.

The more I think about it the more I like this new method… which makes me quite suspicious of a downside, but so far, so good.

1 Like

Yes, you’re correct, although it wasn’t the behavior that I had expected!!

I’ve just been wading through the Mariadb Documentation (again!) and it’s not clear to me what advantage setting a ‘root’ password brings, or what disadvantages there are with not setting one.
-otherwise in the ‘secure installation’ script why does it state;

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Is it to be taken in context of this option;

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network   
Disallow root login remotely? [Y/n] y

and, if we had selected ‘No’ (which we haven’t), would we then be relying upon the Mariadb root password to protect the database? - otherwise setting a root password is superfluous…

@pb66 - Paul, yes agree with you about phpmyadmin, I’m currently using the emoncms ‘user’ for phpmyadmin - and works fine.
Any thoughts on my comments above please.

Paul

probably because until now the default behavior was to rely on a root password and the secure installation script is a generic unix utility to automate specific general mysql security tasks such as setting a root password and blocking external access etc.

Pretty much, yes. If you were running an air-gapped PC which no one else had access to, there would be little point in setting a password, hence it’s optional. As soon as there is a chance of other users using the PC or it has ports exposed to the outside world, some sort of security is needed, the most basic level is a simple password.

With the adoption of mariadb in Raspbian Stretch it seems the default is now for root to have the unix_socket authentication plugin enabled. This apparently over rules using a root password and allows unix to manage security instaed, which may mean a simple password is still relied upon or it might mean a step up with an rsa key pair etc.

As i posted previously, the alternatively is to disable the unix_socket authentication plugin for the root user before running the secure install script and behavior would as we are used to, but no more secure and less friendly to use too.

I’m guessing the secure installation script is just too basic to be aware and accommodate the unix_socket authentication plugin being enabled. We could in fact avoid using it altogether to avoid confusion and just issue a

sudo mysql -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1'); DELETE FROM mysql.user WHERE User=''; DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%'; FLUSH PRIVILEGES;"

command instead, this performs the other 3 settings non-interactively, blocking anonymous users, allows root login from localhost only and deletes the test db’s.

I had a look through the script and to set the root password it just updates the user table with the new password, then saves it to a file so it can execute subsequent mysql cmds with it until the script terminates. It doesn’t check any plugins.
So @Paul is correct that it does update the password, its just that it is never even checked when the plugin is active, running as (unix)root or not.

The script also does one extra thing not included in @pb66’s post above (drops the database) so the full command to emulate the script would be:
sudo mysql -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1'); DELETE FROM mysql.user WHERE User=''; DROP DATABASE IF EXISTS test; DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%'; FLUSH PRIVILEGES;"

Does the missing DROP DATABASE matter? I have no idea, but if we’re going to replace it we may as well do everything it did :slight_smile:

Another benefit of using non-interactive options like this is for automation of an install. It’s getting pretty close now to all going into a single script instead of a set of instructions.

Any volunteer?
It would be great if the script also prompted the user for a ‘user’ password, which would then go on to setting up the emoncms db user too.
This would then simplify the ‘Setup the Mariadb server (MYSQL)’ section to just one sentence.

Paul

Thanks for adding that, I assumed it would need tinkering with as I just quickly drew this together from several sources that all instructed the same common tasks (albeit all with slightly different commands structures).

No idea! But I agree, if the unix recommended secure installation script does it, we should try to follow that until we know differently (eg the root password), bear in mind all this is all new to this install procedure, the secure install script was not used in the previous guides and the steps were not manually done either. So some testing and tinkering may be required.

I have been making notes/changes as I work through the guide in it’s current form. My intention was to make a script, predominantly for my own use but I’m happy to share it or contribute to a team effort.

There are 1 or 2 thing’s I do differently that may not be agreeable to some. For example I do not edit /etc/apache2/apache2.conf, I create a emoncms.conf virtualhost file in /etc/apache2/sites-available and enable the site using the correct a2ensite command and disabling the 000-default.conf with a2dissite

I also put the feed folders in a “emoncms” sub-folder rather than directly into /var/lib.

Regarding the emoncms password, I prefer a non-interactive installer and in the past I have just had the script generate a strong random password which is used to set up the emoncms user and then written into the settings.php.

For my purpose the install script should have one optional command-line argument, name, if left blank then the instance will be called “emoncms” and if provided eg “emoncms1” the emoncms table, user, virtual hast, feeds folder etc would all be created using “emoncms1” rather than the default “emoncms”.

I am also considering a matching uninstaller so test setups can be removed as easily as they are added although this will be interactive for a confirmation before removal. but I’m guessing there will be less interest in that.

That would be possible, although I would recommend doing it in a couple of steps (or more) so that it can be explained, sorting the security, adding a db and setting up a user in one chunk of code with no comments or documentation isn’t a good idea for future dev or debugging.

1 Like

Thanks for the comments @pb66 & @Greebo, I’ve now updated the build guide in my repo if you could take a look please.
I’ve used it to build a system this evening, which worked fine.

Paul - Very much agree about keeping the emoncms feed folders together in a sub-folder of their own as it compartmentalizes the feeds, makes backups easier, and could also be created automatically by adding the (almost) empty folders to the emoncms repo, to cut down the installation process further.
It would be good to discuss with G&T making this change global, but not sure how backwards-compatibility could be managed.

As for managing apache via a virtualhost file, yes, gets my vote.

Many aspects of the emoncms build guide have been copied & pasted forward over the years, and maybe changes such as these need to be integrated as part of continual development.

Paul

Well! Funny you should say that…

Over on the “Local emoncms processes and feeds lost after change to a process - Help please” I just discovered that the feeds folders on an emonPi are no longer symlinked and despite the default.emonpi.settings.php file stating

// Engines working folder. Default is /var/lib/phpfiwa,phpfina,phptimeseries

the feeds are then defined as

in the default.emonpi.settings.php so some consistency is indeed desirable.

At least if emonpi installs are pointing directly at the /home/pi/data folder we should be able to easily add a /var/lib/emoncms folder with 3 symlinks in it to gain some consistency so that all feed folders can be found in /var/lib/emoncms on all (unix) installs without changing any thing structural in emonpi, as long as the emoncms folder and symlinks get added at the same time as the settings.php gets updated all should be fine.

Yeah, I agree. My notes (thus far) leave very little unchanged, which is why I have reservations about how that might be received, so I will just share what I’ve done and let you and the others pick and choose what you might or might not want to use.

1 Like

I haven’t made much progress on this (some! but not a lot), however I am in the process of setting up a new emoncms server and I am going to try Debian9 Stretch, although it’s not a Pi the process should be pretty much the same with only a couple of minor differences.

I have started a thread on migrating an existing mysql db into a freshly installed image that has mariadb installed rather than mysql, (Restoring mySQL database to mariadb) I currently believe it should just drop in as long as “mysql_upgrade” is used afterwards, I will update there with any findings on that, however…

Whilst reading up on this I found there seems to be a Debian mariadb-client package and we appear to be still using the mysql-client in this “Raspian Stretch” emoncms install guide and thought I would just make a note here for consideration.

I do not think this is a problem par se as apparently “The mysql-client package also works with MariaDB server.” plus you guys have it working and haven’t suggested there are any issues.

However. The Debian recommended route to getting the right mysql database is to install via the default-mysql-server and default-mysql-client package names and for Stretch it would default to installing mariadb-server and mariadb-client (see Chapter 2. What's new in Debian 11)

So should we be using the default-mysql-server and default-mysql-client packages and let the installer do it’s thing OR should we dictate using the mariadb-client with the mariadb-server for a fully mariadb install OR are we deliberately hanging on to the mysql-client for a good reason?

When I read this statement it suggests (but doesn’t definitively say) when a system gets upgraded to Stretch it would replace mysql-client with mariadb-client, so could we end up with some users using mysql-client and others using mariadb-client depending on whether they fresh installed or upgraded? and/or would that matter?