3 ms·

> Since connection poolers reuse connections between clients, the connection state of one client “leaks” into the connection state of another. Wow this is very

by petters 3mo ago

> Since connection poolers reuse connections between clients, the connection state of one client “leaks” into the connection state of another. Wow this is very bad. This actually happens in typical Postgres setups?

vizzier 3mo ago
by definition connection poolers re-use connections so it it can happen with any connection pooling setup, PG or no. in pgbouncer the connection is reset via a customisable command [0] which should reset the connection to a clean state. [0] https://www.pgbouncer.org/config.html#server_reset_query https://www.pgbouncer.org/config.html#server_reset_query
llimllib 3mo ago
Yes, as a consequence of how aggressively transparent to the postgres wire protocol pgbouncer wants to be. This article does a good job explaining it: https://www.augusteo.com/blog/how-pgbouncer-works https://www.augusteo.com/blog/how-pgbouncer-works
McGlockenshire 3mo ago
You'll see this kind of fun in other databases that support "persistent connections." When you start up, you have absolutely no idea what the state of the database is. If a previous process errored out, you might find yourself in the middle of a broken transaction for example. Did the last session do some weird SET magic to make things work? Did it create temporary tables? Well guess what, it's all still there!
danudey 3mo ago
It's not unique to postgres, as others have said; the same thing can happen with e.g. MySQL poolers/proxies/etc., since the behavior of the connection can be changed dynamically and it persists for the lifetime of the connection. Example: legacy client A connects to MySQL via the bouncer and says 'I want all of our conversations to use latin-1, not utf-8'. This changes the character set that MySQL parses queries with and returns responses in. The legacy client does some queries and then disconnects. Now a new client connects to MySQL, and the bouncer just assigns it to the still-open connection from before. The new client is fully UTF-8 compatible and since this is the default for our database it doesn't explicitly say so; it just assumes that UTF-8 is the way to go. Unfortunately, the database server is still thinking in latin-1, meaning that if this new client sends UTF-8 data it will be parsed as latin-1; latin-1 is a subset of UTF-8, meaning that queries will actually work fine unless they need to use a character outside of latin-1, in which case they will get an error, or corrupted data, from the server. The only solutions around this are: 1. Ensure that every client is using the same settings; if your database is for a single app that uses the same ORM, then this is automatic. 2. Ensure that every client is always explicit about everything it might need to change e4very time, so that every UTF-8 client explicitly sets UTF-8 connections even when that's the default; clients that need utf8mb4 ask for it explicitly and clients that can't handle it ask for something else. One way of ensuring this happens is to configure the server (or the bouncer) to use defaults which are not valid for anyone, or which are going to cause errors frequently and not rarely (e.g. setting the default character set to 7-bit swedish, which would cause frequent errors). 3. Use a bouncer which can either disallow these changes or detect and revert them after the original client has disconnected. I'm not sure if this exists for MySQL at least. 4. Use separate bouncers for each application that might be different (extension of #1); in other words, instead of having a bouncer or set of bouncers for each pool of database servers, you have them for each application; your web app gets one, your legacy reporting tool gets one, your ODBC connector gets one, and so on. It's kind of a huge mess in theory; in practice, a lot of installations fall into the #1 case so it never matters, but that makes the occasional instance where it does matter extremely difficult to debug.
- nijave 3mo ago
  Recently ran into a related bug in duckdb. They implemented a basic http connection pooler (described as a parking lot) but there are edge cases where broken connections get returned to the pool then the next thing tries to use a busted connection and fails.
  - lgas 3mo ago
    DuckDB is in-process though?
    - nijave 3mo ago
      Yeah, just another example of "connections pools are hard in general"
