I set up Postgres 16 and configured it to be accessed from two IPs, my home public IP and the private IP of another VPS on the same private network as the DB. I found that while login worked from home, it did not work from my VPS (the one on the same private network)
pg_hba.conf was like this:
host all all 107.x.x.x/32 scram-sha-256
host all all 10.x.x.x/20 scram-sha-256
On the client side (both home and VPS), I am connecting using Python3/SQLALchemy/psycopg2. However when running the script on the VPS, I got password authentication failed
I then changed the IP used to access the DB from the private IP to the DB's public IP. I also change pg_hba.conf to allow the public IP of the VPS, and all of a sudden, I could connect!
host all all 107.x.x.x/32 scram-sha-256
host all all 10.x.x.x/20 scram-sha-256
host all all 138.x.x.x/32 scram-sha-256
It's nice that I can now access my database, but doesn't this defeat the purpose of having my DB and VPS on the same private network? If I access the DB using its public IP, it is routed publicly even if I am acessing it from a server on the exact same rack, right? I'm not sure if it's the networking or db configuration side of this causing the issue. I hid the IPs, so you can't tell if I did something wrong with the IP ranges, but in that case it wouldn't give a password error, would it?
Why should a password that is accepted from a public IP be rejected from a private IP?
Edits in response to questions:
The full error on the client when connecting using the local IP:
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "10.108.0.2", port 6262 failed: FATAL: password authentication failed for user "myapp"
The last connection attempt in my postgres logs is my attempt to connect using public ips. Based on the time point, I know that it was that attempt that is responsible for the failed attempt that shows in the log:
2023-10-03 02:31:47.832 EDT [12728] FATAL: no pg_hba.conf entry for host "138.x.x.x", user "siteapp", database "sitedata", SSL encryption
2023-10-03 02:31:47.837 EDT [12729] FATAL: no pg_hba.conf entry for host "138.197.16.205", user "siteapp", database "sitedata", no encryption
2023-10-03 02:36:15.206 EDT [12712] LOG: checkpoint starting: time
2023-10-03 02:36:15.240 EDT [12712] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.012 s, total=0.034 s; sync files=2, longest=0.008 s, average=0.006 s; distance=0 kB, estimate=0 kB; lsn=0/1BCF0B0, redo lsn=0/1BCF078
The error message
password authentication failed for user "%s"
comes from the PostgreSQL server. If you cannot see a corresponding message in the PostgreSQL server log, that means that either logging is configured to not log errors (which is not the case in your situation) or that the client attempted to connect to a different PostgreSQL cluster than the one whose log file you show.