Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
43 changes: 36 additions & 7 deletions Dockerfile
Original file line number Diff line number Diff line change
@@ -1,18 +1,47 @@
# Use stackql image
FROM stackql/stackql:latest as stackql

# Use postgres image
FROM postgres:latest as postgres

# Environment variables for postgres backend
ENV POSTGRES_HOST=127.0.0.1
ENV POSTGRES_PORT=5432
ENV POSTGRES_USER=stackql
ENV POSTGRES_PASSWORD=stackql
ENV POSTGRES_DB=stackql

# Environment variable to toggle SECURE_MODE
ENV SECURE_MODE=false

# Environment variable for Key Vault name for SECURE_MODE, if local use local cert and key files
ENV KEYVAULT_NAME=local
ENV KEYVAULT_CREDENTIAL=notset

# Environment variable for StackQL server configuration
ENV PGSRV_PORT=7432

# Copy initialization script for database
COPY ./init-db.sh /docker-entrypoint-initdb.d/init-db.sh
RUN chmod +x /docker-entrypoint-initdb.d/init-db.sh

# Copy stackql binary
COPY --from=stackql /srv/stackql/stackql /srv/stackql/stackql
RUN apt-get update && apt-get install -y ca-certificates && update-ca-certificates
EXPOSE 7432

# Environment variables for StackQL server
ENV PGSRV_PORT=7432
ENV SQL_BACKEND_JSON='{"dbEngine": "postgres_tcp", "sqlDialect": "postgres", "dsn": "postgres://stackql:stackql@127.0.0.1:5432/stackql"}'
# Install certificates

RUN apt-get update && \
apt-get install -y curl jq ca-certificates && update-ca-certificates

# Expose port
EXPOSE $PGSRV_PORT

# Volume for certificates
VOLUME ["/opt/stackql/srv/credentials"]

# Copy the startup script
COPY startup.sh /usr/local/bin/startup.sh
RUN chmod +x /usr/local/bin/startup.sh

# Start both PostgreSQL and StackQL server
CMD ["sh", "-c", "/usr/local/bin/docker-entrypoint.sh postgres & /srv/stackql/stackql srv --approot=/srv/stackql/.stackql --pgsrv.port=$PGSRV_PORT --sqlBackend='{\"dbEngine\": \"postgres_tcp\", \"sqlDialect\": \"postgres\", \"dsn\": \"postgres://stackql:stackql@127.0.0.1:5432/stackql\"}'"]
# Set the startup script as the entrypoint
ENTRYPOINT ["/usr/local/bin/startup.sh"]
245 changes: 187 additions & 58 deletions README.md
Original file line number Diff line number Diff line change
@@ -1,99 +1,228 @@
# StackQL Server with PostgreSQL Backend

# Table of Contents

