Is there an existing issue already for this feature request/idea?

• I have searched for an existing issue, and could not find anything. I believe this is a new feature request to be evaluated.

What problem is this feature going to solve? Why should it be added?

We want to use certificate authentication for our CNPG setup.
The certificates should be issued one per Pooler, so we keep clean separation there.

Currently the auth_user is extracted automatically from the certificate.

We would like to decouple the certificate CN from the role the pooler uses on the database by allowing the pooler to connect as role pgbouncer (set by auth_user), even if the certificate CN has a different value.
The pg_ident configuration would look something like this:

pg_ident:
    - pgbouncer mydb-team1-ro-pooler
    - pgbouncer mydb-team1-rw-pooler
    - pgbouncer mydb-team2-ro-pooler
    - pgbouncer mydb-team2-rw-pooler
[...]

This would allow us to set permissions for the poolers once (by granting to the pgbouncer role) and I can add more poolers with the same permissions by adding them to the pg_ident configuration.
That way, the poolers can all have their own certificate with its own, distinct CN, but the role on the database is shared.

I would like to do for every team we offer the PostgreSQL to internally, so there would be about 10 PgBouncers, which would have configurations drifting apart over time if they'd all have their own role.
But if they share a role, the configuration is the same for all of them always.
This is beneficial for example when managing permissions required for the auth_query.

Describe the solution you'd like

I'd like to be able to set the auth_user explicitly, overwriting the value read from the certificate.

A setup like this should work:

spec:
  pgbouncer:
    # We have to set authQuerySecret, because we specify our own authQuery
    authQuerySecret:
      name: my-pooler
    authQuery: "SELECT rolname, '' from pg_roles where rolname = $1"
    parameters:
      # We want clients to connect only via TLS certificate
      auth_type: cert
      # We set one username for all PgBouncers to make our setup easier
      auth_user: pgbouncer

Describe alternatives you've considered

Having function execute permissions per Pooler
This adds additional steps for our setup when adding a new pooler, which increases the risk for human error and is therefore less desirable.

Additional context

I already have a PR ready for this that works well for us and is not a lot of changes.

Backport?

Yes

Are you willing to actively contribute to this feature?

Yes

Code of Conduct

• I agree to follow this project's Code of Conduct

EDIT: Added pg_ident example
EDIT2: Copied explanation given in Slack to the issue