pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Need Help On Upgrade
16+ messages / 4 participants
[nested] [flat]

* Need Help On Upgrade
@ 2023-07-10 18:15 Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 14:16 ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  0 siblings, 2 replies; 16+ messages in thread

From: Johnathan Tiamoh @ 2023-07-10 18:15 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hello,

I upgraded from postgresql 9.5 to 14.8. After the upgrade users were not
able to connect due to password hash.

The password encryption was on scram-sha-256. The password hash was on md5.
in the pg_hba.conf file the authentication method was md5. I do not know
why users could not connect because as per postgresql documentation, that
should work.

I have previously done a similar upgrade and did not face similar issues.


Kind regards
Johnathan Tiamoh

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
@ 2023-07-10 19:38 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:02   ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  1 sibling, 2 replies; 16+ messages in thread

From: Adrian Klaver @ 2023-07-10 19:38 UTC (permalink / raw)
  To: Johnathan Tiamoh <johnathantiamoh@gmail.com>; pgsql-general@lists.postgresql.org

On 7/10/23 11:15, Johnathan Tiamoh wrote:
> Hello,
> 
> I upgraded from postgresql 9.5 to 14.8. After the upgrade users were not 
> able to connect due to password hash.
> 
> The password encryption was on scram-sha-256. The password hash was on 
> md5. in the pg_hba.conf file the authentication method was md5. I do not 
> know why users could not connect because as per postgresql 
> documentation, that should work.
> 

1) You are going to have to be more specific about where and what the 
settings are or where for both Postgres versions and the *.conf files. So:

Postgres 9.5

    postgresql.conf

       password_encryption  = ?

    pg_hba.conf

        Provide the relevant lines

Postgres 14.8

    postgresql.conf

       password_encryption  = ?

    pg_hba.conf

        Provide the relevant lines

2) Provide the error messages received when trying to connect.


> I have previously done a similar upgrade and did not face similar issues.
> 
> 
> Kind regards
> Johnathan Tiamoh

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-10 20:00   ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  1 sibling, 1 reply; 16+ messages in thread

From: Adrian Klaver @ 2023-07-10 20:00 UTC (permalink / raw)
  To: Johnathan Tiamoh <johnathantiamoh@gmail.com>; +Cc: pgsql-general

On 7/10/23 12:55, Johnathan Tiamoh wrote:

Please reply to list also.
Ccing list

> Ok Adrian,
> 
> Postgres 9.5
> 
>      postgresql.conf
> 
>         password_encryption  = ?  md5  ====> wish is the default for 9.5
> 
>      pg_hba.conf    -----------------> md5
> 
>          Provide the relevant lines
> 
> Postgres 14.8
> 
>      postgresql.conf
> 
>         password_encryption  = ?   scram-sha-256  ====> wish is the 
> default for 14.8
> 
>      pg_hba.conf      md5

You have not answered:

2) Provide the error messages received when trying to connect.

> 
> 
> Thank you.
> 
> 
> On Mon, Jul 10, 2023 at 3:38 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 7/10/23 11:15, Johnathan Tiamoh wrote:
>      > Hello,
>      >
>      > I upgraded from postgresql 9.5 to 14.8. After the upgrade users
>     were not
>      > able to connect due to password hash.
>      >
>      > The password encryption was on scram-sha-256. The password hash
>     was on
>      > md5. in the pg_hba.conf file the authentication method was md5. I
>     do not
>      > know why users could not connect because as per postgresql
>      > documentation, that should work.
>      >
> 
>     1) You are going to have to be more specific about where and what the
>     settings are or where for both Postgres versions and the *.conf
>     files. So:
> 
>     Postgres 9.5
> 
>          postgresql.conf
> 
>             password_encryption  = ?
> 
>          pg_hba.conf
> 
>              Provide the relevant lines
> 
>     Postgres 14.8
> 
>          postgresql.conf
> 
>             password_encryption  = ?
> 
>          pg_hba.conf
> 
>              Provide the relevant lines
> 
>     2) Provide the error messages received when trying to connect.
> 
> 
>      > I have previously done a similar upgrade and did not face similar
>     issues.
>      >
>      >
>      > Kind regards
>      > Johnathan Tiamoh
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-10 20:20     ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 22:36       ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 16+ messages in thread

From: Johnathan Tiamoh @ 2023-07-10 20:20 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org

> Ok Adrian,
>
> Postgres 9.5
>
>      postgresql.conf
>
>         password_encryption  = ?  md5  ====> wish is the default for 9.5
>
>      pg_hba.conf    -----------------> md5
>
>          Provide the relevant lines
>
> Postgres 14.8
>
>      postgresql.conf
>
>         password_encryption  = ?   scram-sha-256  ====> which is the
> default for 14.8
>
>      pg_hba.conf      md5

You have not answered:

2) Provide the error messages received when trying to connect.
[XXXXXXXXXXXXXXXXXXXXXXX| APPNAME=[unknown]| DB=proftpd| USER=proftpd|
PID=9053| 2| authentication| PGE-28P01: DETAIL:  Connection matched
pg_hba.conf line 113: "host    all         all         0.0.0.0/0
md5"


On Mon, Jul 10, 2023 at 4:00 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 7/10/23 12:55, Johnathan Tiamoh wrote:
>
> Please reply to list also.
> Ccing list
>
> > Ok Adrian,
> >
> > Postgres 9.5
> >
> >      postgresql.conf
> >
> >         password_encryption  = ?  md5  ====> wish is the default for 9.5
> >
> >      pg_hba.conf    -----------------> md5
> >
> >          Provide the relevant lines
> >
> > Postgres 14.8
> >
> >      postgresql.conf
> >
> >         password_encryption  = ?   scram-sha-256  ====> wish is the
> > default for 14.8
> >
> >      pg_hba.conf      md5
>
> You have not answered:
>
> 2) Provide the error messages received when trying to connect.
>
> >
> >
> > Thank you.
> >
> >
> > On Mon, Jul 10, 2023 at 3:38 PM Adrian Klaver <adrian.klaver@aklaver.com
> > <mailto:adrian.klaver@aklaver.com>> wrote:
> >
> >     On 7/10/23 11:15, Johnathan Tiamoh wrote:
> >      > Hello,
> >      >
> >      > I upgraded from postgresql 9.5 to 14.8. After the upgrade users
> >     were not
> >      > able to connect due to password hash.
> >      >
> >      > The password encryption was on scram-sha-256. The password hash
> >     was on
> >      > md5. in the pg_hba.conf file the authentication method was md5. I
> >     do not
> >      > know why users could not connect because as per postgresql
> >      > documentation, that should work.
> >      >
> >
> >     1) You are going to have to be more specific about where and what the
> >     settings are or where for both Postgres versions and the *.conf
> >     files. So:
> >
> >     Postgres 9.5
> >
> >          postgresql.conf
> >
> >             password_encryption  = ?
> >
> >          pg_hba.conf
> >
> >              Provide the relevant lines
> >
> >     Postgres 14.8
> >
> >          postgresql.conf
> >
> >             password_encryption  = ?
> >
> >          pg_hba.conf
> >
> >              Provide the relevant lines
> >
> >     2) Provide the error messages received when trying to connect.
> >
> >
> >      > I have previously done a similar upgrade and did not face similar
> >     issues.
> >      >
> >      >
> >      > Kind regards
> >      > Johnathan Tiamoh
> >
> >     --
> >     Adrian Klaver
> >     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
@ 2023-07-10 22:36       ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 00:38         ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  0 siblings, 1 reply; 16+ messages in thread

From: Adrian Klaver @ 2023-07-10 22:36 UTC (permalink / raw)
  To: Johnathan Tiamoh <johnathantiamoh@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On 7/10/23 13:20, Johnathan Tiamoh wrote:
> 
>> Ok Adrian,
>>
>> Postgres 9.5
>>
>>      postgresql.conf
>>
>>         password_encryption  = ?  md5  ====> wish is the default for 9.5
>>
>>      pg_hba.conf    -----------------> md5
>>
>>          Provide the relevant lines
>>
>> Postgres 14.8
>>
>>      postgresql.conf
>>
>>         password_encryption  = ?   scram-sha-256  ====> which is the
>> default for 14.8
>>
>>      pg_hba.conf      md5
> 
> You have not answered:
> 
> 2) Provide the error messages received when trying to connect.
> [XXXXXXXXXXXXXXXXXXXXXXX| APPNAME=[unknown]| DB=proftpd| USER=proftpd| 
> PID=9053| 2| authentication| PGE-28P01: DETAIL:  Connection matched 
> pg_hba.conf line 113: "host    all         all 0.0.0.0/0 
> <http://0.0.0.0/0>             md5"
> 

The above is not showing any actual error. There should a part that says 
what failed when connecting.


-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 22:36       ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-11 00:38         ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 03:45           ` Re: Need Help On Upgrade Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 1 reply; 16+ messages in thread

From: Johnathan Tiamoh @ 2023-07-11 00:38 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org

Sorry Adrian,

Below is the full error message.

2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)|
APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication|
PGE-28P01: FATAL:  password authentication failed for user "grafana"
2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)|
APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2| authentication|
PGE-28P01: DETAIL:  Connection matched pg_hba.conf line 113: "host
all         all         0.0.0.0/0             md5"

On Mon, Jul 10, 2023 at 6:36 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 7/10/23 13:20, Johnathan Tiamoh wrote:
> >
> >> Ok Adrian,
> >>
> >> Postgres 9.5
> >>
> >>      postgresql.conf
> >>
> >>         password_encryption  = ?  md5  ====> wish is the default for 9.5
> >>
> >>      pg_hba.conf    -----------------> md5
> >>
> >>          Provide the relevant lines
> >>
> >> Postgres 14.8
> >>
> >>      postgresql.conf
> >>
> >>         password_encryption  = ?   scram-sha-256  ====> which is the
> >> default for 14.8
> >>
> >>      pg_hba.conf      md5
> >
> > You have not answered:
> >
> > 2) Provide the error messages received when trying to connect.
> > [XXXXXXXXXXXXXXXXXXXXXXX| APPNAME=[unknown]| DB=proftpd| USER=proftpd|
> > PID=9053| 2| authentication| PGE-28P01: DETAIL:  Connection matched
> > pg_hba.conf line 113: "host    all         all 0.0.0.0/0
> > <http://0.0.0.0/0;             md5"
> >
>
> The above is not showing any actual error. There should a part that says
> what failed when connecting.
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 22:36       ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 00:38         ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
@ 2023-07-11 03:45           ` Laurenz Albe <laurenz.albe@cybertec.at>
  2023-07-11 15:46             ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 16+ messages in thread

From: Laurenz Albe @ 2023-07-11 03:45 UTC (permalink / raw)
  To: Johnathan Tiamoh <johnathantiamoh@gmail.com>; Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org

On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
> Below is the full error message.
> 
> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)| APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication| PGE-28P01: FATAL:  password authentication failed for user
> "grafana"
> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)| APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2| authentication| PGE-28P01: DETAIL:  Connection matched pg_hba.conf line 113:
> "host    all         all         0.0.0.0/0             md5"

Then you must have entered the wrong password.

If in doubt, change the password.

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 22:36       ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 00:38         ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 03:45           ` Re: Need Help On Upgrade Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-07-11 15:46             ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 15:53               ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  0 siblings, 1 reply; 16+ messages in thread

From: Adrian Klaver @ 2023-07-11 15:46 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; Johnathan Tiamoh <johnathantiamoh@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On 7/10/23 20:45, Laurenz Albe wrote:
> On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
>> Below is the full error message.
>>
>> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)| APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication| PGE-28P01: FATAL:  password authentication failed for user
>> "grafana"
>> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)| APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2| authentication| PGE-28P01: DETAIL:  Connection matched pg_hba.conf line 113:
>> "host    all         all         0.0.0.0/0             md5"
> 
> Then you must have entered the wrong password.
> 
> If in doubt, change the password.

Can you connect to the database at all or is this specific to certain users?

What client(s) are you using and is the problem coming from a specific 
client?




> 
> Yours,
> Laurenz Albe

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 22:36       ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 00:38         ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 03:45           ` Re: Need Help On Upgrade Laurenz Albe <laurenz.albe@cybertec.at>
  2023-07-11 15:46             ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-11 15:53               ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 16:19                 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 16+ messages in thread

From: Johnathan Tiamoh @ 2023-07-11 15:53 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org

I can connect.

All applications and other users that connect from to the databases through
the  pgbouncers can't connect.

On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 7/10/23 20:45, Laurenz Albe wrote:
> > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
> >> Below is the full error message.
> >>
> >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)|
> APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication|
> PGE-28P01: FATAL:  password authentication failed for user
> >> "grafana"
> >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)|
> APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2| authentication|
> PGE-28P01: DETAIL:  Connection matched pg_hba.conf line 113:
> >> "host    all         all         0.0.0.0/0             md5"
> >
> > Then you must have entered the wrong password.
> >
> > If in doubt, change the password.
>
> Can you connect to the database at all or is this specific to certain
> users?
>
> What client(s) are you using and is the problem coming from a specific
> client?
>
>
>
>
> >
> > Yours,
> > Laurenz Albe
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 22:36       ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 00:38         ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 03:45           ` Re: Need Help On Upgrade Laurenz Albe <laurenz.albe@cybertec.at>
  2023-07-11 15:46             ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 15:53               ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
@ 2023-07-11 16:19                 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 21:45                   ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  0 siblings, 1 reply; 16+ messages in thread

From: Adrian Klaver @ 2023-07-11 16:19 UTC (permalink / raw)
  To: Johnathan Tiamoh <johnathantiamoh@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org

On 7/11/23 08:53, Johnathan Tiamoh wrote:
> I can connect.
> 
> All applications and other users that connect from to the databases 
> through the  pgbouncers can't connect.

That would have been a good thing to have mentioned in your first post. 
I don't use PgBouncer so I am not going to be of much use going forward. 
For those that do use it and can help answers to the following would be 
helpful:

1) PgBouncer version.

2) Did you change the settings when going from 9.5 to 14.8?

3) Does the PgBouncer log show anything relevant?

4) Does '...through the  pgbouncers...' mean there is more then one 
PgBouncer instance in use?

> 
> On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 7/10/23 20:45, Laurenz Albe wrote:
>      > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
>      >> Below is the full error message.
>      >>
>      >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)|
>     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1|
>     authentication| PGE-28P01: FATAL:  password authentication failed
>     for user
>      >> "grafana"
>      >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)|
>     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2|
>     authentication| PGE-28P01: DETAIL:  Connection matched pg_hba.conf
>     line 113:
>      >> "host    all         all 0.0.0.0/0
>     <http://0.0.0.0/0>             md5"
>      >
>      > Then you must have entered the wrong password.
>      >
>      > If in doubt, change the password.
> 
>     Can you connect to the database at all or is this specific to
>     certain users?
> 
>     What client(s) are you using and is the problem coming from a specific
>     client?
> 
> 
> 
> 
>      >
>      > Yours,
>      > Laurenz Albe
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 22:36       ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 00:38         ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 03:45           ` Re: Need Help On Upgrade Laurenz Albe <laurenz.albe@cybertec.at>
  2023-07-11 15:46             ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 15:53               ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 16:19                 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-11 21:45                   ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 23:32                     ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 16+ messages in thread

From: Johnathan Tiamoh @ 2023-07-11 21:45 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org

Hello Adrian

1) PgBouncer version.

pgbouncer.1.7.2

2) Did you change the settings when going from 9.5 to 14.8?

No. I did not do any configuration changes on the bouncers

3) Does the PgBouncer log show anything relevant?

No. It does not show anything

4) Does '...through the  pgbouncers...' mean there is more then one
PgBouncer instance in use?

Yes, I have more than 3 pgbouncers for different connections.



On Tue, Jul 11, 2023 at 12:19 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 7/11/23 08:53, Johnathan Tiamoh wrote:
> > I can connect.
> >
> > All applications and other users that connect from to the databases
> > through the  pgbouncers can't connect.
>
> That would have been a good thing to have mentioned in your first post.
> I don't use PgBouncer so I am not going to be of much use going forward.
> For those that do use it and can help answers to the following would be
> helpful:
>
> 1) PgBouncer version.
>
> 2) Did you change the settings when going from 9.5 to 14.8?
>
> 3) Does the PgBouncer log show anything relevant?
>
> 4) Does '...through the  pgbouncers...' mean there is more then one
> PgBouncer instance in use?
>
> >
> > On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver
> > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> >
> >     On 7/10/23 20:45, Laurenz Albe wrote:
> >      > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
> >      >> Below is the full error message.
> >      >>
> >      >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)|
> >     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1|
> >     authentication| PGE-28P01: FATAL:  password authentication failed
> >     for user
> >      >> "grafana"
> >      >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)|
> >     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2|
> >     authentication| PGE-28P01: DETAIL:  Connection matched pg_hba.conf
> >     line 113:
> >      >> "host    all         all 0.0.0.0/0
> >     <http://0.0.0.0/0;             md5"
> >      >
> >      > Then you must have entered the wrong password.
> >      >
> >      > If in doubt, change the password.
> >
> >     Can you connect to the database at all or is this specific to
> >     certain users?
> >
> >     What client(s) are you using and is the problem coming from a
> specific
> >     client?
> >
> >
> >
> >
> >      >
> >      > Yours,
> >      > Laurenz Albe
> >
> >     --
> >     Adrian Klaver
> >     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 22:36       ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 00:38         ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 03:45           ` Re: Need Help On Upgrade Laurenz Albe <laurenz.albe@cybertec.at>
  2023-07-11 15:46             ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 15:53               ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 16:19                 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 21:45                   ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
@ 2023-07-11 23:32                     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-12 01:31                       ` Re: Need Help On Upgrade Benedict Holland <benedict.m.holland@gmail.com>
  2023-07-12 04:39                       ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  0 siblings, 2 replies; 16+ messages in thread

From: Adrian Klaver @ 2023-07-11 23:32 UTC (permalink / raw)
  To: Johnathan Tiamoh <johnathantiamoh@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org

On 7/11/23 14:45, Johnathan Tiamoh wrote:
> Hello Adrian
> 
> 1) PgBouncer version.
> 
> pgbouncer.1.7.2

PgBouncer most recent version is from here:

http://www.pgbouncer.org/changelog.html

PgBouncer 1.19.x

2023-05-31 - PgBouncer 1.19.1 - “Sunny Spring”




And since 1.7.x there have been these related auth changes:

PgBouncer 1.11.x

Add support for SCRAM authentication for clients and servers. A new 
authentication type scram-sha-256 is added.

PgBouncer 1.12.x

Accept SCRAM channel binding enabled clients. Previously, a client 
supporting channel binding (that is, PostgreSQL 11+) would get a 
connection failure when connecting to PgBouncer in certain situations. 
(PgBouncer does not support channel binding. This change just fixes 
support for clients that offer it.)

PgBouncer 1.14.x

Add SCRAM authentication pass-through. This allows using encrypted SCRAM 
secrets in PgBouncer (either in userlist.txt or from auth_query) for 
logging into servers

PgBouncer 1.16.x

Mixed use of md5 and scram via hba has been fixed.

PgBouncer 1.17.x

Don’t apply fast-fail at connect time. This is part of the 
above-mentioned change to not report server errors before 
authentication. It also fixes a particular situation with SCRAM 
pass-through authentication, where we need to allow the client-side 
authentication exchange in order to be able to fix the server-side 
connection by re-authenticating. The fast-fail mechanism still applies 
right after authentication, so the effective observed behavior will be 
the same in most situations.


I think an update is in order.

> 
> 2) Did you change the settings when going from 9.5 to 14.8?
> 
> No. I did not do any configuration changes on the bouncers
> 
> 3) Does the PgBouncer log show anything relevant?
> 
> No. It does not show anything
> 
> 4) Does '...through the  pgbouncers...' mean there is more then one
> PgBouncer instance in use?
> 
> Yes, I have more than 3 pgbouncers for different connections.
> 
> 
> 
> On Tue, Jul 11, 2023 at 12:19 PM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 7/11/23 08:53, Johnathan Tiamoh wrote:
>      > I can connect.
>      >
>      > All applications and other users that connect from to the databases
>      > through the  pgbouncers can't connect.
> 
>     That would have been a good thing to have mentioned in your first post.
>     I don't use PgBouncer so I am not going to be of much use going
>     forward.
>     For those that do use it and can help answers to the following would be
>     helpful:
> 
>     1) PgBouncer version.
> 
>     2) Did you change the settings when going from 9.5 to 14.8?
> 
>     3) Does the PgBouncer log show anything relevant?
> 
>     4) Does '...through the  pgbouncers...' mean there is more then one
>     PgBouncer instance in use?
> 
>      >
>      > On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver
>      > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>> wrote:
>      >
>      >     On 7/10/23 20:45, Laurenz Albe wrote:
>      >      > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
>      >      >> Below is the full error message.
>      >      >>
>      >      >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)|
>      >     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1|
>      >     authentication| PGE-28P01: FATAL:  password authentication failed
>      >     for user
>      >      >> "grafana"
>      >      >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX (60930)|
>      >     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2|
>      >     authentication| PGE-28P01: DETAIL:  Connection matched
>     pg_hba.conf
>      >     line 113:
>      >      >> "host    all         all 0.0.0.0/0 <http://0.0.0.0/0;
>      >     <http://0.0.0.0/0 <http://0.0.0.0/0>>             md5"
>      >      >
>      >      > Then you must have entered the wrong password.
>      >      >
>      >      > If in doubt, change the password.
>      >
>      >     Can you connect to the database at all or is this specific to
>      >     certain users?
>      >
>      >     What client(s) are you using and is the problem coming from a
>     specific
>      >     client?
>      >
>      >
>      >
>      >
>      >      >
>      >      > Yours,
>      >      > Laurenz Albe
>      >
>      >     --
>      >     Adrian Klaver
>      > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 22:36       ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 00:38         ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 03:45           ` Re: Need Help On Upgrade Laurenz Albe <laurenz.albe@cybertec.at>
  2023-07-11 15:46             ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 15:53               ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 16:19                 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 21:45                   ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 23:32                     ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-12 01:31                       ` Benedict Holland <benedict.m.holland@gmail.com>
  1 sibling, 0 replies; 16+ messages in thread

From: Benedict Holland @ 2023-07-12 01:31 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Johnathan Tiamoh <johnathantiamoh@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general <pgsql-general@lists.postgresql.org>

This also sounds like a fairly advanced setup and a corporate environment.
Postgresql offers paid support and you probably want that.

Thanks,
Ben

On Tue, Jul 11, 2023, 4:33 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 7/11/23 14:45, Johnathan Tiamoh wrote:
> > Hello Adrian
> >
> > 1) PgBouncer version.
> >
> > pgbouncer.1.7.2
>
> PgBouncer most recent version is from here:
>
> http://www.pgbouncer.org/changelog.html
>
> PgBouncer 1.19.x
>
> 2023-05-31 - PgBouncer 1.19.1 - “Sunny Spring”
>
>
>
>
> And since 1.7.x there have been these related auth changes:
>
> PgBouncer 1.11.x
>
> Add support for SCRAM authentication for clients and servers. A new
> authentication type scram-sha-256 is added.
>
> PgBouncer 1.12.x
>
> Accept SCRAM channel binding enabled clients. Previously, a client
> supporting channel binding (that is, PostgreSQL 11+) would get a
> connection failure when connecting to PgBouncer in certain situations.
> (PgBouncer does not support channel binding. This change just fixes
> support for clients that offer it.)
>
> PgBouncer 1.14.x
>
> Add SCRAM authentication pass-through. This allows using encrypted SCRAM
> secrets in PgBouncer (either in userlist.txt or from auth_query) for
> logging into servers
>
> PgBouncer 1.16.x
>
> Mixed use of md5 and scram via hba has been fixed.
>
> PgBouncer 1.17.x
>
> Don’t apply fast-fail at connect time. This is part of the
> above-mentioned change to not report server errors before
> authentication. It also fixes a particular situation with SCRAM
> pass-through authentication, where we need to allow the client-side
> authentication exchange in order to be able to fix the server-side
> connection by re-authenticating. The fast-fail mechanism still applies
> right after authentication, so the effective observed behavior will be
> the same in most situations.
>
>
> I think an update is in order.
>
> >
> > 2) Did you change the settings when going from 9.5 to 14.8?
> >
> > No. I did not do any configuration changes on the bouncers
> >
> > 3) Does the PgBouncer log show anything relevant?
> >
> > No. It does not show anything
> >
> > 4) Does '...through the  pgbouncers...' mean there is more then one
> > PgBouncer instance in use?
> >
> > Yes, I have more than 3 pgbouncers for different connections.
> >
> >
> >
> > On Tue, Jul 11, 2023 at 12:19 PM Adrian Klaver
> > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> >
> >     On 7/11/23 08:53, Johnathan Tiamoh wrote:
> >      > I can connect.
> >      >
> >      > All applications and other users that connect from to the
> databases
> >      > through the  pgbouncers can't connect.
> >
> >     That would have been a good thing to have mentioned in your first
> post.
> >     I don't use PgBouncer so I am not going to be of much use going
> >     forward.
> >     For those that do use it and can help answers to the following would
> be
> >     helpful:
> >
> >     1) PgBouncer version.
> >
> >     2) Did you change the settings when going from 9.5 to 14.8?
> >
> >     3) Does the PgBouncer log show anything relevant?
> >
> >     4) Does '...through the  pgbouncers...' mean there is more then one
> >     PgBouncer instance in use?
> >
> >      >
> >      > On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver
> >      > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> >     <mailto:adrian.klaver@aklaver.com
> >     <mailto:adrian.klaver@aklaver.com>>> wrote:
> >      >
> >      >     On 7/10/23 20:45, Laurenz Albe wrote:
> >      >      > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
> >      >      >> Below is the full error message.
> >      >      >>
> >      >      >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX
> (60930)|
> >      >     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1|
> >      >     authentication| PGE-28P01: FATAL:  password authentication
> failed
> >      >     for user
> >      >      >> "grafana"
> >      >      >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX
> (60930)|
> >      >     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2|
> >      >     authentication| PGE-28P01: DETAIL:  Connection matched
> >     pg_hba.conf
> >      >     line 113:
> >      >      >> "host    all         all 0.0.0.0/0 <http://0.0.0.0/0;
> >      >     <http://0.0.0.0/0 <http://0.0.0.0/0>;             md5"
> >      >      >
> >      >      > Then you must have entered the wrong password.
> >      >      >
> >      >      > If in doubt, change the password.
> >      >
> >      >     Can you connect to the database at all or is this specific to
> >      >     certain users?
> >      >
> >      >     What client(s) are you using and is the problem coming from a
> >     specific
> >      >     client?
> >      >
> >      >
> >      >
> >      >
> >      >      >
> >      >      > Yours,
> >      >      > Laurenz Albe
> >      >
> >      >     --
> >      >     Adrian Klaver
> >      > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> >     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com
> >>
> >      >
> >
> >     --
> >     Adrian Klaver
> >     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:00   ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-10 20:20     ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 22:36       ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 00:38         ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 03:45           ` Re: Need Help On Upgrade Laurenz Albe <laurenz.albe@cybertec.at>
  2023-07-11 15:46             ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 15:53               ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 16:19                 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-11 21:45                   ` Re: Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-11 23:32                     ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-12 04:39                       ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
  1 sibling, 0 replies; 16+ messages in thread

From: Johnathan Tiamoh @ 2023-07-12 04:39 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org

Thank you very much @Adrian Klaver <adrian.klaver@aklaver.com> .

