CREATE CONNECTION

A connection describes how to connect and authenticate to an external system you want Materialize to read data from. Once created, a connection is reusable across multiple CREATE SOURCE and CREATE SINK statements.

To use credentials that contain sensitive information (like passwords and SSL keys) in a connection, you must first create secrets to securely store each credential in Materialize’s secret management system. Credentials that are generally not sensitive (like usernames and SSL certificates) can be specified as plain text, or also stored as secrets.

Source and sink connections

Kafka

A Kafka connection establishes a link to a Kafka cluster. You can use Kafka connections to create sources and sinks.

Syntax

Connection options

Authentication

CREATE SECRET kafka_ssl_crt AS '<BROKER_SSL_CRT>';
CREATE SECRET kafka_ssl_key AS '<BROKER_SSL_KEY>';

CREATE CONNECTION kafka_connection TO KAFKA (
    BROKER 'rp-f00000bar.data.vectorized.cloud:30365',
    SSL KEY = SECRET kafka_ssl_key,
    SSL CERTIFICATE = SECRET kafka_ssl_crt
);

CREATE SECRET kafka_password AS '<BROKER_PASSWORD>';

CREATE CONNECTION kafka_connection TO KAFKA (
    BROKER 'unique-jellyfish-0000-kafka.upstash.io:9092',
    SASL MECHANISMS = 'SCRAM-SHA-256',
    SASL USERNAME = 'foo',
    SASL PASSWORD = SECRET kafka_password
);

To connect to a Kafka cluster with multiple bootstrap servers, use the BROKERS option:

CREATE CONNECTION kafka_connection TO KAFKA (
    BROKERS ('broker1:9092', 'broker2:9092')
);

Network security

If your Kafka broker is not exposed to the public internet, you can tunnel the connection through an AWS PrivateLink service or an SSH bastion host.

CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK (
    SERVICE NAME 'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',
    AVAILABILITY ZONES ('use1-az1', 'use1-az4')
);

CREATE CONNECTION kafka_connection TO KAFKA (
    BROKERS (
        'broker1:9092' USING AWS PRIVATELINK privatelink_svc,
        'broker2:9092' USING AWS PRIVATELINK privatelink_svc (PORT 9093)
    )
);

CREATE CONNECTION ssh_connection TO SSH TUNNEL (
    HOST '<SSH_BASTION_HOST>',
    USER '<SSH_BASTION_USER>',
    PORT <SSH_BASTION_PORT>
);

CREATE CONNECTION kafka_connection TO KAFKA (
BROKERS (
    'broker1:9092' USING SSH TUNNEL ssh_connection,
    'broker2:9092' USING SSH TUNNEL ssh_connection
    -- Add all Kafka brokers
    )
);

Confluent Schema Registry

A Confluent Schema Registry connection establishes a link to a Confluent Schema Registry server. You can use Confluent Schema Registry connections in the FORMAT clause of CREATE SOURCE and CREATE SINK statements.

Syntax

Connection options

Examples

CREATE SECRET csr_ssl_crt AS '<CSR_SSL_CRT>';
CREATE SECRET csr_ssl_key AS '<CSR_SSL_KEY>';
CREATE SECRET csr_password AS '<CSR_PASSWORD>';

CREATE CONNECTION csr_ssl TO CONFLUENT SCHEMA REGISTRY (
    URL 'https://rp-f00000bar.data.vectorized.cloud:30993',
    SSL KEY = SECRET csr_ssl_key,
    SSL CERTIFICATE = SECRET csr_ssl_crt,
    USERNAME = 'foo',
    PASSWORD = SECRET csr_password
);

Network security

If your Confluent Schema Registry server is not exposed to the public internet, you can tunnel the connection through an AWS PrivateLink service or an SSH bastion host.

CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK (
    SERVICE NAME 'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',
    AVAILABILITY ZONES ('use1-az1', 'use1-az4')
);

CREATE CONNECTION csr_privatelink TO CONFLUENT SCHEMA REGISTRY (
    URL 'http://my-confluent-schema-registry:8081',
    AWS PRIVATELINK privatelink_svc
);

CREATE CONNECTION ssh_connection TO SSH TUNNEL (
    HOST '<SSH_BASTION_HOST>',
    USER '<SSH_BASTION_USER>',
    PORT <SSH_BASTION_PORT>
);

CREATE CONNECTION csr_privatelink TO CONFLUENT SCHEMA REGISTRY (
    URL 'http://my-confluent-schema-registry:8081',
    SSH TUNNEL ssh_connection
);

PostgreSQL

A Postgres connection establishes a link to a single database of a PostgreSQL server. You can use Postgres connections to create sources.

Syntax

Connection options

Example

CREATE SECRET pgpass AS '<POSTGRES_PASSWORD>';

CREATE CONNECTION pg_connection TO POSTGRES (
    HOST 'instance.foo000.us-west-1.rds.amazonaws.com',
    PORT 5432,
    USER 'postgres',
    PASSWORD SECRET pgpass,
    SSL MODE 'require',
    DATABASE 'postgres'
);

Network security

If your PostgreSQL server is not exposed to the public internet, you can tunnel the connection through an AWS PrivateLink service or an SSH bastion host.

CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK (
   SERVICE NAME 'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',
   AVAILABILITY ZONES ('use1-az1', 'use1-az4')
);

CREATE CONNECTION pg_connection TO POSTGRES (
    HOST 'instance.foo000.us-west-1.rds.amazonaws.com',
    PORT 5432,
    DATABASE postgres,
    USER postgres,
    PASSWORD SECRET pgpass,
    AWS PRIVATELINK privatelink_svc
);

CREATE CONNECTION tunnel TO SSH TUNNEL (
    HOST 'bastion-host',
    PORT 22,
    USER 'materialize',
);

CREATE CONNECTION pg_connection TO POSTGRES (
    HOST 'instance.foo000.us-west-1.rds.amazonaws.com',
    PORT 5432,
    SSH TUNNEL tunnel,
    DATABASE 'postgres'
);

Network security connections

AWS PrivateLink

An AWS PrivateLink connection establishes a link to an AWS PrivateLink service. You can use AWS PrivateLink connections in Confluent Schema Registry connections, Kafka connections, and Postgres connections.

Syntax

Connection options

Permissions

Materialize assigns a unique principal to each AWS PrivateLink connection in your region using an Amazon Resource Name of the following form:

arn:aws:iam::664411391173:role/mz_<REGION-ID>_<CONNECTION-ID>

After creating the connection, you must configure the AWS PrivateLink service to accept connections from the AWS principal Materialize will connect as. The principals for AWS PrivateLink connections in your region are stored in the mz_aws_privatelink_connections system table.

SELECT * FROM mz_aws_privatelink_connections;

   id   |                                 principal
--------+---------------------------------------------------------------------------
 u1     | arn:aws:iam::664411391173:role/mz_20273b7c-2bbe-42b8-8c36-8cc179e9bbc3_u1
 u7     | arn:aws:iam::664411391173:role/mz_20273b7c-2bbe-42b8-8c36-8cc179e9bbc3_u7

For more details on configuring a trusted principal for your AWS PrivateLink service, see the AWS PrivateLink documentation.

Accepting connection requests

If your AWS PrivateLink service is configured to require acceptance of connection requests, you must additionally approve the connection request from Materialize after creating the connection. For more details on manually accepting connection requests, see the AWS PrivateLink documentation.

Example

CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK (
    SERVICE NAME 'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',
    AVAILABILITY ZONES ('use1-az1', 'use1-az4')
);

SSH tunnel

An SSH tunnel connection establishes a link to an SSH bastion server. You can use SSH tunnel connections in Kafka connections, and Postgres connections.

Syntax

Connection options

Key pairs

Materialize automatically manages the key pairs for an SSH tunnel connection. Each connection is associated with two key pairs. The private key for each key pair is stored securely within your region and cannot be retrieved. The public key for each key pair is stored in the mz_ssh_tunnel_connections system table.

When Materialize connects to the SSH bastion server, it presents both keys for authentication. To allow key pair rotation without downtime, you should configure your SSH bastion server to accept both key pairs. You can then rotate the key pairs using ALTER CONNECTION.

Materialize currently generates SSH key pairs using the Ed25519 algorithm, which is fast, secure, and recommended by security professionals. Some legacy SSH servers do not support the Ed25519 algorithm. You will not be able to use these servers with Materialize’s SSH tunnel connections.

We routinely evaluate the security of the cryptographic algorithms in use in Materialize. Future versions of Materialize may use a different SSH key generation algorithm as security best practices evolve.

Examples

Create an SSH tunnel connection:

CREATE CONNECTION ssh_connection TO SSH TUNNEL (
    HOST 'bastion-host',
    PORT 22,
    USER 'materialize'
);

Retrieve the public keys for all SSH tunnel connections:

SELECT * FROM mz_ssh_tunnel_connections;

 id    | public_key_1                          | public_key_2
-------+---------------------------------------+---------------------------------------
 ...   | ssh-ed25519 AAAA...76RH materialize   | ssh-ed25519 AAAA...hLYV materialize

Related pages

• CREATE SECRET
• CREATE SOURCE
• CREATE SINK