Horton

Thread

Commits

  1. Fix up usage of krb_server_keyfile GUC parameter.

  2. Improve log messages related to pg_hba.conf not matching a connection.

  3. Fix assorted issues in backend's GSSAPI encryption support.

  4. Fix bugs in libpq's GSSAPI encryption support.

  1. Problem with ssl and psql in Postgresql 13

    Mikael Gustavsson <mikael.gustavsson@smhi.se> — 2020-12-17T07:21:08Z

    Hi,
We are starting our journey to migrate from Postgresql 11 to Postgresql 13 and have run into a problem.

The postgresql 13 version of psql don´t seem to work with ssl.
We use the community built rpms on redhat 8.3:

$ rpm -q postgresql11
postgresql11-11.10-1PGDG.rhel8.x86_64

$ rpm -q postgresql13
postgresql13-13.1-1PGDG.rhel8.x86_64

Is there some configuration we have missed that have changed between Postgresql 11 and 13?

Here is an example, sorry for the obfuscations.

It works as expected from psql 11:
$ psql -d postgres -Ukalle -hserver -W
Password:
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=>

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


Our mean all-ssl test pg_hba.conf file:
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     reject
# IPv4 local connections:
hostssl all             all             0.0.0.0/0                pam
# IPv6 local connections:
#host   all             all              ::1/128                 reject
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication          postgres                               reject


Kind Regards
Mikael Gustavsson, SMHI - Swedish Meteorological and Hydrological Institute

  2. Re: Problem with ssl and psql in Postgresql 13

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-17T08:29:38Z

    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

  3. SV: Problem with ssl and psql in Postgresql 13

    Mikael Gustavsson <mikael.gustavsson@smhi.se> — 2020-12-17T09:11:13Z

    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

  4. Re: SV: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-17T14:20:01Z

    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

  5. SV: SV: Problem with ssl and psql in Postgresql 13

    Mikael Gustavsson <mikael.gustavsson@smhi.se> — 2020-12-17T14:35:45Z

    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

  6. Re: SV: Problem with ssl and psql in Postgresql 13

    Magnus Hagander <magnus@hagander.net> — 2020-12-17T14:52:55Z

    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/

  7. SV: SV: Problem with ssl and psql in Postgresql 13

    Mikael Gustavsson <mikael.gustavsson@smhi.se> — 2020-12-17T16:00:50Z

    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/

  8. Re: SV: Problem with ssl and psql in Postgresql 13

    Magnus Hagander <magnus@hagander.net> — 2020-12-17T16:03:18Z

    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/

  9. SV: SV: Problem with ssl and psql in Postgresql 13

    Mikael Gustavsson <mikael.gustavsson@smhi.se> — 2020-12-17T16:19:33Z

    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/

  10. Re: SV: SV: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-17T16:25:31Z

    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

  11. SV: SV: SV: Problem with ssl and psql in Postgresql 13

    Mikael Gustavsson <mikael.gustavsson@smhi.se> — 2020-12-17T16:33:13Z

    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

  12. SV: SV: SV: Problem with ssl and psql in Postgresql 13

    Mikael Gustavsson <mikael.gustavsson@smhi.se> — 2020-12-18T08:17:03Z

    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

  13. Re: SV: SV: SV: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-18T20:02:50Z

    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

  14. SV: SV: SV: SV: Problem with ssl and psql in Postgresql 13

    Mikael Gustavsson <mikael.gustavsson@smhi.se> — 2020-12-22T08:07:17Z

    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

  15. SV: SV: SV: SV: Problem with ssl and psql in Postgresql 13

    Mikael Gustavsson <mikael.gustavsson@smhi.se> — 2020-12-23T09:50:25Z

    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

  16. Re: SV: SV: SV: SV: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-23T16:28:21Z

    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

  17. Re: SV: SV: SV: SV: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-23T20:58:46Z

    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

  18. Re: Problem with ssl and psql in Postgresql 13

    Stephen Frost <sfrost@snowman.net> — 2020-12-23T21:14:48Z

    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

  19. Re: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-23T21:27:27Z

    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

  20. Re: Problem with ssl and psql in Postgresql 13

    Stephen Frost <sfrost@snowman.net> — 2020-12-23T21:45:34Z

    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

  21. Re: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-23T21:56:07Z

    Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> 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.

