Problem with ssl and psql in Postgresql 13

Hi.

At Thu, 17 Dec 2020 07:21:08 +0000, Gustavsson Mikael <mikael.gustavsson@smhi.se> wrote in

But not from psql 13:
$ /usr/pgsql-13/bin/psql -d postgres -Ukalle -hserver -W
Password:
psql: error: FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off
FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off

It looks like the option "ssl" (in postgresql.conf) is turned off on
the v13 server. You will find lines like the following in the server
log in that case.

LOG: hostssl record cannot match because SSL is disabled
HINT: Set ssl = on in postgresql.conf.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Hi,

SSL is on.

grep ssl /etc/postgresql/13/postgresql.conf
ssl = 'on'
ssl_cert_file = '/etc/pki/private/xxx_incl_key.pem'
ssl_key_file = '/etc/pki/private/xxx.key'

Clarification, its the same postgresql13 server. I just connect with different psql-clients.

KR

________________________________
Från: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Skickat: den 17 december 2020 09:29:38
Till: Gustavsson Mikael
Kopia: pgsql-general@postgresql.org; Svensson Peter
Ämne: Re: Problem with ssl and psql in Postgresql 13

Hi.

At Thu, 17 Dec 2020 07:21:08 +0000, Gustavsson Mikael <mikael.gustavsson@smhi.se> wrote in

But not from psql 13:
$ /usr/pgsql-13/bin/psql -d postgres -Ukalle -hserver -W
Password:
psql: error: FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off
FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off

It looks like the option "ssl" (in postgresql.conf) is turned off on
the v13 server. You will find lines like the following in the server
log in that case.

LOG: hostssl record cannot match because SSL is disabled
HINT: Set ssl = on in postgresql.conf.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

Clarification, its the same postgresql13 server. I just connect with different psql-clients.

Perhaps turning on log_connections on the server would offer some
insight. It sort of looks like the v13 client is trying to connect
with SSL, failing for some unknown reason, and falling back to non-SSL
which also fails.

regards, tom lane

Hi,

log_connections is on. The ERR message is correct, we do not have an entry for SSL off.
The question is why psql(13) is trying to connect without ssl?

2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG: connection received: host=nnn.nn.n.nnn port=40112
2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 pguser=kalle pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off

KR
Mikael Gustavsson, SMHI

________________________________
Från: Tom Lane <tgl@sss.pgh.pa.us>
Skickat: den 17 december 2020 15:20:01
Till: Gustavsson Mikael
Kopia: Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: Re: SV: Problem with ssl and psql in Postgresql 13

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

Clarification, its the same postgresql13 server. I just connect with different psql-clients.

Perhaps turning on log_connections on the server would offer some
insight. It sort of looks like the v13 client is trying to connect
with SSL, failing for some unknown reason, and falling back to non-SSL
which also fails.

regards, tom lane

On Thu, Dec 17, 2020 at 3:36 PM Gustavsson Mikael
<mikael.gustavsson@smhi.se> wrote:

Hi,

log_connections is on. The ERR message is correct, we do not have an entry for SSL off.
The question is why psql(13) is trying to connect without ssl?

2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG: connection received: host=nnn.nn.n.nnn port=40112
2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 pguser=kalle pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off

By default psql/libpq will fall back to a clear text connection if the
ssl encrypted one failed. Specify sslmode=require (or preferably
higher) in the connection string or set PGSSLMODE=require on the
client to disable that behavior. If you do that, my guess is you will
see a direct connection failure instead of that error?

PostgreSQL 13 did change the default value for minimum tls version to
1.2. But that seems unlikely to be the problem since you get TLS 1.3
when you use the old version...

I assume you're running both the 11 and the 13 client on the same host?

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

Hi Magnus,

The clients 11 and 13 is on the same host.

Hmm, I get the same error if I set sslmode=require.

$ /usr/pgsql-13/bin/psql -d postgres --set=sslmode=require -Ukalle -hserver -W
Password:
psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off
FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off

KR Mikael Gustavsson, SMHI

________________________________
Från: Magnus Hagander <magnus@hagander.net>
Skickat: den 17 december 2020 15:52:55
Till: Gustavsson Mikael
Kopia: Tom Lane; Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: Re: SV: Problem with ssl and psql in Postgresql 13

