CODEWITHSUNDEEP
socketsmariadb
Mortred
Mortred

Reputation: 1

Cant Connect through Socket Access Denied on mariadb 10.6

When i check status mariadb socket with command : sudo systemctl status mariadb.socket then the result is :

 mariadb.socket - MariaDB 10.6.12 database server (socket activation)
   Loaded: loaded (/lib/systemd/system/mariadb.socket; enabled; vendor preset: enabled)
  Drop-In: /etc/systemd/system/mariadb.socket.d
           └─mariadb.socket.conf
   Active: failed (Result: resources)
     Docs: man:mariadbd(8)
           https://mariadb.com/kb/en/library/systemd/
           man:mariadbd(8)
           https://mariadb.com/kb/en/library/systemd/
   Listen: @mariadb (Stream)
           /run/mysqld/mysqld.sock (Stream)
           [::]:3306 (Stream)
           @mariadb (Stream)
           /run/mysqld/mysqld.sock (Stream)
           [::]:3306 (Stream)

systemd[1]: mariadb.socket: Socket service mariadb.service already active, refusing.
systemd[1]: Failed to listen on MariaDB 10.6.12 database server (socket activation).

and i have make a user to connect via socket with command :

GRANT ALL PRIVILEGES ON *.* TO `xuserbackup`@`localhost` IDENTIFIED VIA unix_socket WITH GRANT OPTION;

but when i connect through socket :

mysql -uxuserbackup --protocol=socket -S /run/mysqld/mysqld.sock

the result is alaways : ERROR 1698 (28000): Access denied for user 'xuserbackup'@'localhost'

Upvotes: 0

Views: 608

Answers (1)

Georg Richter
Georg Richter

Reputation: 7476

When specifying unix_socket authentication method, the user will be authenticated via system credentials.

For that the server retrieves the uid of the process which connected to the socket. With the uid server reads the name of the system user (in your case, the user which executed the command line client).

Once it has the system user name, it will authenticate the connecting user as the MariaDB account that has the same user name.

That means the user xuserbackup must be defined as a system user too and the user xuserbackup must execute the command line client.

Example:

georg@beethoven:~$ mariadb -ugeorg -e"select 'connected'\G"
*************************** 1. row ***************************
connected: connected

This will not work, since the root user is executing the command line client:

georg@beethoven:~$ sudo ~/mariadb/bin/mysql -ugeorg -e"select 'connected'\G"
ERROR 1698 (28000): Access denied for user 'georg'@'localhost'

Addendum: socket authentication only works if the hostname is localhost. If you don't specify a hostname localhost will be used by default. If you specify 127.0.0.1 the client will attempt to connect on port 3306 instead of using unix_socket.

Upvotes: 1

Related Questions