🛡️ Intro

In this example, we'll secure access to a Postgres server using Border0.
We'll walk through the following steps together.

1. Launch a Postgres server (Docker)
2. Create a database socket and tunnel.
3. Authenticate and Connect
4. Kill the session and watch the recording
5. Using the Border0 Desktop app to access your database

1 Run a local Postgres server with docker

You can make any Postgres database server available with Border0; for testing purposes, we'll use a Postgres Docker container that we'll run locally as the server.

👍

It works through NAT and Firewalls

Don't worry if your laptop is behind NAT. Border0 works perfectly fine with resources that are behind NAT.

Let's start the docket container like this

docker run --name postgres-server \
  -e POSTGRES_PASSWORD=my-secret-pw -d -p 5432:5432  postgres

This will start a local Postgres server listening on port 5432, with my-secret-pw as password for the user postgres.
Before we continue, let's just make sure if it came up ok and test connecting to the Postgres database locally as user postgres and password my-secret-pw

$ psql -h localhost -U postgres
Password for user postgres:
psql (14.5)
Type "help" for help.

postgres=#

Cool! Now we have a working server and can start making it available!

2 - Create a database socket and connect.

Let's create a database socket and set the upstream type to Postgres. We'll need the credentials, so our proxy can connect to the database.

border0 socket create \
    --type database \
    --name "postgrestest" \
    --upstream_type postgres

Note that we're setting Postgres as the upstream type.
Now connect your newly created socket to the Border0 Platform.

border0 socket connect  \
    postgrestest \
    --port 5432 \
    --upstream_username postgres \
    --upstream_password my-secret-pw

Note: that the socket_id was printed when you created the socket in the previous step.
Port 5432 is the port our Postgres test server is listening on (i.e your Docker container). We're forwarding all traffic for this socket to that container.

Now your database is ready to accept connections through border0. Clients can access your database from anywhere (no VPN needed), just using their Single sign-on credentials.

🚧

border version

If you use a border0 version older than v1.1-146 (border0 version check), you must specify the upstream_username and upstream_password flags with the border0 socket create command instead of
the border0 socket connect command.

🚧

Policy

Remember that we haven't yet attached a policy to this socket, so the default organization-wide policies are applied automatically.

By default, our proxy will try to establish a secure database connection using TLS. If the database server does not support TLS, it will fall back to a non-TLS connection.

For an even more secure connection, it's possible to specify the root CA to verify the server certificate (flag: upstream_ca_filename). And for certificate authentication, it is possible to use the flags upstream_certificate_filename and upstream_key_filename. To disable TLS, you can specify the --upstream_tls=false flag.

3 - Authenticate and Connect

The easiest way to connect is to use the border0 CLI. From there, it's easy to discover all your databases and launch your preferred database client.

First, make sure you're logged in to your organization.

border0 client login

📘

Organization name

If this is your first time issuing a client login, it will ask you for an Organization name. The Organization name is the part between the socket name and -border0.io. ie. <socketname>.<orgname>-border0.io

After you have the organization name, then login with
border0 client login --org <orgname>

The next time you log in, we'll remember your organization name, and you can just log in with border0 client login

Then request the list of all databases you have access to and pick your preferred client.

border0 client db

For Postgres, we currently support quick launch integration with psql and pgcli

4 - Connecting manually with your favorite database client

This section describes how to connect manually to the Postgres socket using Border0. For this we need to first login and then fetch some certificates.

First, make sure you're logged in to your organization.

border0 client login

Just to make, let's fetch a certificate. This is used to authenticate against the Border0 proxy

border0 client cert fetch --host postgrestest-acme.border0.io

Note: replace postgrestest-acme.border0.io, with your socket name. You will have to replace the acme part with your organization's name.

We can connect directly using the psql client like below. The key and cert file are created after you execute the border0 client login command. The hostname and port number were printed when you created the socket.

psql -h postgrestest-acme.border0.io --port <PORT>  \
 "sslmode=require sslcert=/Users/$USERNAME/.border0/<ORG_ID>crt  sslkey=/Users/$USERNAME/.border0/<ORG_ID>.key dbname=postgres"

Note that you will need to replace ORG_ID and PORT strings in the command above. These files were written to disk when you executed border0 client cert fetch above. It represents the organization id, and the files contain the TLS key and certificate that will be used to authenticate you.
You can see the files like this ls -rlt ~/.border0
You may also execute this to find your org id border0 organization show | grep ID
The port number was printed when you created the socket.

📘

Short cut

ORGID=$(border0 organization show | grep ID | awk '{print $4}')
psql -h postgrestest-acme.border0.io --port <PORT>  \
 "sslmode=require sslcert=/Users/$USERNAME/.border0/$ORGID.crt  sslkey=/Users/$USERNAME/.border0/$ORGID.key dbname=postgres"

4 - Kill the session and watch the recording

Go to the portal, and click on your database socket. Go to the sessions tab and kill your session.
Refresh the session's webpage, and now click watch recording.