# SSH tunnel connections How to connect Materialize to a Kafka broker, a MySQL database, or PostgreSQL database using an SSH tunnel connection to a SSH bastion server **Cloud:** Materialize can connect to a Kafka broker, a Confluent Schema Registry server, a PostgreSQL database, or a MySQL database through an SSH tunnel connection. In this guide, you will create an SSH tunnel connection, configure your Materialize authentication settings, and create a source connection. Before you begin, make sure you have access to a bastion host. You will need: * The bastion host IP address and port number * The bastion host username 1. Create an SSH tunnel connection. In Materialize, create an [SSH tunnel connection](/sql/create-connection/#ssh-tunnel) to the bastion host: ```mzsql CREATE CONNECTION ssh_connection TO SSH TUNNEL ( HOST '', USER '', PORT ); ``` 1. Configure the SSH bastion host. The bastion host needs a **public key** to connect to the Materialize tunnel you created in the previous step. Materialize stores public keys for SSH tunnels in the system catalog. Query [`mz_ssh_tunnel_connections`](/reference/system-catalog/mz_catalog/#mz_ssh_tunnel_connections) to retrieve the public keys for the SSH tunnel connection you just created: ```mzsql SELECT mz_connections.name, mz_ssh_tunnel_connections.* FROM mz_connections JOIN mz_ssh_tunnel_connections USING(id) WHERE mz_connections.name = 'ssh_connection'; ``` ``` | id | public_key_1 | public_key_2 | |-------|---------------------------------------|---------------------------------------| | u75 | ssh-ed25519 AAAA...76RH materialize | ssh-ed25519 AAAA...hLYV materialize | ``` > Materialize provides two public keys to allow you to rotate keys without connection downtime. Review the [`ALTER CONNECTION`](/sql/alter-connection) documentation for more information on how to rotate your keys. 1. Log in to your SSH bastion server and add each key to the bastion `authorized_keys` file: ```bash # Command for Linux echo "ssh-ed25519 AAAA...76RH materialize" >> ~/.ssh/authorized_keys echo "ssh-ed25519 AAAA...hLYV materialize" >> ~/.ssh/authorized_keys ``` 1. Configure your internal firewall to allow the SSH bastion host to connect to your Kafka cluster or PostgreSQL instance. If you are using a cloud provider like AWS or GCP, update the security group or firewall rules for your PostgreSQL instance or Kafka brokers. Allow incoming traffic from the SSH bastion host IP address on the necessary ports. For example, use port `5432` for PostgreSQL and ports `9092`, `9094`, and `9096` for Kafka. Test the connection from the bastion host to the Kafka cluster or PostgreSQL instance. ```bash telnet telnet ``` If the command hangs, double-check your security group and firewall settings. If the connection is successful, you can proceed to the next step. 1. Verify the SSH tunnel connection from your source to your bastion host: ```bash # Command for Linux ssh -L 9092:kafka-broker:9092 @ ``` Verify that you can connect to the Kafka broker or PostgreSQL instance via the SSH tunnel: ```bash telnet localhost 9092 ``` If you are unable to connect using the `telnet` command, enable `AllowTcpForwarding` and `PermitTunnel` on your bastion host SSH configuration file. On your SSH bastion host, open the SSH config file (usually located at `/etc/ssh/sshd_config`) using a text editor: ```bash sudo nano /etc/ssh/sshd_config ``` Add or uncomment the following lines: ```bash AllowTcpForwarding yes PermitTunnel yes ``` Save the changes and restart the SSH service: ```bash sudo systemctl restart sshd ``` 1. Retrieve the static egress IPs from Materialize and configure the firewall rules (e.g. AWS Security Groups) for your bastion host to allow SSH traffic for those IP addresses only. ```mzsql SELECT * FROM mz_catalog.mz_egress_ips; ``` ``` XXX.140.90.33 XXX.198.159.213 XXX.100.27.23 ``` 1. To confirm that the SSH tunnel connection is correctly configured, use the [`VALIDATE CONNECTION`](/sql/validate-connection) command: ```mzsql VALIDATE CONNECTION ssh_connection; ``` If no validation errors are returned, the connection can be used to create a source connection. **Self-Managed:** Materialize can connect to a Kafka broker, a Confluent Schema Registry server, a PostgreSQL database, or a MySQL database through an SSH tunnel connection. In this guide, you will create an SSH tunnel connection, configure your Materialize authentication settings, and create a source connection. Before you begin, make sure you have access to a bastion host. You will need: * The bastion host IP address and port number * The bastion host username 1. Create an SSH tunnel connection. In Materialize, create an [SSH tunnel connection](/sql/create-connection/#ssh-tunnel) to the bastion host: ```mzsql CREATE CONNECTION ssh_connection TO SSH TUNNEL ( HOST '', USER '', PORT ); ``` 1. Configure the SSH bastion host. The bastion host needs a **public key** to connect to the Materialize tunnel you created in the previous step. Materialize stores public keys for SSH tunnels in the system catalog. Query [`mz_ssh_tunnel_connections`](/reference/system-catalog/mz_catalog/#mz_ssh_tunnel_connections) to retrieve the public keys for the SSH tunnel connection you just created: ```mzsql SELECT mz_connections.name, mz_ssh_tunnel_connections.* FROM mz_connections JOIN mz_ssh_tunnel_connections USING(id) WHERE mz_connections.name = 'ssh_connection'; ``` ``` | id | public_key_1 | public_key_2 | |-------|---------------------------------------|---------------------------------------| | u75 | ssh-ed25519 AAAA...76RH materialize | ssh-ed25519 AAAA...hLYV materialize | ``` > Materialize provides two public keys to allow you to rotate keys without connection downtime. Review the [`ALTER CONNECTION`](/sql/alter-connection) documentation for more information on how to rotate your keys. 1. Log in to your SSH bastion server and add each key to the bastion `authorized_keys` file: ```bash # Command for Linux echo "ssh-ed25519 AAAA...76RH materialize" >> ~/.ssh/authorized_keys echo "ssh-ed25519 AAAA...hLYV materialize" >> ~/.ssh/authorized_keys ``` 1. Configure your internal firewall to allow the SSH bastion host to connect to your Kafka cluster or PostgreSQL instance. If you are using a cloud provider like AWS or GCP, update the security group or firewall rules for your PostgreSQL instance or Kafka brokers. Allow incoming traffic from the SSH bastion host IP address on the necessary ports. For example, use port `5432` for PostgreSQL and ports `9092`, `9094`, and `9096` for Kafka. Test the connection from the bastion host to the Kafka cluster or PostgreSQL instance. ```bash telnet telnet ``` If the command hangs, double-check your security group and firewall settings. If the connection is successful, you can proceed to the next step. 1. Verify the SSH tunnel connection from your source to your bastion host: ```bash # Command for Linux ssh -L 9092:kafka-broker:9092 @ ``` Verify that you can connect to the Kafka broker or PostgreSQL instance via the SSH tunnel: ```bash telnet localhost 9092 ``` If you are unable to connect using the `telnet` command, enable `AllowTcpForwarding` and `PermitTunnel` on your bastion host SSH configuration file. On your SSH bastion host, open the SSH config file (usually located at `/etc/ssh/sshd_config`) using a text editor: ```bash sudo nano /etc/ssh/sshd_config ``` Add or uncomment the following lines: ```bash AllowTcpForwarding yes PermitTunnel yes ``` Save the changes and restart the SSH service: ```bash sudo systemctl restart sshd ``` 1. Ensure materialize cluster pods have network access to your SSH bastion host. 1. Validate the SSH tunnel connection To confirm that the SSH tunnel connection is correctly configured, use the [`VALIDATE CONNECTION`](/sql/validate-connection) command: ```mzsql VALIDATE CONNECTION ssh_connection; ``` If no validation errors are returned, the connection can be used to create a source connection. ## Create a source connection In Materialize, create a source connection that uses the SSH tunnel connection you configured in the previous section: **Kafka:** ```mzsql CREATE CONNECTION kafka_connection TO KAFKA ( BROKER 'broker1:9092', SSH TUNNEL ssh_connection ); ``` You can reuse this Kafka connection across multiple [`CREATE SOURCE`](/sql/create-source/kafka/) statements. **PostgreSQL:** ```mzsql CREATE SECRET pgpass AS ''; 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' SSH TUNNEL ssh_connection ); ``` You can reuse this PostgreSQL connection across multiple [`CREATE SOURCE`](/sql/create-source/postgres/) statements: ```mzsql CREATE SOURCE mz_source FROM POSTGRES CONNECTION pg_connection (PUBLICATION 'mz_source') FOR ALL TABLES; ``` **MySQL:** ```mzsql CREATE SECRET mysqlpass AS ''; CREATE CONNECTION mysql_connection TO MYSQL ( HOST '', SSH TUNNEL ssh_connection, ); ``` You can reuse this MySQL connection across multiple [`CREATE SOURCE`](/sql/create-source/postgres/) statements. ## Related pages - [`CREATE SECRET`](/sql/create-secret) - [`CREATE CONNECTION`](/sql/create-connection) - [`CREATE SOURCE`: Kafka](/sql/create-source/kafka/) - [`CREATE SOURCE`: MySQL](/sql/create-source/mysql) - [`CREATE SOURCE`: PostgreSQL](/sql/create-source/postgres/)