gobuffalo/buffalo

Error: couldn't create database bcms_dev: error creating MySQL database bcms_dev: Error 1045: Access denied for user 'root'@'172.17.0.1' (using password: YES)

esobczak1970 opened this issue · 7 comments

esobczak1970 commented

Even with mariadb set and database.yml configured buffalo is trying to communicate with 172.17.0.1 even though db is localhost at 127.0.0.1
buffalo pop create -a -c database.yml
Error: couldn't create database bcms_prod: error creating MySQL database bcms_prod: Error 1045: Access denied for user 'root'@'172.17.0.1' (using password: YES)

or
buffalo dev
then visiting http://localhost:3000/
couldn't start a new transaction: could not create new transaction: Error 1045: Access denied for user 'root'@'172.17.0.1' (using password: YES)

sio4 commented

Could you please provide outputs of commands buffalo info from your app's root and env from your working environment? Also the content of database.yml? (Please erase password in it)

(The output of buffalo env is always mandatory)

sio4 commented

One quick respones:

The error message Error 1045: Access denied... might be generated by the mariadb which means the network connection to the mariadb (on the localhost if you configured correctly) from pop was successful but the server rejected the access on the authentication stage.

I think you need to check:

  1. Your configurations including auth parts
  2. If the server got the connection.
  3. Server's authentication configuration.

first.

Also, in this part: user 'root'@'172.17.0.1' , the address here is not the address of the server but the address of client which connected to the server so the user root AT 172.17.0.1.

esobczak1970 commented
sio4 commented

Oh, you are using docker! Then it make sense. So you just treat it as "the mariadb is on the localhost" but actually the dbms is not on your localhost but on the docker's network. Just SNAT hides the structure from client but the server still feels it.

Then the address part, 172.17.0.1, could be the IP of your host (maybe your laptop, desktop, or server) on the docker network. Please check your network using ifconfig or whatever. You may be able to see that address on the bridge.

esobczak1970 commented
sio4 commented

Hi @esobczak1970

It seems that buffalo/pop is trying to send username and host to authenticate versus just username that for example mysqlworkbench or a few other things allow so unless there is a way to get it to just authenticate with a username

No, saying again, this is not a thing pop does but is MySQL/MariaDB's behavior. MySQL does authenticate with 'username'@'hostname' form and the hostname part comes from the client's address. (see [1] for more information) In your case, since the connection was SNATed by the docker host for the docker-exposed port, the client address will be set as the host's address which is 172.17.0.1 by default and the MariaDB just think the connection is from this address. (This situation can vary by the runtime environment including but not limited to the bare metal, docker container, and various configurations of k8s env...)

Actually, MariaDB doesn't know if they are running in the container as the same as your application with pop does. pop also doesn't know the address to be NATed. pop is not able to send an authentication request with the NAT address since it doesn't care the network transport.

Again, the address 172.17.0.1 is not a "host address from the perspective of the client (pop)" but is the "client address from the perspective of MariaDB". Please read my previous comment too.

[1] https://dev.mysql.com/doc/refman/8.0/en/connection-access.html

From my laptop, I can see the following:

$ ip addr
<...>
4: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default 
    link/ether 02:42:c0:30:bf:67 brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
       valid_lft forever preferred_lft forever
    inet6 fe80::42:c0ff:fe30:bf67/64 scope link 
       valid_lft forever preferred_lft forever

As you can see, the default IPv4 address for the "docker host" (== my laptop) on the docker bridge is 172.17.0.1.

I may have to setup an account on the docker that matches the ip to be able to get it to authenticate right.

Yeah, you need to check your MariaDB's mysql.user table, mysql.db table, or some more to check what is your current configurations. For security reason, mostly user root is just allowed from the localhost so it could be better setup your application user such as 'app'@'%'. In MySQL, % here means (from) everywhere.

The following is my configuration for my test server:

mysql> select user,host,db from db;
+---------------+-----------+--------------------+
| user          | host      | db                 |
+---------------+-----------+--------------------+
| hxxxxxxxxx    | localhost | hc_%               |
| mysql.session | localhost | performance_schema |
| mysql.sys     | localhost | sys                |
+---------------+-----------+--------------------+
3 rows in set (0.01 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| hxxxxxxxxx       | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

As you can see, I just configured all clients from the localhost, but allowed databases with the same wildcard % so the user hxxxx@localhost can access any database with the name started with hc_.

Please read the MySQL document above for more about MySQL's authentication.

esobczak1970 commented

I reinitized the docker image with a new root password and everything seems to working right. Consistent in mysql workbench and with buffalo and pop itself. Thanks