Database connector (optional)

Optional component

The database connector is optional and off by default. Avatar works fully without it: datasets uploaded as files (CSV, Parquet) are anonymized as usual. Enable it only if your users need database runs.

Database runs read a database and write the anonymized copy into another database. The workers that run the jobs do not open these databases themselves. A separate service, the database connector, holds every database driver and opens the databases. The API stores the connection strings; the connector receives one only for the time of a job; the workers never see one.

This page explains how to enable the connector on a Kubernetes installation. The connector is off by default. Without it, database runs reach no database, PostgreSQL included, and the demo databases are not offered.

Docker Compose

The deployment tool for Docker Compose does not install the connector yet. To use database runs on a Docker Compose installation, contact Octopize support.

How it works

worker ── short-lived token ──▶ connector ──▶ your database
                                   │
                                   └── "what does this name mean?" ──▶ API (internal port)

• A job carries the name of a connection and a token signed by the API. The token is valid for one connection, for one user, and for a limited time.
• The connector checks the token, then asks the API for the connection string on an internal port. That port is served by its own Service (avatar-api-internal), which no Ingress refers to, and a network policy limits it to the connector. The public port of the API does not serve this route.
• The connector keeps the connection string in memory for 30 seconds. It writes nothing to disk.
• Rows go from your database to the object storage of the job. They do not go through the API.
• The connector has no query endpoint. It lists the structure of a database, reads tables, writes into tables that already exist in a target, and deletes the rows of the tables that a run names. It does not create tables: the target must already have the schema. Error messages give a category (for example the database refused the credentials), never a host or a credential.

Supported engines

Engine	Connection string	Status
PostgreSQL	postgresql://user:password@host:5432/database	supported
MySQL	mysql://user:password@host:3306/database	supported
MariaDB	mariadb://user:password@host:3306/database	supported
Oracle	oracle://user:password@host:1521/SERVICE_NAME	supported (thin driver, no Oracle client)
SQL Server	mssql://user:password@host:1433/database	supported
ClickHouse	clickhouse://user:password@host:8123/database	supported (add ?secure=true for 8443)
MongoDB	mongodb://… or mongodb+srv://…	supported (collections read as tables)
Snowflake	snowflake://user:password@account/database/schema?warehouse=…&role=…	preview
Google BigQuery	bigquery://project/dataset?credentials_base64=…	preview, read only (source only)
IBM Db2	db2://user:password@host:50000/database	preview, not in the published image

A preview engine is offered, but it has not been proven against a real server yet. The web application marks it as a preview. The connector reports to the API the engines whose driver it carries, and the API only offers those.

IBM Db2 needs a driver that adds 135 MB and runs on amd64 only, so the published image leaves it out. To offer Db2, build the image yourself from the connector sources with --build-arg WITH_DB2=1 --platform linux/amd64, or contact Octopize support.

The image also contains two small demo databases (Northwind and Chinook). Your users can add them from Account > Database connections and try a database run before they connect their own databases. A copy of a demo target is deleted 24 hours after its last write, and when the connector restarts.

Before you start

• Chart version 0.0.100 or later. Version 0.0.99 added the connector, and 0.0.100 fixed its image pull secret.
• API version 3.18.0 or later. It is the first release with the internal port that the connector asks for connection strings.
• Network policies. The chart refuses to render the connector unless networkPolicy.enabled is true, because only the network policies keep the connector from reaching the rest of the cluster. Your cluster needs a CNI that enforces NetworkPolicy, such as Calico or Cilium.
• Access to the image. The image quay.io/octopize/avatar-connector is private. The chart pulls it with the same image pull credentials as the other Avatar images.

Enable the connector

1. Generate the secrets

The connector needs two secrets:

• connector.secret: shared by the API and the connector. It signs the tokens that jobs carry, and the connector authenticates to the API with it.
• databaseConnections.encryptionKeys: the Fernet key that encrypts the connections that your users and organizations store. Without it, users cannot store their own connections (the API answers 503), but the deployment connections still work.

scripts/generate-secrets.sh creates both, with the other secrets of the chart. See Generating the secrets. To make them yourself:

# connector.secret
python -c "import secrets; print(secrets.token_hex(), end='')"
# databaseConnections.encryptionKeys
python -c "from cryptography.fernet import Fernet; print(Fernet.generate_key().decode(), end='')"

Use a key manager instead of a Fernet key

The API can encrypt the stored connections with a key in a KMS-compatible key manager (AWS KMS, or a compatible service such as Scaleway Key Manager) instead of a Fernet key. The API never holds that key: each connection gets its own data key, which only the key manager can decrypt, and every decryption is subject to the key's policy and audit trail. The chart has no values for this yet; set the API environment variables through api.extraEnvVars:

