[MDEV-25171] Cannot connect via socket until server restarts after `FLUSH PRIVILEGES` Created: 2021-03-17  Updated: 2021-03-28  Resolved: 2021-03-28

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.5.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Thomas Deutschmann Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

I only tested mariadb-10.5.9:

Create the server like

testuser@system ~ $ mkdir /tmp/flush-privs-test-data
 
testuser@system ~ $ mysql_install_db --no-defaults --skip-grant-tables --skip-networking --datadir=/tmp/flush-privs-test-data
 
testuser@system ~ $ mysqld --no-defaults --skip-grant-tables --skip-networking --datadir=/tmp/flush-privs-test-data --socket=/tmp/flush-privs-test-data.socket

In another terminal run

testuser@system ~ $ mysql --socket=/tmp/flush-privs-test-data.socket -u root -e 'SELECT USER()'
 
+--------+
 
| USER() |
 
+--------+
 
| root@  |
 
+--------+
 
testuser@system ~ $ mysql --socket=/tmp/flush-privs-test-data.socket -e 'FLUSH PRIVILEGES'
 
testuser@system ~ $ mysql --socket=/tmp/flush-privs-test-data.socket -u root -e 'SELECT USER()'
 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
 
testuser@system ~ $ mysql --socket=/tmp/flush-privs-test-data.socket -e 'SELECT USER()'
 
+--------------------+
 
| USER()             |
 
+--------------------+
 
| testuser@localhost |
 
+--------------------+

If I can connect as root through socket at the beginning, "FLUSH PRIVILEGES" shouldn't change anything. But if it should change anything, I shouldn't be able to connect again as root after restarting the server.

 Comments   
Comment by Elena Stepanova [ 2021-03-28 ]

If I can connect as root through socket at the beginning, "FLUSH PRIVILEGES" shouldn't change anything.

You don't connect as root at the beginning. Your server is running with skip-grant-tables, it doesn't perform any authentication; so whatever you provide as user option to the client means nothing to the server, other than the value for USER() function call to return. You could have connected as -ufoo, you would have seen foo@ in the USER() output.

When you run flush privileges, you activate the authentication system. And by default, mysql_install_db sets up local root to be authenticated via unix_socket plugin. mysql_native_password is configured, but disabled by an invalidated password. If you want to use password authentication, you need to switch to it.

Generated at Thu Feb 08 09:35:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.