Forwarding port to a PostgreSQL server

September 04, 2014 | Posted in PostgreSQL | Ajoy Oommen

I have a postgres database installed on an EBS drive in EC2. Recently I had to access this DB from the local machine. These are my setup notes. (There is nothing EC2 specific here. Except perhaps that you should ensure SSH access is allowed in your security group and that you have your secret key file.)

Ensure that your user postgres has permissions to access the database. These permissions can be added in pg_hba.conf.

Now you can forward a port, say 54320, on your client, to the port postgres is listening to on your EC2 instance. Like this

ssh -L 54320:localhost:5432 -i key.pem ec2-user@host.ip.address

What -L does is that it listens on 54320. When a connection arrives on the port, SSH forwards it to localhost:5432 on host.ip.address as user ec2-user.

So now you can connect to your database from your local machine:

psql -U postgres dbname -p 54320 -h localhost

Related Posts

04 Feb 2020 » From WordPress and Django to Jekyll

27 Feb 2017 » Provisioning an ubuntu server for Django, Postgres, NGINX

11 Jul 2016 » Installing PostgreSQL

24 Mar 2016 » Configuring SSH servers on Ubuntu

01 May 2015 » Supervisor in Elastic Beanstalk

20 Apr 2015 » Postgres backup and recovery

02 Apr 2015 » PostgreSQL module ltree