On Thu, Dec 17, 2020 at 3:36 PM Gustavsson Mikael
<mikael.gustavsson@smhi.se> wrote:

Hi,

log_connections is on. The ERR message is correct, we do not have an entry for SSL off.
The question is why psql(13) is trying to connect without ssl?

2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG: connection received: host=nnn.nn.n.nnn port=40112
2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 pguser=kalle pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off

By default psql/libpq will fall back to a clear text connection if the
ssl encrypted one failed. Specify sslmode=require (or preferably
higher) in the connection string or set PGSSLMODE=require on the
client to disable that behavior. If you do that, my guess is you will
see a direct connection failure instead of that error?

PostgreSQL 13 did change the default value for minimum tls version to
1.2. But that seems unlikely to be the problem since you get TLS 1.3
when you use the old version...

I assume you're running both the 11 and the 13 client on the same host?

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

That's setting a variable, not a parameter.

You need something like

psql "dbname=postgres user=kalle host=server sslmode=require"

Or
PGSSLMODE=require /usr/bin/pgsql-same-as-you-had-before

//Magnus

On Thu, Dec 17, 2020 at 5:00 PM Gustavsson Mikael
<mikael.gustavsson@smhi.se> wrote:

Hi Magnus,

The clients 11 and 13 is on the same host.

Hmm, I get the same error if I set sslmode=require.

$ /usr/pgsql-13/bin/psql -d postgres --set=sslmode=require -Ukalle -hserver -W
Password:
psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off
FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off

KR Mikael Gustavsson, SMHI

________________________________
Från: Magnus Hagander <magnus@hagander.net>
Skickat: den 17 december 2020 15:52:55
Till: Gustavsson Mikael
Kopia: Tom Lane; Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: Re: SV: Problem with ssl and psql in Postgresql 13

On Thu, Dec 17, 2020 at 3:36 PM Gustavsson Mikael
<mikael.gustavsson@smhi.se> wrote:

Hi,

log_connections is on. The ERR message is correct, we do not have an entry for SSL off.
The question is why psql(13) is trying to connect without ssl?

2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG: connection received: host=nnn.nn.n.nnn port=40112
2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 pguser=kalle pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off

By default psql/libpq will fall back to a clear text connection if the
ssl encrypted one failed. Specify sslmode=require (or preferably
higher) in the connection string or set PGSSLMODE=require on the
client to disable that behavior. If you do that, my guess is you will
see a direct connection failure instead of that error?

PostgreSQL 13 did change the default value for minimum tls version to
1.2. But that seems unlikely to be the problem since you get TLS 1.3
when you use the old version...

I assume you're running both the 11 and the 13 client on the same host?

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

Sorry, my bad. But i get the same result.

$ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server sslmode=require"
psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off
FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off

/Mikael

________________________________
Från: Magnus Hagander <magnus@hagander.net>
Skickat: den 17 december 2020 17:03:18
Till: Gustavsson Mikael
Kopia: Tom Lane; Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: Re: SV: Problem with ssl and psql in Postgresql 13

That's setting a variable, not a parameter.

You need something like

psql "dbname=postgres user=kalle host=server sslmode=require"

Or
PGSSLMODE=require /usr/bin/pgsql-same-as-you-had-before

//Magnus

On Thu, Dec 17, 2020 at 5:00 PM Gustavsson Mikael
<mikael.gustavsson@smhi.se> wrote:

Hi Magnus,

The clients 11 and 13 is on the same host.

Hmm, I get the same error if I set sslmode=require.

$ /usr/pgsql-13/bin/psql -d postgres --set=sslmode=require -Ukalle -hserver -W
Password:
psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off
FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off

KR Mikael Gustavsson, SMHI

________________________________
Från: Magnus Hagander <magnus@hagander.net>
Skickat: den 17 december 2020 15:52:55
Till: Gustavsson Mikael
Kopia: Tom Lane; Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: Re: SV: Problem with ssl and psql in Postgresql 13

On Thu, Dec 17, 2020 at 3:36 PM Gustavsson Mikael
<mikael.gustavsson@smhi.se> wrote:

Hi,

log_connections is on. The ERR message is correct, we do not have an entry for SSL off.
The question is why psql(13) is trying to connect without ssl?

