This is more of a discussion topic then issue, but discussions are disabled, so here we are. :) Interesting (at least for me) functionality would be an ability to route queries to separate read traffic from write. Simple use case: I have one master and two replicas, all write queries, no matter the client, should go to the master and all the rest to replicas. Is this something worth considering?

If a set statement is sent in a transaction then we shouldn't try to send the DISCARD ALL statement

Gets rid of two message clones that are almost always hit

Is your feature request related to a problem? Please describe. we are using spreaker/prometheus-pgbouncer-exporter to monitor our pgbouncers, it will be great if pgcat has compatible metrics

Describe the solution you'd like could be scrape by prometheus exporters, typically existing pgbouncer exporters

Describe alternatives you've considered use general postgres exporter and manually craft the stat sql

Describe the bug selected wrong shard and the connection keep reporting fatal

To Reproduce

• create 2 shards, naming shard 0 and 1
• in a db ide, open a session
• SET shard TO '2';, it reports FATAL: shard 2 is more than configured 2
• SET shard TO '0';, it reports FATAL: shard 2 is more than configured 2

Expected behavior be able to set shard to 0 and 1 after setting a wrong one

Describe the bug pgcat memory usage climbs up monotonically if we repeatedly close connections mid-transaction (Maybe due to a memory leak?)

To Reproduce Run the following Ruby code and monitor pgcat RSS

require 'pg'
$stdout.sync = true

def work
    conn = PG::connect("postgres://main_user:@pgcat:5432/main_db")
    conn.async_exec("BEGIN")
    conn.close
rescue Exception => e
    puts "Encountered #{e}"
end


sleep 5
loop { 10.times.map { Thread.new { work } }.map(&:join) }

Repeated conn.async_exec("SELECT 1") or conn.async_exec("BEGIN") followed by conn.async_exec("COMMIT") don't cause the issue (pgcat RSS hovers around 3MB in these cases) whereas with the unclosed transaction example, I was able to see a value of 55MB RSS after 10 minutes running.

Expected behavior RSS shouldn't grow constantly.

We are not sending DISCARD ALL in all the situations that we should. This can leak session-specific settings/resources between clients.

This is currently easily reproducible by repeatedly running PREPARE prepared_q (int) AS SELECT $1; in psql until an error emerges.

I created a test to reproduce this issue and verified the fix. I am open to changing the name of the method.

Experimenting with Reader/Writer endpoints

With this PR, connecting with postgres://sharding_user:sharding_user@localhost:6432/sharded/writer is equivalent to connecting to postgres://sharding_user:sharding_user@localhost:6432/sharded and then setting role to primary before each query. Connecting with postgres://sharding_user:sharding_user@localhost:6432/sharded/reader is equivalent to connecting to postgres://sharding_user:sharding_user@localhost:6432/sharded and then setting role to replica before each query.

This is useful for clients that maintain their own primary/replica pools but do not have ability ban bad replicas or load balance (e.g. a vanilla Rails 6 app).

Describe the bug

I am working on a test bench to test some edge cases against pgcat. Opening a transaction and then closing the client connection causes pgcat to issue ROLLBACK; DISCARD ALL, however this behavior triggers ERROR: DISCARD ALL cannot run inside a transaction block error on the server. Might be something wrong with my setup or an issue with pgcat

To Reproduce Put the following 3 files in the same directory and run docker compose up

docker-compose.yml

version: '3'
services:
  pg:
    image: postgres:12-bullseye
    environment:
      POSTGRES_HOST_AUTH_METHOD: trust
      POSTGRES_DB: main_db
      POSTGRES_USER: main_user

  pgcat:
    build: https://github.com/levkk/pgcat.git#main
    restart: always
    command: ["pgcat", "/etc/pgcat/pgcat.toml"]
    links:
      - pg
    volumes:
      - "./config.toml:/etc/pgcat/pgcat.toml"

  app:
    image: ruby
    links:
      - pgcat
    volumes:
      - "./entrypoint.rb:/app/entrypoint.rb"
    command: bash -c "gem install pg && ruby /app/entrypoint.rb"

