others-how to allow remote access for postgresql database
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:
root@launch-advisor-20191120:/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:
root@launch-advisor-20191120:/etc/postgresql/10/main# pwd
/etc/postgresql/10/main
root@launch-advisor-20191120:/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
root@launch-advisor-20191120:/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:
root@launch-advisor-20191120:/etc/postgresql/10/main# systemctl restart postgresql.service
root@launch-advisor-20191120:/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.confand is stored in the database cluster’s data directory. (HBA stands for host-based authentication.) A defaultpg_hba.conffile is installed when the data directory is initialized byinitdb. It is possible to place the authentication configuration file elsewhere, however; see the hba_file configuration parameter.The general format of the
pg_hba.conffile 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.,allorreplication) 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:
root@launch-advisor-20191120:/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:
root@launch-advisor-20191120:/etc/postgresql/10/main# systemctl restart postgresql.service
root@launch-advisor-20191120:/etc/postgresql/10/main#
Try again to connect to the postgresql server:
