Обсуждение: pg13 psql can't connect pg instance with ssl enabled after upgrading
Hello,
I got a strange issue with ssl connection after upgrading an instance from pg11 to pg13.
the instance didn't have ssl enabled, we upgraded it to pg13, then enabled ssl, however when we try to login with pg13 psql 13, psql always try ssl=off to login
public_postgresql_13_1/bin/psql "sslmode=require host=xxxxxx dbname=postgres"
psql: error: FATAL: no pg_hba.conf entry for host "xxxxx", user "xxxx", database "postgres", SSL off
however, if we use pg11 psql, it worked with the upgraded instance
public_postgresql_11_8/bin/psql "sslmode=require host=xxxxxx dbname=postgres"
psql (11.8, server 13.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
but pg13 psql can work with other pg13 instance with ssl enabled(new installation), only the upgraded instance doesn't work
any thoughts?
thanks,
James
jian xu <jamesxu@outlook.com> writes:
> the instance didn't have ssl enabled, we upgraded it to pg13, then enabled ssl, however when we try to login with
pg13psql 13, psql always try ssl=off to login
> public_postgresql_13_1/bin/psql "sslmode=require host=xxxxxx dbname=postgres"
> psql: error: FATAL: no pg_hba.conf entry for host "xxxxx", user "xxxx", database "postgres", SSL off
Are you entirely sure that that version of psql (or more accurately,
the libpq it's linked to) was built with SSL support?
If you are in an environment where GSS encryption is possible, it
could also be that you're being bit by the recently-fixed bug that
libpq didn't fall back from GSS to SSL encryption correctly [1].
That seems unlikely though, as both client and server would need to
possess active Kerberos tickets.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=06b844c2b
Thanks Tom. you are right, we are using GSS authentication. and psql version is 13.1
postgresql_13_1/bin/psql --version
psql (PostgreSQL) 13.1
and if I don't enforce ssl connection in pg_hba.conf file(don't use hostssl, and use host), pg13 psql could connect
postgresql_13_1/bin/psql "sslmode=require host=xxxxx dbname=postgres"
pg13 psql can also login with ssl connection on pg11 instancepsql (13.1)
GSSAPI-encrypted connection
Type "help" for help.
postgresql_13_1/bin/psql "sslmode=require host=bbbbbbb dbname=postgres"
psql (13.1, server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
and pg11 psql can also login pg13 instance with ssl enabled
public_postgresql_11_8/bin/psql "sslmode=require host=xxxxxx dbname=postgres"
psql (11.8, server 13.1)
WARNING: psql major version 11, server major version 13.
Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
only pg13 psql doesn't work with pg13 instance with ssl enabled.......
shall I try pg13.2 first?
thanks,
James
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, February 12, 2021 16:50
To: jian xu <jamesxu@outlook.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg13 psql can't connect pg instance with ssl enabled after upgrading
Sent: Friday, February 12, 2021 16:50
To: jian xu <jamesxu@outlook.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg13 psql can't connect pg instance with ssl enabled after upgrading
jian xu <jamesxu@outlook.com> writes:
> the instance didn't have ssl enabled, we upgraded it to pg13, then enabled ssl, however when we try to login with pg13 psql 13, psql always try ssl=off to login
> public_postgresql_13_1/bin/psql "sslmode=require host=xxxxxx dbname=postgres"
> psql: error: FATAL: no pg_hba.conf entry for host "xxxxx", user "xxxx", database "postgres", SSL off
Are you entirely sure that that version of psql (or more accurately,
the libpq it's linked to) was built with SSL support?
If you are in an environment where GSS encryption is possible, it
could also be that you're being bit by the recently-fixed bug that
libpq didn't fall back from GSS to SSL encryption correctly [1].
That seems unlikely though, as both client and server would need to
possess active Kerberos tickets.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=06b844c2b
> the instance didn't have ssl enabled, we upgraded it to pg13, then enabled ssl, however when we try to login with pg13 psql 13, psql always try ssl=off to login
> public_postgresql_13_1/bin/psql "sslmode=require host=xxxxxx dbname=postgres"
> psql: error: FATAL: no pg_hba.conf entry for host "xxxxx", user "xxxx", database "postgres", SSL off
Are you entirely sure that that version of psql (or more accurately,
the libpq it's linked to) was built with SSL support?
If you are in an environment where GSS encryption is possible, it
could also be that you're being bit by the recently-fixed bug that
libpq didn't fall back from GSS to SSL encryption correctly [1].
That seems unlikely though, as both client and server would need to
possess active Kerberos tickets.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=06b844c2b
jian xu <jamesxu@outlook.com> writes:
> Thanks Tom. you are right, we are using GSS authentication. and psql version is 13.1
> postgresql_13_1/bin/psql --version
> psql (PostgreSQL) 13.1
Ah. Then perhaps you want hostgssenc not hostssl in your pg_hba
entries. But I'd definitely recommend an update to 13.2, as it
fixes a number of GSS bugs besides this one.
regards, tom lane
thanks Tom, may I know if there is a way to disable GSS encryption? and only use ssl encryption?
not sure why it worked on pg11, but not on pg13....
does it mean if we use GSS, we are not able to use ssl encryption on pg13?
thanks,
James
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, February 12, 2021 17:59
To: jian xu <jamesxu@outlook.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg13 psql can't connect pg instance with ssl enabled after upgrading
Sent: Friday, February 12, 2021 17:59
To: jian xu <jamesxu@outlook.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg13 psql can't connect pg instance with ssl enabled after upgrading
jian xu <jamesxu@outlook.com> writes:
> Thanks Tom. you are right, we are using GSS authentication. and psql version is 13.1
> postgresql_13_1/bin/psql --version
> psql (PostgreSQL) 13.1
Ah. Then perhaps you want hostgssenc not hostssl in your pg_hba
entries. But I'd definitely recommend an update to 13.2, as it
fixes a number of GSS bugs besides this one.
regards, tom lane
> Thanks Tom. you are right, we are using GSS authentication. and psql version is 13.1
> postgresql_13_1/bin/psql --version
> psql (PostgreSQL) 13.1
Ah. Then perhaps you want hostgssenc not hostssl in your pg_hba
entries. But I'd definitely recommend an update to 13.2, as it
fixes a number of GSS bugs besides this one.
regards, tom lane
I added "gssencmode=disable" in the client connection string, then psql 13 can login pg13 with ssl connection
postgresql_13_1/bin/psql "sslmode=require host=xxxxxxx dbname=postgres gssencmode=disable"
psql (13.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
but not sure why pg11 psql can login pg13 without gssencmode=disable
looks like something changes in pg13 about gssencryption.
can gssencryption be disable on server side? so clients don't need to change connection string anymore.
or is it fixed in pg13.2?
thanks
James
From: jian xu <jamesxu@outlook.com>
Sent: Friday, February 12, 2021 18:40
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg13 psql can't connect pg instance with ssl enabled after upgrading
Sent: Friday, February 12, 2021 18:40
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg13 psql can't connect pg instance with ssl enabled after upgrading
thanks Tom, may I know if there is a way to disable GSS encryption? and only use ssl encryption?
not sure why it worked on pg11, but not on pg13....
does it mean if we use GSS, we are not able to use ssl encryption on pg13?
thanks,
James
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, February 12, 2021 17:59
To: jian xu <jamesxu@outlook.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg13 psql can't connect pg instance with ssl enabled after upgrading
Sent: Friday, February 12, 2021 17:59
To: jian xu <jamesxu@outlook.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg13 psql can't connect pg instance with ssl enabled after upgrading
jian xu <jamesxu@outlook.com> writes:
> Thanks Tom. you are right, we are using GSS authentication. and psql version is 13.1
> postgresql_13_1/bin/psql --version
> psql (PostgreSQL) 13.1
Ah. Then perhaps you want hostgssenc not hostssl in your pg_hba
entries. But I'd definitely recommend an update to 13.2, as it
fixes a number of GSS bugs besides this one.
regards, tom lane
> Thanks Tom. you are right, we are using GSS authentication. and psql version is 13.1
> postgresql_13_1/bin/psql --version
> psql (PostgreSQL) 13.1
Ah. Then perhaps you want hostgssenc not hostssl in your pg_hba
entries. But I'd definitely recommend an update to 13.2, as it
fixes a number of GSS bugs besides this one.
regards, tom lane