Re: Need Help On Upgrade - Mailing list pgsql-general

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