Mysql: Problem with user privileges when using ensurePermissions?

Help
1

Here’s how I use the mysql service:

  services.mysql = {
    enable = true;
    package = pkgs.mariadb;
    bind = "localhost";
    ensureDatabases = [
      configMm.db
    ];
    ensureUsers = [
      {
        name = "${configMm.user}";
        ensurePermissions = {
          "${configMm.db}.*" = "ALL PRIVILEGES";
        };
      }
    ];
  };

However, when I go to my app I get this:

Internal Server Error
A critical error has occurred, and page execution has stopped. Below are the details:
1698: Access denied for user 'myDbUser'@'localhost'

Action taken: Attempted to connect to database on localhost

When I try to use mysql on the command line, it works for the user root, but not for the user myDbUser:

ubuntu-s-1vcpu-1gb-ams3-01# mysql -u myDbUser
ERROR 1698 (28000): Access denied for user 'myDbUser'@'localhost'

ubuntu-s-1vcpu-1gb-ams3-01# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 28
Server version: 10.6.8-MariaDB MariaDB Server
...

The grants for that user:

MariaDB [(none)]> show grants for 'myDbUser'@'localhost';
+-------------------------------------------------------------------------+
| Grants for myDbUser@localhost                                           |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `myDbUser`@`localhost` IDENTIFIED VIA unix_socket |
| GRANT ALL PRIVILEGES ON `mydbname`.* TO `myDbUser`@`localhost`       |
+-------------------------------------------------------------------------+
2 rows in set (0.000 sec)

Shouldn’t the user “myDbUser” (which is the value of configMm.user) be allowed to do this? I’ve gotten this to work in the past, but I don’t have any specifics in my notes about this issue.

2

Additional info:

$ mysql -u myDbUser@localhost -S /run/mysqld/mysqld.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.6.8-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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.003 sec)

But I do see the database if I do mysql -u root. Shouldn’t my user be able to see the database?

3

Does myDbUser exist as a local user? What does id myDbUser say?

4

No such user! I was imagining that the mysql -u username was the mysql user, not the linux user. Similar to how you can do psql -U postgresusername -d dbname with postgres. Is there a way to connect to a specific database with a specific mysql user on the command line, just to test the connection, and/or access levels of that mysql user?

5

Trying a new strategy to test the connection now. I have this script:

<?php
$dbname = 'mydbname';
$dbuser = 'myDbUser';
$dbhost = 'localhost';
$dbpass = null;
$socket = '/run/mysqld/mysqld.sock';

$connect = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname, 3306, $socket) or die("Unable to Connect to '$dbhost'");

And I run it like this:

$ /nix/store/r4m19mpqf9k4ndjs4l5gw56s27aaw4s3-php-with-extensions-8.0.18/bin/php test.php

Warning: mysqli_connect(): (HY000/1698): Access denied for user 'myDbUser'@'localhost' in /root/test.php on line 8
Unable to Connect to 'localhost'#

Don’t the grants look correct?

MariaDB [(none)]> show grants for 'myDbUser'@localhost;
+---------------------------------------------------------------------------+
| Grants for myDbUser@localhost                                           |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `myDbUser`@`localhost` IDENTIFIED VIA unix_socket |
| GRANT ALL PRIVILEGES ON `mydbname`.* TO `myDbUser`@`localhost`       |
+---------------------------------------------------------------------------+
2 rows in set (0.000 sec)

Some more info:

$ cat /etc/my.cnf
[mysqld]
bind-address=localhost
datadir=/var/lib/mysql
port=3306
6

The ensureUsers options only work with mysql/mariadb socket authentication. This authentication method allows you to securely avoid setting a password because it ties authentication to the unix account.

To answer your question: assuming you have created the unix account myDbUser you could run sudo -u MyDbUser mysql -u myDbUser to connect to the database server as your user and you could execute your script with sudo -u myDbUser /nix/store/r4m19mpqf9k4ndjs4l5gw56s27aaw4s3-php-with-extensions-8.0.18/bin/php test.php.

Let me know if you have any issues.

7

Thank you very much for responding again!

Just to be 100% sure: Does that mean that I won’t be able to do mysql -u myDbUser on the command line with a user that I’ve added using ensureUsers? (there’s no linux user named myDbUser)

8

There always has to be a corresponding system (linux) user to match a database user defined by services.mysql.ensureUsers. You need to either create a system user called myDbUser or stop using services.mysql.ensureUsers.

9

Ok, thanks again! This surprises and confuses me, because I thought that there was a disconnect between the mysql users and the unix users on my system. I’ll ask some more questions to try to understand this better:

  1. Is this link (= there has to be a corresponding linux user for the mysql user) only required when I wish to use socket authentication?
  2. Should it be enough to call ensureUsers to create both the mysql user and the linux user, or do I have to create the linux user elsewhere manually?
  3. If I don’t use ensureUsers, but manually create a user (e.g. CREATE USER 'myDbUser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';), do I then need a corresponding linux user?
  4. Do I always need a linux user if I wish to login to mysql from the command line, with the command mysql -u username?
10

Exactly.

ensureUsers creates a mysql user with socket authentication based permissions, but does not create a corresponding linux user - the assumption is that you will do this some other way, like users.users.myDbUser.

You do not require a linux user in this scenario - a corresponding linux user is only required when you are using mysql socket authentication.

Absolutely not, only when using socket authentication.

mysql socket authentication is a special mysql plugin to you can consider utilizing in the scenario that you have an application or daemon that requires database access and runs as its own dedicated linux user. This fits the NixOS model well where we always run (web) applications as their own dedicated linux account. If you are coming from a Debian-with-mod-php background this won’t be as familiar to you, but it is worth learning about as it is superior from a security and configuration perspective.

1 Like