On Tue, Jul 11, 2023 at 7:32 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 7/11/23 14:45, Johnathan Tiamoh wrote:
> > Hello Adrian
> >
> > 1) PgBouncer version.
> >
> > pgbouncer.1.7.2
>
> PgBouncer most recent version is from here:
>
> http://www.pgbouncer.org/changelog.html
>
> PgBouncer 1.19.x
>
> 2023-05-31 - PgBouncer 1.19.1 - “Sunny Spring”
>
>
>
>
> And since 1.7.x there have been these related auth changes:
>
> PgBouncer 1.11.x
>
> Add support for SCRAM authentication for clients and servers. A new
> authentication type scram-sha-256 is added.
>
> PgBouncer 1.12.x
>
> Accept SCRAM channel binding enabled clients. Previously, a client
> supporting channel binding (that is, PostgreSQL 11+) would get a
> connection failure when connecting to PgBouncer in certain situations.
> (PgBouncer does not support channel binding. This change just fixes
> support for clients that offer it.)
>
> PgBouncer 1.14.x
>
> Add SCRAM authentication pass-through. This allows using encrypted SCRAM
> secrets in PgBouncer (either in userlist.txt or from auth_query) for
> logging into servers
>
> PgBouncer 1.16.x
>
> Mixed use of md5 and scram via hba has been fixed.
>
> PgBouncer 1.17.x
>
> Don’t apply fast-fail at connect time. This is part of the
> above-mentioned change to not report server errors before
> authentication. It also fixes a particular situation with SCRAM
> pass-through authentication, where we need to allow the client-side
> authentication exchange in order to be able to fix the server-side
> connection by re-authenticating. The fast-fail mechanism still applies
> right after authentication, so the effective observed behavior will be
> the same in most situations.
>
>
> I think an update is in order.
>
> >
> > 2) Did you change the settings when going from 9.5 to 14.8?
> >
> > No. I did not do any configuration changes on the bouncers
> >
> > 3) Does the PgBouncer log show anything relevant?
> >
> > No. It does not show anything
> >
> > 4) Does '...through the  pgbouncers...' mean there is more then one
> > PgBouncer instance in use?
> >
> > Yes, I have more than 3 pgbouncers for different connections.
> >
> >
> >
> > On Tue, Jul 11, 2023 at 12:19 PM Adrian Klaver
> > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> >
> >     On 7/11/23 08:53, Johnathan Tiamoh wrote:
> >      > I can connect.
> >      >
> >      > All applications and other users that connect from to the
> databases
> >      > through the  pgbouncers can't connect.
> >
> >     That would have been a good thing to have mentioned in your first
> post.
> >     I don't use PgBouncer so I am not going to be of much use going
> >     forward.
> >     For those that do use it and can help answers to the following would
> be
> >     helpful:
> >
> >     1) PgBouncer version.
> >
> >     2) Did you change the settings when going from 9.5 to 14.8?
> >
> >     3) Does the PgBouncer log show anything relevant?
> >
> >     4) Does '...through the  pgbouncers...' mean there is more then one
> >     PgBouncer instance in use?
> >
> >      >
> >      > On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver
> >      > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> >     <mailto:adrian.klaver@aklaver.com
> >     <mailto:adrian.klaver@aklaver.com>>> wrote:
> >      >
> >      >     On 7/10/23 20:45, Laurenz Albe wrote:
> >      >      > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
> >      >      >> Below is the full error message.
> >      >      >>
> >      >      >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX
> (60930)|
> >      >     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1|
> >      >     authentication| PGE-28P01: FATAL:  password authentication
> failed
> >      >     for user
> >      >      >> "grafana"
> >      >      >> 2023-06-27 05:01:27.385 CDT| XXXXXXXXXXXXXXXXXXXXXXX
> (60930)|
> >      >     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2|
> >      >     authentication| PGE-28P01: DETAIL:  Connection matched
> >     pg_hba.conf
> >      >     line 113:
> >      >      >> "host    all         all 0.0.0.0/0 <http://0.0.0.0/0;
> >      >     <http://0.0.0.0/0 <http://0.0.0.0/0>;             md5"
> >      >      >
> >      >      > Then you must have entered the wrong password.
> >      >      >
> >      >      > If in doubt, change the password.
> >      >
> >      >     Can you connect to the database at all or is this specific to
> >      >     certain users?
> >      >
> >      >     What client(s) are you using and is the problem coming from a
> >     specific
> >      >     client?
> >      >
> >      >
> >      >
> >      >
> >      >      >
> >      >      > Yours,
> >      >      > Laurenz Albe
> >      >
> >      >     --
> >      >     Adrian Klaver
> >      > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> >     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com
> >>
> >      >
> >
> >     --
> >     Adrian Klaver
> >     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-10 19:38 ` Re: Need Help On Upgrade Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-10 20:02   ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
  1 sibling, 0 replies; 16+ messages in thread

From: Johnathan Tiamoh @ 2023-07-10 20:02 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org

Ok Adrian,

Postgres 9.5

    postgresql.conf

       password_encryption  = ?  md5  ====> wish is the default for 9.5

    pg_hba.conf    -----------------> md5

        Provide the relevant lines

Postgres 14.8

    postgresql.conf

       password_encryption  = ?   scram-sha-256  ====> wish is the default
for 14.8

    pg_hba.conf      md5


Thank you.

On Mon, Jul 10, 2023 at 3:38 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 7/10/23 11:15, Johnathan Tiamoh wrote:
> > Hello,
> >
> > I upgraded from postgresql 9.5 to 14.8. After the upgrade users were not
> > able to connect due to password hash.
> >
> > The password encryption was on scram-sha-256. The password hash was on
> > md5. in the pg_hba.conf file the authentication method was md5. I do not
> > know why users could not connect because as per postgresql
> > documentation, that should work.
> >
>
> 1) You are going to have to be more specific about where and what the
> settings are or where for both Postgres versions and the *.conf files. So:
>
> Postgres 9.5
>
>     postgresql.conf
>
>        password_encryption  = ?
>
>     pg_hba.conf
>
>         Provide the relevant lines
>
> Postgres 14.8
>
>     postgresql.conf
>
>        password_encryption  = ?
>
>     pg_hba.conf
>
>         Provide the relevant lines
>
> 2) Provide the error messages received when trying to connect.
>
>
> > I have previously done a similar upgrade and did not face similar issues.
> >
> >
> > Kind regards
> > Johnathan Tiamoh
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Need Help On Upgrade
  2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
@ 2023-07-11 14:16 ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
  1 sibling, 0 replies; 16+ messages in thread

From: Johnathan Tiamoh @ 2023-07-11 14:16 UTC (permalink / raw)
  To: KK CHN <kkchn.in@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Hello KK,

In the beginning, it was a little challenging, but as the process went on
it became easy. No, it was not a multistage upgrade. It was possible to
upgrade straight from 9.5 to 14.

We did not change anything on the schema. For data type, we alter all
tables that were created with OIDs to remove the OIDs. We remove all data
with abs time ( absolute time). We equally remove all data with
sql_identifier. We also created a script to check for unknown data type and
another to remove them.

For documentation, we did not use any specific documentation. We just
followed postgresql documentation on upgrading.  We created our one plan
and steps based on our clusters, ensuring we can safely roll back in case
of any challenges.


Thank you.
Johnathan T


On Tue, Jul 11, 2023 at 5:17 AM KK CHN <kkchn.in@gmail.com> wrote:

> Hi Johnathan,
>
> 1. How complex was it upgrading from an old Postgres 9.5 to 14.8 ?   Is it
> multistage upgrading or single shot to 14.8 ?
>
> 2. What about the old schema ( data types of 9.8 when the database   was
> designed couple of years ago? and  the datatypes supported in 14.8  is
> there any manual work was involved in this migration , please let me know
> the manual works needed to perform while migration from 9.8 to 14.8)
>
> 3. Which documentation needs to be  followed to perform this upgrade ??
>
> Any hints much appreciated..
> Krishane
>
> On Mon, Jul 10, 2023 at 11:46 PM Johnathan Tiamoh <
> johnathantiamoh@gmail.com> wrote:
>
>> Hello,
>>
>> I upgraded from postgresql 9.5 to 14.8. After the upgrade users were not
>> able to connect due to password hash.
>>
>> The password encryption was on scram-sha-256. The password hash was on
>> md5. in the pg_hba.conf file the authentication method was md5. I do not
>> know why users could not connect because as per postgresql documentation,
>> that should work.
>>
>> I have previously done a similar upgrade and did not face similar issues.
>>
>>
>> Kind regards
>> Johnathan Tiamoh
>>
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread


end of thread, other threads:[~2023-07-12 04:39 UTC | newest]

Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-10 18:15 Need Help On Upgrade Johnathan Tiamoh <johnathantiamoh@gmail.com>
2023-07-10 19:38 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-10 20:00   ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-10 20:20     ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
2023-07-10 22:36       ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-11 00:38         ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
2023-07-11 03:45           ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-07-11 15:46             ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-11 15:53               ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
2023-07-11 16:19                 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-11 21:45                   ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
2023-07-11 23:32                     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-12 01:31                       ` Benedict Holland <benedict.m.holland@gmail.com>
2023-07-12 04:39                       ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
2023-07-10 20:02   ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
2023-07-11 14:16 ` Johnathan Tiamoh <johnathantiamoh@gmail.com>

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