Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

MariaDB - Unix socket authentication over SSH?

Nisto's profile image Nisto posted 5 months ago in General Permalink

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;
  1. Created a database:
  • CREATE DATABASE db;
  1. Granted user access to database:
  • GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA unix_socket;
  1. Flushed privileges:
  • FLUSH PRIVILEGES;
  1. 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?

ansgar's profile image ansgar posted 5 months ago Permalink

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

Description

Nisto's profile image Nisto posted 5 months ago Permalink

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

ansgar's profile image ansgar posted 5 months ago Permalink

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

Nisto's profile image Nisto posted 5 months ago Permalink

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?

ansgar's profile image ansgar posted 5 months ago Permalink

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.

Nisto's profile image Nisto posted 5 months ago Permalink

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"

ansgar's profile image ansgar posted 5 months ago Permalink

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.
Nisto's profile image Nisto posted 5 months ago Permalink

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?

ansgar's profile image ansgar posted 5 months ago Permalink

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

Description

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

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.