entrypoint.rb

require 'pg'
$stdout.sync = true

def poorly_behaved_client
    conn = PG::connect("postgres://main_user:@pgcat:5432/main_db")
    conn.async_exec("BEGIN")
    conn.async_exec("SELECT 1")
    conn.close
rescue Exception => e
    puts "Encountered #{e}"
end

sleep 5
loop { poorly_behaved_client }

config.toml

[general]
host = "0.0.0.0"
port = 5432
pool_size = 5
pool_mode = "transaction"
connect_timeout = 5000
healthcheck_timeout = 1000000
ban_time = 1 # Seconds
[user]
name = "main_user"
password = ""
[shards]
[shards.0]
servers = [[ "pg", 5432, "primary" ]]
database = "main_db"
[query_router]
default_role = "primary"
query_parser_enabled = false
primary_reads_enabled = false
sharding_function = "pg_bigint_hash"

Expected behavior No server errors.

Currently prometheus endpoint only outputs metrics related with 'stats' (those shown in a SHOW STATS admin command).

This change:

• Adds server metrics to prometheus endpoint.
• Adds database metrics to prometheus endpoint.
• Adds pools metrics to prometheus endpoint.
• Change metrics name to have a prefix of (stats|pools|databases|servers).

This change is not backward compatible as metric names are now prefixed. Maybe we can leave current metric names 'as is' and add prefixes only for the new stuff, but I like it more with everything prefixed.

/cc @dat2

Moves config validation to own functions to enable tools to use them

Moves sharding config to enum

Makes defaults public

Make connect_timeout on pool and option which is overwritten by general connect_timeout

This started as an effort to add SHOW CLIENTS to allow for attribution of server work to specific application_names. What ended up happening is a big refactor for the Pgcat stats after I encountered a bug where it seemed that cl_* stats seemed to wrongfully include admin clients, I wasn't able to fix that without changing a lot of things hence, this refactor.

The major changes here are

• Introduction of SHOW CLIENTS and SHOW SERVERS queries, both are backed by an ArcSwap HashMap that gets updated by the stats collector every second.
• Introduction of client_register and server_register events that mark a clear starting point for tracking stats for both client and server. We collect some information about clients/servers in that point and continue to use that information for subsequent events.
• Using Anonymous structs in the Enum variants to attach relevant information to each event.
• Making a distinction between server_id which is a random identifier we generate for each server connection and the process_id which is the Postgres Backend PID used for cancel query requests. Prior to this PR both concepts were one and the same.
• Report client errors using stats system, so errors like failing to obtain a connection from a pool or a server getting banned while client is talking to it.
• Some changes to the placement of event reporting calls
• Integration tests for stats

If a server is offline during startup or a config reload, instead of failing, we will log the error. Then, when a client tries to connec to a particular pool, pgcat will attempt to connect to the upstream database server and obtain the server info params before passing them to the client. These server info params will then be cached so the next client will not need to wait for pgcat to connect to the upstream database again. These parameters are warmed or re-warmed during a reload.

This resolves https://github.com/levkk/pgcat/issues/254 .

Auth query feature

This change adds a feature that allows setting auth passthrough for md5.

Configuration

• auth_query: An string containing a query that will be executed against every server of a pool on boot to obtain the hash of a given user. This query have to use a placeholder $1, so pgcat can replace it with the user its trying to fetch the hash from.
• auth_query_user: The user to use for connecting to the server and executing the auth_query.
• auth_query_password: The password to use for connecting to the server and executing the auth_query.
• auth_query_database: The database to use for connecting to the server and executing the auth_query.

The behavior is, at boot time, when validating server connections, a hash is fetched per server and stored there. When new server connections are created, that hash is used for creating them, if the hash could not be obtained for whatever reason, it falls back to the password set in the pool.

Client connections are also authenticated using the obtained hash.

Testing