2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG: connection received: host=nnn.nn.n.nnn port=40112
2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 pguser=kalle pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off

By default psql/libpq will fall back to a clear text connection if the
ssl encrypted one failed. Specify sslmode=require (or preferably
higher) in the connection string or set PGSSLMODE=require on the
client to disable that behavior. If you do that, my guess is you will
see a direct connection failure instead of that error?

PostgreSQL 13 did change the default value for minimum tls version to
1.2. But that seems unlikely to be the problem since you get TLS 1.3
when you use the old version...

I assume you're running both the 11 and the 13 client on the same host?

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

$ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server sslmode=require"
psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off
FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off

It'd be useful to verify that that version of psql+libpq is actually
built with ssl support. Try

ldd /usr/pgsql-13/bin/psql

and then repeat "ldd" on whichever libpq.so is mentioned in the output.

regards, tom lane

Here is the result.

ldd /usr/pgsql-13/bin/psql
linux-vdso.so.1 (0x00007ffd714d5000)
libpq.so.5 => /usr/pgsql-13/lib/libpq.so.5 (0x00007f2d1700a000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f2d16dea000)
libreadline.so.7 => /lib64/libreadline.so.7 (0x00007f2d16b9b000)
libm.so.6 => /lib64/libm.so.6 (0x00007f2d16819000)
libc.so.6 => /lib64/libc.so.6 (0x00007f2d16456000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x00007f2d161c2000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f2d15cdc000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f2d15a87000)
libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x00007f2d15830000)
/lib64/ld-linux-x86-64.so.2 (0x00007f2d1725b000)
libtinfo.so.6 => /lib64/libtinfo.so.6 (0x00007f2d15603000)
libz.so.1 => /lib64/libz.so.1 (0x00007f2d153ec000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f2d151e8000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f2d14eff000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f2d14ce8000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f2d14ae4000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f2d148d3000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f2d146cf000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f2d144b8000)
liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x00007f2d142a8000)
libsasl2.so.3 => /lib64/libsasl2.so.3 (0x00007f2d1408a000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f2d13e60000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f2d13c37000)
libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x00007f2d139b3000)

ldd /usr/pgsql-13/lib/libpq.so.5
linux-vdso.so.1 (0x00007fff51f79000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x00007f88432d1000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f8842deb000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f8842b96000)
libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x00007f884293f000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f884271f000)
libc.so.6 => /lib64/libc.so.6 (0x00007f884235c000)
libz.so.1 => /lib64/libz.so.1 (0x00007f8842145000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f8841f41000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f8841c58000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f8841a41000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f884183d000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f884162c000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f8841428000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f8841211000)
liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x00007f8841001000)
libsasl2.so.3 => /lib64/libsasl2.so.3 (0x00007f8840de3000)
/lib64/ld-linux-x86-64.so.2 (0x00007f88437b6000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f8840bb9000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f8840990000)
libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x00007f884070c000)

/Mikael

________________________________
Från: Tom Lane <tgl@sss.pgh.pa.us>
Skickat: den 17 december 2020 17:25:31
Till: Gustavsson Mikael
Kopia: Magnus Hagander; Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: Re: SV: SV: Problem with ssl and psql in Postgresql 13

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

$ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server sslmode=require"
psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off
FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off

It'd be useful to verify that that version of psql+libpq is actually
built with ssl support. Try

ldd /usr/pgsql-13/bin/psql

and then repeat "ldd" on whichever libpq.so is mentioned in the output.

regards, tom lane

Hi,

I did some more tests to try to narrow it down. For me it only added to the confusion but maybe it tells you something.

Test 1:

I changed my pg_hba.conf from hostssl to host.

Now I can connect but SSL is not used even if i use require.

pgsql-13:

$ /usr/pgsql-13/bin/psql -d postgres -Ukalle -hserver
Password for user kalle:
psql (13.1)
Type "help" for help.

postgres=>

pgsql-13 with require:
$ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server sslmode=require"
Password for user kalle:
psql (13.1)
Type "help" for help.

postgres=>

pgsql-11 for reference:
$ /usr/pgsql-11/bin/psql -d postgres -Ukalle -hserver
Password for user kalle:
psql (11.10, server 13.1)
WARNING: psql major version 11, server major version 13.
Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

