maria-discuss team mailing list archive

[PGNet Dev <pgnet.dev@xxxxxxxxx>]

On 6/21/20 7:23 PM, Daniel Black wrote:
>> so, atm, i can't access the 'normal' running server without root user, and can't create the root user when server's running '--skip-grant-tables'.
>>
>> i guess i'm missing the 'perfectly usable' part :-/
> 
> The skip-grant-tables preventing standard modification is a bit horrible still. One day I need to work out why that restriction is there.

it'll certainly be convenient if it _does_ get straightened out.

>> can you provide an explicit example of how to -- at this puposefully fubar'd stage -- create / init a root user?

> $ cat > /tmp/reset.sql
(snip)

well that's a certainly useful idea/reminder!  would be nice on the wiki/docs (of course, might be there, and I missed it)

(clean install & normal start)
	mysql
	>

	SELECT User, Host FROM mysql.global_priv;
		+-------------+-----------+
		| User        | Host      |
		+-------------+-----------+
		| mariadb.sys | localhost |
		| mysql       | localhost |
		| root        | localhost |
		+-------------+-----------+

	DROP USER `root`@`localhost`;
	SELECT User, Host FROM mysql.global_priv;
		+-------------+-----------+
		| User        | Host      |
		+-------------+-----------+
		| mariadb.sys | localhost |
		| mysql       | localhost |
		+-------------+-----------+

	exit

verify no access

	mysql -u root
		ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


kill

	systemctl stop mariadb.service
	killall mysqld
	killall mysqld_safe

gen script

	cat << 'EOSSL' > /tmp/root_reset.sql
	CREATE USER `root`@`localhost`
	 IDENTIFIED VIA unix_socket
	 OR ed25519 USING PASSWORD('s...4');
	 GRANT ALL PRIVILEGES ON *.* TO  `root`@`localhost` WITH GRANT OPTION;
	EOSSL

start/init (needed the '--defaults-file', here; otherwise, lots of errors!)

	sudo -u mysql \
	/sbin/mysqld \
	 --defaults-file=/usr/local/etc/mariadb/my.cnf \
	 --init-file=/tmp/root_reset.sql &

test, the 'new' root user's there

	mysql -e "SELECT User, Host FROM mysql.global_priv;"
		+-------------+-----------+
		| User        | Host      |
		+-------------+-----------+
		| mariadb.sys | localhost |
		| mysql       | localhost |
		| root        | localhost |
		+-------------+-----------+

kill again

	systemctl stop mariadb.service
	killall mysqld
	killall mysqld_safe

start normal

	systemctl start mariadb

check, looks good

	mysql -e "
	 SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv))
	  FROM mysql.global_priv
	  WHERE user = 'root';
	"
		+-------------------------------------------------------------------------+
		| CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv))                                                                                                                                                                                                                                                                     
		+-------------------------------------------------------------------------+
		| root@localhost => {
		    "access": 1073741823,
		    "plugin": "ed25519",
		    "authentication_string": "T...g",
		    "auth_or":
		    [

		        {
		            "plugin": "unix_socket"
		        },

		        {
		        }
		    ],
		    "password_last_changed": 1592795308
		} |


access with

	mysql -e status | grep -i "Current user:"
		Current user:           root@localhost

and

	mysql -u root -pnotsosecure -e status | grep -i "Current user:"
		Current user:           root@localhost

both work.

why this ALSO works,

	mysql -u root -pblah -e status | grep -i "Current user:"
		Current user:           root@localhost

i'm unclear.  but a different issue ...

so, back-in-biz with at least restoring a horked/missing root user.

thxalot!