This is currently tested using the integration tests:

• We test we can connect when no passwords are specified for pools and auth_query is set.
• We test we can connect when auth_query is configured but failing and passwords are set.

Considerations

• A new crate is added to deal with postgres protocol parsing, I first did it myself and didn't like the result so thought, why reinventing the wheel?
• Currently hash refresh is not done when reloading and no pool have changed.
• No documentation is yet written, I want to first discuss about the change and will write later if we agree on the merge.
• This fixes (https://github.com/levkk/pgcat/issues/253)

Currently prometheus endpoint only outputs metrics related with 'stats' (those shown in a SHOW STATS admin command).

This change:

• Adds server metrics to prometheus endpoint.
• Adds database metrics to prometheus endpoint.
• Adds pools metrics to prometheus endpoint.
• Change metrics name to have a prefix of (stats|pools|databases|servers).

This change is not backward compatible as metric names are now prefixed. Maybe we can leave current metric names 'as is' and add prefixes only for the new stuff, but I like it more with everything prefixed.

/cc @dat2

Is your feature request related to a problem? Please describe.

Postgres 14 switched the default password encryption and authentication method from Md5 to SCRAM-SHA-256. PgCat can authenticate to the servers using SCRAM, but it can't authenticate clients using SCRAM, only Md5. Client libraries still support Md5, but medium/long term Postgres is sure to remove that insecure authentication algorithm from libpq, so we need to add support for SCRAM for client auth as well.

Describe the solution you'd like Add support for client-initiated auth to scram.rs and add support for it in client.rs.

Describe alternatives you've considered There aren't any, this is a necessary change.

Additional context #253

Describe the bug ConnectionPool requires all servers to be reachable to reload the config. Specifically, it fetches server information and passes it to the client statically instead of dynamically when the connection is made. This ensures that the servers are correctly configured before a configuration is made live, but it also blocks configuration reload for every server if only one server is down.

To Reproduce Steps to reproduce the behavior:

1. Place an incorrect IP/port into the server config.
2. Try to reload config with kill -HUP $(pgrep pgcat)
3. It should be blocked on reloading config, although the pooler will remain online with existing config.

Expected behavior Good question. I think it should skip the broken server and issue an error to the log.

Desktop (please complete the following information):

• OS: Ubuntu
• Version: latest main

Additional context This can be thought of both as a feature and a bug. It's conceivable that servers that are not reachable should not be allowed into the system, but intermittent issues happen and ideally the pooler shouldn't rely on all servers to be available to do its job, e.g. in multi-tenant or heavily sharded/replicated clusters, where the failure of one shard/replica shouldn't impact the continuous ability of the pooler to do its job.

Is your feature request related to a problem? Please describe.

We currently use PgBouncer with md5 password method using auth_query so we do not need to change its configuration when password changes etc. Also it allows us not to have to set passwords in three places, application -> DB connection pooler -> Database server.

It would be nice to have a feature like PgBouncer/Odyssey have that allows md5 authentication using passwords stored on server backends by means of a query to the target server. This way we only have to tell PgCat one password, the one used to connect to the server for looking up the password. The rest of passwords are obtained from the DB.

Describe the solution you'd like I find Pgbouncer neat.

In the case of PGCat It would be something like:

auth_query string

Enable remote user authentication.

Whenever a new client connection is opened and MD5 auth is used, use 'auth_query' against target server (using auth_user and auth_password) to obtain user password.

auth_query "SELECT usename, passwd FROM pg_shadow WHERE usename=$1"
auth_user ""
auth_password ""

This is usually done using a function so you can use an unprivileged user that have access to just this table (see this)

Disabled by default.

Also, to ease deployment in containerized environments It would be nice to be able to overwrite auth_query_password using an environment variable like (PGCAT_AUTH_QUERY_PASSWORD) , this way, if also admin password can be overridden by an Env var, config file will be password-less which improves security and simplifies deployment in containerized environments.

NOTE: I currently have some bandwidth to implement this.