pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedSV: Problem with ssl and psql in Postgresql 13
6+ messages / 4 participants
[nested] [flat]
* SV: Problem with ssl and psql in Postgresql 13
@ 2020-12-17 09:11 Gustavsson Mikael <mikael.gustavsson@smhi.se>
2020-12-17 14:20 ` Re: SV: Problem with ssl and psql in Postgresql 13 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Gustavsson Mikael @ 2020-12-17 09:11 UTC (permalink / raw)
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; +Cc: pgsql-general; Svensson Peter <peter.svensson@smhi.se>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SV: Problem with ssl and psql in Postgresql 13
2020-12-17 09:11 SV: Problem with ssl and psql in Postgresql 13 Gustavsson Mikael <mikael.gustavsson@smhi.se>
@ 2020-12-17 14:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-17 14:52 ` Re: SV: Problem with ssl and psql in Postgresql 13 Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2020-12-17 14:20 UTC (permalink / raw)
To: Gustavsson Mikael <mikael.gustavsson@smhi.se>; +Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; pgsql-general; Svensson Peter <peter.svensson@smhi.se>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SV: Problem with ssl and psql in Postgresql 13
2020-12-17 09:11 SV: Problem with ssl and psql in Postgresql 13 Gustavsson Mikael <mikael.gustavsson@smhi.se>
2020-12-17 14:20 ` Re: SV: Problem with ssl and psql in Postgresql 13 Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-12-17 14:52 ` Magnus Hagander <magnus@hagander.net>
2020-12-17 16:03 ` Re: SV: Problem with ssl and psql in Postgresql 13 Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 6+ messages in thread
From: Magnus Hagander @ 2020-12-17 14:52 UTC (permalink / raw)
To: Gustavsson Mikael <mikael.gustavsson@smhi.se>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; pgsql-general; Svensson Peter <peter.svensson@smhi.se>
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SV: Problem with ssl and psql in Postgresql 13
2020-12-17 09:11 SV: Problem with ssl and psql in Postgresql 13 Gustavsson Mikael <mikael.gustavsson@smhi.se>
2020-12-17 14:20 ` Re: SV: Problem with ssl and psql in Postgresql 13 Tom Lane <tgl@sss.pgh.pa.us>
2020-12-17 14:52 ` Re: SV: Problem with ssl and psql in Postgresql 13 Magnus Hagander <magnus@hagander.net>
@ 2020-12-17 16:03 ` Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 6+ messages in thread
From: Magnus Hagander @ 2020-12-17 16:03 UTC (permalink / raw)
To: Gustavsson Mikael <mikael.gustavsson@smhi.se>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; pgsql-general; Svensson Peter <peter.svensson@smhi.se>
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* SV: Problem with ssl and psql in Postgresql 13
@ 2020-12-29 15:05 Svensson Peter <peter.svensson@smhi.se>
2020-12-29 15:17 ` Re: SV: Problem with ssl and psql in Postgresql 13 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Svensson Peter @ 2020-12-29 15:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Stephen Frost <sfrost@snowman.net>; +Cc: Gustavsson Mikael <mikael.gustavsson@smhi.se>; Magnus Hagander <magnus@hagander.net>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; pgsql-general
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SV: Problem with ssl and psql in Postgresql 13
2020-12-29 15:05 SV: Problem with ssl and psql in Postgresql 13 Svensson Peter <peter.svensson@smhi.se>
@ 2020-12-29 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2020-12-29 15:17 UTC (permalink / raw)
To: Svensson Peter <peter.svensson@smhi.se>; +Cc: Stephen Frost <sfrost@snowman.net>; Gustavsson Mikael <mikael.gustavsson@smhi.se>; Magnus Hagander <magnus@hagander.net>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; pgsql-general
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-12-29 15:17 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-17 09:11 SV: Problem with ssl and psql in Postgresql 13 Gustavsson Mikael <mikael.gustavsson@smhi.se>
2020-12-17 14:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-17 14:52 ` Magnus Hagander <magnus@hagander.net>
2020-12-17 16:03 ` Magnus Hagander <magnus@hagander.net>
2020-12-29 15:05 SV: Problem with ssl and psql in Postgresql 13 Svensson Peter <peter.svensson@smhi.se>
2020-12-29 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox