SSL Support for connecting to MySQL

[guretno]

Hi,

I'm wondering if sails-mysql-transaction support SSL connection to the database. Using Mysql Client usually SSl connection is made through this command

mysql -h [db url] --ssl-ca=[full path ca cert] --ssl-verify-server-cert -u [username] -p

And I try to set the ssl on the configuration file for sails-mysql-transaction
...
user : db username
password :db password
database: db name
ssl: {
key: null,
cert: null,
ca: fs.readFileSync( full path to CA cert)
}

the connection is failing with the following error:

Encountered an unexpected error: {} Error (E_UNKNOWN) :: Encountered an unexpected error Could not connect to MySQL: Error: unable to verify the first certificate at afterwards (.../node_modules/sails-mysql-transactions/node_modules/sails-mysql/lib/connections/spawn.js:72:13) at .../node_modules/sails-mysql-transactions/node_modules/sails-mysql/lib/connections/spawn.js:40:7 at Handshake.onConnect (.../node_modules/sails-mysql-transactions/node_modules/mysql/lib/Pool.js:54:9) at Handshake.Sequence.end (.../node_modules/sails-mysql-transactions/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24) at .../node_modules/sails-mysql-transactions/node_modules/mysql/lib/protocol/Protocol.js:169:20 at TLSSocket.<anonymous> (.../node_modules/sails-mysql-transactions/node_modules/mysql/lib/Connection.js:292:7) at TLSSocket.emit (events.js:104:17) at TLSSocket._finishInit (_tls_wrap.js:460:8) Details: Error: Could not connect to MySQL: Error: unable to verify the first certificate

Any ideas? Thanks a lot.

[shamasis]

I am not seeing a code in this where the SSL config is not getting forwarded to original sails-mysql... does this work on originals sails-mysql? If yes, which version? If not, then I will take this up as a feature request.

[guretno]

Hi, I saw related bug on sails-mysql and waterline as follows,

https://github.com/balderdashy/sails-mysql/issues/227
https://github.com/balderdashy/waterline/issues/1073

It seems that they support SSL and can have SSL setting in the configuration, please correct me if i'm wrong.

[shamasis]

Great. I'll take this up at the earliest. I'm still not sure why this is not working. I pass all configurations to sails-mysql. Will deep dive. Hope it's not urgent.

[guretno]

Cool. Thanks, really appreciate your help. Please take your time. :)

[mikermcneil]

Looking at this in this in the main repo soon-- it looks like we just need to pass through other options to felixge when we create the connection/pool (it's a different config dictionary). Heads up: that's just a cursory glance though-- haven't verified the problem yet from this end.

[mikermcneil]

Actually... After a second look, I think the issue you're experiencing is more of a cloneDeep thing. It's a different dictionary, but it's nested: https://github.com/felixge/node-mysql/blob/master/Readme.md#ssl-options

[mikermcneil]

Ok so I think what's going on is that the ssl example provided in the node-MySQL readme shows reading the ssl crt as a buffer, but for it to work, it needs to be read in as a string. For the short term, I'll work on documenting this-- for slightly further out, the cloning in waterline core can help make for a better experience here by acknowledging that, like Jon Snow, it knows nothing about buffers, and that it should leave them alone.

[ghost]

@mikermcneil Is there any progress on addressing this? I'm on Sails v.0.12.13 and can't connect to mySQL over SSL using the sails-mysql adapter. Was hoping there might be a solution soon.

A fix seems to have been created here (comment reply by username "cludden" on July 1, 2015):
https://github.com/balderdashy/waterline/issues/1073#issuecomment-117819221

[guretno]

Hi @mikermcneil ,I have upgraded my project in SailsJS to Sails v1.0 and able to use DataStore.transactions using sails-msyql 1.0.0-14 (http://next.sailsjs.com/documentation/reference/waterline-orm/datastores/transaction).

But I still can't find any documentation on how to setup the SSL encrypted connection. Does it already supported on this version? Really appreciate if there's any cursor/enlightenment on this db SSL connection setup.

Thanks in advance.