> 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.

The OP said he's on RHEL 8.3, so it ought to be mostly-stock Kerberos.
I've just been digging through the SRPM to see if they'd done anything
odd for FIPS mode or the like, and I'm not really finding anything.

> If it did fail though, surely the error messages would make that
> clear, and we're not seeing that here..?

No, because we won't report the failure from gss_wrap_size_limit;
we just discard that connection and try again.

> 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.

I already spent most of the day on theories like that, without any
success.  I agree that this theory seems quite unlikely, but you
remember what Sherlock Holmes said.

			regards, tom lane

  22. Re: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-23T22:34:05Z

    Oh ... wait a second.  I've just managed to partially reproduce this
report.  Assume that we have a working Kerberos environment on both
ends.  Then libpq will establish a connection with GSS encryption
and continue on with the usual PG authentication exchange.  If that
part fails, then this logic (fe_connect.c around line 3350):

                    /*
                     * If gssencmode is "prefer" and we're using GSSAPI, retry
                     * without it.
                     */
                    if (conn->gssenc && conn->gssencmode[0] == 'p')
                    {
                        /* postmaster expects us to drop the connection */
                        conn->try_gss = false;
                        pqDropConnection(conn, true);
                        conn->status = CONNECTION_NEEDED;
                        goto keep_going;
                    }

drops the connection and tries again without GSS.  But *we already
cleared allow_ssl_try*, in pqsecure_open_gss, so the next try will
not bother with SSL either.

So this directly explains the original report: if the only applicable
pg_hba.conf entry is "hostssl", right here is where libpq will see
that error, and it will go around and fail again because the next
try uses neither GSS nor SSL.

However, in the tests Mikael ran after backing that pg_hba.conf entry
off to just "host", pg_hba wouldn't have caused an authentication-stage
failure, so it's not so clear why we'd have looped back at this step.
We'd need to explain the later cases to have a full theory.  It's
plenty plausible that something else caused an auth-stage failure,
but if so, why would the second GSS-free try work?

In any case, that allow_ssl_try reset in pqsecure_open_gss is definitely
even more broken than I thought before.  I think we need to rejigger the
state machine so that it doesn't do that at all, but instead skips SSL
establishment when GSS is already active via an explicit test, rather
than munging the state variables.

			regards, tom lane

  23. Re: Problem with ssl and psql in Postgresql 13

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-24T03:05:20Z

    At Wed, 23 Dec 2020 17:34:05 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote in 
> Oh ... wait a second.  I've just managed to partially reproduce this
> report.  Assume that we have a working Kerberos environment on both

If I kninit'ed and pg_hba.conf doesn't have a hostgssenc line and has
a hostssl line, I got the following error from psql.

$ psql -d postgres --set=sslmode=require -Uhoriguti -hmyhost.mydomain
psql: error: FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti", database "postgres", SSL off
FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti", database "postgres", SSL off

The state machine enables gssenc if a valid credential is available
regardless of whether a valid gsshostenc definition is available or not.

I rembmer that I am annoyed a bit by this behavior, but I didn't care
of that since I thought that SSL and GSS are not used togehter in an
environemt.

