Bug 250068 - databases/postgresql{12|13}-server: psql: error: could not connect to server: server closed the connection unexpectedly
Summary: databases/postgresql{12|13}-server: psql: error: could not connect to server:...
Status: New
Alias: None
Product: Ports & Packages
Classification: Unclassified
Component: Individual Port(s) (show other bugs)
Version: Latest
Hardware: Any Any
: --- Affects Only Me
Assignee: pgsql
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-10-03 10:32 UTC by O. Hartmann
Modified: 2020-11-06 08:19 UTC (History)
1 user (show)

See Also:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description O. Hartmann 2020-10-03 10:32:05 UTC
Running databases/postgresql12on a recent CURRENT (at the moment FreeBSD 13.0-CURRENT #50 r366364: Fri Oct  2 17:51:18 CEST 2020 amd64). The problem persists a couple of months for now, last checked in March 2020. OS and ports are updated on a regular basis.

Host is running dual stack IPv4 and IPv6.

We switched password encryption from md5 to scram-sha-256 and I believe since then the mysterious problems occured, not sure. But I'll proceed and explain.

postgresql.conf has been adapted as well via "password_encryption = scram-sha-256". Passwords in question have been changed within databases accordingly, especially the passowrd for database superuser "postgres".

pg_hba.conf has been adapted accordingly as well:

local  all postgres scram-sha-256|trust # alternative for tests

host    all   postgres  127.0.0.1/32   scram-sha-256
host    all   postgres  ::1/128       scram-sha-256

The server is listening on all addresses, checked via sockstat.

Trying to login on any database with the owner of the database or even postgres results in:

psql -U postgres -d postgres -h localhost -W (or IP of the host, even 127.0.0.1 or ::1) 

Result:
psql: error: could not connect to server: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

Login omitting -h localhost (or IP) is all right. Login via any on-host client authenticating locally fails. Login via remote session from another host using psql client also results in a fail.

There are other users accessing databases and authentication via LDAP (OpenLDAP). This works from the local host as well from remote hosts.

Looking at log protocolls revela nothing. The failed logins seeme to bee seen by the server, the server logs aline like

LOG:  connection received: host=localhost port=37906

Then nothing and after a while I receive the error 

psql: error: could not connect to server: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.


This might be a bug.
Comment 1 O. Hartmann 2020-10-18 11:30:42 UTC
I just checked on several other CURRENT installations of ours and with different configuration option set when postgresql12|13-server is compiled - with our setup it is always the same.

The one thing we didn't check so far is whether the database itself, which is migrated via a simple dump (pg_dumpall) and rereading when the new setup postgresql server is running might be the culprit.

Compiling server and client with the standard config options resulted in the same fail as described above. 
Disabling ipfw (the firewall we use) or switching to pass everything doesn't change anything. Even allow explicitely ::1 and 127.0.0.1 doesn't change anything.

Again, hosts are configured to use IPv4 and IPv6!

Remote access via LDAP backed users to databases works fine.

Local access via socket works fine, but not when explicetely connect via tcp ::1 or 127.0.0.1!

Mots hosts in our lab run dual stack, so next I'll try to switch off IPV6.

New setups on freshly installed hosts with IPV4 only seem to work properly.