Test2:

It works when i connect pgsql-13 client to a postgresql-11 server. So it´s only the combination pgsql-13 client and postgresql-13 server that does not work.

$ /usr/pgsql-13/bin/psql -d postgres -Ukalle -hserver11
Password for user kalle:
psql (13.1, server 11.10)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

KR Mikael Gustavsson, SMHI

________________________________
Från: externaly-forwarded@smhi.se <externaly-forwarded@smhi.se> för Gustavsson Mikael <mikael.gustavsson@smhi.se>
Skickat: den 17 december 2020 17:33:13
Till: Tom Lane
Kopia: Magnus Hagander; Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: SV: SV: SV: Problem with ssl and psql in Postgresql 13

Here is the result.

ldd /usr/pgsql-13/bin/psql
linux-vdso.so.1 (0x00007ffd714d5000)
libpq.so.5 => /usr/pgsql-13/lib/libpq.so.5 (0x00007f2d1700a000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f2d16dea000)
libreadline.so.7 => /lib64/libreadline.so.7 (0x00007f2d16b9b000)
libm.so.6 => /lib64/libm.so.6 (0x00007f2d16819000)
libc.so.6 => /lib64/libc.so.6 (0x00007f2d16456000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x00007f2d161c2000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f2d15cdc000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f2d15a87000)
libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x00007f2d15830000)
/lib64/ld-linux-x86-64.so.2 (0x00007f2d1725b000)
libtinfo.so.6 => /lib64/libtinfo.so.6 (0x00007f2d15603000)
libz.so.1 => /lib64/libz.so.1 (0x00007f2d153ec000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f2d151e8000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f2d14eff000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f2d14ce8000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f2d14ae4000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f2d148d3000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f2d146cf000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f2d144b8000)
liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x00007f2d142a8000)
libsasl2.so.3 => /lib64/libsasl2.so.3 (0x00007f2d1408a000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f2d13e60000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f2d13c37000)
libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x00007f2d139b3000)

ldd /usr/pgsql-13/lib/libpq.so.5
linux-vdso.so.1 (0x00007fff51f79000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x00007f88432d1000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f8842deb000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f8842b96000)
libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x00007f884293f000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f884271f000)
libc.so.6 => /lib64/libc.so.6 (0x00007f884235c000)
libz.so.1 => /lib64/libz.so.1 (0x00007f8842145000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f8841f41000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f8841c58000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f8841a41000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f884183d000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f884162c000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f8841428000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f8841211000)
liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x00007f8841001000)
libsasl2.so.3 => /lib64/libsasl2.so.3 (0x00007f8840de3000)
/lib64/ld-linux-x86-64.so.2 (0x00007f88437b6000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f8840bb9000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f8840990000)
libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x00007f884070c000)

/Mikael

________________________________
Från: Tom Lane <tgl@sss.pgh.pa.us>
Skickat: den 17 december 2020 17:25:31
Till: Gustavsson Mikael
Kopia: Magnus Hagander; Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: Re: SV: SV: Problem with ssl and psql in Postgresql 13

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

$ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server sslmode=require"
psql: error: FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off
FATAL: no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database "postgres", SSL off

It'd be useful to verify that that version of psql+libpq is actually
built with ssl support. Try

ldd /usr/pgsql-13/bin/psql

and then repeat "ldd" on whichever libpq.so is mentioned in the output.

regards, tom lane

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

pgsql-13 with require:
$ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server sslmode=require"
Password for user kalle:
psql (13.1)
Type "help" for help.

That is just bizarre. libpq should not ignore the sslmode=require option
like that, unless it thinks it's making a Unix-socket connection, which
it should not think given the host specification. (There's not a slash
in your server's real name, is there? But if there was, v11 should
misbehave too.)

It seems like there must be some environment setting, or maybe a service
file, changing the behavior from what it should be on its face. But
that theory has big flaws too: an explicit sslmode=require setting should
not be overridable from environment, and even if it was, why wouldn't v11
act the same?

The only other conclusion I can think of is that your copy of libpq.so
is broken. Maybe you should try redownloading/reinstalling v13.

regards, tom lane

Hi,

Yes it´s odd. I think we begin with download/reinstall and take it from there.

The server name is just letters and numbers so I think we can rule that out.

Christmas is coming up fast as usual so I think I will pick this up in January.

Thanks for all the help and Happy Christmas! Or God Jul as we say in Sweden.

KR

Mikael Gustavsson, SMHI

________________________________
Från: Tom Lane <tgl@sss.pgh.pa.us>
Skickat: den 18 december 2020 21:02:50
Till: Gustavsson Mikael
Kopia: Magnus Hagander; Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: Re: SV: SV: SV: Problem with ssl and psql in Postgresql 13

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

pgsql-13 with require:
$ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server sslmode=require"
Password for user kalle:
psql (13.1)
Type "help" for help.

That is just bizarre. libpq should not ignore the sslmode=require option
like that, unless it thinks it's making a Unix-socket connection, which
it should not think given the host specification. (There's not a slash
in your server's real name, is there? But if there was, v11 should
misbehave too.)

It seems like there must be some environment setting, or maybe a service
file, changing the behavior from what it should be on its face. But
that theory has big flaws too: an explicit sslmode=require setting should
not be overridable from environment, and even if it was, why wouldn't v11
act the same?

The only other conclusion I can think of is that your copy of libpq.so
is broken. Maybe you should try redownloading/reinstalling v13.

regards, tom lane

Hi,

I did a final test before logging out for Christmas because i found a thread in hackers discussing some issue with GSS and SSL.

So if i set gssencmode=disable on my pgsql-13 to postgres 13 server connection i get an SSL connection.

Is this expected behaviour?

$ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server gssencmode=disable"
Password for user kalle:
psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

KR, Mikael Gustavsson, SMHI

________________________________
Från: externaly-forwarded@smhi.se <externaly-forwarded@smhi.se> för Gustavsson Mikael <mikael.gustavsson@smhi.se>
Skickat: den 22 december 2020 09:07:17
Till: Tom Lane
Kopia: Magnus Hagander; Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: SV: SV: SV: SV: Problem with ssl and psql in Postgresql 13

Hi,

Yes it´s odd. I think we begin with download/reinstall and take it from there.

The server name is just letters and numbers so I think we can rule that out.

Christmas is coming up fast as usual so I think I will pick this up in January.

Thanks for all the help and Happy Christmas! Or God Jul as we say in Sweden.

KR

Mikael Gustavsson, SMHI

________________________________
Från: Tom Lane <tgl@sss.pgh.pa.us>
Skickat: den 18 december 2020 21:02:50
Till: Gustavsson Mikael
Kopia: Magnus Hagander; Kyotaro Horiguchi; pgsql-general@postgresql.org; Svensson Peter
Ämne: Re: SV: SV: SV: Problem with ssl and psql in Postgresql 13

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

pgsql-13 with require:
$ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server sslmode=require"
Password for user kalle:
psql (13.1)
Type "help" for help.

That is just bizarre. libpq should not ignore the sslmode=require option
like that, unless it thinks it's making a Unix-socket connection, which
it should not think given the host specification. (There's not a slash
in your server's real name, is there? But if there was, v11 should
misbehave too.)

It seems like there must be some environment setting, or maybe a service
file, changing the behavior from what it should be on its face. But
that theory has big flaws too: an explicit sslmode=require setting should
not be overridable from environment, and even if it was, why wouldn't v11
act the same?

The only other conclusion I can think of is that your copy of libpq.so
is broken. Maybe you should try redownloading/reinstalling v13.

regards, tom lane

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

I did a final test before logging out for Christmas because i found a thread in hackers discussing some issue with GSS and SSL.
So if i set gssencmode=disable on my pgsql-13 to postgres 13 server connection i get an SSL connection.

Oooh ... that's the missing ingredient. Do you have a GSS credentials
cache on the client side, but no support on the server side?

It looks like, if there is a credentials cache and gssencmode isn't
explicitly disabled, we try GSS first. If the server refuses that:

if (gss_ok == 'N')
{
/* Server doesn't want GSSAPI; fall back if we can */
if (conn->gssencmode[0] == 'r')
{
appendPQExpBufferStr(&conn->errorMessage,
libpq_gettext("server doesn't support GSSAPI encryption, but it was required\n"));
goto error_return;
}

conn->try_gss = false;
conn->status = CONNECTION_MADE;
return PGRES_POLLING_WRITING;
}

