PDO throws connection error about "trust" authentication when using pgbouncer for one database, but not another. Settings are identical

Question

This problem is driving me batty. I have a PHP script which connects to a postgresql database, and I have pgbouncer running for connection pooling.

I've tested it using two databases, and it works just fine for both when I connect directly. Here is my connection code:

        $dbuser='db1'; $dbpass='db1'; $dbname='db1';

        $dsn="pgsql:port=5432;host=/var/run/postgresql;dbname=$dbname";

        $options = [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
        ];
        try {
                $pg_pdo=new PDO($dsn, $dbuser, $dbpass, $options); 
        } catch (\PDOException $e) {
                throw new \PDOException($e->getMessage(), (int)$e->getCode());
        }

This code, with db1, works just fine whether I keep the port 5432, or change the port to to 6432. When I change the databasename, user, and password to db2, it also works fine on port 5432.

But when I am connecting to the second database and set the port to 6432, I get the following error:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[08006] [7] ERROR:  "trust" authentication failed

Now, I've already checked my /etc/pgbouncer/pgbouncer.ini, it contains the lines:

[databases]
* =

Meaning that the connection info for any databases is simply being forwarded. Neither db1 nor db2 is mentioned in the file.

Also, my /var/lib/pgsql/11/data/pg_hba.conf file:

# "local" is for Unix domain socket connections only
local   all              postgres                                peer
local   db1              db1                                     md5
local   db2              db2                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

Yes, I've restarted both pgbouncer.service and postgresql-11.service.

I can't for the life of me figure out why PHP's PDO is falling back on the "trust" authentication method, I don't want it to do this, I want it to send the password normally.

And why would it be working with one database and not the other? Is this a question of the db2 role lacking some sort of privilege that db1 is granted, or something about the database being different?

In case it is relevant, my environment is PHP 7.4, PostgreSQL 11.12, and pgbouncer 1.15.

Answer 1

I eventually solved this myself. Apparently it is also necessary to edit the /etc/pgbouncer/userlist.txt file:

This file had a listing for db1, but not db2. Adding the second line (format is "username" "password"):

"db2" "db2"

However, this was insufficient to fix the problem. Apparently, I had created this file as root. It needs to be owned by pgbouncer, so this command was necessary:

chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt

These two changes fixed the problem. Thank you to this link for helping me, and for this thread for a more thorough explanation of how the pgbouncer configuration works.