Re: [GENERAL] Are new connection/security features in order, givenconnection pooling?

On 01/11/2017 03:10 PM, Stephen Frost wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
 >
 > ...
>
>> If you end up having to dedicate each connection to a particular
>> unprivileged userID, then you can just open the connection as that
>> user to start with; a magic one-way privilege switch doesn't really
>> help.
>
> Well, to that I'd say "it depends." There are certainly scenarios
> where you have multiple userIDs and a connection pooler like
> pgbouncer which handles the different connections to the database and
> it's a lot better than making new connections because new connections
> are so painful and slow to create. This doesn't work great if you
> have thousands of concurrently connected independent users, of
> course.
>
>> We've discussed this problem repeatedly (you might want to search
>> the archives a bit) and never found a solution that was both fully
>> secure and did much of anything for connection-pooling scenarios.
>

Not sure which discussions you had in mind, but I found these two that
seem relevant:

[RFC: Non-user-resettable SET SESSION AUTHORISATION]

https://www.postgresql.org/message-id/flat/CAMsr%2BYHUiukYYxtvc1UahF4yM5Jc1bZAN%2Byt86WXsSVm69XXGg%40mail.gmail.com

[A mechanism securing web applications in DBMS]
https://www.postgresql.org/message-id/CA%2B0EDdCNwJvvb3aHVT4A8ywSwO40JeHj8_CYUx2SBb9%3DR6xHew%40mail.gmail.com

 >
> I don't agree that this is unsolvable, but it would require things
> like protocol-level changes which no one has had the gumption to work
> through and propose.
>

Perhaps it's a mistake to make this work with roles, at least for the
RLS use case. I'd argue roles are kinda orthogonal to the privilege
system we have, and the fact that RLS policies may use current_user does
not necessarily mean the solution needs to be based on roles.

Not only that roles were designed long before RLS, but having to create
a role for each user is quite limiting, and who says role name is the
only aspect useful for policies?

Which is why I think a protected vault-like thingy is a more promising
approach. This is why Oracle based the VPD (Virtual Private Database,
essentially what we call RLS) on 'application contexts', and  set by
'trusted' procedure usually called in a LOGON trigger. That of course
does not work with the connection pooling, but perhaps making it
possible to re-initialize the context would be easier than protecting
SET ROLE.

Admittedly, the solution described in the blog post is not perfect, but
while some protocol-level support would be nice I don't think that's a
requirement as long as the application knows how to initialize the
context, and we reset it on RESET ALL.

 >
> In short, I agree with Guyren, there are features needed here that
> we don't have and it would be a great deal better if we did.
>

Yeah.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services