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

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