[BlueOnyx:24363] Re: In mysql, user at localhost and user at 127.0.0.1 are different.

[Tomohiro Hosaka]

Hi Michael,

Thank you for the reply.

### In 5208R and 5209R, vsite_xx at localhost and vsite_xx at 127.0.0.1 are different.

If you have registered vsite_xx at localhost
    OK: mysql -u vsite_xx --password=pass vsite_xx_db
    OK: mysql -u vsite_xx --password=pass -h localhost vsite_xx_db
    OK: mysql -u vsite_xx --password=pass -h 127.0.0.1 vsite_xx_db

If you have registered vsite_xx at 127.0.0.1
    NG: mysql -u vsite_xx --password=pass vsite_xx_db
    NG: mysql -u vsite_xx --password=pass -h localhost vsite_xx_db
    OK: mysql -u vsite_xx --password=pass -h 127.0.0.1 vsite_xx_db

If you have registered vsite_xx at localhost and user1 at 127.0.0.1
    OK: mysql -u vsite_xx --password=pass vsite_xx_db
    OK: mysql -u vsite_xx --password=pass -h localhost vsite_xx_db
    OK: mysql -u vsite_xx --password=pass -h 127.0.0.1 vsite_xx_db

Therefore, I think it should be distinguished.

### On the admin panel, even if the setting is localhost, the form
value is 127.0.0.1, which causes trouble.

The next scenario can be very natural and can be troublesome.

In previous versions, the value of MySQL.sql_host defaulted to localhost.
Many have MYSQL_Vsite host = localhost because this is used as the
default value for MYSQL_Vsite.host.

    # echo 'show grants for vsite_xx at localhost' | mysql -u root
--password=sql_rootpassword
    Grants for vsite_xx at localhost
    GRANT USAGE ON *.* TO `vsite_xx`@`localhost` IDENTIFIED BY
PASSWORD '*505ABB5935BC8433630EF3C2A1191FF8D63DD190'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
CREATE TEMPORARY TABLES ON `vsite_xx_db`.* TO `vsite_xx`@`localhost`
    # echo 'show grants for vsite_xx at 127.0.0.1' | mysql -u root
--password=sql_rootpassword
    ERROR 1141 (42000) at line 1: There is no such grant defined for
user 'vsite_xx' on host '127.0.0.1'

If MYSQL_Vsite.host is on localhost, then
You can either use easy-migrate.pl or the localhost is entered in the
form when MYSQL_Vsite is created (you will see 127.0.0.1 in the form
immediately afterwards, but it will actually be created as
user at localhost).

However, in the current administration screen, if the value of
MySQL.sql_host or MYSQL_Vsite.host is localhost, the
The form value is set to 127.0.0.1, so if we save the form with no
changes, the value will be overwritten to 127.0.0.1.
The fact that mysql's grant and mysql.user are not immediately changed
delays the discovery of the problem and complicates things.
This is a bug in the hook /usr/sausalito/conf/base/mysql/mysql.conf.

Reset to defaults and Grant all permissions in MariaDB Permissions
does not erase user at localhost, but adds user at 127.0.0.1

    # echo 'SELECT Host, User FROM mysql.user' | mysql -u root
--password=sql_rootpassword | ack vsite_xx
    localhost       vsite_xx
    127.0.0.1       vsite_xx

    # echo 'show grants for vsite_xx at localhost' | mysql -u root
--password=sql_rootpassword
    Grants for vsite_xx at localhost
    GRANT USAGE ON *.* TO `vsite_xx`@`localhost` IDENTIFIED BY
PASSWORD '*505ABB5935BC8433630EF3C2A1191FF8D63DD190'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
CREATE TEMPORARY TABLES ON `vsite_xx_db`.* TO `vsite_xx`@`localhost`
    # echo 'show grants for vsite_xx at 127.0.0.1' | mysql -u root
--password=sql_rootpassword
    Grants for vsite_xx at 127.0.0.1
    GRANT USAGE ON *.* TO `vsite_xx`@`127.0.0.1` IDENTIFIED BY
PASSWORD '*505ABB5935BC8433630EF3C2A1191FF8D63DD190'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
CREATE TEMPORARY TABLES ON `vsite_xx_db`.* TO `vsite_xx`@`127.0.0.1`

At this stage, the following code works because vsite_xx at localhost is
still there, which should have been erased
    OK: mysql -u vsite_xx --password=pass vsite_xx_db
    OK: mysql -u vsite_xx --password=pass -h localhost vsite_xx_db
    OK: mysql -u vsite_xx --password=pass -h 127.0.0.1 vsite_xx_db
    OK: perl -E 'use DBI; say
