Search Postgresql Archives

Re: Are new connection/security features in order, given connection pooling?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 01/11/2017 03:10 PM, Stephen Frost wrote:
* Tom Lane (tgl@xxxxxxxxxxxxx) 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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux