Thanks for the additional info. I have a suspicion what might be going on here.

Are you manually/explicitly setting a password for the MariaDB 'root' user?

TL;DR - if so, that's the issue! Please don't do that. Use the 'adminer" user (password should be set on firstboot) with Adminer/Webmin. Details/context below.

can't reconcile between users root, mysql, admin & adminer

I'm not sure, but I suspect that this may be the root cause of your issue (excuse the pun). These users have different contexts and are used in different scenarios, for different purposes. If I'm correct and understand the issue correctly, it's not the installing the security updates that is the issue, but the reboot.

Let me explain a bit more:

• 'root' is the name of both a Linux user and a mysql/maraidb user. Whilst they are named the same, they are separate users, used in specific contexts. I.e. logging into your Linux system or logging into maraidb respectively. Just to make it clear as mud, they are somewhat linked as I'll explain further down.
• 'mysql' is a Linux (system) user only. It's the limited user account that the mariadb service runs under. All the files that the mariadb service needs to write to need to be owned by the 'mysql' Linux user.
• 'admin' is the default Redmine user. It is only for logging into the Redmine UI via your web browser.
• 'adminer' is a "root-like" mariadb user - for logging into mariadb only. E.g. in Adminer or the Webmin mysql module.
• Just to confuse things some more, there is also a 'vcs' Linux user. That one is used to interact directly with the underlying version control system (git or svn).

Now circling back to the 'root' Linux and MariaDB users; once upon a time (in a release long, long ago), the 'root' MariaDB user had a password set. So the 'root' MariaDB user could be used for both CLI interaction and within Webmin and Adminer.