DBI->connect("DBI:mysql:database=vsite_xx_db",
"vsite_xx", "pass")'
    OK: perl -E 'use DBI; say
DBI->connect("DBI:mysql:database=vsite_xx_db;host=localhost",
"vsite_xx", "pass")'
    OK: perl -E 'use DBI; say
DBI->connect("DBI:mysql:database=vsite_xx_db;host=127.0.0.1",
"vsite_xx", "pass")'

But if the hook bug has been fixed, or if you have created a new
MYSQL_Vsite, you can do the following

    # echo 'SELECT Host, User FROM mysql.user' | mysql -u root
--password=sql_rootpassword | ack vsite_xx
    127.0.0.1       vsite_xx

In this case, you will get the following results
    NG: mysql -u vsite_xx --password=pass vsite_xx_db
    NG: mysql -u vsite_xx --password=pass -h localhost vsite_xx_db
    OK: mysql -u vsite_xx --password=pass -h 127.0.0.1 vsite_xx_db
    NG: perl -E 'use DBI; say
DBI->connect("DBI:mysql:database=vsite_xx_db",
"vsite_xx", "pass")'
    NG: perl -E 'use DBI; say
DBI->connect("DBI:mysql:database=vsite_xx_db;host=localhost",
"vsite_xx", "pass")'
    OK: perl -E 'use DBI; say
DBI->connect("DBI:mysql:database=vsite_xx_db;host=127.0.0.1",
"vsite_xx", "pass")'

That is, if the value of MySQL.sql_host or MYSQL_Vsite.host is localhost, then
If the form is set to 127.0.0.1 and changed (without the user's knowledge), the
Due to a bug in the hook, the double registration of
vsite_xx at localhost and user at 127.0.0.1 continues to work even if you
don't change your connection information, making it difficult to
notice the problem.

However, if you change your password or something like that, only
user at 127.0.0.1 is changed, at which point you'll notice the problem.

    # echo 'show grants for vsite_xx at localhost' | mysql -u root
--password=sql_rootpassword
    Grants for vsite_xx at localhost
    GRANT USAGE ON *.* TO `vsite_xx`@`localhost` IDENTIFIED BY
PASSWORD '*505ABB5935BC8433630EF3C2A1191FF8D63DD190'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
CREATE TEMPORARY TABLES ON `vsite_xx_db`.* TO `vsite_xx`@`localhost`
    # echo 'show grants for vsite_xx at 127.0.0.1' | mysql -u root
--password=sql_rootpassword
    Grants for vsite_xx at 127.0.0.1
    GRANT USAGE ON *.* TO `vsite_xx`@`127.0.0.1` IDENTIFIED BY
PASSWORD '*E5C8CE568DA436CFCC11ED6D71BEC93E9A10461B'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
CREATE TEMPORARY TABLES ON `vsite_xx_db`.* TO `vsite_xx`@`127.0.0.1`

### I'm having the following issue with my customer.

* Can't explain why blueonyx can't set localhost in MYSQL_Vsite.host
anymore. It feels like a worsening.
* I'm already connected from a lot of PHP code, WordPress, and Perl
code, and it's not cool to ask them to change their connection
information.
* Even if I were to change my connection host to 127.0.0.1, I would
not actively recommend it as unix domain sockets are better than
TCP/IP in a local environment.

I consider my requirements to be very general.

I think BlueOnyx should be improved so that it does not force changes
to the connection information in existing code.

I would like an answer.

Thanks,
Tomohiro Hosaka

2020年10月5日(月) 0:36 Michael Stauber <mstauber at blueonyx.it>:
>
> Hi Tomohiro,
>
> > In BlueOnyx's admin panel, I don't think 127.0.0.1 and localhost
> > should be treated the same as the hostname of mysql.
> >
> > The code that causes the problem
> >         if ($sql_host == "localhost") {
> >             $sql_host = "127.0.0.1";
> >         }
>
> Yeah, this was done deliberately and intentionally.
>
> Starting with CentOS 7 the included MariaDB started to treat localhost,
>  127.0.0.1 and ::1 differently - regardless what /etc/hosts said.
>
> Like anyone else we would prefer to use <user>@<localhost>, but that no
> longer worked in certain scenarios and also wrecked portability of MySQL
> users when moved between BlueOnyx versions.
>
> For that reason we've now hard-coded MySQL local credentials to
> <username>@<127.0.0.1> instead to avoid these problems.
>
> --
> With best regards
>
> Michael Stauber
> _______________________________________________
> Blueonyx mailing list
> Blueonyx at mail.blueonyx.it
> http://mail.blueonyx.it/mailman/listinfo/blueonyx