ZDBC: The Future of Private Database Access

INTRODUCTION

Not that much ago Google sent me an email because they'll retire Google IoT Core in August 2023. I actually have a small project of mine running there (maybe I'll talk about it in the future); thing is, I looked into AWS as part of the improvements I'm basically using some Databases Services (Aurora with PGSQL Backend and Redshift).

As still I do have some things running on Google and there're some communications between those two I needed to provided i decided to improve my communications using my new OpenZiti network. ZDBC is an implementation of JDBC that leverages the communication to my Ziti network to provide secure and connections to databases.

Alright, enough talking and let's get into, so, let me introduce my architecture:

OpenZiti/ZDBC Architecture

As you can see, my architecture, is quite simple. I do have my Controller and Fabric Router located in my Data Center. In my VCN in AWS I deployed a Virtual Machine with a Edge Router (I'll put the commands on how to add it to my deployment in a few). This Edge Router is the only one with access to the Aurora-PGSQL in servlerless mode I have and of course to the RedShift Instance I installed as well. So, as you can see with this, it's not necessary to make those instances publicy available.

With the architecture in place, let's discuss the how to, for that I'll divide it into sections.

EDGE ROUTER CONFIGURATION

As we have seen the first part involves create and configure our Private Edge Router. So let's see what it involves:

As we're creating a Private Edge Router, we first need an EC2 instance. Ideally in the same VPC as our databases, but you can choose and then add the policies needed.

After ssh into the server that will work as our private ER. We'll create an environmental file that will contain a few specs about our infra. This is not 100% necessary but it does make it easier. This file basically contains locations of the binaries and of course across this entry we'll use those variables to refer some specific places. Let's call this file private.env

 export ZITI_BIN_DIR="/opt/openziti/edgerouter/ziti-bin/ziti-v0.27.9"  
 export ZITI_BIN_ROOT="/opt/openziti/edgerouter/ziti-bin/ziti-bin"  
 export ZITI_EDGE_ROUTER_HOSTNAME="private-er.mydomain.me"  
 export ZITI_EDGE_ROUTER_IP_OVERRIDE="10.10.10.10"  
 export ZITI_EDGE_ROUTER_PORT="3022"  
 export ZITI_EDGE_ROUTER_RAWNAME="private-er"  
 export ZITI_HOME="/opt/openziti/edgerouter"  
 export ZITI_HOME_OS_SPECIFIC="/opt/openziti/edgerouter"  
 export ZITI_NETWORK="mynetwork"  
 export ZITI_PKI="/opt/openziti/edgerouter/pki"  
 export ZITI_PKI_OS_SPECIFIC="/opt/openziti/edgerouter/pki"  
 export ZITI_QUICKSTART_ENVROOT="/opt/openziti/edgerouter/"  
 export ZITI_SHARED="/opt/openziti/edgerouter/"  
 export PFXLOG_NO_JSON=true  
 if [[ ! "$(echo "$PATH"|grep -q "${ZITI_BIN_DIR}" && echo "yes")" == "yes" ]]; then  
  echo "adding ${ZITI_BIN_DIR} to the path"  
  export PATH=$PATH:"${ZITI_BIN_DIR}"  
 else  
 echo  "         ziti binaries are located at: ${ZITI_BIN_DIR}"  
 echo -e 'add this to your path if you want by executing: export PATH=$PATH:'"${ZITI_BIN_DIR}"  
 echo " "

Now, we need to download the binary, at this moment of this writing the latest ziti version is the 0.27.9. After doing that we' uncompress it.

 source private.env
 mkdir -p $ZITI_BIN_DIR  
 wget https://github.com/openziti/ziti/releases/download/v0.27.9/ziti-linux-amd64-0.27.9.tar.gz  
 tar xzf ziti-linux-amd64-0.27.9.tar.gz  
 mv ziti/* $ZITI_BIN_DIR  
 cd $ZITI_HOME

Now, let's create and configure our private ER, for that first we need to create our configuration file, this command will create it, as a private router. For more information about this command line, refer to the official documentation.

 ziti create config router edge --routerName private-er --lanInterface eth0 --private --tunnelerMode tproxy > $ZITI_HOME/private-er.yml

Then, we need to edit the configuration, because we already set some variables the changes in the file won't be that extensive. So open your file in any editor and edit the lines related to your ER and environment. Here's a self-documented example, but as always if you have questions refer to the official documentation.

 ...  
 ...  
 ctrl:
  # Set the IP/DNS of your openziti controller
  endpoint:       tls:controller.mydomain.me:6262  
 ...  
 ...  
 listeners:  
 # bindings of edge and tunnel requires an "edge" section below  
  - binding: edge  
   address: tls:0.0.0.0:3022  
   options:  
       # Make sure this is called as your ER and your ER actually knows this name.  
    advertise: private-er.mydomain.me:3022  
    connectTimeoutMs: 1000  
    getSessionTimeout: 60  
  - binding: tunnel  
   options:  
    mode: tproxy #tproxy|host  
       # Here we set the localhost or private IP of the Edge Router  
    resolver: udp://10.10.10.10:53  
    lanIf: eth0  
 edge:  
 ...  
 ...  
       # Set all DNS and IP as your ER will be known for
    dns:  
     - private-er  
     - private-er.mydomain.me  
     - localhost  
    ip:  
     - "127.0.0.1"  
     - "10.10.10.10"  
 ...  
 ...

Now, login into your controller (remotely or locally, that's your choice) and proceed to create the router in the controller and generate the JWT that must be copied to your private Edge Router. For more information about this command line, refer to the official documentation.

 ziti edge create edge-router private-er --jwt-output-file private-er.jwt --tunneler-enabled --no-traversal

After you copied the JWT to your ER, it's time to enrroll it. Which is easy as you only need to execute this command, again for more information about this command line, refer to the official documentation.

 ziti-router enroll private-er.yml --jwt private-er.jwt

If the command is executed correctly you'll receive a JSON which will use to start our Edge Router. For that we'll use the following command:

 ziti-router run private-er.yml

systemctl

/etc/systemd/system

 [Unit]  
 Description=Ziti-Router for Private an ER  
 After=network.target  
 [Service]  
 User=root  
 WorkingDirectory=/opt/openziti/edgerouter  
 ExecStart="/opt/openziti/edgerouter/ziti-bin/ziti-v0.27.9/ziti-router" run "/opt/openziti/edgerouter/private-er.yml"  
 Restart=always  
 RestartSec=2  
 LimitNOFILE=65536  
 [Install]  
 WantedBy=multi-user.target

OPENZITI CONFIGURATION

With our Edge Router up and running, the next steps is to create the necessary elements in OpenZiti. So we'll start ir. For this I'll assume you already are logged in your ZAC or your network instance (if using the ziti cli).

We'll start with our Host-V1 configurations.

 # This is the command for the redshit service.  
 ziti edge create config "RedShift-Host.v1" host.v1 \  
  '{"protocol":"tcp", "address":"default.redshift-serverless","port":5439}'  
  
 # This is the command for the aurora service.  
 ziti edge create config "Aurora-Host.v1" host.v1 \  
  '{"protocol":"tcp", "address":"demo-instance-1.rds.amazonaws","port":5432}'

Then we'll create the Intercept configurations.

 # This is the command for the redshit service.  
 ziti edge create config "RedShift-Intercept.v1" intercept.v1 \  
  '{"protocols":["tcp"],"addresses":["default.redshift-serverless.amazonaws", "ziti.redshift.natashell.me"], "portRanges":[{"low":5439, "high":5439}]}'  
  
 # This is the command for the aurora service.  
 ziti edge create config "Aurora-Intercept.v1" intercept.v1 \  
  '{"protocols":["tcp"],"addresses":["demo-instance-1.rds.amazonaws", "ziti.aurora.natashell.me"], "portRanges":[{"low":5432, "high":5432}]}'

We then create the service and the required policies. To simplify future security management of our overlay, we use attributes.

 # This is the commands for the redshit service. We adding the attribute Database to the service.  
 ziti edge create service Redshit-Service --configs "RedShift-Host.v1,RedShift-Intercept.v1" --role-attributes "#Database" 
 
 # This is the commands for the aurora service. We adding the attribute Database to the service.  
 ziti edge create service Aurora-Service --configs "Aurora-Host.v1,Aurora-Intercept.v1" --role-attributes "#Database"  
 
 # Then, we create a Bind policy, in our case we'll bind the RedShift to the PrivateER we created in the last section.  
 ziti edge create service-policy Databases-Bind Bind --service-roles "#Database" --identity-roles "@private-er"  
 
 # Finally we create the Dial policy to allow the access to our Databases to those endpoints that actually will require the access. In our case those with the attribute "DBClients"  
 ziti edge create service-policy Databases-Dial Dial --service-roles "#Database" --identity-roles "#DBClients"

Finally, don't forget to create the endpoint you'll use to connect to the databases.

 ziti edge create identity device ZDBC-Client -o ZDBC-Client.jwt --role-attributes "#DBClients"

Thanks to the attributes, we can see that the policies are working as soon as we add the new endpoint(client).

 openziti@ctrl:~$ ziti edge policy-advisor services Redshit-Service -q  
 OKAY : private-er (2) -> Redshit-Service (2) Common Routers: (2/2) Dial: N Bind: Y  
 OKAY : ZDBC-Client (2) -> Redshit-Service (2) Common Routers: (2/2) Dial: Y Bind: N  
 
 openziti@ctrl:~$ ziti edge policy-advisor services Aurora-Service -q   
 OKAY : private-er (2) -> Aurora-Service (2) Common Routers: (2/2) Dial: N Bind: Y  
 OKAY : ZDBC-Client (2) -> Aurora-Service (2) Common Routers: (2/2) Dial: Y Bind: N

SQuirreL SQL CONFIGURATION

Now that we have all the pieces, we just need to start using our zdbc driver, for that we'll require a few steps:

Because SQuirreL SQL doesn't have the redshift driver embedded you need to Download it and save it into a directory, where we'll save all zdbc artifacts, called $ZDBC_HOME.

My Aurora Database is running with PostgreSQL, so in order to connect to it we'll need to Download the driver save it into $ZDBC_HOME.

You'll need the ZDBC Driver (At the time to write this post, the latest version is 0.24.0 however you can always look for the latest from here. This driver must be available in the $SQUIRREL_HOME\lib directory and $ZDBC_HOME.

SQuirrelSQL Ziti Library

Then, we need to create the Redshift-Ziti driver in our SQuirrelSQL profile, to do it click on "The Plus Icon" in the Drivers tab. Set a name and put the example JDBC URL to: zdbc:redshift://<endpoint>:<port>/<database>. Remember to add the redshift and ziti-jdbc driver. Finally change the Class Name to org.openziti.jdbc.ZitiDriver.
RedShift Ziti Driver
Next, we'll add the PGSQL-Ziti driver, to do it click on "The Plus Icon" in the Drivers tab. Set a name and put the example JDBC URL to: jdbc:ziti:postgresql:[<//host>[:<5432>/]]<database>. Remember to add the redshift and ziti-jdbc driver. Finally change the Class Name to org.openziti.jdbc.ZitiDriver.
PGSQL Ziti Driver
Then we create the RedShift Instance.

We set a name and we'll select the Redshift-Ziti driver we created in the step 4. We need to set the URL as well, remeber use the template: zdbc:redshift://<endpoint>:<port>/<database>. Then we click on "Properties".
Redshift Instance
In the properties we'll set zitiIdentityFile to the path where our enrolled identity created in the last section is located. Then we need to make sure the zitiDriverUrlPattern to ^zdbc:redshift:.* we need to make sure zitiDriverClassname to com.amazon.redshift.jdbc.Driver and the zitiDriverFeatures as seamless.
Redshift Properties
Finally we can test the connection and see the magic happens, while my workstation can't reach the redshift hostname, my SQuirreL SQL connects successfully.
Redshift Test

Next we create the Aurora (PGSQL) Instance.

We set a name and we'll select the PGSQL-Ziti driver we created in the step 5. We need to set the URL as well, remeber use the template: jdbc:ziti:postgresql:[<//host>[:<5432>/]]. Then we click on "Properties".
Aurora Instance
In the properties we'll set ontly the zitiIdentityFile to the path where our enrolled identity created in the last section is located.
Aurora Properties
Finally we can test the connection and see the magic happens, while my workstation can't reach the redshift hostname, my SQuirreL SQL connects successfully.
Aurora Test

FINAL NOTES

ZDBC is a powerful tool that bundles the cool edge networking capabilities of OpenZiti into a single library. This creates a robust and secure solution for connecting to remote databases. Connections can be initiated from client applications, such as SQuirreL SQL. However, ZDBC can be even more powerful when it is embedded directly within code. I plan to discuss this in future discussions.

Search This Blog

Zero Trust