> ends.  Then libpq will establish a connection with GSS encryption
> and continue on with the usual PG authentication exchange.  If that
> part fails, then this logic (fe_connect.c around line 3350):
> 
>                     /*
>                      * If gssencmode is "prefer" and we're using GSSAPI, retry
>                      * without it.
>                      */
>                     if (conn->gssenc && conn->gssencmode[0] == 'p')
>                     {
>                         /* postmaster expects us to drop the connection */
>                         conn->try_gss = false;
>                         pqDropConnection(conn, true);
>                         conn->status = CONNECTION_NEEDED;
>                         goto keep_going;
>                     }
>
> drops the connection and tries again without GSS.  But *we already
> cleared allow_ssl_try*, in pqsecure_open_gss, so the next try will
> not bother with SSL either.
> So this directly explains the original report: if the only applicable
> pg_hba.conf entry is "hostssl", right here is where libpq will see
> that error, and it will go around and fail again because the next
> try uses neither GSS nor SSL.
>
> However, in the tests Mikael ran after backing that pg_hba.conf entry
> off to just "host", pg_hba wouldn't have caused an authentication-stage
> failure, so it's not so clear why we'd have looped back at this step.

In the hostssl case, there are two errors emitted on the server side:

[24494] FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti", database "postgres", SSL off
[24495] FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti", database "postgres", SSL off

However, the first line is bogus. It should be like the following.

FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti", database "postgres", GSSENC on
FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti", database "postgres", SSL off

The attached the first patch does that.
The client picks up only the second error to show from psql.

If I changed the hostssl to just host, host line matches
GSSENC-connection and authentication succeeds without an error.

> We'd need to explain the later cases to have a full theory.  It's
> plenty plausible that something else caused an auth-stage failure,
> but if so, why would the second GSS-free try work?

Does my expample above works?

As the result, psql loses the cause of the first failure and shows the
bogus message. On the other hand the server doesn't know the second
try is the result of the failure of the first try. If we want to show
meaningful messages to users, psql should show both the two messages,
or need to modify the protocol to pass around the information on the
failure of the previous try.

> In any case, that allow_ssl_try reset in pqsecure_open_gss is definitely
> even more broken than I thought before.  I think we need to rejigger the
> state machine so that it doesn't do that at all, but instead skips SSL
> establishment when GSS is already active via an explicit test, rather
> than munging the state variables.

It could make things simpler, but I'm not sure though. Anyway the
states are needed since PQconnectPoll(the state machine) needs to once
exit for negotiation and each of the two paths needs a re-entry point.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  24. Re: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-24T03:22:21Z

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> writes:
> At Wed, 23 Dec 2020 17:34:05 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote in 
>> However, in the tests Mikael ran after backing that pg_hba.conf entry
>> off to just "host", pg_hba wouldn't have caused an authentication-stage
>> failure, so it's not so clear why we'd have looped back at this step.

> In the hostssl case, there are two errors emitted on the server side:
> [24494] FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti", database "postgres", SSL off
> [24495] FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti", database "postgres", SSL off
> However, the first line is bogus. It should be like the following.
> FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti", database "postgres", GSSENC on
> FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti", database "postgres", SSL off
> The attached the first patch does that.

+1, it seems like a clear oversight that the GSSENC patches didn't adjust
these messages.  The reason SSL state is mentioned is that it's relevant
to which pg_hba entry gets chosen; and once we invented "hostgssenc"
entries, GSSENC state is also relevant.  I'm not sure though if we want
to spell it like "GSSENC on", or "GSS encryption on", or something else.
Stephen or other onlookers, any comments?

>> We'd need to explain the later cases to have a full theory.  It's
>> plenty plausible that something else caused an auth-stage failure,
>> but if so, why would the second GSS-free try work?

> Does my expample above works?

I don't see how it explains Mikael's later tests, but maybe I'm missing
something.

			regards, tom lane

  25. Re: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-24T16:54:32Z

    I wrote:
> Kyotaro Horiguchi <horikyota.ntt@gmail.com> writes:
>> The attached the first patch does that.

> +1, it seems like a clear oversight that the GSSENC patches didn't adjust
> these messages.  The reason SSL state is mentioned is that it's relevant
> to which pg_hba entry gets chosen; and once we invented "hostgssenc"
> entries, GSSENC state is also relevant.

Thinking a little more about that: there are not four distinct states,
because GSS and SSL can't both be enabled (ProcessStartupPacket enforces
that).  So I propose that instead of adding a new field, we make the
existing field say one of three things: "GSS encryption", "SSL
encryption", or "no encryption".  As per attached.  In the back branches,
it might be best to spell these as "GSS encryption", "SSL on", and "SSL
off", just to minimize the cosmetic change.

			regards, tom lane

  26. Re: Problem with ssl and psql in Postgresql 13

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-25T02:32:13Z

    At Thu, 24 Dec 2020 11:54:32 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote in 
> I wrote:
> > Kyotaro Horiguchi <horikyota.ntt@gmail.com> writes:
> >> The attached the first patch does that.
> 
> > +1, it seems like a clear oversight that the GSSENC patches didn't adjust
> > these messages.  The reason SSL state is mentioned is that it's relevant
> > to which pg_hba entry gets chosen; and once we invented "hostgssenc"
> > entries, GSSENC state is also relevant.
> 
> Thinking a little more about that: there are not four distinct states,
> because GSS and SSL can't both be enabled (ProcessStartupPacket enforces
> that).  So I propose that instead of adding a new field, we make the
> existing field say one of three things: "GSS encryption", "SSL
> encryption", or "no encryption".  As per attached.  In the back branches,
> it might be best to spell these as "GSS encryption", "SSL on", and "SSL
> off", just to minimize the cosmetic change.

Looks good to me.

I tried the same case where

- did kinit
- pg_hba has hostssl line only

I saw the following lines in server log, which seems good.

FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti@MYDOMAIN.COM", database "postgres", GSS encryption
FATAL:  no pg_hba.conf entry for host "192.168.56.101", user "horiguti@MYDOMAIN.COM", database "postgres", no encryption

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  27. Re: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-26T21:00:10Z

    I wrote:
> So this directly explains the original report: if the only applicable
> pg_hba.conf entry is "hostssl", right here is where libpq will see
> that error, and it will go around and fail again because the next
> try uses neither GSS nor SSL.

> However, in the tests Mikael ran after backing that pg_hba.conf entry
> off to just "host", pg_hba wouldn't have caused an authentication-stage
> failure, so it's not so clear why we'd have looped back at this step.
> We'd need to explain the later cases to have a full theory.  It's
> plenty plausible that something else caused an auth-stage failure,
> but if so, why would the second GSS-free try work?

So, after actually trying to reproduce this error, the answer
to that question is blindingly obvious:

2020-12-26 15:34:01.425 EST [1853] FATAL:  GSSAPI encryption can only be used with gss, trust, or reject authentication methods

You'll recall that Mikael was using PAM auth; I hit this with
just regular password auth.

So, given that the server and client are in a Kerberos-enabled
environment, they'll negotiate a GSS-encrypted connection (that the
user did not ask for), then the server refuses to use it because of
the above restriction, then libpq drops down to a new connection
... which, because of this allow_ssl_try bug, is not SSL-encrypted
but just plain, even if the user had set sslmode=require.

I'd say this is not just a nasty bug, but verges on requiring a CVE.

I'm also of the opinion that this restriction on authentication type
is useless damfool nannyism, and we should just drop it.  Sure, if
you've managed to GSS-ify the connection then the client is known
to the Kerberos system, but that proves little about whether the
client is entitled to log into the database under the username
he's asking for.  Why should we restrict how that auth is done?
Even if there's some reason to do so, it's hard to believe that the
reason justifies forcing a whole new round of encrypted-connection
setup to switch to SSL encryption, when we have a perfectly good
encrypted connection already.

			regards, tom lane

PS: AFAICS, it's also undocumented useless damfool nannyism.

  28. Re: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-26T22:06:17Z

    Here's a draft patch for the libpq-side issues.  The core of the
fix is to get rid of pqsecure_open_gss's clearing of allow_ssl_try,
and instead check whether GSS encryption is already enabled before
we try to enable SSL.  While I was at it I also fixed the places
where we drop an attempted GSS connection: they should set
need_new_connection = true rather than incompletely doing it for
themselves.  Notably that coding misses resetting auth_req_received
and password_needed; the consequences of that are minor but not zero.

