Problem

Assume that we have installed a postgresql server in ubuntu system, and we have done these jobs:

  • Created a system/postgresql user named ‘bswen’
  • Created a postgresql database named ‘bswendb’
  • Changed the owner of the database ‘bswendb’ to user ‘bswen’

Now, we want to connect the the remote postgresql server from local desktop, we got this problem:

could not connect to server: Connection refused
	Is the server running on host "47.1.1.7" and accepting
	TCP/IP connections on port 5432?

Environment

  • Postgresql 10.15
  • Ubuntu 18.04.4 LTS

Reason

We can debug this problem by using netstat command on the server:

[email protected]:/etc/postgresql/10/main# netstat -an|grep LISTEN
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN

You can see that the postgresql is listening on ‘127.0.0.1:5432’ by default, which can only be accessed from localhost, so if you want to access the server from remote client, you should change this.

Solution

Step 1: Make the postgresql server listening on all network address

The configration files of postgresql server (version 10.15+) is located at directory ‘/etc/postgresql/10/main’, just as follows:

[email protected]:/etc/postgresql/10/main# pwd
/etc/postgresql/10/main
[email protected]:/etc/postgresql/10/main# ls -l
total 52
drwxr-xr-x 2 postgres postgres  4096 Nov 22 15:18 conf.d
-rw-r--r-- 1 postgres postgres   315 Nov 22 15:18 environment
-rw-r--r-- 1 postgres postgres   143 Nov 22 15:18 pg_ctl.conf
-rw-r----- 1 postgres postgres  4754 Nov 22 16:24 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 Nov 22 15:18 pg_ident.conf
-rw-r--r-- 1 postgres postgres 23191 Nov 22 17:27 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 Nov 22 15:18 start.conf
[email protected]:/etc/postgresql/10/main#

Open the ‘postgresql.conf’, and change the listening configuration of the server like this:

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
listen_addresses = '*'

Save it , and then restart the postgresql service by root or sudo like this:

[email protected]:/etc/postgresql/10/main# systemctl restart postgresql.service
[email protected]:/etc/postgresql/10/main#

But when we try to connect the remote postgresql server, we get this error :

FATAL:  no pg_hba.conf entry for host "222.3.212.2", user "bswen", database "bswendb", SSL on

Please continue to read the following:

Step 2: Allow the remote client address to access the database

According to this document, the pg_hba.conf is responsible for client authentication of postgresql:

Client authentication is controlled by a configuration file, which traditionally is named pg_hba.conf and is stored in the database cluster’s data directory. (HBA stands for host-based authentication.) A default pg_hba.conf file is installed when the data directory is initialized by initdb. It is possible to place the authentication configuration file elsewhere, however; see the hba_file configuration parameter.

The general format of the pg_hba.conf file is a set of records, one per line. Blank lines are ignored, as is any text after the # comment character. Records cannot be continued across lines. A record is made up of a number of fields which are separated by spaces and/or tabs. Fields can contain white space if the field value is quoted. Quoting one of the keywords in a database, user, or address field (e.g., all or replication) makes the word lose its special character, and just match a database, user, or host with that name.

Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

So, the connection from our desktop to the postgresql must match at least a line in the pg_hba.conf file, let’s check it:

By default, there are these lines in the pg_hba.conf:

[email protected]:/etc/postgresql/10/main# vi pg_hba.conf
......
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# 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

We can ignore the ‘local’ and ‘replication’ , because it’s used by the system, the only lines that affected our connection are as follows:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

These configurations mean that it only allow connections from localhost to access the postgresql server. So ,we should add the following lines to match our connection:

host    all             all             0.0.0.0/0       md5
host    all             all             ::/0            md5

The final content of the pg_hba.conf is as follows(the comments are removed):

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

Save the file and restart the postgresql service:

[email protected]:/etc/postgresql/10/main# systemctl restart postgresql.service
[email protected]:/etc/postgresql/10/main#

Try again to connect to the postgresql server:

image-20201122180106255