Access Denied after database restart (but granted after login via CLI) #2686
Comments
|
Can you check what is the auth sequence initially ( when it fails ) and after ? mysql2 currently always connects node-mysql2/lib/auth_plugins/caching_sha2_password.js Lines 56 to 95 in 0edda85 caching_sha2_password documentation - https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html Note that for "insecure" ( e.i no tls ) connections it might default to "non fast flow". You can change that by specifying |
|
I've tried specifying the import mysql from "npm:mysql2";
import dbConfig from "../config/db.config.ts";
import logger from "../services/logger.service.ts";
const connection = mysql.createConnection({
host: dbConfig.DB_HOST,
user: dbConfig.DB_USER,
password: dbConfig.DB_PASS,
database: dbConfig.DB_NAME,
authPlugins: {
caching_sha2_password: mysql.authPlugins.caching_sha2_password({
onServerPublicKey: function (key) {
console.log(key);
},
serverPublicKey: `-----BEGIN PUBLIC KEY-----
REDACTED
REDACTED
REDACTED
REDACTED
REDACTED
REDACTED
REDACTED
-----END PUBLIC KEY-----`,
overrideIsSecure: true //
})
},
debug: true
});
connection.connect(err => {
if (err) {
logger.fatal(err)
Deno.exit(1);
}
})
export default connectionBy the way I noticed a typo in the typescript definition here ( node-mysql2/typings/mysql/lib/Auth.d.ts Line 17 in 52d8c50 I'm not sure how to check the auth sequence, I've added the debug flag and this is what I get when database has just started: Then I Login through the CLI with the same credencials, this is the trace |
|
unfortunately logs are not super helpful, the whole "auth switch" is part of ClientHandshake command in both examples. Could you add and run again 2 scenarios? |
|
First iteration (access denied): Same logs after loggin in through CLI and no access denied: |
|
can you also add |
|
It's seems you are right! Access denied gaves me 04 (PERFORM_FULL_AUTHENTICATION_PACKET): Then it gaves 03 (FAST_AUTH_SUCCESS_PACKET): |
|
ok, that explains the difference. The next step is to decide who is wrong - mysql2 logic for PERFORM_FULL_AUTHENTICATION_PACKET or something on your side Can you try just setting |
|
That was with the overrideIsSecure. I've made it a couple of times more. First with this code: import mysql from "npm:mysql2";
import dbConfig from "../config/db.config.ts";
import logger from "../services/logger.service.ts";
const connection = mysql.createConnection({
host: dbConfig.DB_HOST,
user: dbConfig.DB_USER,
password: dbConfig.DB_PASS,
database: dbConfig.DB_NAME,
});
connection.connect(err => {
if (err) {
logger.fatal(err)
Deno.exit(1);
}
})
export default connectionI got Then with the the import mysql from "npm:mysql2";
import dbConfig from "../config/db.config.ts";
import logger from "../services/logger.service.ts";
const connection = mysql.createConnection({
host: dbConfig.DB_HOST,
user: dbConfig.DB_USER,
password: dbConfig.DB_PASS,
database: dbConfig.DB_NAME,
authPlugins: {
caching_sha2_password: mysql.authPlugins.caching_sha2_password({
onServerPublicKey: function (key) {
console.log(key);
},
serverPublicKey: `-----BEGIN PUBLIC KEY-----
REDACTED
REDACTED
REDACTED
REDACTED
REDACTED
REDACTED
REDACTED
-----END PUBLIC KEY-----`,
overrideIsSecure: true //
})
},
debug: true
});
connection.connect(err => {
if (err) {
logger.fatal(err)
Deno.exit(1);
}
})
export default connectionAnd this is the output After the manual login both answers are similar: |
|
Can we double check the server public key you use is correct? If you use TLS ( or have |
|
I don't have TLS, but when using 'overrideIsSecure: false' and just restarted the server I get the access denied and the server public key, but afterwards It's receives directly the import mysql from "npm:mysql2";
import dbConfig from "../config/db.config.ts";
import logger from "../services/logger.service.ts";
const connection = mysql.createConnection({
host: dbConfig.DB_HOST,
user: dbConfig.DB_USER,
password: dbConfig.DB_PASS,
database: dbConfig.DB_NAME,
authPlugins: {
caching_sha2_password: mysql.authPlugins.caching_sha2_password({
onServerPublicKey: function (data) {
console.log("onServerPublicKey", data);
console.log("onServerPublicKey", data.toString());
},
overrideIsSecure: false //
})
},
debug: false
});
connection.connect(err => {
if (err) {
logger.fatal(err)
Deno.exit(1);
} else {
logger.info("Connected to the database!")
Deno.exit(0)
}
})
export default connectionSame code after login through CLI: |
|
I just checked with docker started server ( public key seems to be similar - to yours |
|
Interesting... I created the docker the same way and I still got access denied, showing you the full test: [pvillaverde@Fedellux radio_dixital]$ docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=test -d mysql:8
Unable to find image 'mysql:8' locally
8: Pulling from library/mysql
fcbdc4090331: Pull complete
95eb5073c36f: Pull complete
5e5ba0e6412a: Pull complete
ee4654eb29b7: Pull complete
1d2218160b86: Pull complete
bbe712936412: Pull complete
4a6efa152609: Pull complete
0f7c3b67bb2b: Pull complete
7ccd17e83c8a: Pull complete
04bf2c116556: Pull complete
Digest: sha256:4a4e5e2a19aab7a67870588952e8f401e17a330466ecfc55c9acf51196da5bd0
Status: Downloaded newer image for mysql:8
5652372d5652219375caf3625200e574e3f57697f39fd8c7517384b8b115bef6
[pvillaverde@Fedellux radio_dixital]$ mysql -u root -ptest -h 127.0.0.1
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.0 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> SELECT user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| root | % | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0,001 sec)
MySQL [(none)]> exit
Bye
[pvillaverde@Fedellux radio_dixital]$ docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
5652372d5652 mysql:8 "docker-entrypoint.s…" 6 minutes ago Up 6 minutes 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp pedantic_elion
[pvillaverde@Fedellux radio_dixital]$ docker restart pedantic_elion
pedantic_elion
[pvillaverde@Fedellux radio_dixital]$ deno task dev
Task dev deno run --allow-sys --allow-read --allow-env --allow-net --watch src/dev/mysql.ts
Watcher Process started.
caching_sha2_password state: 0
caching_sha2_password data: <Buffer 6d 60 54 78 45 02 5f 04 47 07 62 75 68 74 52 0d 0b 66 68 5d 00>
caching_sha2_password state: 1
caching_sha2_password data: <Buffer 04>
caching_sha2_password state: 2
caching_sha2_password data: <Buffer 2d 2d 2d 2d 2d 42 45 47 49 4e 20 50 55 42 4c 49 43 20 4b 45 59 2d 2d 2d 2d 2d 0a 4d 49 49 42 49 6a 41 4e 42 67 6b 71 68 6b 69 47 39 77 30 42 41 51 45 ... >
onServerPublicKey <Buffer 2d 2d 2d 2d 2d 42 45 47 49 4e 20 50 55 42 4c 49 43 20 4b 45 59 2d 2d 2d 2d 2d 0a 4d 49 49 42 49 6a 41 4e 42 67 6b 71 68 6b 69 47 39 77 30 42 41 51 45 ... >
onServerPublicKey -----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEArLrJ9qT3mOeF0Ji1Pp1f
c65WuxUcaN1dcqBqpfOYtWuvlzBX8uNYMtKW/KEP7a/yzHNwRL+J8DFl6jdq6/e3
78KikH33gKQe0VJme2FiABmq9HdXLVr/VNN9gJvGQRcFvBCchr7AOwEP8O9TVh98
NuUrqSZv1BUSvjUuWm74WKTGcjsc4SYSjorg2IWOJKMaaWVlqyEXTAqSpNdu9PIs
AZAN264bLmWjNRqVC2mAnlalB4Z1hd3e9oRrBHl2FM3vIMnAjSpsPAIDYCy6bsBH
TYxmiVRtaDzChaSRXClL7NxE6XrRUBRPlJI6MDcQ6z4wxsH6NIOWAgV46OYdEbyL
ywIDAQAB
-----END PUBLIC KEY-----
2024-05-23 18:08:30.040 FATAL /src/dev/mysql.ts:23
Error Access denied for user 'root'@'172.17.0.1' (using password: YES), ER_ACCESS_DENIED_ERROR, 1045, 28000, Access denied for user 'root'@'172.17.0.1' (using password: YES), , [object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]
error stack:
• packet.js Packet.asError
/home/pvillaverde/.cache/deno/npm/registry.npmjs.org/mysql2/3.9.7/lib/packets/packet.js:728
• command.js ClientHandshake.execute
/home/pvillaverde/.cache/deno/npm/registry.npmjs.org/mysql2/3.9.7/lib/commands/command.js:29
• connection.js Connection.handlePacket
/home/pvillaverde/.cache/deno/npm/registry.npmjs.org/mysql2/3.9.7/lib/connection.js:481
• connection.js PacketParser.onPacket
/home/pvillaverde/.cache/deno/npm/registry.npmjs.org/mysql2/3.9.7/lib/connection.js:97
• packet_parser.js PacketParser.executeStart
/home/pvillaverde/.cache/deno/npm/registry.npmjs.org/mysql2/3.9.7/lib/packet_parser.js:75
• connection.js Socket.<anonymous>
/home/pvillaverde/.cache/deno/npm/registry.npmjs.org/mysql2/3.9.7/lib/connection.js:104
• _stream.mjs Socket.emit
deno_node/_stream.mjs:1851
• _stream.mjs addChunk
deno_node/_stream.mjs:2873
• _stream.mjs readableAddChunk
deno_node/_stream.mjs:2852
• _stream.mjs Socket.Readable.push
deno_node/_stream.mjs:2791
[pvillaverde@Fedellux radio_dixital]$ mysql -u root -ptest -h 127.0.0.1
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.4.0 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0,016 sec)
MySQL [(none)]> ^C
MySQL [(none)]> exit
Bye
[pvillaverde@Fedellux radio_dixital]$ deno task dev
Task dev deno run --allow-sys --allow-read --allow-env --allow-net --watch src/dev/mysql.ts
Watcher Process started.
caching_sha2_password state: 0
caching_sha2_password data: <Buffer 6c 7e 2f 01 7b 41 68 25 17 35 35 7f 22 1f 02 32 67 65 4c 09 00>
caching_sha2_password state: 1
caching_sha2_password data: <Buffer 03>
2024-05-23 18:10:22.846 INFO /src/dev/mysql.ts:26 Connected to the database!The code I used: export default {
DB_HOST: "localhost",
DB_USER: "root",
DB_PASS: "test",
DB_NAME: "mysql",
};import mysql from "npm:mysql2";
import dbConfig from "../config/db.config.ts";
import logger from "../services/logger.service.ts";
const connection = mysql.createConnection({
host: dbConfig.DB_HOST,
user: dbConfig.DB_USER,
password: dbConfig.DB_PASS,
database: dbConfig.DB_NAME,
authPlugins: {
caching_sha2_password: mysql.authPlugins.caching_sha2_password({
onServerPublicKey: function (data) {
console.log("onServerPublicKey", data);
console.log("onServerPublicKey", data.toString());
},
overrideIsSecure: false //
})
},
debug: false
});
connection.connect(err => {
if (err) {
logger.fatal(err)
Deno.exit(1);
} else {
logger.info("Connected to the database!")
Deno.exit(0)
}
})
export default connectionJust in case we missed something else out with the versions. I'm using Deno and these versions: Aaaand, it might be related to Deno(?). I've rewrote the code for NodeJS, installed the library and now I get what you get: import mysql from 'mysql2';
const dbConfig = {
DB_HOST: "localhost",
DB_USER: "root",
DB_PASS: "test",
DB_NAME: "mysql",
};
const connection = mysql.createConnection({
host: dbConfig.DB_HOST,
user: dbConfig.DB_USER,
password: dbConfig.DB_PASS,
database: dbConfig.DB_NAME,
authPlugins: {
caching_sha2_password: mysql.authPlugins.caching_sha2_password({
onServerPublicKey: function (data) {
console.log("onServerPublicKey", data);
console.log("onServerPublicKey", data.toString());
},
overrideIsSecure: false
})
},
debug: false
});
connection.connect(err => {
if (err) {
console.error(err);
process.exit(1);
} else {
console.info("Connected to the database!");
process.exit(0);
}
});
export default connection;[pvillaverde@Fedellux dev]$ docker restart pedantic_elion
pedantic_elion
[pvillaverde@Fedellux dev]$ node mysql.mjs
caching_sha2_password state: 0
caching_sha2_password data: <Buffer 4d 01 3f 50 42 20 75 7f 4f 60 6e 0a 28 30 68 6c 6b 14 21 7b 00>
caching_sha2_password state: 1
caching_sha2_password data: <Buffer 04>
caching_sha2_password state: 2
caching_sha2_password data: <Buffer 2d 2d 2d 2d 2d 42 45 47 49 4e 20 50 55 42 4c 49 43 20 4b 45 59 2d 2d 2d 2d 2d 0a 4d 49 49 42 49 6a 41 4e 42 67 6b 71 68 6b 69 47 39 77 30 42 41 51 45 ... 401 more bytes>
onServerPublicKey <Buffer 2d 2d 2d 2d 2d 42 45 47 49 4e 20 50 55 42 4c 49 43 20 4b 45 59 2d 2d 2d 2d 2d 0a 4d 49 49 42 49 6a 41 4e 42 67 6b 71 68 6b 69 47 39 77 30 42 41 51 45 ... 401 more bytes>
onServerPublicKey -----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEArLrJ9qT3mOeF0Ji1Pp1f
c65WuxUcaN1dcqBqpfOYtWuvlzBX8uNYMtKW/KEP7a/yzHNwRL+J8DFl6jdq6/e3
78KikH33gKQe0VJme2FiABmq9HdXLVr/VNN9gJvGQRcFvBCchr7AOwEP8O9TVh98
NuUrqSZv1BUSvjUuWm74WKTGcjsc4SYSjorg2IWOJKMaaWVlqyEXTAqSpNdu9PIs
AZAN264bLmWjNRqVC2mAnlalB4Z1hd3e9oRrBHl2FM3vIMnAjSpsPAIDYCy6bsBH
TYxmiVRtaDzChaSRXClL7NxE6XrRUBRPlJI6MDcQ6z4wxsH6NIOWAgV46OYdEbyL
ywIDAQAB
-----END PUBLIC KEY-----
Connected to the database!
[pvillaverde@Fedellux dev]$ node mysql.mjs
caching_sha2_password state: 0
caching_sha2_password data: <Buffer 4e 7a 2f 61 0d 06 74 62 1d 58 57 1e 79 4f 5e 55 6c 40 03 45 00>
caching_sha2_password state: 1
caching_sha2_password data: <Buffer 03>
Connected to the database!Strange... the code from the library is exactly the same so... the problem is elsewhere, but I have no clue where it might be... and why it only fails the first time... |
It fails the first time because failure path is somewhere in cert + encryprion. The server remembers client somehow and chooses "fast auth" sequence I tried with very old deno version - maybe you are right and it's something on deno side, maybe crypto implementation or something else. I'll try with 1.43.2 |
|
@pvillaverde I can replicate the issue with deno v1.43.2 and I'll try do debug what's happening Could you try to use deno v1.29.4 - this is the version that worked with no issues for me |
|
I think I found the culprit: |
|
not just different type, but also result is different this example const stage1 = Buffer.from('332b264f3d', 'hex');
const key = Buffer.from('2d2d2d2d2d424547494e205055424c4943204b45592d2d2d2d2d0a4d494942496a414e42676b71686b6947397730424151454641414f43415138414d49494243674b43415145416e346e644c4650414f715351516e6d6243366e6a0a7471753746554930436e7076744947467a6a544c463541577268524153336141754e414e6b39624f5738515a355a32634f356d4c53627a71525a584e555131350a377048717363414e6d7a616b4456576a57312b455674696d6130496168346b49516f6347477948455a44442f6171384b64635a564f46797666792b3841535a520a376555597871383857764f4868467537366a35684a33446d5a6279614d704e36554f464869594d377043394c7164716339784e4e4f39345a315a6b4b646852440a2f304a6c556f7277486d45704e496e4c3873694e435a575777494e62367151476a7271565572477a4a30476a466a2b424f50666e6f7336796571446634774a450a77627870305a7669586f6261777635584d6c4237694b596d383170676d4c7a6f78645a6d777272537351752f4657714b78355356564467626733597751425a720a6f774944415141420a2d2d2d2d2d454e44205055424c4943204b45592d2d2d2d2d0a', 'hex');
const result = crypto.publicEncrypt(key, stage1);
console.log(result);
process.exit(0);with deno v1.43.2 it returns deno v1.29.4 node 22: bun 1.0.2: |
|
Silly me - |
|
@pvillaverde got it working with deno 1.43.2 by adding I guess default padding is different between deno and node maybe related: nodejs/help#1093 |
|
yes, confirming that default padding in node is and in deno its 1 - RSA_PKCS1_PADDING the fix is to explicitly set it to |
|
Hey there! Apologies I've been out a couple of days! I've tried adding the return crypto.publicEncrypt(key, stage1);
return crypto.publicEncrypt({
key,
padding: crypto.constants.RSA_PKCS1_OAEP_PADDING
}, stage1);Task dev deno run --allow-sys --allow-read --allow-env --allow-net --watch src/dev/mysql.ts
Watcher Process started.
caching_sha2_password state: 0
caching_sha2_password data: <Buffer 62 75 78 06 4e 3e 69 0a 49 77 2d 19 72 57 06 49 19 36 48 3f 00>
caching_sha2_password state: 1
caching_sha2_password data: <Buffer 04>
caching_sha2_password state: 2
caching_sha2_password data: <Buffer 2d 2d 2d 2d 2d 42 45 47 49 4e 20 50 55 42 4c 49 43 20 4b 45 59 2d 2d 2d 2d 2d 0a 4d 49 49 42 49 6a 41 4e 42 67 6b 71 68 6b 69 47 39 77 30 42 41 51 45 ... >
onServerPublicKey <Buffer 2d 2d 2d 2d 2d 42 45 47 49 4e 20 50 55 42 4c 49 43 20 4b 45 59 2d 2d 2d 2d 2d 0a 4d 49 49 42 49 6a 41 4e 42 67 6b 71 68 6b 69 47 39 77 30 42 41 51 45 ... >
onServerPublicKey -----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAnWeCp0I/GsDWGrvJ9xdA
8SvZrjKzrkDvvA6c/Cj1l3nvSNj2lUcdyNygMBQForAapNPac3yvWZT/ZIMH+W86
dWZ6rK7YSZktMDrz8Rg7Y8/4tfBEt7bSUhUX/DdgXNR1soCn21wQxEJ+QAtIHOWV
93lRy51gktVEutX9Vz4Jnxqky6MgCh0YGLJsf2L5DUvXPwJ0BQ+PEiQB1/JC4gG0
x9Kw9esOCUy9jNdN5HE7ofEg9YlyntzwGSL3iByXiT7U+iMNG1u1dWwUeoh5b2dt
nyzzaSvkAU+nes0Ej7ivlc8Ci5c3y4b3jxC2fnRWHM0Uk9Dk2z10xtAceWBwG7S7
0QIDAQAB
-----END PUBLIC KEY-----
2024-05-26 09:40:14.694 FATAL /src/dev/mysql.ts:28
Error Access denied for user 'root'@'172.17.0.1' (using password: YES), ER_ACCESS_DENIED_ERROR, 1045, 28000, Access denied for user 'root'@'172.17.0.1' (using password: YES), , [object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]
error stack:I'm using the same code and docker run commands from the last time. |
|
Never mind, for some reason the Deno script was using the local node_modules from the NodeJS test instead of the one installed with Deno. It's working! Great Job, thanks!! |
|
Thanks for your help @pvillaverde ! I'll try to get deno ci running and then publish the fix |
|
@pvillaverde fix is published as v3.9.8 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hey there!
I've found what it seems to be a strange bug in the authentication with the database. Whenever the database server is restarted, I am getting an access denied error like this one:
The user has the permissions to login from anywhere ('%') and is using the new
caching_sha2_password, as I was tired of the deprecation warnings logs in the database about themysql_native_passwordplugin.My workarround at this moment is the most weird part. If I login to the database with the same credentials using the mysql CLI, I got not error at all, and afterwards the application can also login without getting any more errors until the next database restart.
I've been researching a lot in the last day about this issue but can't find any answer or permanent solution. Anyone else has run into the same issue and can give me some guidance? If you need more information let me know.
Other information
MySQL Version: 8.0.36
Mysql2 version: 3.9.7
The text was updated successfully, but these errors were encountered: