search

schemaspy / schemaspy

Database documentation built easy
http://schemaspy.org
GNU Lesser General Public License v3.0
3.21k stars 314 forks source link

Unable to connect Google Cloud SQL - mysql using SSL #561

Closed ShubhamTatvamasi closed 5 years ago

ShubhamTatvamasi commented 5 years ago

I am trying to connect mysql with SSL certificates, I have tried to use-connprops, but it's not working, I have 3 certificate files which I need to add client-cert.pem client-key.pem server-ca.pem.

This is how I connect using mysql:

mysql -u root -p -h 0.0.0.0 \
   --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem \
   --ssl-key=client-key.pem

docker-compose.yml

version: "3"
services:
  schemaspy:
    container_name: schemaspy
    image: schemaspy/schemaspy
    volumes:
      - ./keys:/keys
      - ./output:/output
      - ./schemaspy.properties:/schemaspy.properties

schemaspy.properties

# type of database. Run with -dbhelp for details
schemaspy.t=mysql
# database properties: host, port number, name user, password
schemaspy.host=0.0.0.0
schemaspy.port=3306
schemaspy.db=db
schemaspy.u=root
schemaspy.p=pass
# output dir to save generated files
schemaspy.o=/output
# db scheme for which generate diagrams
schemaspy.s=db

Current Behavior

$ docker-compose up
Starting schemaspy ... done
Attaching to schemaspy
schemaspy    | Running Main-Class org.springframework.boot.loader.JarLauncher
schemaspy    | With drivers:jtds-1.3.1.jar, mariadb-java-client-1.1.10.jar
schemaspy    | mysql-connector-java-6.0.6.jar, postgresql-42.1.1.jre7.jar
schemaspy    |   ____       _                          ____
schemaspy    |  / ___|  ___| |__   ___ _ __ ___   __ _/ ___| _ __  _   _
schemaspy    |  \___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
schemaspy    |   ___) | (__| | | |  __/ | | | | | (_| |___) | |_) | |_| |
schemaspy    |  |____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
schemaspy    |                                              |_|    |___/
schemaspy    |
schemaspy    |                                               6.0.0-SNAPSHOT
schemaspy    |
schemaspy    | SchemaSpy generates an HTML representation of a database schema's relationships.
schemaspy    | SchemaSpy comes with ABSOLUTELY NO WARRANTY.
schemaspy    | SchemaSpy is free software and can be redistributed under the conditions of LGPL version 3 or later.
schemaspy    | http://www.gnu.org/licenses/
schemaspy    |
schemaspy    | INFO  - Starting Main v6.0.0-SNAPSHOT on 8a3fbf871374 with PID 1 (/schemaspy-6.0.0-SNAPSHOT.jar started by java in /)
schemaspy    | INFO  - The following profiles are active: default
schemaspy    | INFO  - Found configuration file: schemaspy.properties
schemaspy    | INFO  - Started Main in 1.631 seconds (JVM running for 2.317)
schemaspy    | INFO  - Starting schema analysis
schemaspy    | Mon Jun 03 10:37:15 GMT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
schemaspy    | WARN  - Connection Failure
schemaspy exited with code 3
npetzall commented 5 years ago

You should probably use tag snapshot of the docker image. There are fixes in that tag.

Adding -debug will give you more information about the connection failure and I might be able to help you using that information.

If keys are needed and they are connection properties, they are easier to add using a properties file then as individual -connprop key\\=value

ShubhamTatvamasi commented 5 years ago

I have updated the docker-compose.yml

version: "3"
services:
  schemaspy:
    container_name: schemaspy
    image: schemaspy/schemaspy:snapshot
    volumes:
      - ./keys:/keys
      - ./output:/output
      - ./schemaspy.properties:/schemaspy.properties
    command: -debug -connprops useSSL\\=true;--ssl-ca\\=/keys/server-ca.pem;--ssl-cert\\=/keys/client-cert.pem;--ssl-key\\=/keys/client-key.pem

Output

