Configure PostgreSQL to allow remote connections
1. Remote connection to Postgres
Sometimes you create a remote connection to a Postgres database and receive an error notification like this:
could not connect to server: Connection refused (0x0000274D/10061)The reason is that the database only accepts local connections, therefore, you need to configure Postgres so that it also accepts remote connections.
Before we begin, it is important to know that there are two configuration files controlling how the Postgres database server works, and you need to change some of the parameters on these two files.
- postgresql.conf
- pg_hba.conf
You can find the above two files in the folder:
- /etc/postgresql/13/main (Linux)
- C:/Program Files/PostgreSQL/13/data (Windows)
postgresql.conf
The postgresql.conf file allows you to configure the IP addresses that Postgres uses to listen for connections to it. By default Postgres only listens on localhost address, so this is the reason why you cannot remotely connect to it.
Open Terminal on Linux (Ubuntu, etc) and go to to the folder where the postgresql.conf file is located.
For Linux (Ubuntu, etc), use the nano command to open and modify the postgresql.conf file.
sudo nano postgresql.conf
Find line:
#listen_addresses = "localhost"
Then replace it with:
listen_addresses = '*'
The above change makes it possible for Postgres to listen on all IP addresses of the computers installing it. You may also specify a list of listen addresses for Postgres, which are separated by commas.
listen_addresses = '192.168.0.1,112.113.10.1'Next, press CTRL + O --> ENTER to save the changes, and CTRL + X to exit the nano.
pg_hba.conf
The pg_hba.conf file is used for client authentication. In other words, it allows you to specify which clients are permitted to connect to Postgres. The word HBA stands for "Host-Based Authentication".
For Linux (Ubuntu, etc), use the nano command to open and modify the pg_hba.conf file:
sudo nano pg_hba.conf
Find line:
# IPv4 local connections:
host all all 127.0.0.1/32 md5
Then replace it with:
# IPv4 local connections:
host all all 0.0.0.0/0 md5
The above change will allow all clients to be connected with Postgres.
You can also configure to give permission for a range of IP to connect to Postgres:
# IPv4 local connections:
host all all 112.100.20.1/30 md5Finally restart Postgres for the changes to take effect.
sudo service postgresql stop
sudo service postgresql startSee more: