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 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:
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
$ 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!
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!