Implemented PGBouncer like database Proxy functionality using Auth_Query

[rvsoni]

Implemented PGBouncer like database Proxy functionality using Auth_Query

The new implementation behavior is as follows, For any pool, if proxy configuration is made true, the pool would not initialize on the pgcat startup, on the first connection, pgcat would connect to the PostgreSQL cluster using auth_query and retrieve a password hash and established pool on the fly,

This implementation fixes the following issues. #443, #509

Following PR #521, #631 should marge to enhance this implementation,

Use pgcat.proxy.toml as a configuration sample.

Added new configuration proxy variable per pool to indicate this is pgbouncer like proxy behaviour and would use auth_query base login.

I am doing Rust programming for the first time, so I see many code improvements in this implementation. Also, I have made a lot of duplicate code to implement this functionality.

Please review the code and suggest any changes to make this PR ready to merge.

Feel free to ask any questions on this implementation.

[SoulKyu]

Hello @rvsoni

I'm looking to use pgcat as a general PostgreSQL proxy/connection pooler for multiple databases using multiple users without having to explicitly configure each database and user combination. Current limitation:

• Each database requires its own pool configuration
• Users must be explicitly defined for each pool
• No wildcard or pattern matching support for database names or usernames
• Each pool can only connect to one specific database

I manage a PostgreSQL cluster with hundreds of databases and multiple users. Currently, I would need to generate and maintain a massive configuration file with every possible database/user combination, which is impractical and error-prone. Dynamic proxy support would allow pgcat to function more like a traditional connection proxy while still providing its advanced pooling features. Creating databases and users at a huge frequency.

Did you think that your PR will respond to my need ? I'm not 100% sure of the scope of your PR, i would be very excited to test it if it could respond to my need !

[rvsoni]

Hi @SoulKyu

This PR implementation solves the current limitations of your messages,

1. Each database requires its own pool configuration No, you need only one Pool Configuration to point to one PostgreSQL Server

2. Users must be explicitly defined for each pool No, you need to use auth_query configuration to find all users from Postgresql and connect to the users you are connecting

3. No wildcard or pattern matching support for database names or usernames You need to make sure a Postgresql database and User name is the same while connecting to PostgreSQL; if the database name is not provided, PostgreSQL considers the user name as the database name and automatically connects to that database

4. Each pool can only connect to one specific database No, each pool only connects to one PostgreSQL server and all databases on that server using auto_query configuration.

Refer: pgcat.proxy.toml as configuration sample file to set PGCAT with proxy configuration

You give a try to this docker image to try my code fix on PGCAT https://hub.docker.com/r/rvsoni/pgcat/

[rvsoni]

Hi @SoulKyu

Did you try PR ?

Let me know if you face any issue or need more info in this

Ravi

[SoulKyu]

Hi @SoulKyu

Did you try PR ?

Let me know if you face any issue or need more info in this

Ravi

I should try next week and give feedback !

[SoulKyu]

Hello @rvsoni

I'm trying your MR using your images and it doesn't seems to do the job.

Here is my configuration file :

[general]

log_level = "DEBUG"

host = "0.0.0.0"
port = 5432
admin_username = "postgres"
admin_password = "passwordblabla"

[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 = "passwordblabla"

proxy = true

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

[pools.pgml.shards.0]
servers = [
  ["pgsql2.soulkyu.net", 5432, "primary"]
]
database = "postgres"

When i try to connect to a specific database using a specific user :

psql postgresql://[email protected]/netbox_dev

Here is the error :

2024-12-17T17:38:34.177256Z  WARN ThreadId(05) pgcat: Client disconnected with error ClientGeneralError("Invalid pool name", ClientIdentifier { application_name: "psql", username: "gule_bdv1vtbt6r7g", pool_name: "netbox_dev" })

Do i need something else in my configuration ?

Its look like it should automatically create pool but it doesn't.

Did you know what i'm missing ?

[rvsoni]

Hello @SoulKyu

PGCat required the pool name to be used as a virtual database name for the connection.

So, in this case pgml is the database name to be used,

Refer:

1. https://postgresml.org/docs/open-source/pgcat/configuration#shards
2. https://jdbc.postgresql.org/documentation/use/#connecting-to-the-database

Now, the target database name is the same as the user name gule_bdv1vtbt6r7g

the connection URL is as follow postgresql://[email protected]/pgml

You can use different usernames to connect to a database,

Note: Database name and user name must be the same.

[rvsoni]

Hi @SoulKyu

Any update on this, Ravi

[p-bakker]

Hi @drdrsh, it seems you're the most recent committer on this project, with @levkk having moved on to pgDog (a fork?)

Is there any change of moving this PR forward and getting it merged?

[levkk]

PgDog is a complete rewrite that adds quite a bit more features. Happy to have you migrate to PgDog if you're interested and build whatever features that are missing for your use case. PgDog is now in production and fully tested.

[p-bakker]

I looked at pgDog, but from a quick glance at the GitHub repo it looked a bit like a one-man-band project to be honest (but maybe I jumped to conclusions 🤔). Are any other devs but you involved? Is it backed by a company in any way?

Also, from quickly scanning the website, it wasn't clear to me how pgDog compares to pgCat/pgBouncer feature-wise

What was the reasoning behind starting from scratch?

Does pgDog have this particular proxy feature already?

[levkk]

I looked at pgDog, but from a quick glance at the GitHub repo it looked a bit like a one-man-band project to be honest (but maybe I jumped to conclusions 🤔). Are any other devs but you involved? Is it backed by a company in any way?

Yes, it's backed by a company. We just graduated from YC and doing this full time. Happy to tell you more: https://calendly.com/lev-pgdog/30min

Also, from quickly scanning the website, it wasn't clear to me how pgDog compares to pgCat/pgBouncer feature-wise

Let's chat, I can give you the play by play.

What was the reasoning behind starting from scratch?

The architecture in pgcat doesn't support what I wanted: cross-shard queries (and some other features that I wanted). To make this work here would of been a complete rewrite.

Does pgDog have this particular proxy feature already?

Probably, but tell me more about your use case when we talk! Checkout our docs as well: https://docs.pgdog.dev

[p-bakker]

@levkk Tnx for the invite, but after some more reading/research, I concluded we're currently not ready to start thinking about all this