Record all the steps to enable remote access by Postgresql

Preface to

After installing the PostgreSQL database, the default is to accept only local access connections. If you want to access the PostgreSQL database server on another host, you need to configure it accordingly. The following words do not say much, to see the detailed introduction together.

steps are as follows:

To allow remote access in Postgresql, you need to set up two files :

postgresql.conf pg_hba.conf

However, since the system was installed with Centos 7 and the default Postgresql version was 9.2, a version 9.6 was installed, only to find that it could not be accessed remotely when configured. The default path cannot find the above two files. At this time, you can check the path of their corresponding files in the following way :

dog=# show config_file; 
    config_file     
------------------------------------------ 
 /etc/postgresql/9.4/main/postgresql.conf 
(1 row) 
dog=# show hba_file; 
    hba_file     
-------------------------------------- 
 /etc/postgresql/9.4/main/pg_hba.conf 
(1 row) 

Other configurations can be viewed via show all.

First we configure pg_hba.conf and set the locally accessible IP address range :

... 
host all all 127.0.0.1/32  trust 
host all all 192.168.1.0/24 md5 

Here we run 192.168.1.0 through 192.168.1.255 host access.

Then modify the postgresql.conf file :

listen_addresses='localhost' 

Replace localhost with * and remove the comment.

Then restart the server and view the IP:
it is listening to in the following way

dog@debian:~$ sudo netstat -plunt | grep postgres 
tcp  0  0 :5432   0.0.0.0:*    LISTEN  787/postgres  
tcp6  0  0 ::1:5432    :::*     LISTEN  787/postgres 

In the remote case, you will see an asterisk instead of 127.0.0.1.

Reference articles: https: / / www postgresql. org docs / 9.3 / static sql - show. html

summary