CREATE CONNECTION

A connection describes how to connect and authenticate to an external system youwant Materialize to read from or write to. Once created, a connectionisreusable across multipleCREATE SOURCE andCREATE SINK statements.

To use credentials that contain sensitive information (like passwords and SSLkeys) in a connection, you must firstcreate secrets tosecurely store each credential in Materialize’s secret management system.Credentials that are generally not sensitive (like usernames and SSLcertificates) can be specified as plaintext, or also stored as secrets.

Source and sink connections

AWS

An Amazon Web Services (AWS) connection provides Materialize with access to anIdentity and Access Management (IAM) user or role in your AWS account. You canuse AWS connections to performbulk exports to Amazon S3,performauthentication with an Amazon MSK cluster, orperformauthentication with an Amazon RDS MySQL database.

Connection options

WITH options

Permissions

When using role assumption-based authentication, you must configure atrustpolicy on the IAM role that permits Materialize to assume the role.

Materialize always uses the following IAM principal to assume the role:

arn:aws:iam::664411391173:role/MaterializeConnection

Materialize additionally generates anexternal ID which uniquely identifiesyour AWS connection across all Materialize regions. To ensure that otherMaterialize customers cannot assume your role, your IAM trust policymustconstrain access to only the external ID that Materialize generates for theconnection:

