postgresml/pgcat

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

Opened this issue · 0 comments

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"
  1. 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" })