Installing and Configuring TimescaleDB for Storing Audit Data

TimescaleDB is a time-series database that is built on top of PostgreSQL. It is designed to handle large amounts of time-series data and provide fast querying and aggregation capabilities. It is an open-source project and can be used on various platforms such as Linux, Windows and MacOS.

One of the key features of TimescaleDB is its ability to scale horizontally. This means that as data grows, you can add more machines to the cluster, rather than upgrading a single machine, to maintain fast query performance. This is achieved by using a technique called "time-series partitioning" where the data is automatically partitioned based on the time interval.

Another beneficial feature of TimescaleDB is its ability to handle large amount of data, it can handle Billion of rows, this is possible thanks to its hybrid storage engine, where the database uses disk-based storage for historical data, and RAM-based storage for recent data. This enables fast queries on recent data while still being able to query and analyze historical data.

At SecureAuth, to install and configure TimescaleDB, we use Helm - a popular package manager for Kubernetes that allows users to easily install and configure complex software such as TimescaleDB on a Kubernetes cluster. By using Helm to install TimescaleDB, users can take advantage of several benefits that make the process of deploying and managing TimescaleDB much simpler and more efficient.

Firstly, Helm provides a convenient way to define and manage the configuration of TimescaleDB, including the number of nodes, storage settings, and networking settings, in a single, easy-to-read file called a chart. This makes it easy to understand and modify the configuration of TimescaleDB as needed.

Additionally, Helm provides the ability to manage and upgrade the TimescaleDB deployment in a controlled and repeatable way, this means that any updates or upgrades to the TimescaleDB software can be easily rolled out to the cluster in a predictable manner, avoiding any possible disruption to the service.

When you install the SecureAuth platform on Kubernetes using Helm Charts, you can see that the TimescaleDB dependency is included in our kube-acp-stack Helm Chart.

If you wish to configure the connection between the SecureAuth platform and TimescaleDB configure the values.yaml file for your SecureAuth deployment and apply the changes. To learn more, see Configure Helm Charts

If your TimescaleDB deployment is configured incorrectly, you can see the following error message appearing in SecureAuth logs:

{"error":"failed to create database client: failed to connect to `host=timescale user=postgres database=acp`: hostname resolving error (lookup timescale on 1.0.0.0:1: server misbehaving)","level":"fatal","msg":"failed to connect to timescale database"}         

If there is a connection issue between the SecureAuth platform and TimescaleDB, you can see the following error message appearing in SecureAuth logs:

{"error":"failed to create database client: failed to connect to `host=acp-cockroachdb-public user=root database=defaultdb`: dial error (dial tcp 1.0.0.0:1: connect: connection refused)","level":"fatal","msg":"failed to connect to the database"}         

Before going to production environment, make sure your TimescaleDB deployment is hardened properly by implementing various security measures and best practices. The goal is to protect sensitive data and maintain the integrity, availability, and confidentiality of the datastore.

# Generate CA key and certificate
openssl req -new -nodes -text -out ca.csr -keyout ca.key -subj "/CN=timescaledb-ca"
openssl x509 -req -in ca.csr -text -out ca.crt -extensions v3_ca -signkey ca.key -days 3650

# Generate server key and certificate
openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=timescaledb-server"
openssl x509 -req -in server.csr -text -out server.crt -extensions v3_req -CA ca.crt -CA