On 01/11/2017 03:10 PM, Stephen Frost wrote:
* Tom Lane (t...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