$ docker-compose up schemaspy
Recreating schemaspy ... done
Attaching to schemaspy
schemaspy    | Running Main-Class org.springframework.boot.loader.JarLauncher
schemaspy    | With drivers:jtds-1.3.1.jar, mariadb-java-client-1.1.10.jar
schemaspy    | mysql-connector-java-6.0.6.jar, postgresql-42.1.1.jre7.jar
schemaspy    |   ____       _                          ____
schemaspy    |  / ___|  ___| |__   ___ _ __ ___   __ _/ ___| _ __  _   _
schemaspy    |  \___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
schemaspy    |   ___) | (__| | | |  __/ | | | | | (_| |___) | |_) | |_| |
schemaspy    |  |____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
schemaspy    |                                              |_|    |___/
schemaspy    |
schemaspy    |                                               6.1.0-SNAPSHOT
schemaspy    |
schemaspy    | SchemaSpy generates an HTML representation of a database schema's relationships.
schemaspy    | SchemaSpy comes with ABSOLUTELY NO WARRANTY.
schemaspy    | SchemaSpy is free software and can be redistributed under the conditions of LGPL version 3 or later.
schemaspy    | http://www.gnu.org/licenses/
schemaspy    |
schemaspy    | INFO  - Starting Main v6.1.0-SNAPSHOT on cb3fba3c4b2e with PID 1 (/schemaspy-6.1.0-SNAPSHOT.jar started by java in /)
schemaspy    | INFO  - The following profiles are active: default
schemaspy    | INFO  - Found configuration file: schemaspy.properties
schemaspy    | INFO  - Started Main in 1.632 seconds (JVM running for 2.309)
schemaspy    | DEBUG - Debug enabled
schemaspy    | INFO  - Loaded configuration from schemaspy.properties
schemaspy    | INFO  - Starting schema analysis
schemaspy    | DEBUG - Resolving dbType: mysql ->
schemaspy    |  /schemaspy-6.1.0-SNAPSHOT.jar!/BOOT-INF/classes!/org/schemaspy/types/mysql.properties
schemaspy    | DEBUG - DbSpecificOption name: 'hostOptionalPort' value: '0.0.0.0:3306' description: 'null'
schemaspy    | DEBUG - DbSpecificOption name: 'db' value: 'db' description: 'database name'
schemaspy    | WARN  - Connection Failure
schemaspy    | org.schemaspy.input.dbms.exceptions.ConnectionFailure: Failed to connect to database URL [jdbc:mysql://0.0.0.0:3306/db] Communications link failure
schemaspy    |
schemaspy    | The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
schemaspy    |  at org.schemaspy.input.dbms.DbDriverLoader.getConnection(DbDriverLoader.java:101)
schemaspy    |  at org.schemaspy.input.dbms.DbDriverLoader.getConnection(DbDriverLoader.java:75)
schemaspy    |  at org.schemaspy.input.dbms.service.SqlService.connect(SqlService.java:70)
schemaspy    |  at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:220)
schemaspy    |  at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:123)
schemaspy    |  at org.schemaspy.cli.SchemaSpyRunner.runAnalyzer(SchemaSpyRunner.java:98)
schemaspy    |  at org.schemaspy.cli.SchemaSpyRunner.run(SchemaSpyRunner.java:87)
schemaspy    |  at org.schemaspy.Main.main(Main.java:55)
schemaspy    |  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
schemaspy    |  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
schemaspy    |  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
schemaspy    |  at java.lang.reflect.Method.invoke(Method.java:498)
schemaspy    |  at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
schemaspy    |  at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
schemaspy    |  at org.springframework.boot.loader.Launcher.launch(Launcher.java:50)
schemaspy    |  at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:51)
schemaspy    | Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
schemaspy    |
schemaspy    | The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
schemaspy    |  at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:590)
schemaspy    |  at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:57)
schemaspy    |  at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1606)
schemaspy    |  at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633)
schemaspy    |  at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347)
schemaspy    |  at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219)
schemaspy    |  at org.schemaspy.input.dbms.DbDriverLoader.getConnection(DbDriverLoader.java:94)
schemaspy    |  ... 15 common frames omitted
schemaspy    | Caused by: com.mysql.cj.core.exceptions.CJCommunicationsException: Communications link failure
schemaspy    |
schemaspy    | The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
schemaspy    |  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
schemaspy    |  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
schemaspy    |  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
schemaspy    |  at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
schemaspy    |  at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54)
schemaspy    |  at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:93)
schemaspy    |  at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:133)
schemaspy    |  at com.mysql.cj.core.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:149)
schemaspy    |  at com.mysql.cj.mysqla.io.MysqlaProtocol.negotiateSSLConnection(MysqlaProtocol.java:309)
schemaspy    |  at com.mysql.cj.mysqla.authentication.MysqlaAuthenticationProvider.negotiateSSLConnection(MysqlaAuthenticationProvider.java:769)
schemaspy    |  at com.mysql.cj.mysqla.authentication.MysqlaAuthenticationProvider.proceedHandshakeWithPluggableAuthentication(MysqlaAuthenticationProvider.java:482)
schemaspy    |  at com.mysql.cj.mysqla.authentication.MysqlaAuthenticationProvider.connect(MysqlaAuthenticationProvider.java:204)
schemaspy    |  at com.mysql.cj.mysqla.io.MysqlaProtocol.connect(MysqlaProtocol.java:1414)
schemaspy    |  at com.mysql.cj.mysqla.MysqlaSession.connect(MysqlaSession.java:132)
schemaspy    |  at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1726)
schemaspy    |  at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1596)
schemaspy    |  ... 19 common frames omitted
schemaspy    | Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
schemaspy    |  at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
schemaspy    |  at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1949)
schemaspy    |  at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:302)
schemaspy    |  at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:296)
schemaspy    |  at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1509)
schemaspy    |  at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:216)
schemaspy    |  at sun.security.ssl.Handshaker.processLoop(Handshaker.java:979)
schemaspy    |  at sun.security.ssl.Handshaker.process_record(Handshaker.java:914)
schemaspy    |  at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1062)
schemaspy    |  at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1375)
schemaspy    |  at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1403)
schemaspy    |  at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1387)
schemaspy    |  at com.mysql.cj.core.io.ExportControlled.transformSocketToSSLSocket(ExportControlled.java:156)
schemaspy    |  at com.mysql.cj.mysqla.io.MysqlaProtocol.negotiateSSLConnection(MysqlaProtocol.java:304)
schemaspy    |  ... 26 common frames omitted
schemaspy    | Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
schemaspy    |  at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:387)
schemaspy    |  at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:292)
schemaspy    |  at sun.security.validator.Validator.validate(Validator.java:260)
schemaspy    |  at sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:324)
schemaspy    |  at sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:229)
schemaspy    |  at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:124)
schemaspy    |  at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1491)
schemaspy    |  ... 35 common frames omitted
schemaspy    | Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
schemaspy    |  at sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)
schemaspy    |  at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)
schemaspy    |  at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:280)
schemaspy    |  at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:382)
schemaspy    |  ... 41 common frames omitted
schemaspy exited with code 3
npetzall commented 5 years ago

