Re: Feature Proposal: Connection Pool Optimization - Change the Connection User

Hi Everyone,

Here is a progress update. I have an established team of 2 fulltime systems
programmers who have been working on this area for a couple of months now.

- Past
- *Impersonation* - a prototype has been completed for Option-1
"Impersonation"
- *Benchmarking* - has been completed on a range of options,
including Option-0 "Reconnection".
- Both Impersonation and the Benchmarking is currently on the
backburner
- Current:* Notification Concentrator* - This is not PostgreSQL Codebase
work. This project makes NOTIFY/LISTEN work in Odyssey (and others) while
in Transaction mode. (Until now, NOTIFY/LISTEN can only work in SESSION
mode). We intend to also build patches for PgBouncer, and other popular
Connection Pool systems.
- It works in Session mode, but my organisation needs it to work in
Transaction mode. It works by intercepting LISTEN/UNLISTEN in SQL and
redirecting them to a single shared connection. There will be a Pub/Sub
system within Odyssey. The LISTEN/UNLISTEN is only sent for the first
subscriber or last unsubscriber accordingly. The NOTIFICATION
messages are
then dispatched to the Subscriber list. At most only one SESSION
connection
is required.
- Next:
- *Update Benchmarking:* I then expect to update Benchmarks with a range
of prototype solutions, with both Impersonation and Notification
Concentrator for final review.
- *Publishing Benchmarking*: I will send our results here, and offer
a patch for such benchmarking code.
- *Final Implementation:* The team will finalise code for
production-grade implementations, and tests
- *Patches:* Then my team will submit a patch for PostgreSQL, Odyssey
and others; working to polish anything else that might be required of us.

Todd

On Wed, 2 Feb 2022 at 10:56, Todd Hubers <todd(dot)hubers(at)gmail(dot)com> wrote:

