In this blog post we will explore the Ockam command line interface, and see how we can connect a traditional web app to a PostgreSQL database, with minimal / no code changes. We will create a very basic Python Flask app that simply increments a counter in a PostgreSQL database. Then we will move the connection between the application and database through an Ockam secure channel. ockam What is Ockam? is a suite of libraries, command line tools, and managed cloud services for orchestrating end-to-end encryption, mutual authentication, key management, credential management, and authorization policy enforcement — all on a massive scale. Ockam's end-to-end guarantee authenticity, integrity, and confidentiality of all data-in-motion at the application layer. Ockam Rust secure channels If you store your data in a relational database, , graph database, or something similar, that data is probably private. And you probably don't want to expose it to the Internet. So you can resolve this issue by placing it inside a private subnet. However, now you have to manage network access control lists, security groups, or route tables to allow other machines to open a connection to the database. That is a lot of overhead. NoSQL With Ockam, network administrators don't have to update network access control lists, security groups, or route tables. Ockam applies fine-grained control to your services via Attribute-Based Access Control. And you can even like Okta to restrict who can access your services. integrate with an external identity provider In this blog post, we will explore the Ockam command line interface, and see how we can connect a traditional web app to a PostgreSQL database, with minimal / no code changes. We will create a very basic Python Flask app that simply increments a counter in a PostgreSQL database. Then we will move the connection between the application and database through an Ockam secure channel. ockam Our journey Before we get started, let's take a look at the steps we'll perform in this blog post. Use to install the Ockam application and create an Ockam project. This is the first prerequisite. ockam enroll Set up the PostgreSQL database. This is the second prerequisite. Then configure an Ockam "outlet" to the database server. We will learn more about this in the "connect the database" section below. Set up the web app (Python Flask). This is the third prerequisite. Then configure an Ockam "inlet" from the Python app. We will learn more about this in the "connect the web app" section below. Prerequisites In order to follow along, please make sure to install all the prerequisites listed below. Ockam Command Run to install this via . You'll then be able to run the CLI app in your terminal. brew install build-trust/ockam/ockam brew ockam Python, and libraries: Flash, psycopg2 Run to install this via . You'll then be able to run the command in your terminal. brew install python brew python3 Instructions on how to get the dependencies ( , ) are in the section below. Flask psycopg2 Python Code Postgresql Run via . You'll then be able to run the PostgreSQL database server on your machine on the default port of . Please make sure to follow 's instructions and add PostgreSQL to your path. brew install postgresql@15 brew 5432 brew Run to start the PostgreSQL server. brew services start postgresql@15 Then you can set a new password for the database user . Set this password to . The Python Code below uses as the connection string for the db driver. postgres password postgres:password@localhost The instructions below allow you to do this on and macOS: Linux In a terminal run to login to the database locally as the user. sudo -u postgres psql --username postgres --password --dbname template1 postgres Then type this into REPL: , and finally type . ALTER USER postgres PASSWORD 'password'; exit The Web App - Python Code The Python Flask web app increments a counter in a PostgreSQL database. The entire app fits in a single file. Create a file on your machine and copy and paste the code below into it. main.py import os
import psycopg2
from flask import Flask

CREATE_TABLE = (
    "CREATE TABLE IF NOT EXISTS events (id SERIAL PRIMARY KEY, name TEXT);"
)

INSERT_RETURN_ID = "INSERT INTO events (name) VALUES (%s) RETURNING id;"

app = Flask(__name__)
pg_port = os.environ['APP_PG_PORT'] # 5432 is the default port
url = "postgres://postgres:password@localhost:%s/"%pg_port
connection = psycopg2.connect(url)

@app.route("/")
def hello_world():
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(CREATE_TABLE)
            cursor.execute(INSERT_RETURN_ID, ("",))
            id = cursor.fetchone()[0]
    return "I've been visited {} times".format(id), 201 In this script, we use to establish a connection to the database. "postgres://postgres:password@localhost:%s/"%pg_port gets its value from the environment variable . pg_port APP_PG_PORT We will set the environment variable to before we run the Python script (instructions below). APP_PG_PORT 5432 So the database connection string simply points to . localhost:5432 Please make a note of the Python variable and environment variable. In production, we usually load the port from an environment variable and it is not hardcoded in the source. pg_port APP_PG_PORT Run the web app Follow the instructions below to run the web app. First, make sure to add the required Python dependencies with: # Install flask.
