Connect to MySQL with custom SSL certs

Help me!
1

Hello guys,

I’m trying to connect a MySQL DB hosted on Google Cloud SQL, and exposed through SSL.

Expected behavior

Successful connection

Actual behavior

The SSL=true option is not enough, I need to provide the certs to the connection.

Is it possible to provide custom SSL certs to connect to MySQL with ForestAdmin please?

Thanks for you help

2

Hello @Pierre. Welcome in the community :wave:, and thanks for your feedback!

Could you explain me further what you are trying to achieve?

Do you have a problem with the generation of a first project with lumber-cli, or is it when you launch the project for the first time?

3

Hi @GuillaumeGautreau,
Yes I’m trying to generate my first project with the CLI and it cannot connect to my MySQL DB, which requires to provide custom SSL certs.

Any idea how to fix this please?

4

Thanks for the precision @Pierre. While I’m trying to reproduce your issue, could you please clarify the exact version number of lumber that you are using?

You can execute lumber --version in your console.

There was a bug about ssl connection that has been fixed in lumber-cli in version 3.7.0, I just want to be sure that you are using a version that is newer or equal to this one.

Thanks

5

In addition to that information, can you tell me if you can successfully connect to the SQL database locally, from the computer you are using to execute lumber generate?

Can you paste the error message you are obtaining? For the moment, with the latest version of lumber, ssl certificates are not verified so you don’t need to explicitly pass them to the CLI.

If you are using the last version of lumber, a connection error is more likely to occur because of an impossibility to contact the server, which is what I want to validate with you.

6

The lumber --version is 3.10.3

I can successfully connect from my computer to the DB with MySQL Workbench

And the error is:

✖ Connecting to your database
> Cannot connect to the database due to the following error:
> SequelizeAccessDeniedError: Access denied for user 'xxx'@'xxx.xxx.xxx.xxx' (using password: YES)

Maybe you could spin up a micro MySQL v8 instance on Google Cloud SQL, with SSL and exposed to the internet to reproduce this issue? otherwise feel free to send me a MP for a screenshare

7

Thanks @Pierre for the clarification.

The errors seems to indicate that there is no problem with the SSL certificates, but that your DB is not accepting the credentials you are sending, for the given IP address.

Can you check that:

  • the username and password you are using in mysql workbench and in lumber are the same?
  • that you are using the same way of connecting to the DB? Maybe you are using a SSH tunnel in workbench, that you are not using with lumber?
8

When I try to connect to the DB with SSL (in MySQL Workbench) without providing the certs and without verification, it doesn’t connect and returns the same error.

I strongly believe that it cannot connect without providing the certs (key, certificate, and CA).

PS: supporting SSL fully (with certs and verification) should not be an option, we are talking about DB access here!!

9

@Pierre can you add a screen capture of the configuration you are using in workbench to specify the certificates you are using?

10

Here is the working config:
Capture d’écran 2020-12-01 à 12.01.16

And here is the not working config:
Capture d’écran 2020-12-01 à 12.01.25

1 Like
11

SSL in MySQL is different than HTTP SSL, both the server and client(s) have their own pair of key/cert.

When SSL is “REQUIRED” for incoming connections (which is my case), the client needs to provide it’s own cert.

What Google Cloud SQL says in the config:
Capture d’écran 2020-12-01 à 12.15.02

What MySQL documentation says:

For accounts created with a REQUIRE X509 clause, clients must specify at least --ssl-cert and --ssl-key.

…

or accounts created with a REQUIRE ISSUER or REQUIRE SUBJECT clause, the encryption requirements are the same as for REQUIRE X509

PS: 6.3.1 Configuring MySQL to Use Encrypted Connections

How can we move forward, a feature request?

1 Like
12

Hi @Pierre,

It looks like indeed lumber does not support SSL certificates when generating a project. I’ll turn this need into a feature request. Thank you for outlining that missing feature!

However, by design, ForestAdmin is built to generate a local version of your admin panel first, plugged onto a local database. Then you create a production environment that you plug onto your production database.

This means that in your case, you should (and that’s supposed to be the preferred way) have a local copy of your production database (ideally with a lighter and anonymised content), on which you would generate your project using lumber. Then, when putting this development project into production, you’d be able to edit the sequelize connection defined in the models/index.js file to include your ssl certificates (as described here for example)

2 Likes
13

Hi @anon37102731,

Thanks for the details, I didn’t know the nitty-gritty details of Forest yet because I’ve been stuck at the project generation.

I will follow your recommendations to build the admin on a local DB! as long as the prod deployment can be secured properly with a small config tweak that’s fine :slight_smile:

1 Like