How to generate authentication file for PgBouncer
PgBouncer is a lightweight connection pooler that prevents the MAX_CONNECTION reached error in PostgreSQL. Basically, it does a very good job for what it says. It maintains a pool of connections for your applications. This helps in reducing the overhead of setting up and tearing down connections for every client incoming request. [link]
By default, it uses a plain text password file for authenticating users to the PostgreSQL database. This will obviously raise a red flag during security audit! Let’s see what we can do to prevent that red flag 🙂
First, we have to install pg_bouncer and psycopg2.
yum install pgbouncer.x86_64 python-psycopg2.x86_64
Now that we got PgBouncer installed. We have to do configuration.
# cd /etc # chown -R postgres. pgbouncer/ # su - postgres $ cd /etc/pgbouncer/ $ ls mkauth.py mkauth.pyc mkauth.pyo pgbouncer.ini
Now, let’s generate that password file 🙂
$ ./mkauth.py "/etc/pgbouncer/password.txt" "dbname='postgres' user='postgres' host='localhost'" $ ls mkauth.py mkauth.pyc mkauth.pyo password.txt pgbouncer.ini
Let’s see if we can view the password!
$ cat password.txt "postgres" "" "" "repmgr" "md58ea99ab1ec3bd8d8a6162df6c8e1ddcd" "" "testuser" "md5d70a0452418aeb8fb4030eae69ca2856" ""
It uses the pg_shadow table and generate the password file. Obviously, it is only salted and double hashed with MD5. It is not as secure as SHA-256, but still a lot more secure that reading the password in plain text isn’t it?