1. [Architecture](#architecture)
2. [Deployment Options](#deployment-options)
- [Container Deployment Modes](#container-deployment-modes)
- [Database Configuration Modes](#database-configuration-modes)
- [Client/Server Authentication Modes](#clientserver-authentication-modes)
3. [Authenticating to Cloud Providers](#authenticating-to-cloud-providers)
4. [Building the Container](#building-the-container)
4. [Running the Container](#running-the-container)
- [Without mTLS (`SECURE_MODE=false`)](#without-mtls-secure_modefalse)
- [With mTLS (`SECURE_MODE=true`)](#with-mtls-secure_modetrue)
5. [Running the Container in Azure Container Instances (ACI)](#running-the-container-in-azure-container-instances-aci)
- [Push Image to Azure Container Registry (ACR)](#push-image-to-azure-container-registry-acr)
- [Start ACI Container](#start-aci-container)
- [Set Secrets in Azure Key Vault (AKV)](#set-secrets-in-azure-key-vault-akv)
- [Retrieve Certificates using KEYVAULT_NAME and KEYVAULT_CREDENTIAL](#retrieve-certificates-using-keyvault_name-and-keyvault_credential)
- [Getting Fully Qualified Domain Name (FQDN) of the ACI](#getting-fully-qualified-domain-name-fqdn-of-the-aci)
- [Connecting to the Server](#connecting-to-the-server-2)

## Architecture

The architecture involves two primary components:
The architecture consists of two primary components:

1. **StackQL Server**: This server starts a [__`stackql`__](https://github.com/stackql/stackql) server accepting stackql queries using the PostgreSQL wire protocol.
2. **PostgreSQL Server**: Backend database server used for relational algebra and temporary storage (for materalized views).
1. **StackQL Server**: A server that starts a [StackQL](https://github.com/stackql/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.

```mermaid
graph TD;
subgraph Container;
subgraph Docker_or_ACI["Docker or Azure Container Instances (ACI)"];
B[StackQL Server];
C[PostgreSQL Instance];
C["Local PostgreSQL Instance\n(if POSTGRES_HOST == 127.0.0.1)"];
B <-- uses --> C;
end;
A[StackQL Client] <-- postgre wire protocol port 7432 --> B;
B <-- gets data from --> E[Cloud/SaaS Providers];
B <-- uses --> C;
A[StackQL Client] <-- PostgreSQL wire protocol port 7432\n(SECURE_MODE uses mTLS) --> 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;

%% Positioning the remote DB at the bottom
B <-.->|if POSTGRES_HOST != 127.0.0.1| RemoteDB["Remote PostgreSQL Database"];
```

## 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 to your specific cloud providers. For more information on which environment variables to populate, see the [__`stackql provider registry`__](https://github.com/stackql/stackql-provider-registry) documentation.
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](https://github.com/stackql/stackql-provider-registry) documentation.

## Running the Container from the DockerHub Image
## Building the Container

To run the container, execute the following command:
**To build:**
```bash
docker build --no-cache -t stackql-server .
```

## Running the Container

### Without mTLS (`SECURE_MODE=false`)

**To run locally:**
```bash
# use -e to supply provider creds as needed
# github creds are used in this example
# 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
```

## Building and Running the Container
**Connecting to the server:**
```bash
psql -h localhost -p 7432 -U stackql -d stackql
```

**To stop the container:**
```bash
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)
```

To run the container, execute the following command:
### With mTLS (`SECURE_MODE=true`)

**To prepare certificates and keys:**
```bash
# 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 locally:**
```bash
docker build --no-cache -t stackql-server .
# use -e to supply provider creds as needed
# github creds are used in this example
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
```

## Connecting to the StackQL Server
**Or using Dockerhub image:**
```bash
# 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 \
-e SECURE_MODE=true -v $(pwd)/creds:/opt/stackql/srv/credentials \
stackql/stackql-server
```

**Connecting to the Secure Server:**
```bash
export PGSSLCERT=creds/client_cert.pem
export PGSSLKEY=creds/client_key.pem
export PGSSLROOTCERT=creds/server_cert.pem
export PGSSLMODE=require
psql -h localhost -p 7432 -d stackql
```

## Running the Container in Azure Container Instances (ACI)

To submit a query to the StackQL server using `psql`, use the following command:
To deploy the container in Azure Container Instances (ACI) using an image from Docker Hub, you can follow these steps:

1. **Set Secrets in Azure Key Vault (AKV):**
If you're using Azure Key Vault for storing secrets, replace the `vault-name` and the values with your specific details:
```bash
psql -h localhost -p 7432 -U stackql -d stackql
# create secret for stackql server cert
az keyvault secret set \
--vault-name stackqlkv \
--name stackql_server_cert \
--value secretvalue
# create secret for stackql server key
az keyvault secret set \
--vault-name stackqlkv \
--name stackql_server_key \
--value secretvalue
# create secret for stackql client cert
az keyvault secret set \
--vault-name stackqlkv \
--name stackql_client_cert \
--value secretvalue
```

## Pull a Provider and Run a Query
2. **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.
```bash
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 \
POSTGRES_HOST=postgres-host \
POSTGRES_PORT=postgres-port \
POSTGRES_USER=postgres-user \
POSTGRES_PASSWORD=postgres-password \
POSTGRES_DB=postgres-db \
SECURE_MODE=false \
KEYVAULT_NAME=keyvault-name \
KEYVAULT_CREDENTIAL=keyvault-credential
```
Make sure to replace the environment variable values with the ones you need for your setup.

3. **Retrieve the Fully Qualified Domain Name (FQDN) of the ACI:**
After the ACI is successfully deployed, retrieve its FQDN:
```bash
az container show \
--name stackqlserver \
--resource-group stackql-activity-monitor-rg \
--query ipAddress.fqdn \
--output tsv
```
This FQDN is used to connect to your StackQL server.

4. **Connect to the Server:**
Use the FQDN obtained above to connect to your StackQL server using a PostgreSQL client:
```bash
psql -h stackql.eastus.azurecontainer.io -p 7432 -U stackql -d stackql
```
For connections over mTLS, ensure that the client machine has the necessary client certificates configured, for example:

```bash
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
```

With the `stackql-server` running on port `7432`, you can pull a provider and run a query. Having populated credentials for the `github` provider as an example in the previous code block, you can now pull the `github` provider and run a query, as shown here:
5. **Monitor the Container Instance:**

To quickly check the logs of your container instance, you can use the Azure CLI:
```bash
$ psql -h localhost -p 7432 -U stackql -d stackql
psql (14.9 (Ubuntu 14.9-0ubuntu0.22.04.1), server 0.0.0)
Type "help" for help.

stackql=> registry pull github;
OK

stackql=> select
JSON_EXTRACT(v.value, '$.count') as count,
JSON_EXTRACT(v.value, '$.uniques') as uniques,
JSON_EXTRACT(v.value, '$.timestamp') as date
from github.repos.view_traffic, json_array_elements_text(views) v
WHERE owner = 'stackql' AND repo = 'stackql'
ORDER BY date ASC;
count | uniques | date
-------+---------+----------------------
43 | 7 | 2023-11-18T00:00:00Z
121 | 9 | 2023-11-19T00:00:00Z
74 | 8 | 2023-11-20T00:00:00Z
64 | 7 | 2023-11-21T00:00:00Z
87 | 17 | 2023-11-22T00:00:00Z
85 | 9 | 2023-11-23T00:00:00Z
41 | 6 | 2023-11-24T00:00:00Z
55 | 6 | 2023-11-25T00:00:00Z
228 | 24 | 2023-11-26T00:00:00Z
137 | 13 | 2023-11-27T00:00:00Z
46 | 7 | 2023-11-28T00:00:00Z
41 | 9 | 2023-11-29T00:00:00Z
44 | 7 | 2023-11-30T00:00:00Z
101 | 19 | 2023-12-01T00:00:00Z
42 | 9 | 2023-12-02T00:00:00Z
(15 rows)

stackql=>
```
az container logs \
--resource-group stackql-activity-monitor-rg \
--name stackqlserver
```
This command retrieves the logs produced by the container.
6 changes: 6 additions & 0 deletions creds/.gitignore
Original file line number Diff line number Diff line change
@@ -0,0 +1,6 @@
# Ignore everything in this directory
*

# Except these files
!.gitignore
!openssl.cnf
24 changes: 24 additions & 0 deletions creds/openssl.cnf
Original file line number Diff line number Diff line change
@@ -0,0 +1,24 @@
[ req ]
default_bits = 4096
default_md = sha256
prompt = no
encrypt_key = no
distinguished_name = pg_server
# stackql_ca
[ pg_ca ]
countryName = "AU" # C=
stateOrProvinceName = "VIC" # ST=
localityName = "Melbourne" # L=
organizationName = "StackQL" # O=
organizationalUnitName = "Core Functions" # OU=
commonName = "pg_ca" # CN=
emailAddress = "krimmer@stackql.io" # CN/emailAddress=
# stackql_server
[ pg_server ]
countryName = "AU" # C=
stateOrProvinceName = "VIC" # ST=
localityName = "Melbourne" # L=
organizationName = "StackQL" # O=
organizationalUnitName = "Core Functions" # OU=
commonName = "127.0.0.1" # CN=
emailAddress = "krimmer@stackql.io" # CN/emailAddress=
Loading