Query control with a smart database proxy

OPTIONAL: If you prefer, you can watch this tutorial as a video (8:10), below.

But if you have Docker installed and running on your machine, you'll probably get more out of running it yourself (it takes about 15 minutes, depending on your download speed).

Introduction

Most applications that talk to a database use a well-defined set of queries.

Given this, it might be worthwhile to capture those queries while the application is being tested, and use that knowledge in production to make sure that no other queries are being run, to "lock down" the application and make sure it is behaving as expected.

This is easily done with a smart database proxy, using Redis to record the queries.

This tutorial will show you how to set this up, how to create the filters, and how to see them in action. This tutorial should take about 15 minutes.

Important: we'll assume that you have run one of the basic Gallium Data tutorials before you run this tutorial. If you haven't, we recommend you do that first, as we'll assume a minimum of familiarity with Gallium Data.

Architecture

The actors in this tutorial will be:

We also add:

We're going to define two filters in Gallium Data: a learning filter and an enforcement filter.

The learning filter has a simple workflow:

At some point, we'll turn off the learning filter and activate the enforcement filter, which has a different flow:

Step 1: Start Postgres, Gallium Data and PGAdmin

⇨ Run the following 4 commands:

⇨ 1

docker network create gallium-net

Note: this may fail with Error response from daemon: network with name gallium-net already exists, which can safely be ignored.

⇨ 2

docker run -d --name postgres-gallium --network gallium-net -e POSTGRES_PASSWORD=Password1 -e PGDATA=/pgdata galliumdata/gallium-data-demo-postgres:4

⇨ 3

docker run -d --name gallium-data --network gallium-net -p 8089:8080 -e repository_location=/galliumdata/repo_postgres galliumdata/gallium-data-engine:1.7.0-1448

⇨ 4

docker run -d --name pgadmin-gallium --network gallium-net -p 8088:80 galliumdata/gallium-data-demo-pgadmin:6

At this point, you should have all three Dockers running, just like in the basic tutorial:

It may be a good idea to verify that they are all up and running: 

$ docker ps --format "table {{.Names}}\t{{.Image}}\t{{.Ports}}"

NAMES              IMAGE                                      PORTS

pgadmin-gallium    galliumdata/gallium-data-demo-pgadmin:x    443/tcp, 0.0.0.0:8088->80/tcp

gallium-data       galliumdata/gallium-data-engine:xxx        0.0.0.0:8089->8080/tcp

postgres-gallium   galliumdata/gallium-data-demo-postgres:x   5432/tcp

Step 2: Make sure Postgres and Gallium Data are up and running:

⇨ Connect to pgAdmin at: http://localhost:8088 with user name  demo@galliumdata.com  and password Password1

⇨ Make sure you can browse the database:

If you can open the database, everything is fine. If not, you'll need to fix the problem before you can continue.

Step 3: Start Redis

Next, we need to start a Redis instance, and a RedisInsight client to browse its contents.

⇨ Run the following commands:

docker run --name gallium-redis --network gallium-net -d redis

docker run -d --name redis-insight --network gallium-net -p 8001:8001 -v redisinsight:/db redislabs/redisinsight:latest

⇨ Wait 10-20 seconds - RedisInsight takes a moment to start.

⇨ Connect to http://localhost:8001 (try again if it doesn't connect right away)

You'll be prompted to connect to an existing database, use the following information:

If you open that database and click on the Browser link on the left, you will see the (empty) Redis database. It won't be empty for long.

Step 4: Talking to Redis from a Gallium Data filter

We're going to create two filters in Gallium Data, which are going to talk to Redis, so clearly we need to have a Redis driver available. We'll use the lettuce library for that, so let's add it to our repository.