that is, it decides the connection it has is good enough. This
is not OK if SSL should have been used.

regards, tom lane

I wrote:

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

So if i set gssencmode=disable on my pgsql-13 to postgres 13 server connection i get an SSL connection.

It looks like, if there is a credentials cache and gssencmode isn't
explicitly disabled, we try GSS first. If the server refuses that:
...
that is, it decides the connection it has is good enough. This
is not OK if SSL should have been used.

No, I misread the code; what will happen next is that an SSL connection
will be tried. At least, it looks like that should happen, and it does
happen for me.

However: it is true (and undocumented, so we have at least a docs bug
to fix) that v12-and-later libpq will try for GSS encryption first,
and if it succeeds then it will not consider using SSL, regardless of
sslmode. So about 95% of your report could be explained by assuming
that you have a working Kerberos environment and the newer libpq is
preferring GSS encryption over SSL. There is just one thing this
theory is failing to explain: instead of "SSL connection", psql
should have printed "GSSAPI-encrypted connection" in your test
shown in <d3ab9042bce34aae85d323d69e3ee430@smhi.se>. It didn't,
so this can't be the true explanation. I think what must be
happening is that libpq is trying for GSS (hence you have at least
a credentials cache somewhere), failing to establish it, and then
for some reason advancing to the startup-packet step without
trying for SSL. But I can't see how to get the state machine to
do that.

Have you got any environment variables, service files, etc
that would affect libpq's behavior?

regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

I wrote:

Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:

So if i set gssencmode=disable on my pgsql-13 to postgres 13 server connection i get an SSL connection.

It looks like, if there is a credentials cache and gssencmode isn't
explicitly disabled, we try GSS first. If the server refuses that:
...
that is, it decides the connection it has is good enough. This
is not OK if SSL should have been used.

No, I misread the code; what will happen next is that an SSL connection
will be tried. At least, it looks like that should happen, and it does
happen for me.

However: it is true (and undocumented, so we have at least a docs bug
to fix) that v12-and-later libpq will try for GSS encryption first,
and if it succeeds then it will not consider using SSL, regardless of
sslmode. So about 95% of your report could be explained by assuming
that you have a working Kerberos environment and the newer libpq is
preferring GSS encryption over SSL. There is just one thing this
theory is failing to explain: instead of "SSL connection", psql
should have printed "GSSAPI-encrypted connection" in your test
shown in <d3ab9042bce34aae85d323d69e3ee430@smhi.se>. It didn't,
so this can't be the true explanation. I think what must be
happening is that libpq is trying for GSS (hence you have at least
a credentials cache somewhere), failing to establish it, and then
for some reason advancing to the startup-packet step without
trying for SSL. But I can't see how to get the state machine to
do that.

Not sure how much it helps, but yes, the general idea is that if you've
got a Kerberos credential cache, then we're going to try GSS encryption
first and, if that succeeds, we'll use it.

The docs do say this-

https://www.postgresql.org/docs/current/libpq-connect.html

under gssencmode / prefer (default)

if there are GSSAPI credentials present (i.e., in a credentials cache),
first try a GSSAPI-encrypted connection; if that fails or there are
no credentials, try a non-GSSAPI-encrypted connection. This is the
default when PostgreSQL has been compiled with GSSAPI support.

Though we also say under sslmode / prefer (default) -

first try an SSL connection; if that fails, try a non-SSL connection

Obviously they can't both be 'first', so it would probably make sense to
update the documentation, though exactly how I'm not sure. Perhaps
under sslmode / prefer:

first try an SSL connection (note, however, that if there are GSSAPI
credentials present and gssencmode is also set to 'prefer', then a
GSSAPI-encrypted connection will be attempted first); if that fails, try
a non-SSL connection

?

Or perhaps a Note would be better to explain that we try GSSAPI
encryption first if GSSAPI credentials exist and both are set to
'prefer'. The whole situation around 'prefer' is pretty grotty for all
cases, though I suppose that isn't really news these days.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

