SQL Workbench/J + (Redshift / RDS)

SQL Workbench/J is Java program, recommended by AWS, to use for connecting to Redshift, if you’re planning to connect to any cluster that enforces SSL you must take a look at the documentation, the gist is the following (steps are assuming you’re using MacOS, Linux instructions should be really similar):

1. Download the certificate.
2. Import the certificate to your keystore, something like: keytool -keystore ${JAVA_HOME}/lib/security/cacerts -import -alias redshift -file <file.pem>; follow the prompt and make sure you use the default password changeit.
3. Update your sqlworkbench.sh to append the required java parameters to properly load the new store, something like this:

exec $JAVACMD \
  -Djavax.net.ssl.trustStore=$JAVA_HOME/lib/security/cacerts -Djavax.net.ssl.trustStorePassword=changeit \
  -Dvisualvm.display.name=SQLWorkbench -Xmx1024m -cp $cp workbench.WbStarter $@

Also, do not forget to append ssl=true to your connection string, and that should do it.

Please notice this is the best case scenario, I always prefer downloading the tar.gz from the manual downloads page add manually update the PATH to the user folder, in other cases when using the installer you will need to do more to accomplish what you just read above.

For Microsoft Windows things are bit simpler however you still have to consider permissions when modifying the cacerts file.

RDS

Similar to SQL Workbench/J, connecting to RDS+SSL requires:

1. Downloading the certificate
2. Updating your connecting string to:
3. Use certificate
4. Force the sslmode

In practical terms this means modifying your connecting string, in Rails you can easily do this by modifying the config/database.yml, in PostreSQL adding the following should be enough:

sslrootcert: /path/to/rds-combined-ca-bundle.pem
sslmode: require

For MySQL something similar:

sslca: /path/to/rds-combined-ca-bundle.pem
sslmode: 'REQUIRED'

Back to posts