Variable	Value
CONNECTION_CIPHER	aws_kms. Chosen automatically when CONNECTION_KMS_KEY_ID is set
CONNECTION_KMS_KEY_ID	The key ID, ARN or alias
CONNECTION_KMS_REGION	The region of the key
CONNECTION_KMS_ENDPOINT_URL	The endpoint of a KMS-compatible service. Leave it unset for AWS
CONNECTION_KMS_ACCESS_KEY_ID, CONNECTION_KMS_SECRET_ACCESS_KEY	Optional. Without them, the API uses the standard AWS credential chain

Connections already stored with a Fernet key stay readable as long as that key stays set, so you can move from one to the other.

Bring your own Secrets

If you bring your own Kubernetes Secrets, use connector.existingSecret (key connector-secret) and databaseConnections.existingSecret (keys database-connections and connection-encryption-keys). Change the key names with connector.secretKeys.* and databaseConnections.secretKeys.*.

2. Turn the connector on

Add this to your values file. Pin the image tag: the chart refuses to render when both connector.image.tag and connector.image.digest are empty.

networkPolicy:
  enabled: true

connector:
  enabled: true
  image:
    tag: "0.4.0"

Then upgrade the release:

helm upgrade avatar <chart> --values my-values.yaml --values avatar-secrets.yaml

The chart then runs the connector, opens the internal port of the API, and adds the network policies between them.

3. Let the connector reach your databases

The connector has its own network policy. By default, it can reach:

• DNS;
• the internal port of the API;
• the in-chart SeaweedFS, when it is enabled;
• the public internet, except the private, shared, link-local and loopback ranges (10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16, 100.64.0.0/10, 169.254.0.0/16, 127.0.0.0/8).

A database on a public address works without any change. A database on a private network (behind a VPN or a peering) needs its network in allowedDatabaseCIDRs:

connector:
  networkPolicy:
    allowedDatabaseCIDRs:
      - 10.20.0.0/16

An external object storage on a private address must be listed there too, because the connector writes the tables it reads into the object storage of the job.

If your DNS pods or your object storage pods do not carry the default labels, set connector.networkPolicy.dnsSelector (default k8s-app: kube-dns) and connector.networkPolicy.storageSelector (default app.kubernetes.io/name: seaweedfs, app.kubernetes.io/component: s3).

4. Check it works

Log in to the web application as any user, go to Account > Database connections, click Add demo connections, then Test next to the demo source. If the test reports the database as reachable, the API, the connector and the network policies work together. Then create a test connection to one of your own databases.

Declare connections for every user (optional)

Users can store their own connections from the web application or the Python client. You can also declare connections for the whole deployment. Every user sees them, and nobody can change them from the application:

databaseConnections:
  connections:
    - name: crm-prod
      label: CRM (production)
      dsn: "postgresql://avatar_reader:<password>@crm.internal:5432/crm"
    - name: crm-test
      label: CRM (test)
      dsn: "postgresql://avatar_writer:<password>@crm-test.internal:5432/crm"

Each entry has a name, a dsn in the format of Supported engines, and an optional label, which is what users see in the pickers.

These values contain passwords. Keep them in a Kubernetes Secret (databaseConnections.existingSecret, key database-connections, as a JSON list) instead of a values file.

Workers read the API Secret by name

The workers read the deployment connections from the Secret api, key database_connections. A Secret named in databaseConnections.existingSecret does not reach them on its own: point the DATABASE_CONNECTIONS entry of dask.worker.env at it as well.

Give the source a role that can only read. Give the target a role that can write and delete rows. The target must already have the schema: the run does not create tables.

Other settings

Value	Default	Description
connector.replicaCount	1	Number of connector pods. The connector is stateless: you can scale it freely.
connector.tokenTtlSeconds	21600	How long the token of a job stays valid. Set it longer than your longest job.
connector.storageEndpoints	[]	host:port addresses the connector can write extracted tables to. Empty allows any. We recommend the host:port of your object storage, as the workers name it.
connector.internalPort	8001	Internal port of the API that the connector asks for connection strings. Must differ from api.containerPort.
connector.containerPort	8080	Port the connector listens on. Its Service is always ClusterIP.
connector.resources	requests 250m / 512 MiB, limits 2 CPU / 2 GiB	Compute resources of the connector container.
connector.extraEnvVars	[]	Extra environment variables of the connector container.
connector.nodeSelector, tolerations, affinity, podAnnotations, podLabels	empty	Scheduling and metadata of the connector pods.

The connector runs as a non-root user (UID 10001) with a read-only root file system. The complete list of connector.* and databaseConnections.* values is in the README.md of the chart.

Rotate the secrets

• connector.secret: when you change it, the tokens of the jobs that are running become invalid. Rotate it when no database job runs.
• databaseConnections.encryptionKeys: a comma-separated list. The first key encrypts, and all the keys decrypt. Put the new key first, wait until every stored connection is written again, then remove the old key.