How to configure remote access to PostgreSQL from other servers
There are two ways to set up remote access to PostgreSQL from external servers:
- Opening up access to your PostgreSQL from all servers on the Internet and
- Manually configuring the servers from which you can connect to your database.
The first method is good only in the case of test or training projects, because a small bug in the code may reveal the access information for your database and compromise its contents. But even if you think your project is small and it's not a big deal, you're wrong. I recently set up a brand new server with a new IP and left open access to the database. Within an hour, a malicious bot find me and wiped the entire database and demanded a ransom for the data's return. So, it's best to be extra careful when it comes to the security of your database.
Setting up access for your ip's
To configure access to PostgreSQL from the servers you allow, you must first open access to the PostgreSQL server from external servers. Open the file /etc/postgresql/14/main/postgresql.conf Note that you may have a different path to the file, for example with the release of version 15 of PostgreSQL
In Connection Settings you need to uncomment the line listen_addresses and make it look like this:
listen_addresses = '*'
Then in the same directory open the file pg_hba.conf and at the very bottom add the ip addresses of those servers that you want to give access to your PostgreSQL.
Example strings for each server:
# TYPE DATABASE USER ADDRESS METHOD
host all all 80.121.11.21/32 scram-sha-256
# TYPE DATABASE USER ADDRESS METHOD
host all all 120.121.11.11/32 scram-sha-256
In this example, I opened remote access for server 84.121.11.21 and 124.121.11.11
In my configuration open access to all users, to all databases: host all all for more security you can limit this set. For example to give access only to user remote: host all remote to all databases or limit it to one user and one database: host orders remote As you can see from the example, only user remote can connect from a remote server and get access only to the orders database
Do not forget to restart PostgreSQL after each change of configuration files
sudo service postgresql restart
Allow access from all ip's
If the warning above does not work for you, you are free to expose your database for all incoming traffic from the Internet at your own risk.
To do this you need to add the following configuration to the pg_hba.conf file
host all all 0.0.0.0/0 md5
That's all 🤘