- drdexebtjl 3mo ago
  > 4. Use separate bouncers for each application that might be different (extension of #1) I wonder if clients send something equivalent to a User-Agent, such that the connection pooler could assign them to different pools automatically.
  - tpetry 3mo ago
    This exists, its called a user. You just use different database users for this.
    - drdexebtjl 3mo ago
      I was thinking of something that only exists at the pool level. Every new version of your app has the potential to change behavior in a way that would affect the previous version if the connection was recycled during a progressive rollout. But I don’t think I would want to create a real database user for every version of the app. I suppose the connection pooler could map versioned users to the same real user, and use separate pools, but a dedicated UA field is probably better.
nijave 3mo ago
pgbouncer has different pool modes you can pick from that have some impact over what's possible to leak https://www.pgbouncer.org/features.html https://www.pgbouncer.org/features.html
hnarn 3mo ago
”typical Postgres setups” do not include connection poolers to begin with, this is not a Postgres issue.

by petters 3mo ago

vizzier 3mo ago

by definition connection poolers re-use connections so it it can happen with any connection pooling setup, PG or no. in pgbouncer the connection is reset via a customisable command [0] which should reset the connection to a clean state. [0] https://www.pgbouncer.org/config.html#server_reset_query https://www.pgbouncer.org/config.html#server_reset_query

llimllib 3mo ago

Yes, as a consequence of how aggressively transparent to the postgres wire protocol pgbouncer wants to be. This article does a good job explaining it: https://www.augusteo.com/blog/how-pgbouncer-works https://www.augusteo.com/blog/how-pgbouncer-works

McGlockenshire 3mo ago

You'll see this kind of fun in other databases that support "persistent connections." When you start up, you have absolutely no idea what the state of the database is. If a previous process errored out, you might find yourself in the middle of a broken transaction for example. Did the last session do some weird SET magic to make things work? Did it create temporary tables? Well guess what, it's all still there!

danudey 3mo ago

It's not unique to postgres, as others have said; the same thing can happen with e.g. MySQL poolers/proxies/etc., since the behavior of the connection can be changed dynamically and it persists for the lifetime of the connection. Example: legacy client A connects to MySQL via the bouncer and says 'I want all of our conversations to use latin-1, not utf-8'. This changes the character set that MySQL parses queries with and returns responses in. The legacy client does some queries and then disconnects. Now a new client connects to MySQL, and the bouncer just assigns it to the still-open connection from before. The new client is fully UTF-8 compatible and since this is the default for our database it doesn't explicitly say so; it just assumes that UTF-8 is the way to go. Unfortunately, the database server is still thinking in latin-1, meaning that if this new client sends UTF-8 data it will be parsed as latin-1; latin-1 is a subset of UTF-8, meaning that queries will actually work fine unless they need to use a character outside of latin-1, in which case they will get an error, or corrupted data, from the server. The only solutions around this are: 1. Ensure that every client is using the same settings; if your database is for a single app that uses the same ORM, then this is automatic. 2. Ensure that every client is always explicit about everything it might need to change e4very time, so that every UTF-8 client explicitly sets UTF-8 connections even when that's the default; clients that need utf8mb4 ask for it explicitly and clients that can't handle it ask for something else. One way of ensuring this happens is to configure the server (or the bouncer) to use defaults which are not valid for anyone, or which are going to cause errors frequently and not rarely (e.g. setting the default character set to 7-bit swedish, which would cause frequent errors). 3. Use a bouncer which can either disallow these changes or detect and revert them after the original client has disconnected. I'm not sure if this exists for MySQL at least. 4. Use separate bouncers for each application that might be different (extension of #1); in other words, instead of having a bouncer or set of bouncers for each pool of database servers, you have them for each application; your web app gets one, your legacy reporting tool gets one, your ODBC connector gets one, and so on. It's kind of a huge mess in theory; in practice, a lot of installations fall into the #1 case so it never matters, but that makes the occasional instance where it does matter extremely difficult to debug.

nijave 3mo ago
Recently ran into a related bug in duckdb. They implemented a basic http connection pooler (described as a parking lot) but there are edge cases where broken connections get returned to the pool then the next thing tries to use a busted connection and fails.
- lgas 3mo ago
  DuckDB is in-process though?
  - nijave 3mo ago
    Yeah, just another example of "connections pools are hard in general"
drdexebtjl 3mo ago
> 4. Use separate bouncers for each application that might be different (extension of #1) I wonder if clients send something equivalent to a User-Agent, such that the connection pooler could assign them to different pools automatically.
- tpetry 3mo ago
  This exists, its called a user. You just use different database users for this.
  - drdexebtjl 3mo ago
    I was thinking of something that only exists at the pool level. Every new version of your app has the potential to change behavior in a way that would affect the previous version if the connection was recycled during a progressive rollout. But I don’t think I would want to create a real database user for every version of the app. I suppose the connection pooler could map versioned users to the same real user, and use separate pools, but a dedicated UA field is probably better.

nijave 3mo ago

pgbouncer has different pool modes you can pick from that have some impact over what's possible to leak https://www.pgbouncer.org/features.html https://www.pgbouncer.org/features.html

hnarn 3mo ago

”typical Postgres setups” do not include connection poolers to begin with, this is not a Postgres issue.