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

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