There are things to fix on the server side, and the documentation
needs work, but this should be enough to solve Mikael's problem
if he's in a position to apply the patch locally.

			regards, tom lane

  29. Re: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-27T22:32:54Z

    Here's a more complete patchset.

0001 is the same libpq fixes I posted before.

0002 fixes a collection of random server-side issues, including:

* The GSS encryption code figured it could just ereport(ERROR) or
ereport(FATAL) for unrecoverable errors.  This seems entirely unsafe,
because elog.c will try to send the error message to the client,
likely re-triggering the same error and leading to infinite recursion.
Even if that doesn't happen, we might have sent a partial packet
to the client meaning that protocol synchronization is lost.  The
right thing to do is like what the SSL encryption code has long done:
log the error at COMMERROR level and then return errno = ECONNRESET
to shut things down.

* Fix sloppiness about whether pg_GSS_error()'s message parameter
is already translated or not.  (BTW, I'm a bit inclined to remove
pg_GSS_error()'s elevel parameter altogether and hardwire it as
COMMERROR, thus removing a bug temptation and making it more like the
similarly-named frontend function.  I didn't pull the trigger on that
here, though.)

* I really didn't like that we had the postmaster allocating the
port->gss struct (and committing hara-kiri if it could not).
Admittedly, the struct is not so large that an OOM failure is
likely, but it's just dumb to make the postmaster allocate a
struct it has zero use for.  I moved that allocation to the code
that actually needs it, GSSAPI encryption or auth startup.

