I’ve got what feels like a simple problem with a simple solution, but I always get so turned around with SSH and RSA security keys that I don’t know how to proceed and could really use a step by step guide if one exists.
I’m trying to get started with self-hosting some services that require a MariaDB instance. I’m virtualizing the MariaDB database via an LXC container in Proxmox, so for simplicity’s sake I just want to use one database server and have the various services that need a database share it.
So, as far as the database server is concerned, all of these services would be accessing it via remote hosts.
I know that MariaDB best practice is to restrict connections to localhost only. The only way I know to get this working smoothly with remote client systems is via SSH port forwarding with auto-connection on system boot via ssh keys, but I am really struggling to get this working. The whole process is a bit too byzantine and SSH’s spectacularly vague error messages aren’t giving me anything to work with.
I’ve managed to generate keys on the MariaDB server and use those keys to manually access the server from a remote computer, so I know that works, but automating everything has been a problem.
So I suppose I have two questions:
Is there a really good guide to getting this working?
In the alternative, since I’m just a home user who wants to self-host some things, is it really so horrible to let MariaDB accept incoming connections on the network? I’m still planning to give each separate application/service that needs database access its own separate username and password on the database server, and the DB server itself will never be exposed directly to the internet (though things like a Ghost blog server that use the DB on the back end might be).
ETA: I’ve got the MariaDB server on a separate VLAN and should be able to block external IPs once I figure out how to do firewall rules in OPNSense.
I don’t have a lot of time to tinker with this stuff outside work hours, and messing with this particular problem is just no fun at all.
I would start here, this recommendation is off. Either something was lost in translation or original recommendation is plain strange.
There is absolutely nothing wrong, security wise, with opening mariadb to remote access., as long you basic security precautions.
Having your client and server communicate over LAN is after all standard practice. Stranger yet, its the primary use case of having db server, so that recommendation is real headscratcher.
your server and your clients are on same LAN
db access config is fully granular over combination of user : db : access subnet, securing access is trivial compared to you ssh proxy approach
Basics:
enable SSL/TLS connection security (manual ref. here
enable remote access to mariadb server (manual ref. here)
then start granting users right to connect from specific hosts or subnets only as needed.
mysql and MariaDB, for years, have pushed disallowing remote access out of the box. My default install only provides localhost access, as described in the article you linked, and there are years of cruft when you google re: “don’t allow remote access, use SSH tunneling instead because the security is superior.”
That, and I’m new enough to modern MariaDB that I didn’t know enough to do a good job of searching for what I needed.
I think I must have just … internalized … that at some point, years ago. I had completely missed that SSL/TLS based authentication was an option.
I’ve used auto-generated certs through a reverse proxy manager, though I don’t have one set up now. I’ll have to see what I need to get that set up. I might as well go ahead and do it since I’m planning to self-host some publicly accessible stuff later.
I read through MariaDB’s guide for deploying TLS, and that part that’s over my head right now is how to get a valid certificate for something that’s meant to only be on my local network.
I know there must be a reasonably painless way to do this. I even think I’ve seen a tutorial online (as part of deploying something else). I just can’t remember how I found it.
just … don’t use 2048bit rsa … use something more modern like ecdsa
… and don’t forget to do the “alter user” commands … or … as a super user check the mysql.user table contents, and see if all the users are there.
(don’t worry if you accidentally remove all users, you can restart mysqld with network and authentication off, and create your user again if you need to, … it’s in the manual).
I have a bit of trouble with their wiki, actually. It’s very dense, which is fine, but I’ve gotten a bit turned around and confused more than once because some of it reads like it was lifted from old MySQL docs (and then I realized this is because there’s still some remnant mysql stuff in the server), and other parts of it are just outdated.
(At one point, a piece of the documentation told me to find the my.cnf file to adjust my configuration, which does not exist at all on the version in the Debian repos.)
Is there any downside to self-signing my TLS certificate? I thought about trying to get a third-party signed one, but all the tools I know how to use to do that require a database be set up and running, and on further thought I decided I wanted my database’s certificate to exist independent of whatever reverse proxy/other tool I was experimenting with for in-network certificates for my other servers.
I’ll use ecdsa for this. Thanks!
(I’ve used 4096-bit RSA2 for SSH keys in the past. Is that catastrophically bad? I’m admittedly guessing a bit at what I should be doing as a home enthusiast. So many docs are oriented at large corporate use.)
EDIT: The MariaDB guide creates a 2048-bit RSA key.
The process to create an ECSDA key appears to be significantly different and more complex, and I’m not exactly sure how. to do it from reading the docs. Is there a tutorial you would recommend? I understand what I need to do; the ECDSA commands are just different enough I’m not sure how to do it.
There’s “too many cooks in the kitchen” with openssl, … which is great for security due to lots of eyeballs, but it sucks for usability as you end up with a gazillion subtly different ideas about APIs and command-line interfaces all around the openssl library/cli.
… and there’s lazy developers who hate updating their software to work with new openssl, and what the client supports might be something ancient.
RSA-4096 is considered good for cert signing, just not as efficient…so it’s ok, rsa-2048 is probably ok too, but big tech rarely uses it.
With ECDSA certs, there are genpkey and ecparam subcommands (instead of genrsa) that can get you a private/public keypair for e.g. secp384r1 or a secp521r1 curve.
Use req subcommand to make a cert out of a key, and use x509 to sign it (google some combination of these, or look at other people’s shell scripts on github for examples).
… at the end of the day the cert is just one part of the whole “crypto suite”, and the cert itself is a datastructure that’s serialized and hashed using SHA-something.
Ed25519 self-signed certificate generated, signed, and installed into MariaDB.
MariaDB reports TLS enabled, and registered my connection (using the mariadb command to interact with the server) as using TLS.
I ended up using a one-way certificate without server certificate verification (as that didn’t work and I don’t care to figure out why at this point).
I am unlikely to remember how I did it in the morning. I saved a couple of web pages and will hopefully be able to reproduce the process later and post it here for future searches when someone else is trying to do this.
That said, the hardest part of all this was the self-signed certificate generation, since I wasn’t using RSA and all the instructions use 2048-bit RSA.
I need the database up to get a reverse proxy that can hand out Let’s Encrypt certs up. Once that’s set up, I’m likely switching to those for MariaDB and never ever doing this again.
I’m interested in the answer to that, as well, but I think you’re correct.
I don’t know what it’s doing to verify the TLS certificate, but a CA I created with a couple of commands at my CLI, that only exists in a folder in my root user’s home directory, can’t have any actual authority. If it did, the whole system would be meaningless.
The one way connection with TLS on the server encrypts connections to my DB server, and that’s all I needed. I’m not trying to prove my identity.
I may have missed something in the thread, but there’s this ssl_ca setting, which lets you specify the root of trust for the client and/or the server. Whatever public certs you have installed on the system shouldn’t matter.
I’ve never ever used a public CA with MySQL or Maria, I’d have to think hard to think of a reason to do that.
Internal CA - yes, used a lot.
public CA - never.
self-signed - a few times my test scripts would generate a throwaway key pair and a would sign the CSR with the key used to generate it, and I’d just reuse them because it’s handy and it works.
This self signed, share the same key for client and server would actually work reasonably securely even across the internet - it’s way safer than speaking plaintext over the internet. But in practice, MySQL/MariaDB used to not have the most efficient TCP server implementation out there - but maybe it’s better these days.
One thing you may also want to consider with containers, you can set up a shared tmpfs volume, and you could have clients talk to your DB server over a local filesystem socket – not sure how to pull it off with proxmox, there’s probably a way.
So, after having to redo my MariaDB install because I wrecked something and none of my users had permission to create databases (now fixed), I tried to set this up again.
This time, openssl noticed what i was doing and refused to validate the certificate I created for MariaDB because both the CA self-signed cert I made and the mariadb self-signed cert I tried to make use the same CN (domain), which of course they do because they are on the same VM that has one hostname.
I get that this is sleight of hand, but it feels completely ridiculous as a way to enable encryption for MariaDB.
feels completely ridiculous as a way to enable encryption for MariaDB.
Very common pitfall with self signerd certs, and also correct behaviour on system side.
You have to either:
import the CA authority you used into system cert store, depends on system/distro (search phrase “adding custom CA”)
change client configuration to accept certificate without verifying the issuer ( howto differs per client)
However, as long you LAN is considered reasonably secure and not open to unknown actors, using session encryption is not really necessary.
Good security posture, but absolutely not necessary. Its unnecessary for selfhosting, better idea would be to use containered db engine that isolated by vxlans and communicate only with target service only. Docker compose style.
It also has nothing to do with DB encryption, that different, specialized and complex task to implement.
I strongly recommend being kicked to face by horse rather than implementing that. Its way more pleasant.
