Access to a Postgres server

🛡️ 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 link it to our connector
  3. Authenticate and Connect
  4. Kill the session and watch the recording
  5. Using the Border0 Desktop app to access your database
  6. Troubleshooting

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.

Now that we have a working database for our example it's time to create a database socket and link it to our connector.

  • In the Sockets page, click on new Database socket
  • set a name and optionally a description

Upstream Configuration
here we can configure how your connector can connect and authenticate to the upstream database server.

  • Select standard for Service type
  • For authentication select username and password.
  • Protocol: Postgres since we're working with a Postgres server
  • For hostname and port we'll use localhost and port 3306. Adjust as needed for your environment.
  • Credentials, in our example we used username postgres and password my-secret-pw Adjust as needed for your environment.
  • Connector: Select your connector from the list

That's it, now click create new socket button. And you should have access to your MySQL database.

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.

📘

Securing your credentials

In the preceding example, we configured Border0 with credentials to access an upstream database. These credentials are essential for the connector running in your environment to establish a secure connection. While Border0 stores these credentials in encrypted form, we strongly recommend that you keep them local to the connector.
You can have the Border0 connector integrated with your secret management solution for enhanced security and keep your secrets local. Learn more about this in our guide:

https://docs.border0.com/docs/using-secret-managers-to-store-credentials

🚧

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.

Troubleshooting
Should you see any issues with your new socket, it's typically because it can't connect to the upstream database. To troubleshoot that take a look at the connector logs as described here

3 - Authenticate and Connect

The easiest way to connect to your database is the client portal. If you prefer the CLI, then 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.

For Postgres, we currently support quick launch integration with psql and pgcli as well as the GUI tools dbeaver and datagrip

If your favorite client is not listed, you can always follow this process and use a Local Listener. Which allows you to use any client.

border0 client db

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

Now issue the command below to get access to all the database servers you have access to

border0 client db

After selecting the socket / database, you'll be asked what client you'd like to use.

We can also start our favorite client directly, like below, which will connect to the socket called mypsql-border0-demo.border0.io use psql to connect and connect to the postgres database

border0 client db:psql --host mypsql-border0-demo.border0.io postgres

You can use any client you'd like when using the local listener option.

border0 client db -l -p 1234 --host mypsql-border0-demo.border0.io This will start a listener on port 1234 on localhost. You can now use your favorite client and have it connect to localhost port 1234.

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.

5 -Using the Border0 Desktop app to access your database

Using the Border0 Desktop App, you'll be able to quickly discover all services (sockets) you have access to as an end-user.

6 - Troubleshooting

Should you see any issues with your new socket, it's typically because it can't connect to the upstream database. To troubleshoot that take a look at the connector logs as described here.

If that doesn't help resolve the issue, feel free to contact us, and we'd be happy to help you troubleshoot.