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:
- 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"
- 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" })