Skip to content

mysql service role: root_password config is not honored for password login (auth_socket left active) #1

Closed

Description

@yakari007

Summary

The mysql service role (from GET /leo/iaas/service-roles) accepts a
root_password config value and installs/starts the MySQL server
correctly, but the configured password is never actually usable to log in
as root@localhost — the account is left on the auth_socket
authentication plugin, which ignores passwords entirely for local
connections.

Repro

  1. POST /leo/iaas/virtual-machines/create-wizard with:
    {
      "iaas_repository_image_id": "f5148186-5fd6-4ca9-af25-0e4861601d31",
      "service_roles": { "mysql": { "config": { "root_password": "<random test password>" } } }
    }
    (image: "Ubuntu 24.04 x64 (Ext4) (WS) (V1.1)", has_plusclouds_service: true;
    role id 5f0c9691-72ff-4710-8ad7-a2649b7a7dca from /leo/iaas/service-roles)
  2. Wait for the VM to reach running and the role to finish installing.
  3. SSH in and run:
    mysql -u root -p'<the same password>' -e 'SELECT 1'
    
  4. Observe:
    ERROR 1698 (28000): Access denied for user 'root'@'localhost'
    
    despite the service being installed and active (systemctl is-active mysql → active).

Likely root cause

Ubuntu/Debian's mysql-server package defaults root@localhost to the
auth_socket plugin, which authenticates via OS-user match over the unix
socket and ignores any password entirely. Whatever the role runs to apply
root_password (e.g. a legacy mysqladmin -u root password '<pw>'-style
call, or an UPDATE mysql.user SET authentication_string=... without
changing plugin) does not switch the account off auth_socket, so the
configured password is silently never honored for password-based logins.

Fix is likely to have the role explicitly run something equivalent to:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '<password>';
FLUSH PRIVILEGES;

(or mysql_native_password, depending on the target client compatibility
needed) instead of whatever it currently does.

Scope

This looks isolated to the mysql role's password-application step, not a
systemic problem with the service-roles framework — in the same test run,
the postgresql role's password config did work correctly (verified a
password-authenticated login as postgres), and the tailscale role
installed and enabled correctly too.

Found via leo4.tester's new iaas-service-roles automated test scenario.

Activity

  1. yakari007 commented on Aug 2, 2026

    @yakari007
    ContributorAuthor

    Fixed in 871ae2d (tagged v1.4.0): the role now runs ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '<password>' instead of a bare IDENTIFIED BY, which explicitly switches the account off auth_socket so the configured password is actually honored for local logins.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions