Materialize/doc/user/layouts/shortcodes/network-security/privatelink-postgres.md

1. Create target groups

   Create a dedicated target group for your RDS or Aurora or Aurora instance with the following details:

   a. Target type as IP address.

   b. Protocol as TCP.

   c. Port as 5432, or the port that you are using in case it is not 5432.

   d. Make sure that the target group is in the same VPC as the RDS or Aurora instance.

   e. Click next, and register the respective RDS or Aurora instance to the target group using its IP address.

2. Verify security groups and health checks

   Once the target groups have been created, make sure that the health checks are passing and that the targets are reported as healthy.

   If you have set up a security group for your RDS or Aurora instance, you must ensure that it allows traffic on the health check port.

   Remarks:

   a. Network Load Balancers do not have associated security groups. Therefore, the security groups for your targets must use IP addresses to allow traffic.

   b. You can't use the security groups for the clients as a source in the security groups for the targets. Therefore, the security groups for your targets must use the IP addresses of the clients to allow traffic. For more details, check the AWS documentation.

3. Create a Network Load Balancer (NLB)

   Create a Network Load Balancer that is enabled for the same subnets that the RDS or Aurora instance is in.

4. Create TCP listeners

   Create a TCP listener for your RDS or Aurora instance that forwards to the corresponding target group you created.

5. Create a VPC endpoint service

   Create a VPC endpoint service and associate it with the Network Load Balancer that you’ve just created.

   Note the service name that is generated for the endpoint service.

6. Create an AWS PrivateLink connection

   In Materialize, create an AWS PrivateLink connection that references the endpoint service that you created in the previous step.

   ↕️ In-region connections

   To connect to an AWS PrivateLink endpoint service in the same region as your Materialize environment:

     CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK (
       SERVICE NAME 'com.amazonaws.vpce.<region_id>.vpce-svc-<endpoint_service_id>',
       AVAILABILITY ZONES ('use1-az1', 'use1-az2', 'use1-az4')
     );
   

   • Replace the SERVICE NAME value with the service name you noted earlier.

   • Replace the AVAILABILITY ZONES list with the IDs of the availability zones in your AWS account. For in-region connections the availability zones of the NLB and the consumer VPC must match.

   To find your availability zone IDs, select your database in the RDS Console and click the subnets under Connectivity & security. For each subnet, look for Availability Zone ID (e.g., use1-az6), not Availability Zone (e.g., us-east-1d).

   ↔️ Cross-region connections

   To connect to an AWS PrivateLink endpoint service in a different region to the one where your Materialize environment is deployed:

     CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK (
       SERVICE NAME 'com.amazonaws.vpce.us-west-1.vpce-svc-<endpoint_service_id>',
       -- For now, the AVAILABILITY ZONES clause **is** required, but will be
       -- made optional in a future release.
       AVAILABILITY ZONES ()
     );
   

   • Replace the SERVICE NAME value with the service name you noted earlier.

   • The service name region refers to where the endpoint service was created. You do not need to specify AVAILABILITY ZONES manually — these will be optimally auto-assigned when none are provided.

Configure the AWS PrivateLink service

1. Retrieve the AWS principal for the AWS PrivateLink connection you just created:

   SELECT principal
   FROM mz_aws_privatelink_connections plc
   JOIN mz_connections c ON plc.id = c.id
   WHERE c.name = 'privatelink_svc';
   

                                    principal
   ---------------------------------------------------------------------------
    arn:aws:iam::664411391173:role/mz_20273b7c-2bbe-42b8-8c36-8cc179e9bbc3_u1
   

   Follow the instructions in the AWS PrivateLink documentation to configure your VPC endpoint service to accept connections from the provided AWS principal.

2. If your AWS PrivateLink service is configured to require acceptance of connection requests, you must manually approve the connection request from Materialize after executing CREATE CONNECTION. For more details, check the AWS PrivateLink documentation.

   Note: It might take some time for the endpoint service connection to show up, so you would need to wait for the endpoint service connection to be ready before you create a source.

Validate the AWS PrivateLink connection

Validate the AWS PrivateLink connection you created using the VALIDATE CONNECTION command:

VALIDATE CONNECTION privatelink_svc;

If no validation error is returned, move to the next step.

Create a source connection

In Materialize, create a source connection that uses the AWS PrivateLink connection you just configured:

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
);

This PostgreSQL connection can then be reused across multiple CREATE SOURCE statements:

CREATE SOURCE mz_source
  FROM POSTGRES CONNECTION pg_connection (PUBLICATION 'mz_source')
  FOR ALL TABLES;