access denied for user 'user'@'localhost' (using password: no)

[Kapelianovych]

When I am trying to connect to database, I always get this error:

Error from connection - Error: (conn=64, no: 1045, SQLState: 28000) Access denied for user 'admin'@'localhost' (using password: NO)
(node:61034) UnhandledPromiseRejectionWarning: TypeError: mariadb.query is not a function
at Object.query (/Users/ky/Projects/test/server/db.js:28:28)
at processTicksAndRejections (internal/process/task_queues.js:86:5)
(node:61034) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 2)

I execute command mysql_secure_installation. Everything went fine. Database exists. But I still got this error...
My code:

const mariadb = require('mariadb')

let connection = null

module.exports = {
  async connect() {
    try {
      connection = await mariadb.createConnection({
        user:'admin',
        password: 'password',
        database: 'dbName',
        host: 'localhost'
      })
    } catch (error) {
      console.log(`Error from connection - ${error}`)
    }
  },
  /**
   * Executes sql and returns result
   * @param {String} sql SQL to execute
   * @param {Array<String>} values placeholder values
   */
  async query(sql, values) {
    if (connection) {
      return await mariadb.query(sql, values)
    } else {
      await this.connect()
      return await mariadb.query(sql, values)
    }
  }
}

[knoxcard]

Using password: no

In your code password: 'password', you tried password: '' ?

[Kapelianovych]

@knoxcard no. Password is provided - I just replace it with 'password' word.

[knoxcard]

@YevhenKap - gotchya, lol. Just checkin to make sure that was indeed the case. This has got to be something simple!

[rusher]

hmm, this is the standard message when authorization fails. So this can be for multiple reasons.
After running mysql_secure_installation default remote access is removed.

Can you connect to the database using command line with the same information ?

# mysql -u admin-p

I would first query mysql.user to see how your 'admin' user is created ( like is remote access permitted, SSL mandatory, ... ), and if this user has access to 'dbName' database.

[Kapelianovych]

@rusher I can use admin user in order to connect to the database (with password). Actually, I previously granted to him all privileges to alter all databases and tables.

MySQL [mysql]> select * from user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host      | User             | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                                                  | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| %         | admin            | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$Qq|Gi)#A8E&D_gC7GdAu9nj.2YoV9BuM12/DBiNvW4taJ9ocFk0DQKVUqD | N                | 2019-04-29 23:28:39   |              NULL | N              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | admin            | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$]ep6}9sE[~cXX1F6SgTL0hotIBp83BJ0DYIsvEhnzkJgZrO0d/BvGZ. | N                | 2019-04-29 22:13:52   |              NULL | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | mysql.infoschema | Y           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2019-04-01 23:22:17   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | mysql.session    | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2019-04-01 23:22:17   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | mysql.sys        | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2019-04-01 23:22:17   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | root             | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | cacf:/1SPWkpbIIcVVskUV3XmcCZpsgGRAxzNcPnOK/XHA5/TC5 | N                | 2019-05-01 19:11:10   |              NULL | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
6 rows in set (0.001 sec)

[rusher]

ah ok. This is MySQL 8 'caching_sha2_password' that is not supported for now.

You must either use 'mysql_native_password' changing your user with

ALTER USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

or create a new user with mysql_native_password.

For the story: MySQL did add the 'caching_sha2_password' with version 8.0. This means having the server public key configured on each client - or leaving the server to send public key, but that is then a security risk.
MariaDB implements a new authentication solution : (https://mariadb.com/kb/en/library/connection/#client_ed25519-plugin) plugin that permits the same level of security without having to maintain a public key client side. see blog. This is still in beta since a few years, but will be GA soon. So will be in next 2.1.0.

Maybe 'caching_sha2_password' will be supported in 2.1.0, but still not sure.

[Kapelianovych]

@rusher thanks too much! It works!) I do not use relational databases, so I do not know much about them. But in this case, I needed it, and without you, I wouldn't be able to find the problem.