* That also allows fixing a bug I noticed earlier, that the
"connection authorized" log message fails to mention GSS if we are
only using GSS for encryption without invoking GSS auth.  We can
use whether or not port->gss is non-null to figure out whether
the GSS clause of the message should appear.  (This is related to,
but distinct from, the pg_hba.conf-related log message fixes
discussed upthread.  I've not included those changes here, but
they're still on the table.)

* BackendStatusShmemSize failed to account for GSS-related space.
With large max_connections this could make a serious dent in our
shared-memory slop space.

* Various places knew more than they needed to about whether
port->gss->auth being set is equivalent to port->gss->princ being set.

* Remove long-dead call of secure_close() in postmaster's
ConnFree subroutine.

The 0003 patch removes the restriction I griped of earlier about
which auth methods can be used on a GSS-encrypted connection.
I made that a separate patch just in case it's controversial.

Finally, 0004 tries to improve the documentation in this area.
Some of that is reflective of 0003, but most of it is just
cleanup.

I propose to back-patch all of this as far as v12.

			regards, tom lane

  30. SV: Problem with ssl and psql in Postgresql 13

    Svensson Peter <peter.svensson@smhi.se> — 2020-12-29T15:05:31Z

    Hi,

Yes, libpq patch solved our problem.

Before patch:
psql -h myserver -U myuser -d postgres
psql: error: FATAL:  no pg_hba.conf entry for host "ip", user "myuser", database "postgres", SSL off
FATAL:  no pg_hba.conf entry for host "ip", user "myuser", database "postgres", SSL off


With patched libpq:

$ psql -h myserver -U myuser -d postgres
Password for user myuser:
psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>



Best regards,

Peter Svensson, SMHI

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

Here's a draft patch for the libpq-side issues.  The core of the
fix is to get rid of pqsecure_open_gss's clearing of allow_ssl_try,
and instead check whether GSS encryption is already enabled before
we try to enable SSL.  While I was at it I also fixed the places
where we drop an attempted GSS connection: they should set
need_new_connection = true rather than incompletely doing it for
themselves.  Notably that coding misses resetting auth_req_received
and password_needed; the consequences of that are minor but not zero.

There are things to fix on the server side, and the documentation
needs work, but this should be enough to solve Mikael's problem
if he's in a position to apply the patch locally.

                        regards, tom lane

  31. Re: SV: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-29T15:17:04Z

    Svensson Peter <peter.svensson@smhi.se> writes:
> Yes, libpq patch solved our problem.

Thanks for confirming!  I've pushed that patch and the server-side
fixes, so it'll be in February's releases.

			regards, tom lane

  32. Re: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-29T17:32:36Z

    I've got one last complaint about the backend GSS code: we are doing
things randomly differently in the two places that install
krb_server_keyfile as the active KRB5_KTNAME value.  secure_open_gssapi()
sets KRB5_KTNAME unconditionally (and doesn't bother to check for error,
either, not a good thing in a security-critical operation).  But the older
code in pg_GSS_recvauth() is written to not override KRB5_KTNAME if it's
already set.  This of-course-totally-undocumented behavior seems like a
fairly bad idea to me: as things stand, the client-side choice of whether
to initiate GSS encryption or not could result in two different server
keytabs being used.

I think we'd be best off to always override KRB5_KTNAME if we have a
nonempty krb_server_keyfile setting, so the attached proposed patch
makes both functions do it the same way.  (I did not make an effort
to remove the dependency on setenv, given the nearby thread to
standardize on that.)

I'm not sure whether there's any documentation change that needs to
be made.  The docs don't suggest that you're allowed to set
krb_server_keyfile to an empty string in the first place, so maybe
we needn't explain what happens if you do.

			regards, tom lane

  33. Re: Problem with ssl and psql in Postgresql 13

    Stephen Frost <sfrost@snowman.net> — 2020-12-30T15:00:40Z

    Greetings,

First, thanks a lot for working on all of this and improving things!

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> I've got one last complaint about the backend GSS code: we are doing
> things randomly differently in the two places that install
> krb_server_keyfile as the active KRB5_KTNAME value.  secure_open_gssapi()
> sets KRB5_KTNAME unconditionally (and doesn't bother to check for error,
> either, not a good thing in a security-critical operation).  But the older
> code in pg_GSS_recvauth() is written to not override KRB5_KTNAME if it's
> already set.  This of-course-totally-undocumented behavior seems like a
> fairly bad idea to me: as things stand, the client-side choice of whether
> to initiate GSS encryption or not could result in two different server
> keytabs being used.

I agree that we should be consistent and that always setting the
environment variable if krb_server_keyfile has been set is the right
approach.

> I think we'd be best off to always override KRB5_KTNAME if we have a
> nonempty krb_server_keyfile setting, so the attached proposed patch
> makes both functions do it the same way.  (I did not make an effort
> to remove the dependency on setenv, given the nearby thread to
> standardize on that.)

+1.

> I'm not sure whether there's any documentation change that needs to
> be made.  The docs don't suggest that you're allowed to set
> krb_server_keyfile to an empty string in the first place, so maybe
> we needn't explain what happens if you do.

Perhaps saying something about 'system default' or 'taken from the
environment' might make sense.  That said, the system default almost
always ends up not being usable since it'll be owned by root and we
won't run as root.  Perhaps there are some who prefer to set it via the
environment variable, though I don't think I've ever seen that myself.

I didn't look too closely at the patch itself but on a once-over it
seemed fine to me.

Thanks,

Stephen

  34. Re: Problem with ssl and psql in Postgresql 13

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-30T16:41:14Z

    Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> I think we'd be best off to always override KRB5_KTNAME if we have a
>> nonempty krb_server_keyfile setting, so the attached proposed patch
>> makes both functions do it the same way.  (I did not make an effort
>> to remove the dependency on setenv, given the nearby thread to
>> standardize on that.)

> +1.

Done, thanks for looking at the patch.

>> I'm not sure whether there's any documentation change that needs to
>> be made.  The docs don't suggest that you're allowed to set
>> krb_server_keyfile to an empty string in the first place, so maybe
>> we needn't explain what happens if you do.

> Perhaps saying something about 'system default' or 'taken from the
> environment' might make sense.

I went with "If this parameter is set to an empty string, it is ignored
and a system-dependent default is used."  I don't think we need to go
into more detail than that, since as you say it's unlikely to be a
useful case.

			regards, tom lane