Cannot login to mysql with non root user from command line

Question

I am setting up a mysql server and I am trying to create a non privileged user to login with for each database that I create. I can successfully login as root from the console but when I try to login as the "rails" user it fails. This is what my users looks like:

mysql> select user,host,password from mysql.user;
+---------+-----------+-------------------------------------------+
| user    | host      | password                                  |
+---------+-----------+-------------------------------------------+
| root    | localhost | *9CAF1C22F1F66A8C47C74E7F6700391F0AB817F0 |
| root    | 127.0.0.1 | *C453FFD4FD2470D5CD56BB9A229BDC7388F3A170 |
| datadog | localhost | *AEB79FF15C0A9CA185F3768235927ADDD9667C85 |
| rails   | localhost | *9CAF1C22F1F66A8C47C74E7F6700391F0AB817F0 |
+---------+-----------+-------------------------------------------+

The grants for the rails user look like this:

mysql> show grants for 'rails'@'localhost';                                                                                                                                          ';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for rails@localhost                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rails'@'localhost' IDENTIFIED BY PASSWORD '*9CAF1C22F1F66A8C47C74E7F6700391F0AB817F0' REQUIRE SSL |
| GRANT ALL PRIVILEGES ON `resumecompanion_staging`.* TO 'rails'@'localhost'                                               |
+--------------------------------------------------------------------------------------------------------------------------+

please note that I have set the root user password and the rails password as the same. I did this to confirm that I am entering the correct password and it still does not work.

When I attempt to login with the rails users I see this:

mysql -u rails -h localhost -p --protocol=TCP resumecompanion_staging
Enter password:
ERROR 1045 (28000): Access denied for user 'rails'@'localhost' (using password: YES)

Does anybody have any ideas what I am doing wrong?

Thanks,

Josh

Answer 1

GRANT ... TO 'rails'@'localhost' ... REQUIRE SSL

For starters requiring SSL for connections to/from localhost is completely senseless. SSL or TLS provide transport security which you don't need for communications that won't leave the actual system, that will not provide any additional security and only extra overhead.

In addition the official MySQL libraries on Linux/UNIX treat the keyword localhost different from what you would expect and will use the Unix socket, not TCP. from the manual:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. ... You can also specify the connection protocol explicitly, by using the --protocol=TCP option.

Using the socket gives you much more performance than using TCP/IP and AFAIK the socket does not support SSL (and would not need to either).

If you do force a TCP connection, you'll need to GRANT access over TCP as well, with GRANT ... TO 'rails'@'127.0.0.1' ... , because granting connections from localhost implies the Unix socket.

Particularly unhelpful is that for error messages MySQL follows the convention to do a reverse DNS lookup for ip-addresses which will convert the ip-address 127.0.0.1 to localhost.