`sudo mysql` logs in fine, `sudo mycli` gives permission denied; root has no password

[cmcaine]

On ubuntu 16.04, mariadb 10.0.31-0ubuntu0.16.04.2

$  mycli --version
Version: 1.5.2

$ sudo mycli
(1698, "Access denied for user 'root'@'localhost'")

[tsroten]

Hi @cmcaine I'm not that familiar with how sudo affects database clients on Ubuntu, but I believe you need to set a password for your root database user.

Also, if possible you might want to install mycli via pip instead of apt-get as we're currently on version 1.13.1 instead of 1.5.2.

[meeuw]

Hmm, could you please post the output from sudo mysql and then the \s command? Maybe your ~root/.my.cnf contains a configuration which isn't supported by mycli ?

[cmcaine]

This could be helpful(?):

$ sudo mysql --silent --skip-column-names -e"show grants"              
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION

As requested:

$ sudo mysql            
[sudo] password for colin: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.0.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:      54
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server:         MariaDB
Server version:     10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:         13 hours 13 min 53 sec

Threads: 1  Questions: 130  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 78  Queries per second avg: 0.002
--------------

MariaDB [(none)]> 

Neither root nor my user have any maria/mysql config files:

$ sudo ls ~root -a
.  ..  .bash_history  .bashrc  .cache  client1.crt  client1.csr  client1.key  .nano  .profile  .rnd  .ssh  .zcompdump

Same error with mycli 1.13.1

[meeuw]

Hmm could you try to connect (as a normal user) using mycli -S /var/run/mysqld/mysqld.sock -u root ? Could you also check the permissions on the socket file? ls -la /var/run/mysqld/mysqld.sock (and the parent directories)

[cmcaine]

sudo ~/.local/bin/mycli -S /var/run/mysqld/mysqld.sock -u root works. Without sudo does not.

$ namei -mo /var/run/mysqld/mysqld.sock 
f: /var/run/mysqld/mysqld.sock
 drwxr-xr-x root  root  /
 drwxr-xr-x root  root  var
 lrwxrwxrwx root  root  run -> /run
   drwxr-xr-x root  root  /
   drwxr-xr-x root  root  run
 drwxr-xr-x mysql root  mysqld
 srwxrwxrwx mysql mysql mysqld.sock

$ ls -la /var/run/mysqld/
total 4
drwxr-xr-x  2 mysql root    80 Oct 26 23:37 .
drwxr-xr-x 29 root  root  1040 Oct 27 12:51 ..
-rw-rw----  1 mysql mysql    5 Oct 26 23:37 mysqld.pid
srwxrwxrwx  1 mysql mysql    0 Oct 26 23:37 mysqld.sock

[meeuw]

Hmm, do you have selinux enabled? (getenforce) Secondly, could you try running env -i ~/.local/bin/mycli -S /var/run/mysqld/mysqld.sock -u root as a normal user? sudo does some environment cleanup so maybe that's why it's working?

I didn't know namei, very useful, thanks!

[cmcaine]

Selinux isn't installed. Apparmor is, but doesn't seem to have a policy for mariadb:

$ sudo apparmor_status
apparmor module is loaded.
13 profiles are loaded.
13 profiles are in enforce mode.
   /sbin/dhclient
   /usr/bin/lxc-start
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/lib/NetworkManager/nm-dhcp-helper
   /usr/lib/connman/scripts/dhclient-script
   /usr/lib/lxd/lxd-bridge-proxy
   /usr/lib/snapd/snap-confine
   /usr/lib/snapd/snap-confine//mount-namespace-capture-helper
   /usr/sbin/tcpdump
   lxc-container-default
   lxc-container-default-cgns
   lxc-container-default-with-mounting
   lxc-container-default-with-nesting
0 profiles are in complain mode.
1 processes have profiles defined.
1 processes are in enforce mode.
   /sbin/dhclient (837) 
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.

python3 needs a locale envvar:

# Fails as regular user:
env -i LANG=C.UTF-8 ~/.local/bin/mycli -S /var/run/mysqld/mysqld.sock -u root
(1698, "Access denied for user 'root'@'localhost'")

# Succeeds as root (need HOME to find local python modules):
sudo env -i LANG=C.UTF-8 HOME=$PWD ~/.local/bin/mycli -S /var/run/mysqld/mysqld.sock -u root

[meeuw]

Hmm I don't have any experience with apparmor, is it possible to temporary disable it like setenforce Permissive in selinux?

Have you tried passing the same environment variables when running with env -i as a normal user?

How did you install mycli in ~/.local/bin/mycli? Are you using a Python virtualenv for ~/.local ?

Could you try to add/use a different (non privileged) user on your system by running: sudo -u differentuser ~/.local/bin/mycli -S /var/run/mysqld/mysqld.sock -u root