> Hi Everyone,
>
> Benchmarking work has commenced, and is ongoing.
>
> - *OPTIONS 5/6/7* - `SET SESSION AUTHORIZATION` takes double the time
> of a single separate SimpleQuery. This is to be expected, because double
> the amount of SimpleQuery messages are being sent, and that requires a full
> SimpleQuery/Result/Ready cycle. If there is significant latency between a
> Connection Pooler and the database, this delay is amplified. It would be
> possible to concatenate text into a single SimpleQuery. In the real world,
> the performance impact MAY be negligible.
> - *OPTION 0* - The time to reconnect (start a new connection from
> scratch with a different username/password) was found to be faster than
> using `SET SESSION AUTHORIZATION`.
> - *OPTION 1* - My team is continuing to explore a distinct Impersonate
> message (Option-1). We are completing a prototype-quality implementation,
> and then benchmarking it. Given that Option-1 is asynchronous (Request and
> expect to succeed) and it can even be included within the same TCP packet
> as the SimpleQuery (at times), we expect the performance will be better
> than restarting a connection, and not impacted by links of higher latency.
>
> I will be recording benchmark results in the document:
> https://docs.google.com/document/d/1u6mVKEHfKtR80UrMLNYrp5D6cCSW1_arcTaZ9HcAKlw/edit#
> after completion of the OPTION-1 prototype and benchmarking of that
> prototype.
>
> Note: In order to accommodate something like OPTION-8, an Impersonation
> message might have a flag (valid for 1x SimpleQuery only, then
> automatically restore back to the last user).
>
> Regards,
>
> Todd
>
>
> On Fri, 7 Jan 2022 at 10:55, Todd Hubers <todd(dot)hubers(at)gmail(dot)com> wrote:
>
>> Hi Everyone,
>>
>> I have started working on this:
>>
>> - Benchmarking - increasingly more comprehensive benchmarking
>> - Prototyping - to simulate the change of users (toggling back and
>> forth)
>> - Draft Implementation - of OPTION-1 (New Protocol Message)
>> - (Then: Working with Odyssey and PgBouncer to add support (when the
>> GRANT role privilege is available))
>>
>> I hope to have a patch ready by the end of March.
>>
>> Regards,
>>
>> Todd
>>
>> On Wed, 24 Nov 2021 at 02:46, Todd Hubers <todd(dot)hubers(at)gmail(dot)com> wrote:
>>
>>>
>>> Hi Jacob and Daniel,
>>>
>>> Thanks for your feedback.
>>>
>>> >@Daniel - I think thats conflating session_user and current_user, SET
>>> ROLE is not a login event. This is by design and discussed in the
>>> documentation..
>>>
>>> Agreed, I am using those terms loosely. I have updated option 4 in the
>>> proposal document. I have crossed it out. Option 5 is more suitable "SET
>>> SESSION AUTHORIZATION" for further consideration.
>>>
>>> >@Daniel - but it's important to remember that we need to cover the
>>> functionality in terms of *tests* first, performance benchmarking is
>>> another concern.
>>>
>>> For implementation absolutely, but not for a basic feasibility
>>> prototype. A quick non-secure non-reliable prototype is probably an
>>> important first-step to confirming which options work best for the stated
>>> goals. Importantly, if the improvement is only 5% (whatever that might
>>> mean), then the project is probably not work starting. But I do expect that
>>> a benchmark will prove benefits that justify the resources to build the
>>> feature(s).
>>>
>>> >@Jacob - A more modern approach might be to attach the authentication
>>> to the packet itself (e.g. cryptographically, with a MAC), if the goal is
>>> to enable per-statement authentication anyway. In theory that turns the
>>> middleware into a message passer instead of a confusable deputy. But it
>>> requires more complicated setup between the client and server.
>>>
>>> I did consider this, but I ruled it out. I have now added it to the
>>> proposal document, and included two Issues. Please review and let me know
>>> whether I might be mistaken.
>>>
>>> >@Jacob - Having protocol-level tests for bytes on the wire would not
>>> only help proposals like this but also get coverage for a huge number of
>>> edge cases. Magnus has added src/test/protocol for the server, written in
>>> Perl, in his PROXY proposal. And I've added a protocol suite for both the
>>> client and server, written in Python/pytest, in my OAuth proof of concept.
>>> I think something is badly needed in this area.
>>>
>>> Thanks for highlighting this emerging work. I have noted this in the
>>> proposal in the Next Steps section.
>>>
>>> --Todd
>>>
>>> Note: Here is the proposal document link again -
>>> https://docs.google.com/document/d/1u6mVKEHfKtR80UrMLNYrp5D6cCSW1_arcTaZ9HcAKlw/edit#
>>>
>>> On Tue, 23 Nov 2021 at 12:12, Jacob Champion <pchampion(at)vmware(dot)com>
>>> wrote:
>>>
>>>> On Sat, 2021-11-20 at 16:16 -0500, Tom Lane wrote:
>>>> > One more point is that the proposed business about
>>>> >
>>>> > * ImpersonateDatabaseUser will either succeed silently (0-RTT), or
>>>> > fail. Upon failure, no further commands will be processed until
>>>> > ImpersonateDatabaseUser succeeds.
>>>> >
>>>> > seems to require adding a huge amount of complication on the server
>>>> side,
>>>> > and complication in the protocol spec itself, to save a rather minimal
>>>> > amount of complication in the middleware. Why can't we just say that
>>>> > a failed "impersonate" command leaves the session in the same state
>>>> > as before, and it's up to the pooler to do something about it? We are
>>>> > in any case trusting the pooler not to send commands from user A to
>>>> > a session logged in as user B.
>>>>
>>>> When combined with the 0-RTT goal, I think a silent ignore would just
>>>> invite more security problems. Todd is effectively proposing packet
>>>> pipelining, so the pipeline has to fail shut.
>>>>
>>>> A more modern approach might be to attach the authentication to the
>>>> packet itself (e.g. cryptographically, with a MAC), if the goal is to
>>>> enable per-statement authentication anyway. In theory that turns the
>>>> middleware into a message passer instead of a confusable deputy. But it
>>>> requires more complicated setup between the client and server.
>>>>
>>>> > PS: I wonder how we test such a feature meaningfully without
>>>> > incorporating a pooler right into the Postgres tree. I don't
>>>> > want to do that, for sure.
>>>>
>>>> Having protocol-level tests for bytes on the wire would not only help
>>>> proposals like this but also get coverage for a huge number of edge
>>>> cases. Magnus has added src/test/protocol for the server, written in
>>>> Perl, in his PROXY proposal. And I've added a protocol suite for both
>>>> the client and server, written in Python/pytest, in my OAuth proof of
>>>> concept. I think something is badly needed in this area.
>>>>
>>>> --Jacob
>>>>
>>>
>>>
>>> --
>>> --
>>> Todd Hubers
>>>
>>
>>
>> --
>> --
>> Todd Hubers
>>
>
>
> --
> --
> Todd Hubers
>

--
--
Todd Hubers