how to connect to PostgreSQL using a stable way

All we need is an easy explanation of the problem, so here it is.

Now I am install PostgreSQL 13 in remote cloud server and expose the 5432 port to public network. I add a whitle list in PostgreSQL config file pg_hba.conf like this:

host    all             all             127.113.122.1/24           scram-sha-256

which 127.113.122.x is my public ip address of my network, but the problem is the ip address always change when I restart the Router or other reason. When the IP changed, I have to login into remote server and change the PostgreSQL config file and remove the old ip and add new ip. It make me crazy, I have to change the IP of PostgreSQL config again and again, is there any way to connect PostgreSQL using a stable way? both secure and easy to connect.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Pick a very good password and then don’t share it with any other accts and don’t leak it. Hackers generally break into cloud postgresql servers by guessing crappy passwords, not by guessing good ones. Then open up pg_hba to everyone, or at least the entire range of your isp.

Better yet, do this only for your nonsuper user acct, and block your superuser acct from connecting except for locally.

Method 2

This will let all ip adresses starting with 127.113.122

host    all             all             127.113.122.0/24           scram-sha-256  

This will let all ip addresses starting with 127.113

host    all             all             127.113.0.0/24           scram-sha-256

And this will let all ips connect database using scram

host    all             all             0.0.0.0/0           scram-sha-256

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply