pgbouncer like dynamic database support

[szguoxz]

Currently, pgcat requires clients to use poolname as database name in their connectionstring, which is really limiting its use. How about have a default pool, then any database name in the provided connectionstring does not have a matching pool name will use the default pool? And the default pool will simply connect to the database specified in the connectionstring, and ignore the database specified in shard config.

together with the auth_query feature, this could solve a lot problems.

[rvsoni]

Hello @szguoxz

Can you check my implementation of PGBouncer like proxy using auth_query base autnatication with dynamic pool creation

https://github.com/rvsoni/pgcat

https://hub.docker.com/r/rvsoni/pgcat

[bharling]

@rvsoni this looks like it would solve issues I'm facing too - can you provide some guidance on how to get this setup? I'd love to test

[rvsoni]

@bharling

You need to use the Docker image https://hub.docker.com/r/rvsoni/pgcat and a config example from https://github.com/rvsoni/pgcat/blob/main/pgcat.toml to start. The most essential config is to use aut_query based login for the pool, which dynamically looks up the user and PostgreSQL database from the cluster.

Ensure that your database and the user associated with that database name match.

Below is the config for setting the pool on pgcat

[pools.pgml]
auth_query="SELECT usename, passwd FROM pg_shadow WHERE usename='$1'"

# Be sure to grant this user LOGIN on Postgres
auth_query_user = "postgres"
auth_query_password = "postgres"

proxy = true

The below config is to point the pgcat pool to the PostgreSQL database cluster.

[pools.pgml.users.0]
username = "postgres"
#password = "postgres"
pool_size = 10
min_pool_size = 1
pool_mode = "session"

[pools.pgml.shards.0]
servers = [
  ["localhost", 5432, "primary"]
]
database = "postgres"