Setting up the Snowflake Connector for PostgreSQL Agent container¶

Choose a container orchestration system¶

The agent is packaged as a standard Docker container image, and can be run on any orchestration system that supports the standard. This can be a stand-alone Docker (https://www.docker.com/) instance, Kubernetes (https://kubernetes.io/), RedHat OpenShift (https://www.redhat.com/en/technologies/cloud-computing/openshift), a cloud-managed solution, such as AWS Fargate (https://aws.amazon.com/fargate/), and others. Your organization will often have a preferred, existing system for you to use.

Pay attention to the agent configuration section of this document, because different orchestration systems come with different constraints. Your system, or specific setup, may not permit you to mount writable volumes (as is required with the agent’s primary configuration option).

Later examples will focus on Kubernetes as the most popular orchestration system. The approach will often be similar in other systems, and you will need to adjust the examples for your setup.

Confirm required system resources¶

• The agent is a memory-intensive application, and requires a minimum 6GB of RAM to operate correctly.

• The optimal number of CPUs is 4. Fewer CPUs can decrease performance. More CPUs will not improve performance.

Set up connectivity¶

The agent needs to connect to every source database where data will be read. Configure your network and firewalls, so that direct connections are possible, and PostgreSQL’s client port is reachable. Typically port 5432. For more information see PostgreSQL’s Connection Settings documentation (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-PORT).

If your source databases reside in isolated networks, and connecting from a single agent isn’t possible, you will need to install additional instances of the connector’s native application, each one with its own agent. This feature is currently in private preview. Please contact Snowflake Support to request access.

The agent also connects directly to your Snowflake deployment. For information on which hostnames need to be available see Allowing Hostnames.

If any of the agent’s connections pass through a proxy, you will need to pass additional configuration to the agent. See Review optional configuration environment variables.

[Optional] Obtain and prepare SSL certificates for source databases¶

If you intend to use SSL connections between the agent and the source databases, you need to acquire the root certificate for your PostgreSQL instances, and mount it into the agent’s container under /home/agent/.postgresql/root.crt.

Prepare agent configuration¶

The agent can be configured via container-mounted JSON files, or environment variables, or a mix of both. The access keys required to connect to Snowflake can be mounted from the host’s file system, supplied as container secrets, or as environment variables.

The following sections describe different configuration options, from the most straightforward, to the most comprehensive. Choose an approach based on the specifics of your infrastructure.

 {
   "<data_source_name_1>": {
       "url": "jdbc:postgresql://<host>:<port>/<databaseName>[?<key1>=<value1>[&<key2>=<value2>]]",
       "username": "<postgresql_db_username>",
       "password": "<postgresql_db_password>",
       "publication": "<postgresql_db_publication>",
       "ssl": false
   },
   "<data_source_name_2>": {
       "url": "jdbc:postgresql://<host>:<port>/<databaseName>[?<key1>=<value1>[&<key2>=<value2>]]",
       "username": "<postgresql_db_username>",
       "password": "<postgresql_db_password>",
       "publication": "<postgresql_db_publication>",
       "ssl": false
   }
}

docker run -d \
   --restart unless-stopped \
   --name database-connector-agent \
   --volume </path/to/ssh/keys/directory>:/home/agent/.ssh \
   --volume </path/to/snowflake.json>:/home/agent/snowflake.json \
   --volume </path/to/datasources.json>:/home/agent/datasources.json \
   --volume </path/to/root.crt>:/home/agent/.postgresql/root.crt \
   -m 6g \
   snowflakedb/database-connector-agent:latest

docker run \
  -e SNOWFLAKE_USERNAME="POSTGRESQL_AGENT_USER" \
  -e SNOWFLAKE_APPLICATION_NAME="SNOWFLAKE_CONNECTOR_FOR_POSTGRESQL" \
  -e SNOWFLAKE_ALLOWLIST_0_HOST="example_account.us-west-2.aws.snowflakecomputing.cn" \
  -e SNOWFLAKE_ALLOWLIST_0_PORT=443 \
  -e SNOWFLAKE_ALLOWLIST_0_TYPE="SNOWFLAKE_DEPLOYMENT" \
  ...

{
  "userName": "POSTGRESQL_AGENT_USER",
  "applicationName": "SNOWFLAKE_CONNECTOR_FOR_POSTGRESQL",
  "allowlist": [
    {
      "host": "example_account.us-west-2.aws.snowflakecomputing.cn",
      "port": 443,
      "type": "SNOWFLAKE_DEPLOYMENT"
    }
  ]
}

docker run \
    -e SNOWFLAKE_USERNAME="POSTGRESQL_AGENT_USER" \
    -e SNOWFLAKE_APPLICATION_NAME="SNOWFLAKE_CONNECTOR_FOR_POSTGRESQL" \
    -e SNOWFLAKE_ENFORCEDURL="example_account.us-west-2.aws.snowflakecomputing.cn:443" \
    ...

docker run \
  -e SNOWFLAKE_DATASOURCES_POSTGRESQLDS1_URL="jdbc:postgresql://example.internal:5432/", \
  -e SNOWFLAKE_DATASOURCES_POSTGRESQLDS1_USERNAME="example_user" \
  -e SNOWFLAKE_DATASOURCES_POSTGRESQLDS1_PASSWORD="example_password" \
  -e SNOWFLAKE_DATASOURCES_POSTGRESQLDS1_PUBLICATION="example_publication" \
  -e SNOWFLAKE_DATASOURCES_POSTGRESQLDS1_SSL="false" \
  ...

{
    "POSTGRESQLDS1": {
        "url": "jdbc:postgresql://example.internal:5432/",
        "username": "example_user",
        "password": "example_password",
        "publication": "example_publication",
        "ssl": false
    }
}

Review optional configuration environment variables¶

The agent supports the following, optional settings, available by setting additional environment variables on the container:

SNOWFLAKE_PRIVATEKEYPATH

Specifies the absolute path to the file with the agent user’s private key. This is used when mounting your own private key to the container, usually via an orchestration system’s secret.

SNOWFLAKE_PRIVATEKEYPASSWORD

Specifies the password for agent user’s the private key. If you let the agent generate the keys, this password will be set on the private key. If you reuse existing keys, this password will be used to access the existing private key.

SNOWFLAKE_PRIVATEKEY

Specifies the content of the agent user’s private key. This can be set, when mounting the private key as a file in the container is not an option.

SNOWFLAKE_ENFORCEDURL

Specifies the URL to connect to Snowflake, overriding the agent’s own discovery mechanism. This is primarily used to connect to PrivateLink deployments.

JAVA_OPTS

Specifies additional Java settings or properties that will be passed to the agent’s process.

The most commonly used are:

• The -Xmx option to set the maximum Java heap size. Snowflake recommends setting this value to the amount of memory available to the container, minus 1GB.

  For example, if the container has 6GB of RAM available, set the following:

  JAVA_OPTS=-Xmx5g
  

  Copy

• When the connection from agent to Snowflake requires a proxy, set the following:

  JAVA_OPTS=-Dhttp.useProxy=true -Dhttp.proxyHost=<proxy-host> -Dhttp.proxyPort=<proxy-port>
  

  Copy

• To bypass the proxy for some hosts or IP addresses, for instance, source databases, set the additonal http.nonProxyHosts property. Use a pipe symbol (|) to separate multiple host names. Use an asterisk (*) as a wildcard character.

  JAVA_OPTS=-Dhttp.useProxy=true -Dhttp.proxyHost=<proxy-host> -Dhttp.proxyPort=<proxy-port>
    -Dhttp.nonProxyHosts='*.my_company.com|localhost|myorganization-myaccount.snowflakecomputing.cn|192.168.91.*'
  

  Copy

• To pass credentials for the proxy, set the http.proxyUser and http.proxyPassword system properties.

  JAVA_OPTS=-Dhttp.useProxy=true -Dhttp.proxyHost=<proxy-host> -Dhttp.proxyPort=<proxy-port>
    -Dhttp.proxyUser=<proxy-user> -Dhttp.proxyPassword=<proxy-pass>
  

  Copy

Set up PrivateLink connectivity¶

If you’re connecting to a PrivateLink deployment, you must provide the URL for the agent to connect to explicitly by setting the SNOWFLAKE_ENFORCEDURL environment variable.

To determine the PrivateLink URL of your account, you can either:

• Open the snowflake.json file that you obtained during the configuration process. Find the array named allowlistPrivatelink, and then the entry with the type of SNOWFLAKE_DEPLOYMENT.

• Use the SYSTEM$GET_PRIVATELINK_CONFIG function.

Understanding Snowflake access keys¶

The agent authenticates with Snowflake as a service account, created by the connector’s setup wizard in Snowsight, using a set of access keys. The setup wizard creates temporary access keys, and adds the private key to the snowflake.json file in a field named temporaryPrivateKey.

During its initial startup, the agent replaces these temporary keys by:

1. Generating a new set of access keys, and storing them under /home/agent/.ssh as database-connector-agent-app-private-key.p8 and database-connector-agent-app-public-key.pub inside the container. This directory should be mounted as an external, writable volume to the container, so that the keys persist when the container shuts down.

2. Altering its service account to use the new keys.

3. Removing the temporaryPrivateKey field from the snowflake.json file.

After the initial key replacement, the agent never rotates access keys.

You can use the keys generated by the agent. Or you can create your own set, alter the service account, and provide the private key to the agent.

The agent’s private key discovery order is:

1. Any key passed using the SNOWFLAKE_PRIVATEKEY environment variable. If this value is found, the connector will ignore the temporary key from snowflake.json.

2. Keys found on mounted volumes in /home/agent/.ssh/database-connector-agent-app-private-key.p8. If this file is found, the connector will ignore the temporary key from snowflake.json.

3. The value of the temporaryPrivateKey field from the snowflake.json file.

Accessing the health check endpoint¶

The agent exposes a HTTP endpoint with health information. You can use this endpoint when running the agent in an orchestration system to determine when the agent has fully launched and whether it is healthy. The endpoint is available under port 8080 and path /actuator/health.

To use the endpoint as a liveness probe in Kubernetes, add the following to your Pod configuration:

apiVersion: v1
kind: Pod
spec:
  containers:
  - ...
    livenessProbe:
      httpGet:
        path: /actuator/health
        port: 8080
      initialDelaySeconds: 5
      periodSeconds: 10