2020/10/27/db

From Woozle Writes Code
< 2020/10/27
Revision as of 20:39, 27 October 2020 by Woozle (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Main Problem

I decided to try replcing MySQL with MariaDB, in the hope that a fresh install would fix the problem (as well as wanting to switch to MariaDB generally, and this looking like a prime opportunity). It didn't... but I decided it was worth a bit of time to try to get it working anyway.

One symptom: MySQL on cloud5 (which works properly) will only let me connect via CLI if I have a username and password. MariaDB on cloud1 (not working for ssh tunnel connections) will let me connect from CLI with or without a username and with or without a password.[1]

After looking extensively at configuration files, one relevant difference seems to be the GRANTS (SHOW GRANTS FOR 'root'@'localhost';):

cloud1 (kind of not working)

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)

cloud5 (working properly)
Grants for root@localhost
GRANT
  SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
  PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,
  CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
  REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,
  CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE
ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                               

GRANT  
  APPLICATION_PASSWORD_ADMIN, 
  AUDIT_ADMIN,BACKUP_ADMIN,
  BINLOG_ADMIN,
  BINLOG_ENCRYPTION_ADMIN,
  CLONE_ADMIN,
  CONNECTION_ADMIN,
  ENCRYPTION_KEY_ADMIN,
  GROUP_REPLICATION_ADMIN,
  INNODB_REDO_LOG_ARCHIVE,
  INNODB_REDO_LOG_ENABLE,
  PERSIST_RO_VARIABLES_ADMIN,
  REPLICATION_APPLIER,
  REPLICATION_SLAVE_ADMIN,
  RESOURCE_GROUP_ADMIN,
  RESOURCE_GROUP_USER,
  ROLE_ADMIN,
  SERVICE_CONNECTION_ADMIN,
  SESSION_VARIABLES_ADMIN,
  SET_USER_ID,
  SHOW_ROUTINE,
  SYSTEM_USER,
  SYSTEM_VARIABLES_ADMIN,
  TABLE_ENCRYPTION_ADMIN,
  XA_RECOVER_ADMIN
ON *.* TO `root`@`localhost` WITH GRANT OPTION

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

3 rows in set (0.00 sec)

Trying to apply the cloud5 settings to cloud1, I get this for the first GRANT query:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION' at line 1

Sooo... let's take a closer look at what the cloud1 setup does.

GRANT ALL PRIVILEGES ON *.* this user is God
TO `root`@`localhost` only if connected locally
IDENTIFIED VIA mysql_native_passwordmysql_native_password plugin
USING 'invalid' OR unix_socket "invalid" is maybe like saying FALSE -- it won't ever work? ...leaving unix_socket as the only viable option
WITH GRANT OPTION allow user to create other users with same privileges

Let's try

GRANT ALL PRIVILEGES ON *.*
  TO `root`@`localhost`
  IDENTIFIED VIA ed25519
  USING PASSWORD('password_here') OR unix_socket
  WITH GRANT OPTION

That gives me

ERROR 1524 (HY000): Plugin 'ed25519' is not loaded
INSTALL SONAME 'auth_ed25519';
Query OK, 0 rows affected (0.002 sec)

Trying the GRANT again...

Query OK, 0 rows affected (0.001 sec)

Now when I try to connect with Workbench, I get a much quicker error about not being able to load the ed25519 plugin. Okay, so we need to make that automatic...

Unfortunately, the client_ed25519 plugin seems to be separate from auth_ed25519? this page suggested a package to install in order to get the client library, and I did, but it still didn't work. This post suggests that the problem has been fixed, but... kinda no.

For now, then, let's try the default plugin, if there is one.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '[password]' WITH GRANT OPTION;

This finally worked.

Creating Users

Apparently MariaDB is not happy with Workbench's account-creation syntax. Fortunately it's easy enough to do this through the SQL interface:

CREATE USER 'vbz-rc-bot'@localhost IDENTIFIED BY '[password]';

Footnote

  1. Update: this page seems to explain this behavior: "The root@localhost user created by mysql_install_db is created with the ability to use two authentication plugins. First, it is configured to try to use the unix_socket authentication plugin. This allows the the root@localhost user to login without a password via the local Unix socket file defined by the socket system variable, as long as the login is attempted from a process owned by the operating system root user account. Second, if authentication fails with the unix_socket authentication plugin, then it is configured to try to use the mysql_native_password authentication plugin. However, an invalid password is initially set, so in order to authenticate this way, a password must be set with SET PASSWORD."