Image of Navicat to EC2 connection

How to connect Navicat to your remote PostgreSQL server on EC2

The purpose of this article is to show you how to connect you Navicat Database Admin Software tool to PostgreSQL server hosted on AWS EC2. Navicat is a vendor that offers a series of database management tools for PostgreSQL, Oracle, MariaDB, Microsoft SQL Server, MySQL and more. This article will assume that you already have a running EC2 Ubuntu 16.04 LTS instance with a PostgreSQL server running.

Step overview

  • Step 1 – Configure Security Group on EC2
  • Step 2 – Update PostgreSQL Configuration Files
  • Step 3 – Connect from Navicat

Step 1 – Configure Security Group on EC2

Log into your AWS account and select the AWS instance running the PostgreSQL server you would like to connect to on the EC2 dashboard. Next, you will edit the Security Group Inbound Rules.

Add an inbound rule with the following parameters:

  • Type: PostgreSQL
  • Protocol: TCP
  • Port Range: 5432 (This is the PostgreSQL port by default. If you changed the default port then use your custom port instead of the default one)
  • Source: [Write the single IP or IP range that you will be accessing your PostgreSQL database. Please be sure to restrict inbound port access to those who should not be able to access your database]

Once the inbound rules have been updated let’s proceed to update the PostgreSQL configuration files on the server.

Step 2 – Update PostgreSQL Configuration Files

First, we will ssh into the server via the command line and edit the postgresql.conf file with the following command.

$ sudo vim /etc/postgresql/9.5/main/postgresql.conf

Once in the file, we will include this line below which means the PostgreSQL server will listen for inbound communications from all IP addresses.

... listen_address='*' ... 

Next, we’ll edit the user access in the pg_hba.conf file.

$ sudo vim /etc/postgresql/9.5/main/pg_hba.conf 

Once in the file, we will include this line below which means the PostgreSQL server will allow access to only those who have the username and password to the database.

host    all    all    0.0.0.0/0    md5 

Keep in mind that these configurations may be too open to your preferences. I encourage you to update your database access to more exclusive IPs.

Step 3 – Connect from Navicat

All the hard work is done! Now the easy part. Open up the Navicat application and add a new connection. All you’ll need to do is enter the following information to make a new connection:

  • Host: [The Public IP of your EC2]
  • Port: [The port your PostgreSQL server is accessed thorugh]
  • Initial Database: [The name of the database you want to connect to on the server first]
  • User Name: [The username of the PostgreSQL user that has permissions to access the database]
  • Password: [The password of the PostgreSQL user that has permissions to access the database]

Once all of this information is filled out you should be able to connect no problem!

Final Notes

After you are connected you can begin to utilize the full potential of Navicat by generating relational diagrams, update your database, running backups, and so much more. I used to be a user of phppgadmin and I longed for the days I could seriously upgrade my database admin tool and Navicat was the answer.

Please like, share or comment if you found this article useful!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.