I needed to connect a Postgres database living in a Heroku Private space to Looker, a data intelligence and vizualization tool. This is how I did it.
A few months ago, Heroku enabled AWS PrivateLink integration, to allow users to connect Postgres databases in Private spaces to Amazon VPC.
Heroku has a handy tutorial on how to go about doing that. This post is largely based off that, but with a few improvements based on my experience executing those steps, and additionally setting up an EC2 as a TCP proxy for Looker to talk to.
To start with, I’m assuming you already have:
- A Heroku Postgres database in a Private space
- The Heroku CLI
- The AWS CLI (optional)
- Your AWS account ID (you can find it under
- An existing AWS VPC
For the purposes of this walkthrough, the name of the Heroku app will be
heroku-app, the name of the attached Postgres database will be
heroku-postgres, the AWS account ID will be
Also, do note that the Heroku Postgres database lives in the same region as the VPC and the EC2 instance. Multi-region deployments are out of the scope of this walkthrough.
1. Install the PrivateLink CLI plugin.
heroku plugins:install data-privatelink
2. Create a PrivateLink endpoint.
heroku data:privatelink:create heroku-postgres \ --aws-account-id aws1234 --app heroku-app
This may take up to 10 minutes.
3. Obtain the endpoint’s service name.
heroku data:privatelink heroku-postgres \ --app heroku-app
=== privatelink endpoint status for heroku-postgres Service Name: com.amazonaws.vpce.us-east-2.vpce-svc-123456 Status: Operational === Whitelisted Accounts ARN Status arn:aws:iam::abcd1234:root Active === Connections Endpoint ID Owner ARN Status vpce-55555555 arn:aws:iam::abcd1234:root Available
The service name would be
4. Create VPC endpoint on the AWS management console.
Navigate to the VPC console, and create a new endpoint, like so:
5. Launch an EC2 instance for proxying.
Navigate to the EC2 console, and launch an appropriately sized instance. Any OS is fine, as long as it can serve the purposes of proxying TCP traffic. I will use Ubuntu 18.04 with HAProxy in this example.
Instantiate one, making sure that it is connected to the same VPC as above.
Skip creating the security group for now — we will do that right next.
6. Create security groups.
We need two security groups, one for the VPC endpoint, and one for the EC2 instance.
The one for the VPC endpoint just needs to allow inbound access from the EC2 instance, so create one that allows TCP access on ports 5432 from the private IP of the EC2 instance (which you can grab from the Description pane in the EC2 console).
The one for the EC2 instance needs to allow inbound access from the external parties you’re giving access to. In my case, this is Looker, whose IP addresses you can find here.
You will also need to give yourself SSH access and (optionally) TCP access on port 5432 as well, for Step 8.
Attach them to the VPC endpoint and EC2 instance respectively.
7. Install HAProxy on the EC2 instance.
This is fairly straightforward, so I won’t cover it here.
The configuration can be as basic as something like this:
global ...snip... defaults ...snip... mode tcp option tcplog ...snip... frontend fr1 bind 0.0.0.0:5432 default_backend bk1 backend bk1 server srv1 <vpc endpoint>:5432
You can find the address of the VPC endpoint by:
heroku config --app heroku-app
and grepping for the
DATABASE_ENDPOINT_<ENDPOINTID>_URL entry. It will be in
the form of a standard Postgres connection string
host part of it will be
what goes into the HAProxy config.
8. Verify that everything works.
To verify that everything works, take the connection string in Step 7, replace
host part of the connection string with the public IP of the EC2 instance,
and attempt to connect to the Postgres database from your local machine:
psql postgres://user:password@<public ip of ec2>:5432/database
If you see a
psql prompt, it works!
9. Enter connection credentials into Looker.
Once step 8 is done, you may remove access to port 5432 from your local machine. Then input the connection credentials (the same ones in Step 8) into Looker:
The last few steps is adaptable for any external party that needs access to the Postgres database. Just make sure that you adjust your security groups appropriately - after all, that’s why the Postgres database is in a Private space in the first place, right?