FATAL: server login failed: wrong password type

[pip8786]

Describe the bug

I've worked on updating the repo to what's required to run on Google (mostly updating versions and getting rid of deprecated modules). Here's my fork: https://github.com/social-cascade/terraform-google-cloud-sql-pgbouncer. I've tried getting this working with a database that was already set up and a fresh setup using the examples/postgres_private/ but in both cases once I finally got things running, I'd get the error FATAL: server login failed: wrong password type when trying to log into it.

Steps to reproduce

Set up PGBouncer using terraform on my fork.
Try to connect to the database.

Expected behavior

Connect to the database.

** Additional Context **

Is it possible that a SQL Proxy needs to be added to the infrastructure setup? GoogleCloudPlatform/cloud-sql-proxy#1099

[drekthral]

Describe the bug

I've worked on updating the repo to what's required to run on Google (mostly updating versions and getting rid of deprecated modules). Here's my fork: https://github.com/social-cascade/terraform-google-cloud-sql-pgbouncer. I've tried getting this working with a database that was already set up and a fresh setup using the examples/postgres_private/ but in both cases once I finally got things running, I'd get the error FATAL: server login failed: wrong password type when trying to log into it.

Steps to reproduce

Set up PGBouncer using terraform on my fork. Try to connect to the database.

Expected behavior

Connect to the database.

** Additional Context **

Is it possible that a SQL Proxy needs to be added to the infrastructure setup? GoogleCloudPlatform/cloud-sql-proxy#1099

Hi, have you managed to make it all work?

I am also trying to create connection pooler for prisma, but keep failing for days 😣. I tried this and also yours (forked) repo, but in both cases I can not get past prisma error saying that it can't reach the database.

If you made it work, please, it would be extremly helpful if you could create step by step instuctions as you pointed in previous issue.

[pip8786]

I have not been able to get it to work. I even posted on the pgbouncer gitter chat and didn't get much of a response there. I had to give up and move on for now. I hope Google implements a built in solution soon as they have an issue open for that.

[joanaferreira0011]

Is it possible this is because this Pgbouncer image uses md5 encryption for passwords while new versions of Postgres in CloudSQL use scram-sha-256 by default?
You should be able to see what is your default database encryption by running show password_encryption. If so, you should be able to change the flag on cloudsql

[mlushpenko]

@joanaferreira0011 great suggestion. I had to struggle with quite a few steps to get it working, I am playing with alloydb, but I think the problems are the same, so here are my steps:

1. Allow postgresql user to querypg_shadow by changing database flags in the console (or whatever method you prefer). You can create a separate user for pgbouncer as described here
2. Confirm database encryption as you mentioned:

postgres=> show password_encryption;
 password_encryption 
---------------------
 scram-sha-256

3. Set the following settings for pgbouncer, I am using this helm chart for deployments, and settings are based on that (just adding here for complete reference) :

settings:
  auth_type: scram-sha-256
  auth_query: "auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename='$1'"
databases:
  postgres:
    auth_user: postgres
users:
  postgres: XXXXXXXXX

4. Validate postgresql user can perform auth_query after changing database flags:

postgres=> SELECT usename, passwd FROM pg_shadow WHERE usename='postgres';
 usename  |                                                                          passwd                                                                           
----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------
 postgres | SCRAM-SHA-256$4096:XXXXXX

5. Build an image with a newer version of pgbouncer based on the dockerfile from the same repo as support for scram-sha-256 comes in 1.11.x version