Exposing Private Heroku Postgres Databases

March 06, 2020

I needed to con­nect a Post­gres data­base living in a Heroku Pri­vate space to Looker, a data intel­li­gence and vizual­iza­tion tool. This is how I did it.

A few months ago, Heroku enabled AWS Pri­vateLink inte­gra­tion, to allow users to con­nect Post­gres data­bas­es in Pri­vate spaces to Amazon VPC.

Heroku has a handy tuto­r­i­al on how to go about doing that. This post is large­ly based off that, but with a few improve­ments based on my expe­ri­ence exe­cut­ing those steps, and addi­tion­al­ly set­ting up an EC2 as a TCP proxy for Looker to talk to.

To start with, I’m assum­ing you already have:

  • A Heroku Post­gres data­base in a Pri­vate space
  • The Heroku CLI
  • The AWS CLI (option­al)
  • Your AWS account ID (you can find it under Security Credentials)
  • An exist­ing AWS VPC

For the pur­pos­es of this walk­through, the name of the Heroku app will be heroku-app, the name of the attached Post­gres data­base will be heroku-postgres, the AWS account ID will be aws1234.

Also, do note that the Heroku Post­gres data­base lives in the same region as the VPC and the EC2 instance. Multi-region deploy­ments are out of the scope of this walk­through.

heroku plugins:install data-privatelink
heroku data:privatelink:create heroku-postgres \
  --aws-account-id aws1234
  --app heroku-app

This may take up to 10 min­utes.

3. Obtain the end­point’s ser­vice 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 ser­vice name would be com.amazonaws.vpce.us-east-2.vpce-svc-123456.

4. Create VPC end­point on the AWS man­age­ment con­sole.

Nav­i­gate to the VPC con­sole, and create a new end­point, like so:

vpc create endpoint

Search for the service using the service name in Step 3.
Search for the service using the service name in Step 3.

5. Launch an EC2 instance for prox­y­ing.

Nav­i­gate to the EC2 con­sole, and launch an appro­pri­ate­ly sized instance. Any OS is fine, as long as it can serve the pur­pos­es of prox­y­ing TCP traf­fic. I will use Ubuntu 18.04 with HAProxy in this exam­ple.

Instan­ti­ate one, making sure that it is con­nect­ed to the same VPC as above.

ec2 vpc

Skip cre­at­ing the secu­ri­ty group for now — we will do that right next.

6. Create secu­ri­ty groups.

We need two secu­ri­ty groups, one for the VPC end­point, and one for the EC2 instance.

The one for the VPC end­point just needs to allow inbound access from the EC2 instance, so create one that allows TCP access on ports 5432 from the pri­vate IP of the EC2 instance (which you can grab from the Descrip­tion pane in the EC2 con­sole).

new sg

vpc sg

The one for the EC2 instance needs to allow inbound access from the exter­nal par­ties you’re giving access to. In my case, this is Looker, whose IP address­es you can find here.

You will also need to give your­self SSH access and (option­al­ly) TCP access on port 5432 as well, for Step 8.

ec2 sg

Attach them to the VPC end­point and EC2 instance respec­tive­ly.

7. Install HAProxy on the EC2 instance.

This is fairly straight­for­ward, so I won’t cover it here.

The con­fig­u­ra­tion can be as basic as some­thing 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 end­point by:

heroku config --app heroku-app

and grep­ping for the DATABASE_ENDPOINT_<ENDPOINTID>_URL entry. It will be in the form of a stan­dard Post­gres con­nec­tion string postgres://user:password@host:5432/database. The host part of it will be what goes into the HAProxy config.

8. Verify that every­thing works.

To verify that every­thing works, take the con­nec­tion string in Step 7, replace the host part of the con­nec­tion string with the public IP of the EC2 instance, and attempt to con­nect to the Post­gres data­base from your local machine:

psql postgres://user:password@<public ip of ec2>:5432/database

If you see a psql prompt, it works!

9. Enter con­nec­tion cre­den­tials into Looker.

Once step 8 is done, you may remove access to port 5432 from your local machine. Then input the con­nec­tion cre­den­tials (the same ones in Step 8) into Looker:

looker connection

The last few steps is adapt­able for any exter­nal party that needs access to the Post­gres data­base. Just make sure that you adjust your secu­ri­ty groups appro­pri­ate­ly - after all, that’s why the Post­gres data­base is in a Pri­vate space in the first place, right?