⇨ Open Gallium Data (http://localhost:8089)

⇨ Go to the Libraries page (left nav)

⇨ Select the Find tab

⇨ In the Organization field, enter io.lettuce

⇨ In the Artifact field, enter lettuce-core

⇨ Click Find Libraries, and you should see a list of available versions.

⇨ Select version 6.1.1-RELEASE and add it to the repository.

⇨ Click Publish (at the top)

Note that it takes longer than usual (it might take 10-30 seconds). This is because Gallium Data has to download the lettuce library and its dependencies. This delay only happens the first time you install a new library.

Step 5: Create the recording filter

In Gallium Data:

⇨  Click Repository (left nav)

⇨  Open the Simple Demo - PostgreSQL project and make sure that all its filters are inactive (i.e. greyed out) -- we want to keep things simple.

⇨ Create a new request filter of type Query filter - Postgres. Name it Record queries in Redis.

⇨ Make sure your filter is of type Query filter - Postgres

⇨ Set the code to:

log.info("Query: " + context.packet);
const sql = context.packet.getQuery();

if (!context.filterContext.redisAsync) {
    const RedisClient = Java.type("io.lettuce.core.RedisClient");

    const redisUrl = "redis://gallium-redis:6379/0";
    context.filterContext.redisAsync = RedisClient.create(redisUrl).connect().async();
}
context.filterContext.redisAsync.incr(sql);

The code creates a connection to Redis if it hasn't already, and puts that connection in the filter context, which is available to all invocations of this filter.

Then, it records the query by incrementing its count in Redis. As a bonus, we get to see how many times each query has been run. 

⇨ Click Publish (at the top) to put this filter into Gallium Data

Now we're going to run some queries so that they get recorded in Redis.

⇨ Go back to pgAdmin (if you closed the tab, it's at http://localhost:8088)

⇨ Select the postgres database in the left tree

⇨ Click the Query Tool icon (top left, see screenshot)

⇨ Run a few queries, e.g. :

select * from gallium_demo.customers

select * from gallium_demo.products where status is null

Hint: the F5 key will execute the query on most computers, otherwise use the arrow icon.

⇨ Go back to RedisInsight (if you closed the tab, it's at http://localhost:8001)

⇨ Click the Browser link (left nav)

⇨ Click the refresh button next to the funnel icon

You should see a few queries show up.

Note that you will not recognize some of these SQL statements. That's because they are sent automatically by pgAdmin.

Once you have run a few queries in pgAdmin, and you see them in Redis, you are ready for the last stage.

⇨ Go back to Gallium Data (if you closed the tab, it's at http://localhost:8089)

⇨ Deactivate this filter, since it should not run at the same time as the filter we're going to create next.

Step 6: Create the enforcement filter

⇨ Create a new request filter of type Query filter - Postgres. Name it Reject unknown queries

⇨ Set the code to :

log.info("Check query: " + context.packet);

if (!context.filterContext.redisSync) {
    const RedisClient = Java.type("io.lettuce.core.RedisClient");

    const redisUrl = "redis://gallium-redis:6379/0";
    context.filterContext.redisSync = RedisClient.create(redisUrl).connect().sync();
}

const sql = context.packet.getQuery();
if ( ! context.filterContext.redisSync.get(sql)) {
    context.result.success = false;
    context.result.errorMessage = "Unknown queries are not allowed";
}

The code connects to Redis if necessary, and then does a synchronous lookup of the SQL command. If the SQL is not found in Redis, then the filter rejects the request.

⇨ Click Publish to put this filter into Gallium Data.

⇨ Go back to pgAdmin

⇨ Run a few queries again, some that you've already run before, some new ones

The queries that are already recorded in the Redis database should run normally, but any queries that are not found in Redis will now be rejected with an error message.

You're done

Congratulations: you have created a query restriction system. It consists of two filters:

In a few steps, using off-the-shelf components and 15 lines of code, you have created a small but useful system that would be (at best) very difficult to create using any other solution. And that's just for starters!

Next steps

There is a lot more you could do at this point:

Cleanup

To stop everything started during this tutorial, run the following commands:

docker stop gallium-redis
docker stop redis-insight
docker stop gallium-data
docker stop pgadmin-gallium
docker stop postgres-gallium
docker rm gallium-redis
docker rm redis-insight
docker rm gallium-data
docker rm pgadmin-gallium
docker rm postgres-gallium
docker network rm gallium-net

This will stop all the Docker containers started during this tutorial.

If you also want to remove the Docker images:

docker rmi galliumdata/gallium-data-demo-pgadmin:6
docker rmi galliumdata/gallium-data-engine:1.7.0-1448
docker rmi galliumdata/gallium-data-demo-postgres:4
docker rmi redislabs/redisinsight:latest
docker volume rm redisinsight
docker rmi redis

This will remove everything installed by this tutorial.