[#MCOL-3722] Connect to unix socket file as mysql@localhost user account by default for Columnstore cross-engine joins

Currently, for Columnstore's cross-engine joins, the default behavior is that it connects to the server using TCP/IP as the root@127.0.0.1 user account (aka root@localhost if skip_name_resolve is not configured) without a password:

	<CrossEngineSupport>

		<Host>127.0.0.1</Host>

		<Port>3306</Port>

		<User>root</User>

		<Password></Password>

		<TLSCA></TLSCA>

		<TLSClientCert></TLSClientCert>

		<TLSClientKey></TLSClientKey>

	</CrossEngineSupport>

https://github.com/mariadb-corporation/mariadb-columnstore-engine/blob/columnstore-1.4.2-2/oam/etc/Columnstore.xml#L529

This does not work in MariaDB Enterprise Server 10.4, because the root@localhost user does not allow password-less authentication by default anymore:

MariaDB [(none)]> SHOW GRANTS FOR 'root'@'localhost';

+-----------------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@localhost                                                                                                               |

+-----------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |

| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |

+-----------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.000 sec)

One potential solution would be to make the following changes:

Connect to the server's unix socket file instead of connecting to 127.0.0.1 over TCP/IP.
Connect as the mysql user account instead of the root user account.

This should allow passwordless access for cross-engine joins, because MariaDB Enterprise Server 10.4 creates a mysql@localhost user account that allows unix socket authentication:

MariaDB [(none)]> SHOW GRANTS FOR 'mysql'@'localhost';

+------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for mysql@localhost                                                                                                               |

+------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |

+------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.000 sec)

And of course, the mysqld process runs as the mysql OS user by default:

$ sudo ps -o user= -p $(pidof mysqld)

mysql

So if Columnstore's threads also run as the mysql OS user, then those threads should be able to use unix socket authentication as the mysql@localhost user account without any problems.

[MCOL-3722] Connect to unix socket file as mysql@localhost user account by default for Columnstore cross-engine joins Created: 2020-01-10 Updated: 2020-03-11 Resolved: 2020-03-11
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	ExeMgr
Affects Version/s:	1.4.2
Fix Version/s:	Icebox

[MCOL-3722] Connect to unix socket file as mysql@localhost user account by default for Columnstore cross-engine joins Created: 2020-01-10 Updated: 2020-03-11 Resolved: 2020-03-11