pip3 install flask
# Install psycopg2.
pip3 install psycopg2-binary Then start the app ( ) with: Flask main.py export APP_PG_PORT=5432
flask --app main run Finally, in a web browser open this URL: . http://localhost:5000/ Install Ockam Now that we have set up our web app and database let's do this next: Add Ockam to the mix. Update our environment variable so that it connects to a new port (not which is the where the PostgreSQL server runs). APP_PG_PORT 5432 First, let's run . Make sure that you've already installed the Ockam CLI as described in the prerequisites section above. ockam enroll In a terminal window, run this command and follow the prompts to complete the enrollment process (into Ockam Orchestrator). ockam enroll This is what the command does: ockam enroll It checks that everything is installed correctly after successful enrollment with Ockam Orchestrator. It creates a Space and Project for you in Ockam Orchestrator and provisions an End-to-End Encrypted Relay in your project at . default /project/default Connect the database Next, let's set up a that allows us to send raw TCP traffic to the PostgreSQL server on the port . Then create a relay in our default Orchestrator project. To do this, run these commands in your terminal. tcp-outlet 5432 export PG_PORT=5432
ockam tcp-outlet create --to $PG_PORT
ockam relay create Notes: We use environment variable here, and not (which is used in our web app). It points to the default PostgreSQL port of . In the section below we will change to a different value. PG_PORT APP_PG_PORT 5432 APP_PG_PORT We'll create the corresponding in the next section. tcp-inlet Relays allow you to establish end-to-end protocols with services that operate in remote private networks. They eliminate the need to expose ports on the remote service (to a hostile network like the Internet). Connect the web app Finally, let's setup a local so we can receive raw TCP traffic on port before it is forwarded. tcp-inlet 5433 export OCKAM_PORT=5433
ockam tcp-inlet create --from $OCKAM_PORT Notes: The new environment variable points to a new port . $OCKAM_PORT 5433 This is the port that the will listen on. And it is different from the default PostgreSQL port. tcp-inlet A TCP inlet is a way to define where a node listens for its connections. And then where it should forward that traffic to. An inlet and outlet work together to form a portal. Next, start your web app again with the commands below. export APP_PG_PORT=$OCKAM_PORT
flask --app main run Finally, connect to this URL again from your web browser . http://localhost:5000/ We have changed the to the same value as ( ). Our web app ( script) does not directly connect to the unsecure database server (on port ). It now goes through the secure channel 🔐. $APP_PG_PORT $OCKAM_PORT 5433 main.py 5432 The counter will continue to increment just as it did before, with zero code changes to your application. But the web app now communicates with the database through an Ockam secure channel 🎉. Multiple machines You can also extend this example and move the PostgreSQL service into a Docker container or to an entirely different machine. Once the nodes are registered (after runs), this demo will continue to work, with no application code changes and no need to expose the PostgreSQL ports directly to the Internet. ockam enroll Also, you can run the web app and the database on different machines. To do this: Change in the script to the IP address of the machine that hosts the database. localhost main.py Run on both machines (the web app machine and the database server machine). ockam enroll Explore other commands Now that you've completed this example, here are some commands for you to try and see what they do. You can always look up the details of what they do in the . As you try each of these, please keep an eye out for things you may have created in this exercise. manual Try . Do you see the nodes that you created in this exercise? ockam node list Try . These are shorter examples for you to get familiar with commands. ockam node --help Try . Do you see the that you created in this exercise? ockam node show web tcp-inlet Try . Do you see the that you created in this exercise? ockam node show db tcp-outlet Try . Do you see the identities you created in this exercise? ockam identity list Also published here.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Securely connect a web app to a remote Postgres database

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

A CTO’s Reflection of the 2023 RSA Conference

10 Principles of Proper Database Benchmarking

5 Most Common Data Quality Issues For Business

A Brief Introduction to MongoDB

A Comprehensive Guide for Using DuckDB With Go

A CTO’s Reflection of the 2023 RSA Conference

10 Principles of Proper Database Benchmarking

5 Most Common Data Quality Issues For Business

A Brief Introduction to MongoDB

A Comprehensive Guide for Using DuckDB With Go

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps