allowing remote connections on Apple M1 mini, macOS 13.3.1, and Postgres 14.7

[dwilbur-sutrobio]

adding this line to pg_hba.conf:
host all all 0.0.0.0/0 password

and this line to postgresql.conf:
listen_addresses = '*'

restart postgres.

try to connect:
psql bonkers --host=10.1.10.89 --username=wildboar
psql: error: connection to server at "10.1.10.89", port 5432 failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

look at log file:
2023-04-26 17:16:03.873 PDT [19878] LOG: database system is ready to accept connections
2023-04-26 17:17:36.969 PDT [19878] LOG: setsockopt(TCP_NODELAY) failed: Invalid argument

is there something else I need to do? this is all I would need to do on ubuntu 22.04 provided postgres 14.7...

I guess I should add that it's working fine for local connections, eg:
psql bonkers

[jakob]

I would recommend to use md5 auth method instead of password, but otherwise it should be enough.

I'm not sure why you are getting the setsockopt(TCP_NODELAY) error. Since we received a lot of support requests from homebrew users recently, can you confirm that you are using Postgres.app?

Which version / build of Postgres.app are you using? (Open Postgres.app GUI, select "About Postgres" from the "Postgres" menu)

[dwilbur-sutrobio]

I apologize for missing your response earlier, I just noticed the close mail. I would have to reinstall on a current macOS machine since we were forced to find alternate ubuntu solution so we could move forward... however it's suboptimal and we would like to resolve this for macOS. I had set it to password to remove that being an issue, my default is to set it to what you stated, ie: md5. as to what version and was I using postgres.app it would be what ever was current at the time of the original posting for all relevant software, and os... and it would have to have been postgres.app because that was all that was installed on the machine other then iTerm, sublime, homebrew (for python and flask) and a default macOS install. just to be clear we had tried installing homebrew's postgres on ->another machine<- and experienced similar issues at the time. that was why were were trying postgres.app as an alternate solution.

[jakob]

Thanks for the update. Please try reinstalling Postgres.app and see if the issue persists.

It could be that the problem is related to one of the network interfaces on your Mac. What does your network look like? Are you using a VPN, a firewall, a network extension like Little Snitch?

Are you using any Anti-Virus or other Security software on your Mac?

Instead of using listen_addresses = '*', can you try listening on just specific network interfaces? Eg. first try with
'127.0.0.1', and see if that works (sql -h 127.0.0.1), then try all the other IP addresses your Mac has.