Manage PostgreSQL database remotely with Navicat

Question

I installed PostgreSQL on my server today and quickly took notes. I hope that I have compiled the answers I found for my question beautifully for you.

Answer 1

UPDATE PG_HBA.CONF

If you are using an ubuntu distribution, the file path should be this way. I use the 10 version and use the ubuntu distribution. This path may differ depending on your operating system. Find this file on your system. etc/postgressql/10/main/pg_hba.conf Using nano, you can open it directly with the command.

nano etc/postgresql/10/main/pg_hba.conf

here are two settings we need to make.

# "local" is for Unix domain socket connections only
local   all             all                                     md5

# Remote connections for navicat
host    samerole        all             0.0.0.0/0               md5

don't touch the others, should look like this in its final form.

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

# Remote connections for navicat
host    samerole        all             0.0.0.0/0               md5

UPDATE POSTGRESQL.CONF

Open this file with nano, find the line below and update it as follows.

nano etc/postgresql/10/main/postgresql.conf

listen_addresses = 'localhost'  

change it

listen_adresses = '*'

The settings required for the translate remote connection to work are only related to these two settings. But if your system has a Firewall, we need to whitelist the ports for PostgreSQL like UFW. Let's do this when we say it.

$ sudo ufw status

When you check the firewall status, if it is active, you will see an output like this. If disabled, inactive appears.

To                         Action      From
--                         ------      ----
22/tcp                     ALLOW       Anywhere                               
5432/tcp                   ALLOW       Anywhere                  
22/tcp (v6)                ALLOW       Anywhere (v6)                       
5432/tcp (v6)              ALLOW       Anywhere (v6) 

This place is very important! Make sure you have ssh permission when activating UFW. I will always let this habit. If you do not do this, you need to login to the system with root and interfere with ufw settings. you kind of lock yourself up :)

Let's continue with UFW permissions.

$ sudo ufw allow ssh
$ sudo ufw allow 5432/tcp

We also gave the necessary permissions. Let's do one more check. Let's make sure our PostgreSQL password is default postgres. Because it's a nonsense by the way. Let's secure our business. Time is cash.

CHECKING THE DEFAULT USERNAME AND PASSWORD (postgres, postgres, postgres)

$ sudo -u postgres psql

\password postgres

\q

just restart the postgres service.

$ sudo service postgresql restart

NOW WE START THE NAVICAT SETTINGS

Open your Navicat application and create a new connection that selects the PostreSQL connection from the new connection tab.

Two setting steps are waiting for you in the connection you created. General and SSH settings.

I enter my default PostgreSQL information in General settings. Just like in the picture.

Connection Name: Your Connection Name Host: localhost Port: 5432 Initial Database: postgres User Name: postgres Password: postgres

postgres is default database, username and password !

Let's set the SSH connection settings as follows.

Host: your droplet IP Port: 22 is defeault SSH port User Name: root (default root login) Authentication Method: Public Key Private Key: /Users/username/.ssh/id_rsa Passphrase: your passhrase

If we have made our settings for Navicat, we can now test our connection. Click the Test Connection button and see if we connect.

Congratulations !!!