MariaDB - Unix socket authentication over SSH?

I've been desperately trying to get authentication via unix sockets to work over SSH. I have been testing FreeBSD and OpenBSD the past couple of days and did finally manage to connect to a FreeBSD VM by supplying the socket path in HeidiSQL's Hostname / IP field (which I'm not even sure if that's intended to work?). However, trying the same with an OpenBSD VM, it seems I've now hit a dead end.

These are the steps I've followed:

1. Installed mariadb
2. Ran mysql_install_db
3. Ran mysql_secure_installation (enabled unix_socket authentication upon prompt)
4. Created a "regular" user:

• CREATE USER username@hostname IDENTIFIED VIA unix_socket;

5. Created a database:

• CREATE DATABASE db;

6. Granted user access to database:

• GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA unix_socket;

7. Flushed privileges:

• FLUSH PRIVILEGES;

8. Restarted mysqld

I am able to access the OpenBSD machine via PuTTy (both Plink.exe and PuTTy.exe) and open a MySQL client prompt without problems. But for some reason HeidiSQL just doesn't seem to be able to! I've checked firewalls, I've tried changing port numbers, I've tried all of the different library DLLs, even re-installing MariaDB -- nothing seems to work! The only thing that currently does seem to work is using password authentication.

Here are the errors I get for my respective input:

• Hostname/IP = 127.0.0.1 OR localhost:
  • Access denied for user '<username>'@'localhost'
• Hostname/IP = /var/run/mysql/mysql.sock:
  • Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 0

That second error message is also returned for basically any "incorrect" hostname/ip value. So my guess is the server-end doesn't even recognize what HeidiSQL sends over as a socket path (yes, the path is correct).

Any ideas?

Did you try out the named pipe option in HeidiSQL? That's meant for sockets, only without an SSH tunnel in the back.

@ansgar how would I connect to, say, /var/run/mysql/mysql.sock on another machine using the named pipe option? I currently don't see how without SSH, but maybe I'm missing something?

No clue. I just can tell you the value of "Socket name" gets passed as unix_socket argument to mysql_real_connect. Probably the documentation is more helpful: https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-connect.html

Been experimenting some more with the "SSH tunnel" option. I could be wrong, but the problem seems to be that HeidiSQL (or maybe something inbetween?) translates localhost to 127.0.0.1.

To make sure, I've tried enabling skip-name-resolve on the MySQL server, but HeidiSQL still shows "access denied" for 127.0.0.1, as opposed to localhost, which I enter into the host field.

For a socket connection, the host must not be a direct IP address it seems. I have tried tweaking my.cnf with a bunch of different directives and calling mysql explicitly with --protocol socket on a shell, but it simply never works with 127.0.0.1 specified as host where user must be identified via unix_socket exclusively.

Maybe there's a simple fix that can be made?

In case the error says something like "access denied for username@127.0.0.1" - that has nothing to do with what you enter as the hostname. It's the client IP address.

Sorry for assuming the worst. This is all so confusing, ugh! It looks like I'll have to keep digging on possibly configuring OpenBSD/MySQL.

Hate to bother again, but do you know what the equivalent parameter for HeidiSQL's "hostname" parameter would be in the official mysql client? I'm asking because, as mentioned before, specifying a socket path in HeidiSQL as hostname actually does work to connect to the FreeBSD MySQL server. But I haven't been able to reproduce this behavior on a shell. I've tested running mysql --host /path/to/socket, but that only returns the following error: "ERROR 2004 (HY000): Can't create TCP/IP socket"

Probably something you need:

--protocol=name     The protocol to use for connection (tcp, socket, pipe, memory).
-S, --socket=name   The socket file to use for connection.

These do indeed work, but I was wondering if there was maybe a parameter where hostname and socket-path would both be valid arguments. Since HeidiSQL offers no direct way of supplying protocol or socket parameters, I'm curious how specifying a socket as host can work when connecting to FreeBSD. Does HeidiSQL change behavior if the value entered into the hostname looks like a socket path or something?

No, HeidiSQL uses the socket name field if you select named pipe as network type:

Yes, the field is just renamed from host to socket name when you change the network type.

@Nisto: did you solve this somehow?

@Nisto: did you solve this somehow?

Kind of? Although the solution is not as elegant as I'd like it to be, especially as it's not all handled directly within the HeidiSQL interface. Basically, what I've resorted to is opening a tunnel to the MariaDB unix socket separately using OpenSSH, since I've found that PuTTY, which HeidiSQL uses, does not seem to have support for forwarding sockets over tunnels. The OpenSSH command looks like this:

ssh.exe -nNT -L localhost:2222:/var/run/mysqld/mysqld.sock username@123.123.123.123

This basically says "forward /var/run/mysqld/mysqld.sock on 123.123.123.123 to port 2222 on localhost". In HeidiSQL, I then connect to the database using network type MariaDB or MySQL (TCP/IP), hostname localhost and port 2222.

Still quite stumped as to why FreeBSD doesn't require jumping through the hoops of setting up a tunnel like this though. I've also tested NetBSD, Debian, Ubuntu and Alpine, and just like with OpenBSD, they all require explicitly setting up a tunnel. I know FreeBSD networking is kinda known for being rather complex/flexible, so who knows, but hopefully it's not due to security holes somewhere or something. Anyway, probably too off-topic for this forum!

Hope that helps.

Hope that helps.

Sure it does, thanks a lot!