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
- 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.
- 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.
- 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.
- 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.
- 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.
- 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:
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 " "
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
ziti create config router edge --routerName private-er --lanInterface eth0 --private --tunnelerMode tproxy > $ZITI_HOME/private-er.yml
...
...
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"
...
...
ziti edge create edge-router private-er --jwt-output-file private-er.jwt --tunneler-enabled --no-traversal
ziti-router enroll private-er.yml --jwt private-er.jwt
ziti-router run private-er.yml
Of course, you can always decide to use systemctl to take care of your service, and for that you can use this skeleton as template for your ziti-router service. This file is called ziti-router.service and it's located in /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.
- Then we'll create the Intercept configurations.
- We then create the service and the required policies. To simplify future security management of our overlay, we use attributes.
- Finally, don't forget to create the endpoint you'll use to connect to the databases.
- Thanks to the attributes, we can see that the policies are working as soon as we add the new endpoint(client).
# 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}'
# 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}]}'
# 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"
ziti edge create identity device ZDBC-Client -o ZDBC-Client.jwt --role-attributes "#DBClients"
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.
- 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.
SQuirrelSQL Ziti Library |
- 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.
Comments
Post a Comment