Hmmm, I found some stuff on the interwebs that I'm gonna look into.

But the -connprops should be something like

-connprops useSSL\\=true;clientCertificateKeyStoreUrl\\=/keys/client-cert.pem;trustCertificateKeyStoreUrl=/keys/server-cert.pem

From the top of my head I'm not sure if pem is supported, also in some cases (haproxy) cert and key needs to be in same file.

I'll look into this when kids are a sleep (about 1hr).

If you want to look into it on your own, if I remember correctly Java comes with a key tool so that you can create the required two files, client store and server.

ShubhamTatvamasi commented 5 years ago

This method is also not working. :(

npetzall commented 5 years ago

I hope you noticed that server-cert.pem should have been server-ca.pem.

Also since it's certificates does server-ca.pem contain root and any intermediate certifications that might be needed to verify the server certificate.

Did you try the keytool approach?

ShubhamTatvamasi commented 5 years ago

Yes I changed that, I have also tried keytool approach it also didn't work.

npetzall commented 5 years ago

So, this is gonna be a pain. But I got it working.

Source is but I'm gonna write the steps https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-using-ssl.html

Convert CA pem into Keystore: keytool -importcert -alias MysqlCACert -file server-ca.pem -keystore serverTrust.jks -storepass password

You can choose a different password but you'll need it for the connection string later on. Convert Client-Cert and Key into Keystore: 1, Cert and Key to p12 openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -name "mysqlclient" -passout pass:password -out client-keystore.p12 2, p12 to keystore keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 -srcstorepass password -destkeystore clientkeystore.jks -deststoretype JKS -deststorepass password

Move serverTrust.jks and clientkeystore.jsk into keys folder that you mount at /keys in the container.

Finally use this with schemaspy and mysql jdbc: -connprops "useSSL\\=true;clientCertificateKeyStoreUrl\\=file:/keys/clientkeystore.jks;clientCertificateKeyStorePassword\\=password;trustCertificateKeyStoreUrl\\=file:/keys/serverTrust.jks;trustCertificateKeyStorePassword\\=password"

If the db user doesnt have a password, specify the user, omitt the password and use -sso to get single sign on. (Remove the requirement for password)

ShubhamTatvamasi commented 5 years ago

This method worked, Thanks man, thanks a bunch. :pray: