Quick Fix: To fix the issue, you need to force PostgreSQL to use md5 authentication by setting the POSTGRES_HOST_AUTH_METHOD and POSTGRES_INITDB_ARGS environment variables in the postgres service configuration.
The Problem:
Configure pgbouncer and postgresql in docker-compose to enable connection pooling and fix the server login failed: wrong password type error.
The Solutions:
Solution 1: Use MD5 Authentication
To fix the error, force Postgres to use MD5 authentication by setting the following environment variables:
POSTGRES_HOST_AUTH_METHOD=md5
POSTGRES_INITDB_ARGS=--auth=md5
These variables will instruct Postgres to use MD5 authentication during initialization and when connecting to the database. With this configuration, you can connect to the database through Pgbouncer successfully.
Solution 2: Use scram-sha-256 instead of md5 authentication
To resolve the issue with new PostGRESql versions using scram-sha-256 by default, you can use the following steps:
Use the following docker image: rmccaffrey/pgbouncer:latest
Set the AUTH_TYPE environment variable to scram-sha-256 in the pgbouncer service in your docker-compose file.
Ensure that you use 127.0.0.1 as the database host instead of localhost for production environments, especially with tools like kubernetes.
Refer to the provided GitHub repository for a detailed demonstration of configuring pgbouncer with md5 and scram authentication for latest PostGRESql versions.
Q&A
How do I apply connection pooling with pgb· in my docker compose file?
—
Apply the connection pooling by setting POSTGRES_HOST_AUTH_METHOD=md5 and POSTGRES_INITDB_ARGS=–auth=md5 environment variables in postgres services.
How do I include connection pooling for scram-sha-256
—
You can include connection pooling by using the correct image for pgb· (image: rmcca·/pgb·:latest) and make sure to use 127.0.0.1 instead of localhost.
Video Explanation:
The following video, titled "How to configure PostgreSQL | Learning | DevOps - YouTube", provides additional insights and in-depth exploration related to the topics discussed in this post.
... configuration files to use 21:49 Run our PostgreSQL container 22:15 Docker Compose 23:10 Outtro. How to configure PostgreSQL | Learning | DevOps.