However, some time ago, a better security system for accessing MariaDB became the default for the 'root' MariaDB user. This "new" authentication system is called "socket authentication" (incidentally PostgreSQL has used it forever, but let's not go there). Socket auth requires both a Linux user and a MariaDB user of the same name and somewhat links them for the purposes of MariaDB access. Any user can use socket auth to get access, but on TurnKey only the 'root' user is configured by default (inherited from Debian).

I won't go into the nuts and bolts of "socket auth" works, but effectively it allows a logged in Linux user to access MariaDB without requiring a password. No other Linux users can access MariaDB by that user at all - unless running as that user (e.g. using sudo or su). Plus it also only works for local MariaDB access - e.g. via the CLI.

However, that security improvement could also be considered a downside of socket auth. Tools such as Webmin and Adminer (and any other third party access inc remote access except local CLI) can only use password auth. Enter the "root like" 'adminer' MariaDB user. That's a (MariaDB only) user that we create specifically to allow access via username/password (Webmin & Adminer).

One of the security disadvantages of username/password auth is that to start/stop the MariaDB service, a 'root-like' Linux/MariaDB system user pair is required. By requirement that means that a plain text password needs to be stored in a config file (in /etc). With socket auth, the MariaDB can be stopped/started by 'root', with the MariaDB service itself using the limited 'mysql' Linux user.

My suspicion is that there is something you are doing along the line that is breaking our assumptions. Likely setting a password for the 'root' MariaDB user? But perhaps something else? You could revert to the old way of things ('root' using user/pass auth), but it would also require the additional steps to (re)create a 'root-like' Linux/MariaDB user pair - with the password saved somewhere.

If that sounds right, please let me know. Although if I have time, I intend to double check whether I can recreate the issue when installing as ISO.

You nailed it! It's the 'adminer' account - or more to the point the lack of one! Gross oversight on my behalf! That's what happens when I almost never use Webmin - except when people have issues.

Sorry that I didn't see it sooner! Not paying enough attention when going through the steps...

FWIW it doesn't actually matter what it's called, and it doesn't make much sense to call it 'adminer' - because the Redmine appliance doesn't have the Adminer DB webUI (just the Webmin MySQL module).

So it seems you were thinking, creating a new "root like" user is the way to go - although to use it with the Webmin MySQL module, you will need to set a password. As per above, I'd probably call it 'admin' or similar. Assuming a username of 'admin', on the CLI, run the below command. Replace DB_PASS with the desired password:

mysql --execute "GRANT ALL PRIVILEGES ON *.* to admin@localhost identified by 'DB_PASS'; FLUSH PRIVILEGES;"

Then use that user account in Webmin - NOT the root account. I.e. DO NOT set a MariaDB root user password. Then you should be golden. You should be able to access MariaDB via the Webmin module. If/when you use the CLI you can use either 'admin' (with a password) or 'root' without. Please let me know if that's not the case.

It's been like that for ages (at least v16.0, perhaps v15.0)! TBH it blows my mind that no one has ever reported this before now. Or perhaps they have and they weren't persistent enough and I just didn't join the dots? I wonder how many people have launched our Redmine appliance (or one of the other ones with MariDB, the Webmin module but not Adminer), done the same thing and just thrown their arms in the air and gone elsewhere!? So whilst it's a pain, thank you so much from reporting and persisting! Now I know what is going on, I can look at addressing it!

Which brings me to the best way forward... And I'd love your feedback/input.

As I say, it doesn't make sense to call the "root like" MariaDB user 'adminer' on servers that don't have Adminer. We could just use 'admin' on apps (like Redmine) that don't have Adminer and leave it as 'adminer' on one's that do. But IMO it might make sense to rename the 'adminer' DB user to 'admin' too so it's consistent everywhere.

I guess another option is to install Adminer on Redmine (and others too), but it's a PHP app. As all PHP based apps include Adminer anyway and Redmine is a Ruby app, it's a whole lot of extra dependencies for limited value.

Beyond that, we probably need to consider how we handle the Webmin MySQL module. IIRC on all the LAMP based appliances, we're adding 'adminer' as the default user, and perhaps even a warning about usage of root. Obviously we need to address that in Redmine (and others). But I suspect that's not enough? Perhaps we need to block setting of the 'root' MariaDB user via the Webmin module? Perhaps even a link to a doc page that explain why?

I'd love your thoughts!

Thanks for taking the time to add your voice to this issue.

I've just written a response above. Hopefully that helps you work around the issue.

I'd also appreciate your thoughts on the way forward/

Also do you want a "proper" user account so you don't need to guest post and wait for me to approve your posts? Either set one up your self and let me know so I can approve it. Or I can create one using the email address in your previous post.

I meant to say a big thanks for this. I won't reply properly yet, but I do hope to circle back around soon. It's just that I'm really under the pump with a few things, have a huge backlog and also have a few non-TKL things I really need to take care of today.

I just wanted to quickly note that I really appreciate your input here. As an experienced Linux user and dev, with a strong preference for CLI and limited "dogfooding" of TKL servers I have huge blind spots! FWIW we do run a few TKL servers; this site runs on our Drupal appliance and we have a (private) Mattermost server for planning and private team coms. I also have a local personal Gitea server.

Anyway, I hope to circle back to this and reply properly ASAP.

It looks like when you first tried to add the user, MariaDB wasn't running, hence the failure you note first.

Did a new vm deploy, no updates installed/ no manual changes done. Besides the username or maybe related, there are some others errors see bold text:

Command used:  journalctl -xeu mariadb.service
[...]
InnoDB: Starting shutdown...
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[...]
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

Hmm, that is interesting - and unexpected! That's the same info report originally by Daniele - which I couldn't reproduce. And if that's before making any changes, that suggests perhaps something else/more than what I thought was going on.

I'll try again and explicitly check the journal. And I'll follow your lead and even if I can't exactly reproduce it, I'll try your other steps too.

Thanks for the extra info.

That is super interesting! I hadn't expected using root without a password to work!?

I'll do some more testing and see what I can work out...

I'll reconsider what steps I take to make the user experience better once I understand it all better.

Thanks for persisting with this and sorry it's still causing pain...

Re your latest experience, assuming I understand correctly, in Domhnall's last post he noted that he used 'root' without a password. To quote him (my bold):

[...] threw root in there with no password and it came right up and ran [...]

Although he did also note:

[...] I had tried that previously on the ISO version and it didn't work [...]

So I really don't understand what the hell is going on here and it's really frustrating...

I can only imagine what a PITA it is for you! My guess is that you're just trying to get some work done... So much for "turnkey"... :(

Regardless, thanks again for persevering and helping out.

Moving on to your post specifically (that I'm replying to):

[...] used root and the password, and can see inside the db.

[...]

i rebooted the server, and error 500 reappeared.

TBH, I think that's the bit causing the issue: I.e. setting a root password, without also recreating/adding an additional system user - with the password stored somewhere and the service adjusted to use that user/password. As I think I may have mentioned elsewhere above, AFAIK the service uses 'root' (mysql user) for starting/stopping, then drops to the 'mysql' user for all other functionality. I do recall working that out back when the 'root' user socket authentication first became the default. But given the experiences noted in this thread, I need to double check my understanding.

In the meantime, do you still have a snapshot of a working system? If so, could you try NOT setting a 'root' MariaDB user password as per what Domhall noted above and see how that goes? If MariaDB and Redmine continue to work after that, please double check by rebooting and/or restarting the MariaDB service.

If not that's cool.

I should also post how the 'root' user can be configured back to using socket authentication. If we can confirm that not setting a 'root' MariaDB "fixes" it, then that would be additional confirmation that my thoughts here are correct.

Rereading this thread, I'm still a bit concerned as some of your and Domhnall's other posts (in this thread) do suggest that perhaps there is something else/additional going on here too? Perhaps a race condition somewhere? *sigh* They're the bane of my existence as they are hard to recreate, thus really hard to fix... As a general rule, if I can recreate the issue myself, I can fix it - or at least devise a workaround.

Regardless, now you guys have provided more info, I definitely need to try to recreate this again. That will hopefully assist me to fully understand this issue better and both provide a work around for "fixing" it, plus look at how I can ensure that users such as you guys avoid hitting it in future.

I don't have VMware (which shouldn't matter), but other than that, I'll follow step by step and report back.

Thanks for your patience...

Yeah 80% of our apps include MariaDB. And it's explicitly the same version and same package direct from Debian. So it's super weird!

No one else has reported it on any of our other appliances and I couldn't find a bug report that matches on the Debian or upstream MariaDB bug trackers. There are a few issues that may be vaguely related but it doesn't exactly match our experience - or the log entries, so I suspect that they are unrelated.

So for some weird reason it seems limited to Redmine. But it doesn't make sense to me that it only affects the Redmine ISO and only happens on first reboot. The LXC build is built from the ISO, so I would expect that to also be affected. Super weird indeed!

Thanks for noting the typo. It's non-critical as it's just text (not code). But it's a quick and easy fix.

I see that you have another post that relates to the Webmin login issue, so I'll reply with thoughts there.

Also, re your website user account, I've just enabled it.

FYI literally hundreds of user accounts are created a day, most are clearly spammers or SEO farmers (just looking at the usernames). So I only enable accounts that have demonstrated that they're human - which clearly you've done. I did check for an account linked to your email initially, but as you didn't note that you'd created one (until now) I hadn't checked again... Anyway, it's done now.

Thanks for your reassurance. That makes me feel much more comfortable about this.

Although MariaDB is installed as a fresh install and as I noted above, this particular issue seems to only affect the Redmine appliance?! The other strange thing is that my "fix" script essentially does exactly what the initial build code should be doing?!

Anyway, I 100% completely agree with your suggestion of taking a daily backup!

We do encourage use of TKLBAM (our built in backup tool) linked to the TurnKey Hub (our SaaS product) for that. That's our main source of revenue and allows me to dedicate my time to TurnKey and pay a couple of other devs part time.

Although I am a strong supporter of "free software" in both senses. So while we hope people find our appliances useful enough to contribute in some way (which you've done here), there is zero obligation to use TKLBAM/Hub, and we don't cripple TurnKey appliances in any way. Your suggestion of a DB dump (i.e. using mysqldump) is completely valid.

I suspect that loosing access to Webmin login is because fail2ban is locking you out. I forget exactly what our default config is, but IIRC by default it will lock you out for 10 minutes after 3 log in failures within a minute.

I suspect that the initial Webmin login failures you're hitting are poor communication on my part!? Your suggestion of incorrect password escaping is possible. but I'm pretty sure the primary reason is that the Webmin login user remains 'root' (via PAM authentication). The 'mariabd_admin' user is a MariaDB user - intended to be used explicitly for the purposes of MariaDB access within Webmin.

Related to that, after finishing the "fix" script (which took me way longer than it should have...!) it did occur to me that perhaps I should have just been setting a random string myself - rather than asking the user? Once the Webmin config is prefilled (which I'm already doing in my script anyway) the user doesn't really need to know it.

As I'm sure I've noted above somewhere, default root CLI MariaDB access uses socket authentication - so the root Linux user is bound to the root MariaDB user and a password isn't required. So IMO using root when wanting CLI access is the easier and most logical (although I often suffer from the curse of knowledge). I did have a quick fiddle with the Webmin MySQL/MariaDB module using root and socket auth. But in my brief adventures, I couldn't get it to working. Hence the additional account. I suspect with a little more fiddling it's possible but I've got a massive backlog, so not for now...

So if I go generating a random password, I'm thinking perhaps a long base64 string? E.g. using openssl:

openssl rand -base64 40

Which produces random strings like this:

• vym35hnN4E1UPVo9rtk/8kUW9Q9HPDXyNWv19uN7Qd6/QNKh7aDHDA==
• 36ybbkk/wm3QUAFvMmZukwH28+cImHZn+DnMKz1VHa55q0KI64S06A==
• z93uy15He5NrKptRyOUrZp1Gzl9/wHBL+cmV8rWJVCrbRhv3Z1MoFw==
• Mcc8N23N7TEwbM/6Q9DiXElvBFyfyJFeNTpZT1MhQvmoWqksF6b/Rw==
• etc...

What do you think?

Got the Webmin to work. I was a bit confused because i was earlyer able to login with admin on webmin. And did not think of using root to login webmin. So All is working now!

Thanks for the info, although TBH, I'm not sure why you were able to log into Webmin as "admin"?! It should always be 'root' - with servers on AWS Marketplace being the exception - as that's a strict requirement of AWS.

Usually if i install a debian machine, i disallow root to be able to login remotely to the machine. Then i have to login with normal user account, then i sudo root myself. We did this default on all linux machines.

As you seem aware, under the hood TKL is Debian. So you can create a new sudo user if you want and disable root. Although I'm not sure how Webmin will cope? You may need some further config there there, but feel free to report any issues (or even success!) and I'll do what I can to assist. Or if you work it out, please share. It's probably a good thing to document for others that would prefer that.

Actually (t osupport AWS requirements), we do have a tool that creates a sudo user called 'admin' which does work with Webmin - although one of the things it does (which you may not like - but may be required for Webmin?) is it disables the sudo password - i.e. no password is required to run sudo. Running our built in tool it with no args will show the help:

turnkey-sudoadmin

Re context of root/sudo user: That is an ideological decision - one which quite a few technical server projects adopt.

Regardless, a sudo user on a Linux desktop is must! Only Linux Desktop users with a desire for pain and trouble run as root!

On a single user server IMO its more of a personal choice. Almost every command you will want to run will require root and it's security by obscurity at best and IMO just an extra layer of PITA (essentially "Simon says"). Unless there is at least some "real security" then security by obscurity can give a false sense of security. If you have the "real sec" covered, then there are things such as a sudo user that can raise the bar a bit.

There are security screws you can tighten to harden your server, some "real" and others additional security by obscurity. But with fail2ban and a good root password, I personally don't think it's necessary.

One other question, does turnkey have packages of software from the other appliances to install inside this one? Like for example installing media wiki next to redmine, so it runs on the same server.

You're no the first to ask that question! :) But strictly speaking no. Having said that, many of our users run TurnKey under LXC - e.g. as a Proxmox container. So whilst it's still a separate server, it uses very little additional resources. We have done Docker builds in the past - albeit via a hack that loads the full rootfs into a Docker container. So it is essentially the same scenario as LXC, but in Docker. IIRC the reason we stopped was issues with Docker, but AFAIK, that's been resolved upstream. I'd like to get back to doing Docker builds - even if via the hack I noted. But I've got a huge backlog and it's not a huge priority - we've only had a few requests to do them again since we stopped.

So if you don't want/have LXC, then OTTOMH the only way would be to manually install the secondary software yourself. Not very TurnKey... It should be possible to run the scripts we use to build all our appliances but it's not something we technically support and might have issues.

[...] Databases stay healthy.

Yay! :) Thanks for confirming.

Glad it's working out for you!

Sorry, it sounds like miscommunication by me. I'm pretty sure I've confused you with my choices and words within the script.

To clarify, you will still need to log into Webmin with 'root' - the same password as 'root' login via SSH. Webmin initial login authenticates via PAM - i.e. as a Linux user. The 'mariadb_admin' user is a MariaDB user (only), as opposed to a Linux user.

The purpose of the 'mariadb_admin' user is purely to give you full control of MariaDB in Webmin - via the MariaDB Webmin module. As it can be preconfigured (as my script does) you shouldn't need to enter the 'mariadb_admin' password anywhere. It should "just work".

Because you should never actually need to enter it manually, I think it'd be better to just generate a complex random password for the 'mariadb_admin' user, rather than asking the user to set it. My guess is that was the bit that created the confusion!?

Hopefully that clarifies thing for you? Can you please confirm that it works as I've descried?

Thanks again for all your feedback. I get that you may feel a bit "dumb" or whatever sometimes. But your feedback is incredibly useful! It helps me understand the sort of issues and confusion that "inexperienced" Linux users encounter. After all, people like yourself are our target market! As a seasoned Linux user, I often suffer from the "curse of knowledge". So it's easy to miss issues/problems that I miss because may be unintuitive to many.

FWIW, I'm trying to get a proper understanding of what causes the initial Redmine issue and fix it is a really high priority. I want to rebuild and publish a bugfixed update ASAP. I'm quite concerned that someone might launch it, put a ton of data in and not realize the issue until they reboot! That would be really bad!

Now that I have tested a bit more, I am happy with the updated Redmine appliance.

Webmin works as I expected - the 'mariadb_admin' user password is randomly (non-interactively) generated on firstboot and added to the Webmin config. So other than logging into Webmin (via 'root') no user interaction is required. The MariaDB Webmin module "just works"!

But more importantly MariaDB survives restarts and/or reboots, no problem. And so Redmine continues to work as it should! Yay :)

Regardless, please feel free to provide any feedback you have.

I've dissected the broken build, comparing before and after the issue becomes apparent. I've compared them with the new working build. Regardless, I'm still not sure why/how the DB issue occurred. All I needed to do to "fix" it was rebuild it. Along why the issue didn't affect any other apps that include MariaDB. I can only assume that there was some random corruption at build time...!?!

TBH, I don't like it when I don't understand why an issue has occurred and/or what it was that fixed it. Regardless, I'm not going to fight it.

Despite it ending up that there was little to do to fix the issue, I also tidied up a few other bits and pieces that I noticed while pulling the appliance apart .

For further detail, please see the code I changed via the pull requests on GitHub:

• https://github.com/turnkeylinux-apps/redmine/pull/29
• https://github.com/turnkeylinux/common/pull/303

Once the changes have been reviewed by a colleague, I'll merge them and build v18.1 of our Redmine appliance. It will be published early next week.

Thanks again to both you guys for all you've done here.

That sounds awesome. I'm pretty confident but more testing and feedback is always good! :)

Thanks mate! :)