{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Principal":{"AWS":"arn:aws:iam::664411391173:role/MaterializeConnection"},"Action":"sts:AssumeRole","Condition":{"StringEquals":{"sts:ExternalId":"<EXTERNAL ID FOR CONNECTION>"}}}]}

You can retrieve the external ID for the connection, as well as an example trustpolicy, by querying themz_internal.mz_aws_connectionstable:

SELECTid,external_id,example_trust_policyFROMmz_internal.mz_aws_connections;

Examples

{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Principal":{"AWS":"arn:aws:iam::000000000000:role/MaterializeConnection"},"Action":"sts:AssumeRole","Condition":{"StringEquals":{"sts:ExternalId":"mz_00000000-0000-0000-0000-000000000000_u0"}}}]}

CREATECONNECTIONaws_role_assumptionTOAWS(ASSUMEROLEARN='arn:aws:iam::000000000000:role/WarehouseExport');

CREATESECRETaws_secret_access_keyAS'...';CREATECONNECTIONaws_credentialsTOAWS(ACCESSKEYID='ASIAV2KIV5LPTG6HGXG6',SECRETACCESSKEY=SECRETaws_secret_access_key);

Kafka

A Kafka connection establishes a link to aKafka cluster. You can use Kafkaconnections to createsources andsinks.

Syntax

Connection options

WITH options

To connect to a Kafka cluster with multiple bootstrap servers, use theBROKERSoption:

CREATECONNECTIONkafka_connectionTOKAFKA(BROKERS('broker1:9092','broker2:9092'));

Security protocol examples

CREATECONNECTIONkafka_connectionTOKAFKA(BROKER'unique-jellyfish-0000.prd.cloud.redpanda.com:9092',SECURITYPROTOCOL='PLAINTEXT',SSHTUNNELssh_connection);

CREATESECRETkafka_ssl_certAS'-----BEGIN CERTIFICATE----- ...';CREATESECRETkafka_ssl_keyAS'-----BEGIN PRIVATE KEY----- ...';CREATESECRETca_certAS'-----BEGIN CERTIFICATE----- ...';CREATECONNECTIONkafka_connectionTOKAFKA(BROKER'rp-f00000bar.cloud.redpanda.com:30365',SECURITYPROTOCOL='SSL'SSLCERTIFICATE=SECRETkafka_ssl_cert,SSLKEY=SECRETkafka_ssl_key,-- Specifying a certificate authority is only required if your cluster's-- certificates are not issued by a CA trusted by the Mozilla root store.SSLCERTIFICATEAUTHORITY=SECRETca_cert);

CREATESECRETca_certAS'-----BEGIN CERTIFICATE----- ...';CREATECONNECTIONkafka_connectionTOKAFKA(BROKER='rp-f00000bar.cloud.redpanda.com:30365',SECURITYPROTOCOL='SSL',SSHTUNNELssh_connection,-- Specifying a certificate authority is only required if your cluster's-- certificates are not issued by a CA trusted by the Mozilla root store.SSLCERTIFICATEAUTHORITY=SECRETca_cert);

CREATESECRETkafka_passwordAS'...';CREATECONNECTIONkafka_connectionTOKAFKA(BROKER'unique-jellyfish-0000.us-east-1.aws.confluent.cloud:9092',SECURITYPROTOCOL='SASL_PLAINTEXT',SASLMECHANISMS='SCRAM-SHA-256',-- or `PLAIN` or `SCRAM-SHA-512`SASLUSERNAME='foo',SASLPASSWORD=SECRETkafka_password,SSHTUNNELssh_connection);

CREATESECRETkafka_passwordAS'...';CREATESECRETca_certAS'-----BEGIN CERTIFICATE----- ...';CREATECONNECTIONkafka_connectionTOKAFKA(BROKER'unique-jellyfish-0000.us-east-1.aws.confluent.cloud:9092',SECURITYPROTOCOL='SASL_SSL',SASLMECHANISMS='SCRAM-SHA-256',-- or `PLAIN` or `SCRAM-SHA-512`SASLUSERNAME='foo',SASLPASSWORD=SECRETkafka_password,-- Specifying a certificate authority is only required if your cluster's-- certificates are not issued by a CA trusted by the Mozilla root store.SSLCERTIFICATEAUTHORITY=SECRETca_cert);

CREATECONNECTIONaws_mskTOAWS(ASSUMEROLEARN='arn:aws:iam::000000000000:role/MaterializeMSK');CREATECONNECTIONkafka_mskTOKAFKA(BROKER'msk.mycorp.com:9092',SECURITYPROTOCOL='SASL_SSL',AWSCONNECTION=aws_msk);

Network security

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

CREATECONNECTIONprivatelink_svcTOAWSPRIVATELINK(SERVICENAME'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',AVAILABILITYZONES('use1-az1','use1-az4'));CREATECONNECTIONkafka_connectionTOKAFKA(BROKERS('broker1:9092'USINGAWSPRIVATELINKprivatelink_svc,'broker2:9092'USINGAWSPRIVATELINKprivatelink_svc(PORT9093)));

CREATECONNECTIONprivatelink_svcTOAWSPRIVATELINK(SERVICENAME'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',AVAILABILITYZONES('use1-az1'));CREATECONNECTIONkafka_connectionTOKAFKA(AWSPRIVATELINK(PORT30292)SECURITYPROTOCOL='SASL_PLAINTEXT',SASLMECHANISMS='SCRAM-SHA-256',SASLUSERNAME='foo',SASLPASSWORD=SECRETred_panda_password);

CREATECONNECTIONssh_connectionTOSSHTUNNEL(HOST'<SSH_BASTION_HOST>',USER'<SSH_BASTION_USER>',PORT<SSH_BASTION_PORT>);CREATECONNECTIONkafka_connectionTOKAFKA(BROKER'broker1:9092',SSHTUNNELssh_connection);

CREATECONNECTIONssh1TOSSHTUNNEL(HOST'ssh1',...);CREATECONNECTIONssh2TOSSHTUNNEL(HOST'ssh2',...);CREATECONNECTIONkafka_connectionTOKAFKA(BROKERS('broker1:9092'USINGSSHTUNNELssh1,'broker2:9092'USINGSSHTUNNELssh2)SSHTUNNELssh_1);

Confluent Schema Registry

A Confluent Schema Registry connection establishes a link to aConfluent SchemaRegistry server. You can use Confluent Schema Registry connections in theFORMAT clause ofCREATE SOURCE andCREATE SINK statements.

Syntax

Connection options

WITH options

Examples

Using username and password authentication with TLS encryption:

CREATESECRETcsr_passwordAS'...';CREATESECRETca_certAS'-----BEGIN CERTIFICATE----- ...';CREATECONNECTIONcsr_basicTOCONFLUENTSCHEMAREGISTRY(URL'https://rp-f00000bar.cloud.redpanda.com:30993',USERNAME='foo',PASSWORD=SECRETcsr_password-- Specifying a certificate authority is only required if your cluster's-- certificates are not issued by a CA trusted by the Mozilla root store.SSLCERTIFICATEAUTHORITY=SECRETca_cert);

Using TLS for encryption and authentication:

CREATESECRETcsr_ssl_certAS'-----BEGIN CERTIFICATE----- ...';CREATESECRETcsr_ssl_keyAS'-----BEGIN PRIVATE KEY----- ...';CREATESECRETca_certAS'-----BEGIN CERTIFICATE----- ...';CREATECONNECTIONcsr_sslTOCONFLUENTSCHEMAREGISTRY(URL'https://rp-f00000bar.cloud.redpanda.com:30993',SSLCERTIFICATE=SECRETcsr_ssl_cert,SSLKEY=SECRETcsr_ssl_key,-- Specifying a certificate authority is only required if your cluster's-- certificates are not issued by a CA trusted by the Mozilla root store.SSLCERTIFICATEAUTHORITY=SECRETca_cert);

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 SSHbastion host.

CREATECONNECTIONprivatelink_svcTOAWSPRIVATELINK(SERVICENAME'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',AVAILABILITYZONES('use1-az1','use1-az4'));CREATECONNECTIONcsr_privatelinkTOCONFLUENTSCHEMAREGISTRY(URL'http://my-confluent-schema-registry:8081',AWSPRIVATELINKprivatelink_svc);

CREATECONNECTIONssh_connectionTOSSHTUNNEL(HOST'<SSH_BASTION_HOST>',USER'<SSH_BASTION_USER>',PORT<SSH_BASTION_PORT>);CREATECONNECTIONcsr_sshTOCONFLUENTSCHEMAREGISTRY(URL'http://my-confluent-schema-registry:8081',SSHTUNNELssh_connection);

MySQL

A MySQL connection establishes a link to aMySQL server. You can useMySQL connections to createsources.

Syntax

Connection options

WITH options

Example

CREATESECRETmysqlpassAS'<POSTGRES_PASSWORD>';CREATECONNECTIONmysql_connectionTOMYSQL(HOST'instance.foo000.us-west-1.rds.amazonaws.com',PORT3306,USER'root',PASSWORDSECRETmysqlpass);

Network security

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

CREATECONNECTIONprivatelink_svcTOAWSPRIVATELINK(SERVICENAME'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',AVAILABILITYZONES('use1-az1','use1-az4'));CREATECONNECTIONmysql_connectionTOMYSQL(HOST'instance.foo000.us-west-1.rds.amazonaws.com',PORT3306,USER'root',PASSWORDSECRETmysqlpass,AWSPRIVATELINKprivatelink_svc);

CREATECONNECTIONtunnelTOSSHTUNNEL(HOST'bastion-host',PORT22,USER'materialize');CREATECONNECTIONmysql_connectionTOMYSQL(HOST'instance.foo000.us-west-1.rds.amazonaws.com',SSHTUNNELssh_connection);

CREATECONNECTIONaws_rds_mysqlTOAWS(ASSUMEROLEARN='arn:aws:iam::000000000000:role/MaterializeRDS');CREATECONNECTIONmysql_connectionTOMYSQL(HOST'instance.foo000.us-west-1.rds.amazonaws.com',PORT3306,USER'root',AWSCONNECTIONaws_rds_mysql,SSLMODE'verify_identity');

PostgreSQL

A Postgres connection establishes a link to a single database of aPostgreSQL server. You can use Postgres connections to createsources.

Syntax

Connection options

WITH options

Example

CREATESECRETpgpassAS'<POSTGRES_PASSWORD>';CREATECONNECTIONpg_connectionTOPOSTGRES(HOST'instance.foo000.us-west-1.rds.amazonaws.com',PORT5432,USER'postgres',PASSWORDSECRETpgpass,SSLMODE'require',DATABASE'postgres');

Network security

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

CREATECONNECTIONprivatelink_svcTOAWSPRIVATELINK(SERVICENAME'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',AVAILABILITYZONES('use1-az1','use1-az4'));CREATECONNECTIONpg_connectionTOPOSTGRES(HOST'instance.foo000.us-west-1.rds.amazonaws.com',PORT5432,DATABASEpostgres,USERpostgres,PASSWORDSECRETpgpass,AWSPRIVATELINKprivatelink_svc);

CREATECONNECTIONtunnelTOSSHTUNNEL(HOST'bastion-host',PORT22,USER'materialize');CREATECONNECTIONpg_connectionTOPOSTGRES(HOST'instance.foo000.us-west-1.rds.amazonaws.com',PORT5432,SSHTUNNELtunnel,DATABASE'postgres');

SQL Server

A SQL Server connection establishes a link to a single database of aSQL Server instance. You can use SQL Server connections to createsources.

Syntax

Connection options

SSL MODE

disabled - no encryption.required - encryption required, no certificate validation.verify - encryption required, validate server certificate using OS configured CA.verify_ca - encryption required, validate server certificate using provided CA certificates (requiresSSL CERTIFICATE AUTHORITY).

WITH options

Example

CREATESECRETsqlserver_passAS'<SQL_SERVER_PASSWORD>';CREATECONNECTIONsqlserver_connectionTOSQLSERVER(HOST'instance.foo000.us-west-1.rds.amazonaws.com',PORT1433,USER'SA',PASSWORDSECRETsqlserver_pass,DATABASE'my_db');

Network security connections

AWS PrivateLink

An AWS PrivateLink connection establishes a link to anAWS PrivateLink service.You can use AWS PrivateLink connections inConfluent Schema Registry connections,Kafka connections, andPostgres connections.

Syntax

Connection options

Permissions

Materialize assigns a unique principal to each AWS PrivateLink connection inyour region using an Amazon Resource Name of thefollowing form:

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

After creating the connection, you must configure the AWS PrivateLink serviceto accept connections from the AWS principal Materialize will connect as. Theprincipals for AWS PrivateLink connections in your region are stored inthemz_aws_privatelink_connectionssystem table.

SELECT*FROMmz_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 theAWS PrivateLink documentation.

Accepting connection requests

If your AWS PrivateLink service is configured to require acceptance ofconnection requests, you must additionally approve the connection request fromMaterialize after creating the connection. For more details on manuallyaccepting connection requests, see theAWS PrivateLink documentation.

Example

CREATECONNECTIONprivatelink_svcTOAWSPRIVATELINK(SERVICENAME'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',AVAILABILITYZONES('use1-az1','use1-az4'));

SSH tunnel

An SSH tunnel connection establishes a link to an SSH bastion server. You canuse SSH tunnel connections inKafka connections,MySQL connections,andPostgres 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 keypair is stored securely within your region and cannot be retrieved. The publickey for each key pair is stored in themz_ssh_tunnel_connections systemtable.

When Materialize connects to the SSH bastion server, it presents both keys forauthentication. To allow key pair rotation without downtime, you shouldconfigure your SSH bastion server to accept both key pairs. You canthenrotate the key pairs usingALTER CONNECTION.

Materialize currently generates SSH key pairs using theEd25519 algorithm,which is fast, secure, andrecommended by securityprofessionals. Some legacy SSH servers do not support theEd25519 algorithm. You will not be able to use these servers with Materialize’sSSH tunnel connections.

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

Examples

Create an SSH tunnel connection:

CREATECONNECTIONssh_connectionTOSSHTUNNEL(HOST'bastion-host',PORT22,USER'materialize');

Retrieve the public keys for the SSH tunnel connection you just created:

SELECTmz_connections.name,mz_ssh_tunnel_connections.*FROMmz_connectionsJOINmz_ssh_tunnel_connectionsUSING(id)WHEREmz_connections.name='ssh_connection';

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

Connection validation

Materialize automatically validates the connection and authentication parametersfor most connection types on connection creation:

For connection types that are validated by default, if the validation stepfails, the creation of the connection will also fail and a validation error isreturned. You can disable connection validation by setting theVALIDATEoption tofalse. This is useful, for example, when the parameters are knownto be correct but the external system is unavailable at the time of creation.

Connection types that require additional setup steps after creation, like AWSand SSH tunnel connections, can bemanually validated using theVALIDATE CONNECTION syntax once all setup steps arecompleted.

Privileges

The privileges required to execute this statement are:

• CREATE privileges on the containing schema.
• USAGE privileges on all connections and secrets used in the connection definition.
• USAGE privileges on the schemas that all connections and secrets in the statement are contained in.

Related pages

• CREATE SECRET
• CREATE SOURCE
• CREATE SINK