PostgreSQL External Connection

In this article, we will be learning the necessary configurations to connect to Postgresql from external.

First, I will talk about two files. postgresql.conf and pg_hba.conf these two files are essential configuration files for postgresql.

Postgresql.conf and pg_hba.conf files are located in the place we specify as the data file when making initdb.

It is located in /var/lib/pgsql/13 (depending on version)/data/  if standard installation is done.

First, let’s briefly talk about postgresql.conf.

Postgresql.conf is used to change and adjust parameters such as data directories of postgresql, connection, authentication, wal, replication, query tuning, reporting, logging, statistics, autovacuum, client connections, lock management, config file on a cluster basis.

Pg_hba.conf is the config file in which configurations such as connecting from a specific IP or only from a specific IP block to postgresql with or without encryption or hash algorithm are made.

After talking about postgresql.conf and pg_hba.conf in general, let’s make the necessary configurations.

First, we take the first step to accept all incoming requests by changing the listen_addresses parameter via postgresql.conf as follows.

By deleting the localhost written against Listen addresses, we save and exit by making *.

We come to the next step, the pg_hba.conf parameter, and here we change the following parameters.

The above changes were made for problem-free connection in the test environment.

In the production environment, instead of trust, scram-sha-256 and 0.0.0.0/0 should come from which ip block or ip-based permission should be defined.

You need to connect to PostgreSQL as follows and change the postgres password with the help of the following command.

After changing the configuration files and the postgres password, we need to restart the postgresql service as follows.

Note : The service may not start in case of errors or missing spellings in the configurations.

If the firewall is open, you need to turn off the firewall with the help of the command below.

(Those who do not want to turn off the firewall can only define permission for that port over the firewall.)

After restarting the service, it accepts the changes made in postgresql and starts serving again with the parameters we specified, and all connection requests become acceptable.