Postgres Pro
Downloads
Home   >   mailing lists

Thread: Function problem

Function problem

From
Domen Šetar
Date:

Hi Admins

 

I’m using pgbouncer in my postgresql installation. For authorization purposese I use function public.lookup (https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/)   which is defined for each database. Since we upgraded postgresql from V14 to V16 I have problems when migrating database from one instance to another:  authentication doesn’t work. I have to drop function and define it again on database to make authentication work.  Any idea why ?

 

Best regards!

izum

Domen Šetar
Computer Systems Support
IZUM – Institute of Information Science | Prešernova ulica 17 | 2000 Maribor | Slovenia
T: +386 2 25 20 339 | M: +386 41 676 342 | www.izum.si | domen.setar@izum.si

 

 

Attachment

Re: Function problem

From
khan Affan
Date:
Hi domen 

Check the function definition on both versions. Check the PgBouncer logs for any errors or warnings related to the function or authentication. This might provide more insight into what goes wrong during the migration. Also, review the PostgreSQL logs for any issues related to function execution or permission problems. Compare user MD5 hashcode in both versions and compare it some time it update the hash code

postgres=# SELECT 'md5' || md5('test' || 'postgres');

 ?column?
-------------------------------------

 md5633bc3c3d823be2a52d3dff94031e2c2

On Tue, Aug 13, 2024 at 2:57 PM Domen Šetar <domen.setar@izum.si> wrote:

Hi Admins

 

I’m using pgbouncer in my postgresql installation. For authorization purposese I use function public.lookup (https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/)   which is defined for each database. Since we upgraded postgresql from V14 to V16 I have problems when migrating database from one instance to another:  authentication doesn’t work. I have to drop function and define it again on database to make authentication work.  Any idea why ?

 

Best regards!

izum

Domen Šetar
Computer Systems Support
IZUM – Institute of Information Science | Prešernova ulica 17 | 2000 Maribor | Slovenia
T: +386 2 25 20 339 | M: +386 41 676 342 | www.izum.si | domen.setar@izum.si

 

 

Attachment

Re: Function problem

From
Laurenz Albe
Date:
On Tue, 2024-08-13 at 09:56 +0000, Domen Šetar wrote:
> I’m using pgbouncer in my postgresql installation. For authorization purposese I use function
> public.lookup (https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/)
> which is defined for each database. Since we upgraded postgresql from V14 to V16 I have
> problems when migrating database from one instance to another:  authentication doesn’t work.
> I have to drop function and define it again on database to make authentication work.  Any idea why ?

I guess that my article is at fault.  It still refers to "md5" authentication rather than
"scram-sha-256", which has been the password authenticatoin method of choice for years now.

I have updated my article.

Yours,
Laurenz Albe