you could also try to use $USER for testing...

[cmcaine]

Huh. After /etc/init.d/apparmor teardown, mycli works if I specify the socket. Quit mycli, reload apparmor (confirm policies are loaded), try again, still works.

Maybe the ubuntu package that installs mariadb modifies the apparmor rules in some way and apparmor needs to be reloaded?

That would be pretty crazy, because the /etc/apparmor.d/usr.sbin.mysqld reads:

# This file is intensionally empty to disable apparmor by default for newer
# versions of MariaDB, while providing seamless upgrade from older versions
# and from mysql, where apparmor is used.
#
# By default, we do not want to have any apparmor profile for the MariaDB
# server. It does not provide much useful functionality/security, and causes
# several problems for users who often are not even aware that apparmor
# exists and runs on their system.

This seems like a bug in ubuntu.

mycli should probably try that socket by default, though.

[cmcaine]

Oh, and I installed mycli with pip install --user mycli, that's why it's in ~/.local/bin

[cmcaine]

Yeah, this doesn't make any sense to me :(

Apparmor isn't supposed to affect any programs that it doesn't have policies for, and it doesn't and never has had any policy defined for mysqld. The only active confinement to be affected by the teardown (and to remain unconfined after reload) was for dhclient, which neither mysqld or mycli should be touching anyway.

[meeuw]

Very weird indeed; as I wrote earlier, I don't have any experience with Apparmor. You might want to report a bug at Ubuntu's bugtracker instead.

I think it makes sense to default to connect to unix domain sockets instead of localhost (127.0.0.1) like the mysql client. Or maybe even better, first try unix domain sockets and if that fails connect to localhost.

The defaults are defined here: https://github.com/dbcli/mycli/blob/master/mycli/main.py#L352

[cmcaine]

I don't have much experience with AppArmor either. I will report a bug with them.

Are you suggesting the line number because you want me to make a PR? I can do.

In the code, it looks like all of these functions are happy to accept a socket and a port and hostname, including pymysql. But if you do specify port, hostname and socket then the socket is the only one that gets used. I'd have made that a runtime error. Whatever.

Under what circumstances would you like mycli to fall-back to tcp/ip? I think only 2001-2003 ref.

[cmcaine]

Also, the official upstream location for the socket is /tmp/mysql.sock, which of course, is crazy and I know of no distro that does that. But it does mean the default location varies. Here's a list from the netdata monitor project with some possible locations.

OTOH, Arch, debian and buntu all put it in the same place. Redhat didn't as of 2014, but IDK now.

[Bost]

I have a similar problem on Guix. Connect with mysql works:

bost@ecke ~/dec/fdk.kit [env]$ mysql --user bost
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.5.12-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:      5
Current database:
Current user:       bost@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server:         MariaDB
Server version:     10.5.12-MariaDB MariaDB Server
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /run/mysqld.sock
Uptime:         6 min 13 sec

Threads: 1  Questions: 8  Slow queries: 0  Opens: 17  Open tables: 10  Queries per second avg: 0.021
--------------

MariaDB [(none)]> SELECT concat(user, '  \'', password, '\'') FROM mysql.user;
+--------------------------------------+
| concat(user, '  \'', password, '\'') |
+--------------------------------------+
| mariadb.sys  ''                      |
| root  'invalid'                      |
| bost  'invalid'                      |
| foo  ''                              |
+--------------------------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> Bye

Connect with mycli doesn't work:

bost@ecke ~/dec/fdk.kit [env]$ mycli --version
Version: 1.25.0
bost@ecke ~/dec/fdk.kit [env]$ mycli --user bost
(1698, "Access denied for user 'bost'@'localhost'")
bost@ecke ~/dec/fdk.kit [env]$ namei -mov /run/mysqld.sock
f: /run/mysqld.sock
drwxr-xr-x bost users /
drwxr-xr-x bost users run
srwxrwxrwx bost users mysqld.sock

However if I use the foo database user, it works:

bost@ecke ~/dec/fdk.kit [env]$ mysql --user foo                                                                                                                                                                                             
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.5.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> Bye
bost@ecke ~/dec/fdk.kit [env]$ mycli --user foo
MariaDB 10.5.12
mycli 1.25.0
Home: http://mycli.net
Bug tracker: https://github.com/dbcli/mycli/issues
Thanks to the contributor - Zhaolong Zhu
MariaDB foo@localhost:(none)>                                                                                                                                                                                                                
Goodbye!

[mc2contributor]

I seem to have the same issue that Bost posted on March 14. I'm authenticating with the unix_socket plugin. The command mysql mysql works, but mycli mysql fails with:

(1698, "Access denied for user 'me'@'localhost'")