Support SSL connections with certificates in MySQL credentials

I’m running the latest docker image of n8n.

The credentials setup for MySQL connection (and also Postgres) to execute queries in the MySQL node doesn’t seem to support SSL options and certificates upload (at least from the user interface).

The node.js driver MySQL2 seems to support this options in a sub-object ssl: { ... }
https://github.com/sidorares/node-mysql2/blob/master/examples/ssl/select-over-ssl.js

I also looked at the code in https://github.com/n8n-io/n8n/blob/master/packages/nodes-base/credentials/MySql.credentials.ts and the properties don’t seem to be implemented.

Is there a way to set this properties and attach certificates files? Editing the configuration directly in the database is not possible because, of course, credentials are encrypted.

Do I have to create a custom node to achieve this?
In our use case SSL certificates are mandatory on the database connections, if someone points me in the right direction on how to set sub-properties and file attachments using the configuration, i could also try to open a PR and add the feature to the official MySql.credentials.

Thanks!

Welcome to the community @cassvail

You do not need to create a custom-node, you can extend the current node. Just add those parameters to the credentials as string inputs.

I guess it should look something similar to:

1 Like

Hi @RicardoE105, thanks a lot for your reply.
I’m just new to the project and maybe there are still parts that I’m missing, so I’m sorry if my questions are trivial…
How do I extend a node without creating a custom node? Is it feasable from the UI or do I have to upload some custom configurations to the nodes? (the SSL flag you showed is not present in my credential settings, which is not actually the node, but the credentials itself)
In this case how do I make sure that the properties that I add are mapped to the nested property ssl: {… } the certificates in mysql2 driver first level options?
Maybe I just need to read the documentation, feel free to just send me the link if you can.

In example:

mysql.createConnection({
  user: 'root',
  password: '',
  database: 'test',
  host: '127.0.0.1',
  port: '3306',
  ssl: {
    // key: fs.readFileSync('./certs/client-key.pem'),
    // cert: fs.readFileSync('./certs/client-cert.pem')
    ca: fs.readFileSync('./certs/ca-cert.pem')
  }
});

Thanks again for your support

How do I extend a node without creating a custom node? Is it feasable from the UI or do I have to upload some custom configurations to the nodes?

Edit the current node, test it, and then send a PR.

The SSL flag you showed is not present in my credential settings, which is not actually the node, but the credentials itself

You can add the parameters. Just add the code below to this file.

Code

import {
ICredentialType,
NodePropertyTypes,
} from ‘n8n-workflow’;

    export class MySql implements ICredentialType {
    	name = 'mySql';
    	displayName = 'MySQL';
    	documentationUrl = 'mySql';
    	properties = [
    		{
    			displayName: 'Host',
    			name: 'host',
    			type: 'string' as NodePropertyTypes,
    			default: 'localhost',
    		},
    		{
    			displayName: 'Database',
    			name: 'database',
    			type: 'string' as NodePropertyTypes,
    			default: 'mysql',
    		},
    		{
    			displayName: 'User',
    			name: 'user',
    			type: 'string' as NodePropertyTypes,
    			default: 'mysql',
    		},
    		{
    			displayName: 'Password',
    			name: 'password',
    			type: 'string' as NodePropertyTypes,
    			typeOptions: {
    				password: true,
    			},
    			default: '',
    		},
    		{
    			displayName: 'Port',
    			name: 'port',
    			type: 'number' as NodePropertyTypes,
    			default: 3306,
    		},
    		{
    			displayName: 'Connect Timeout',
    			name: 'connectTimeout',
    			type: 'number' as NodePropertyTypes,
    			default: 10000,
    			description: 'The milliseconds before a timeout occurs during the initial connection to the MySQL server.',
    		},
    		{
    			displayName: 'SSL',
    			name: 'ssl',
    			type: 'boolean' as NodePropertyTypes,
    			default: false,
    		},
    		{
    			displayName: 'Client Private Key',
    			name: 'clientPrivateKey',
    			typeOptions: {
    				alwaysOpenEditWindow: true,
    			},
    			displayOptions: {
    				show: {
    					ssl: [
    						true,
    					],
    				},
    			},
    			type: 'string' as NodePropertyTypes,
    			default: '',
    		},
    		{
    			displayName: 'Client Certificate',
    			name: 'clientCertificate',
    			typeOptions: {
    				alwaysOpenEditWindow: true,
    			},
    			displayOptions: {
    				show: {
    					ssl: [
    						true,
    					],
    				},
    			},
    			type: 'string' as NodePropertyTypes,
    			default: '',
    		},
    	];
    }

Later you can access those parameters in the execution function using this.getCredentials(). And, set the MySQL connection with them.

Before you start doing anything I would encourage you to skim through the following tutorial:

1 Like

@RicardoE105 Thanks for the suggestions, they have been very helpful!
I managed to open a PR for MySQL: https://github.com/n8n-io/n8n/pull/1644

I’ll try to do the same for the Postgresql credentials & node.
Thanks!

Ahh, really cool. I Will review it as soon as I can. Thanks for the contribution.