Need to improve documentation how to correctly configure to use one user to auth others

[garry-t]

Describe the bug
It is not clear how to achieve same behaviour as we have in pgbouncer when we avoid hardcode all db user to config and instead use auth query and auth user to check password and user name from db side. I read docs multiple times for pgcat and struggled to made it work

To Reproduce
Steps to reproduce the behavior:

1. Set config

[general]
host = "0.0.0.0"
port = 6433
[pools.staging]
pool_mode = "transaction"
default_role = "primary"
prepared_statements_cache_size = 0
query_parser_enabled = false
primary_reads_enabled = true

[pools.staging.users.0]
username = "pgbouncer" # user which check pg_shadow no? 
password = "pass"
# same if we use server_username and server_password.
pool_size = 50
statement_timeout = 0


[pools.staging.shards.0]
servers = [
  ["127.0.0.1", 5432, "primary"]
]
database = "staging"
auth_query = "SELECT usename, passwd FROM user_search($1)"
auth_query_user = "pgbouncer" # duplicate it here since not clear 
auth_query_password = "pass"

2. Run command to test connection psql -h 127.0.0.1 -p 6433 -U test_staging_rw staging

Expected behavior
pgcat takes user name and password from connection string, connect to db with a separate user and check in pg_shadow provided user and password from connection string.

Screenshots
not relevant

Desktop (please complete the following information):
not relevant

Smartphone (please complete the following information):
not relevant

Additional context
so far error but with WARN severity ))

WARN ThreadId(16) pgcat: Client disconnected with error ClientSocketError("password code", ClientIdentifier { application_name: "psql", username: "test_staging_rw", pool_name: "staging" })
 WARN ThreadId(15) pgcat: Client disconnected with error ClientGeneralError("Invalid pool name", ClientIdentifier { application_name: "psql", username: "test_staging_rw", pool_name: "staging" })

[drdrsh]

I dug a bit into this so there are a few things needed to make this work, I highlighted them in comments on the suggested config

FIrst, you need to have your auth query settings directly under the pool settings not under [pools.pool_name.users] nor under [pools.pool_name.shards]

[pools.pool_name]
# Be sure that this view exists and you have single quotes around the placeholder $1
auth_query = "SELECT usename, passwd FROM user_search('$1')" 

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

auth_query_password = "auth_user_password"

[pools.pool_name.users.0]
# Auth passthru only works if this user's password is hashed in md5 
# (It won't work with SCRAM-SHA-256 today)
# the user also needs to be granted LOGIN
username="targeted_user"
# password= <no password is necessary here>