However: it is true (and undocumented, so we have at least a docs bug
to fix) that v12-and-later libpq will try for GSS encryption first,
and if it succeeds then it will not consider using SSL, regardless of
sslmode. So about 95% of your report could be explained by assuming
that you have a working Kerberos environment and the newer libpq is
preferring GSS encryption over SSL. There is just one thing this
theory is failing to explain: instead of "SSL connection", psql
should have printed "GSSAPI-encrypted connection" in your test
shown in <d3ab9042bce34aae85d323d69e3ee430@smhi.se>. It didn't,
so this can't be the true explanation. I think what must be
happening is that libpq is trying for GSS (hence you have at least
a credentials cache somewhere), failing to establish it, and then
for some reason advancing to the startup-packet step without
trying for SSL. But I can't see how to get the state machine to
do that.

Though we also say under sslmode / prefer (default) -
first try an SSL connection; if that fails, try a non-SSL connection
Obviously they can't both be 'first', so it would probably make sense to
update the documentation, though exactly how I'm not sure.

Yeah; the problem is that the sslmode docs don't mention that GSS comes
first. I was thinking of adding verbiage along the lines of "Note that
if GSS encryption is possible, that will be used in preference to SSL,
regardless of the value of sslmode".

In the meantime, I did spot a code path that would explain the symptoms:
pqsecure_open_gss() clears allow_ssl_try sooner than it oughta. If
gss_wrap_size_limit() failed for some reason, we'd abandon the GSS
connection and try another one, and we would *not* try to SSL-ify
the new one. While that's clearly a bug, I'm dubious that it explains
this report, because it hardly seems likely that gss_wrap_size_limit()
would fail when we've already successfully negotiated an encrypted
connection. Can you think of a plausible reason for that to happen?

regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

However: it is true (and undocumented, so we have at least a docs bug
to fix) that v12-and-later libpq will try for GSS encryption first,
and if it succeeds then it will not consider using SSL, regardless of
sslmode. So about 95% of your report could be explained by assuming
that you have a working Kerberos environment and the newer libpq is
preferring GSS encryption over SSL. There is just one thing this
theory is failing to explain: instead of "SSL connection", psql
should have printed "GSSAPI-encrypted connection" in your test
shown in <d3ab9042bce34aae85d323d69e3ee430@smhi.se>. It didn't,
so this can't be the true explanation. I think what must be
happening is that libpq is trying for GSS (hence you have at least
a credentials cache somewhere), failing to establish it, and then
for some reason advancing to the startup-packet step without
trying for SSL. But I can't see how to get the state machine to
do that.

Though we also say under sslmode / prefer (default) -
first try an SSL connection; if that fails, try a non-SSL connection
Obviously they can't both be 'first', so it would probably make sense to
update the documentation, though exactly how I'm not sure.

Yeah; the problem is that the sslmode docs don't mention that GSS comes
first. I was thinking of adding verbiage along the lines of "Note that
if GSS encryption is possible, that will be used in preference to SSL,
regardless of the value of sslmode".

Sure, that seems alright to me.

In the meantime, I did spot a code path that would explain the symptoms:
pqsecure_open_gss() clears allow_ssl_try sooner than it oughta. If
gss_wrap_size_limit() failed for some reason, we'd abandon the GSS
connection and try another one, and we would *not* try to SSL-ify
the new one. While that's clearly a bug, I'm dubious that it explains
this report, because it hardly seems likely that gss_wrap_size_limit()
would fail when we've already successfully negotiated an encrypted
connection. Can you think of a plausible reason for that to happen?

There are certainly some very odd GSSAPI implementations out there
(including hacked up things particularly on Macs that we've seen..),
though I've not run into one where I've seen that call fail.

If it did fail though, surely the error messages would make that
clear, and we're not seeing that here..? If we do succeed with a GSS
connection (which seems extremely unlikely to just happen without the
individual setting the system up knowing that they've created an
appropriate Kerberos keytab with a postgres service princ...), then, as
you say up-thread, psql should have printed out that we made a GSSAPI
encrypted connection.

I'm not sure how, but it sure seems like somehow, in this case, we're
going through the GSSAPI encryption code because we find GSSAPI
credentials (entirely possible, even if you're not using them for
talking to PG), the server responds with a "I haven't got support for
that" in some fashion, and we're skipping SSL at that point. How that's
happening isn't clear to me though, but the idea that it's because
gss_wrap_size_limit() is failing seems very unlikely.

Thanks,

Stephen