StackQL Server with PostgreSQL Backend

Table of Contents

1. Architecture
2. Deployment Options
   • Container Deployment Modes
   • Database Configuration Modes
   • Client/Server Authentication Modes
3. Authenticating to Cloud Providers
4. Building the Container
5. Running the Container
   • Without mTLS (SECURE_MODE=false)
   • With mTLS (SECURE_MODE=true)
6. Running the Container in Azure Container Instances (ACI)

Architecture

The architecture consists of two primary components:

1. StackQL Server: A server that starts a StackQL server, accepting StackQL queries using the PostgreSQL wire protocol.
2. PostgreSQL Server: A backend database server used for relational algebra and temporary storage, particularly for materialized views.

flowchart TD;
subgraph Docker_or_ACI["Docker or Azure Container Instances (ACI)"];
B[StackQL Server];
C["Local PostgreSQL Instance\n(if POSTGRES_HOST == 127.0.0.1)"];
B <-- uses --> C;
end;
A[StackQL Client] <-- uses --> B;
B <-- gets data from\nor interacts with --> E[Cloud/SaaS Providers];
%% KV[Azure Key Vault] -.->|"Stores Secrets\nfor SECURE_MODE\n(if KEYVAULT_NAME && KEYVAULT_CREDENTIAL)"| B;
B <-.->|if POSTGRES_HOST != 127.0.0.1| RemoteDB["Remote PostgreSQL Database"];

Loading

Deployment Options

The different deployment options are as follows:

Container Deployment Modes

• Deployment via ACI: Leverages Azure Container Instances for scalable, cloud-native deployments.
• Deployed using docker run: Ideal for containerized environments, ensuring consistency and portability across different systems.

Database Configuration Modes

• Local DB Mode:
  • Activated when POSTGRES_HOST is set to 127.0.0.1 (default).
  • Runs a local, embedded PostgreSQL backend database.
• Remote DB Mode:
  • Triggered when POSTGRES_HOST is set to any value other than 127.0.0.1.
  • Connects to an externally hosted PostgreSQL database.

Client/Server Authentication Modes

• mTLS Authentication:
  • Enabled by setting SECURE_MODE=true (default is false).
  • Utilizes mutual TLS (mTLS) for enhanced security in communications.
• Keys and Certificates:
  • Can be directly copied into the container.
  • Alternatively, sourced from Azure Key Vault if KEYVAULT_NAME and KEYVAULT_CREDENTIAL are provided.

Authenticating to Cloud Providers

Populate the necessary environment variables to authenticate with your specific cloud providers. For more information on which environment variables to populate, see the StackQL provider registry documentation.

Building the Container

To build the container, run the following command:

docker build --no-cache -t stackql-server .

Running the Container

Without mTLS (SECURE_MODE=false)

To run the container locally without mTLS, use the following command:

# Use -e to supply provider credentials as needed (GitHub credentials used in this example)
docker run -d -p 7432:7432 \
-e STACKQL_GITHUB_USERNAME \
-e STACKQL_GITHUB_PASSWORD \
stackql-server

# or if using the Dockerhub image...
docker run -d -p 7432:7432 \
-e STACKQL_GITHUB_USERNAME \
-e STACKQL_GITHUB_PASSWORD \
stackql/stackql-server

To connect to the server (not configured for mTLS), use the following command:

export PGSSLMODE=allow # or disable
psql -h localhost -p 7432 -U stackql -d stackql

To stop the container, use the following command:

docker stop $(docker ps -a -q --filter ancestor=stackql-server)

# or if using the Dockerhub image...

docker stop $(docker ps -a -q --filter ancestor=stackql/stackql-server)

With mTLS (SECURE_MODE=true)

To prepare certificates and keys, run the following commands:

# Follow these steps to generate Root CA, Server Cert, and Client Cert
openssl req -x509 -keyout creds/server_key.pem -out creds/server_cert.pem -config creds/openssl.cnf -days 365
openssl req -x509 -keyout creds/client_key.pem -out creds/client_cert.pem -config creds/openssl.cnf -days 365
chmod 400 creds/client_key.pem

To run the container locally with mTLS, use the following command:

docker run -d -p 7432:7432 \
-e STACKQL_GITHUB_USERNAME \
-e STACKQL_GITHUB_PASSWORD \
-e SECURE_MODE=true -v $(pwd)/creds:/opt/stackql/srv/credentials \
stackql-server

# or if using the Dockerhub image...

docker run -d -p 7432:7432 \
-e STACKQL_GITHUB_USERNAME \
-e STACKQL_GITHUB_PASSWORD \
-e SECURE_MODE=true -v $(pwd)/creds:/opt/stackql/srv/credentials \
stackql/stackql-server

To connect to the server (configured for mTLS), use the following command:

PGSSLCERT=creds/client_cert.pem
PGSSLKEY=creds/client_key.pem
PGSSLROOTCERT=creds/server_cert.pem
PGSSLMODE=require
psql -h localhost -p 7432 -d stackql

Running the Container in Azure Container Instances (ACI)

To deploy the container in Azure Container Instances (ACI) using an image from Docker Hub, you can follow these steps:

1. Create an Azure Container Instance: To create an instance, use the Azure CLI. Replace values for name, resource-group, and dns-name-label with your specific details. The --dns-name-label should be a unique DNS name for the ACI.

SERVER_CERT=$(base64 -w 0 creds/server_cert.pem)
SERVER_KEY=$(base64 -w 0 creds/server_key.pem)
CLIENT_CERT=$(base64 -w 0 creds/client_cert.pem)

az container create \
--name stackqlserver \
--resource-group stackql-activity-monitor-rg \
--image docker.io/stackql/stackql-server:latest \
--dns-name-label stackql \
--ports 7432 \
--protocol TCP \
--environment-variables \
SECURE_MODE=true \
SERVER_CERT=$SERVER_CERT \
SERVER_KEY=$SERVER_KEY \
CLIENT_CERT=$CLIENT_CERT

Replace the environment variable values with the ones you need for your setup.

2. Retrieve the Fully Qualified Domain Name (FQDN) of the ACI: After the ACI is successfully deployed, retrieve its FQDN:

az container show \
--name stackqlserver \
--resource-group stackql-activity-monitor-rg \
--query ipAddress.fqdn \
--output tsv

4. Connect to the Server: Use the FQDN obtained above to connect to your StackQL server using a PostgreSQL client. For connections over mTLS, ensure that the client machine has the necessary client certificates configured, for example:

export PGSSLCERT=creds/client_cert.pem
export PGSSLKEY=creds/client_key.pem
export PGSSLROOTCERT=creds/server_cert.pem
export PGSSLMODE=require
psql -h stackql.eastus.azurecontainer.io -p 7432 -d stackql

5. Monitor the Container Instance:

To quickly check the logs of your container instance, you can use the Azure CLI:

az container logs \
--resource-group stackql-activity-monitor-rg \
--name stackqlserver

This command retrieves the logs produced by the container.

6. Delete the Container Instance:

To delete the container instance, use the Azure CLI:

az container delete \
--resource-group stackql-activity-monitor-rg \
--name stackqlserver