Subject: [postgresql-interfaces/psqlodbc] issue #73: Strange configuration problem

From: "swadams2 (@swadams2)" <[email-masked]>
Date: Tue, 19 Nov 2024 02:05:25 +0000

I'm trying to use MS Access with PostgreSQL and so want to configure an ODBC file for this.  Testing it resulted in a connection timeout, so I looked at this to troubleshoot:
https://odbc.postgresql.org/faq.html#3.3
Adding a "tcpip=true" line to postgresql.conf and a line to permit a connection from the computer running Access to pg_hba.conf and then restarting PostgreSQL seemed to go without errors, but when I tried to run the psql command afterward, I got this error:
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

The psql command worked again after I undid these changes, so I'm trying to figure out how they caused this error.  The line I added to pg_hba.conf was as follows.
host      spiralxprime     web_app     192.168.0.72     scram-sha-256

Also, the listen_addresses setting in postgresql.conf was set to '*'.

Any ideas as to why this is happening would be appreciated.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 19 Nov 2024 10:09:54 +0000

Is the server running ?

---

From: "swadams2 (@swadams2)" <[email-masked]>
Date: Tue, 19 Nov 2024 17:58:34 +0000

Yes.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 20 Nov 2024 16:27:43 +0000

by default psql will not use TCP try psql -h 127.0.0.1 to see if it will connect to TCP

---

From: "swadams2 (@swadams2)" <[email-masked]>
Date: Wed, 20 Nov 2024 18:29:16 +0000

I tried that and got the same error.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 25 Nov 2024 15:28:38 +0000

So if you received that error then postgresql is not running on TCP/IP. 
Make sure the postgresql.conf has listen_address=* or some TCP/IP address

---

From: "swadams2 (@swadams2)" <[email-masked]>
Date: Tue, 26 Nov 2024 00:49:43 +0000

It's set to '*'.  Oddly, the "psql -h 127.0.0.1" command works if I don't have the "tcpip = true" line in the configuration file.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 29 Nov 2024 13:52:35 +0000

> It's set to '*'. Oddly, the "psql -h 127.0.0.1" command works if I don't have the "tcpip = true" line in the configuration file.

I'm a bit lost. Which configuration file?

---

From: "swadams2 (@swadams2)" <[email-masked]>
Date: Mon, 02 Dec 2024 15:04:10 +0000

I meant postgresql.conf.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 05 Dec 2024 14:41:17 +0000

sorry for the late response.
tcpip is not a valid option in postgresql.conf. I would imagine that the server does not start with that in there.

---

From: "swadams2 (@swadams2)" <[email-masked]>
Date: Thu, 05 Dec 2024 17:43:26 +0000

That's strange.  The web page I put a link to in my original post says, "Postmaster must run with the -i option , or tcpip=true in postgresql.conf to allow remote connections."  Is it wrong?
Restarting PostgreSQL with either that line in postgresql.conf or the "host spiralxprime web_app 192.168.0.72 scram-sha-256" line in pg_hba.conf yields the same result: no error message when it restarts, but the connection error message when I try to use psql.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 05 Dec 2024 19:36:24 +0000

This document https://www.postgresql.org/docs/current/app-postgres.html specifies the command line arguments and tcpip=true is not one of them 
same https://www.postgresql.org/docs/current/runtime-config-connection.html#RUNTIME-CONFIG-TCP-SETTINGS

---

From: "swadams2 (@swadams2)" <[email-masked]>
Date: Fri, 06 Dec 2024 15:18:23 +0000

Why would that line I added to pg_hba.conf cause the same problem?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 09 Dec 2024 13:55:52 +0000

You have to check the logs after changing configuration files to make sure PostgreSQL starts

---

From: "swadams2 (@swadams2)" <[email-masked]>
Date: Mon, 09 Dec 2024 21:28:56 +0000

I tried that and here's what I saw in the log.
2024-12-09 15:27:40.152 CST [1618406] LOG:  starting PostgreSQL 14.13 (Ubuntu 14.13-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2024-12-09 15:27:40.152 CST [1618406] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-12-09 15:27:40.152 CST [1618406] LOG:  listening on IPv6 address "::", port 5432
2024-12-09 15:27:40.153 CST [1618406] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-12-09 15:27:40.156 CST [1618406] LOG:  invalid IP mask "scram-sha-256": Name or service not known
2024-12-09 15:27:40.156 CST [1618406] CONTEXT:  line 101 of configuration file "/etc/postgresql/14/main/pg_hba.conf"
2024-12-09 15:27:40.156 CST [1618406] FATAL:  could not load pg_hba.conf
2024-12-09 15:27:40.158 CST [1618406] LOG:  database system is shut down

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 11 Dec 2024 16:42:24 +0000

right so you need to fix 
```
```

---

From: "swadams2 (@swadams2)" <[email-masked]>
Date: Thu, 12 Dec 2024 02:47:11 +0000

The line that triggers the error is:
host    spiralxprime    web_app         192.168.0.72            scram-sha-256

Why would it think "scram-sha-256" was meant to be an IP mask instead of a method?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 12 Dec 2024 15:09:00 +0000

you need `192.168.0.72/32`  if you want to specify a specific IP address

---

From: "swadams2 (@swadams2)" <[email-masked]>
Date: Sun, 15 Dec 2024 02:35:37 +0000

Thanks for that.  I was able to get Access to connect to it after making that change.