Обсуждение: [GENERAL] Not clear how to switch role without permitting switch back
For my Love Your Database Project:
I’m trying to see how a typical web developer might use Postgres’ roles and row-level security to implement their authorization.
What I’m struggling with is that connection pooling seems to make straightforward use of the roles to enforce access impossible.
If I’m using a connection pool, then I’m not re-connecting to Postgres with the user for the current transaction. But then my only option is to use SET ROLE. But that is not much security at all, because the current user can just do SET ROLE back to the (presumably privileged) default, or to any other user’s role.
What am I missing here?
On 1/9/2017 11:05 PM, Guyren Howe wrote: > > I’m trying to see how a typical web developer might use Postgres’ > roles and row-level security to implement their authorization. too much impedance mismatch with the stateless nature of http -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] Not clear how to switch role without permitting switch back
От
"Alexander M. Sauer-Budge"
Дата:
On Jan 10, 2017, at 2:05 AM, Guyren Howe <guyren@gmail.com> wrote:For my Love Your Database Project:I’m trying to see how a typical web developer might use Postgres’ roles and row-level security to implement their authorization.What I’m struggling with is that connection pooling seems to make straightforward use of the roles to enforce access impossible.If I’m using a connection pool, then I’m not re-connecting to Postgres with the user for the current transaction. But then my only option is to use SET ROLE. But that is not much security at all, because the current user can just do SET ROLE back to the (presumably privileged) default, or to any other user’s role.What am I missing here?
Tomas at 2nd Quadrant wrote a nice article about doing that:
You can also look at how projects like PostgREST (http://postgrest.com/) and PostGaphQL (https://github.com/calebmer/postgraphql) tackle the problem (although I don’t recall at the moment if they are as careful about avoiding the possibility of an unprotected SET ROLE as Tomas is in the above article).
Best,
Alex
Re: [GENERAL] Not clear how to switch role without permitting switch back
От
"Alexander M. Sauer-Budge"
Дата:
On Jan 10, 2017, at 2:05 AM, Guyren Howe <guyren@gmail.com> wrote:For my Love Your Database Project:I’m trying to see how a typical web developer might use Postgres’ roles and row-level security to implement their authorization.What I’m struggling with is that connection pooling seems to make straightforward use of the roles to enforce access impossible.If I’m using a connection pool, then I’m not re-connecting to Postgres with the user for the current transaction. But then my only option is to use SET ROLE. But that is not much security at all, because the current user can just do SET ROLE back to the (presumably privileged) default, or to any other user’s role.What am I missing here?
Tomas at 2nd Quadrant wrote a nice article about doing that:
You can also look at how projects like PostgREST (http://postgrest.com/) and PostGaphQL (https://github.com/calebmer/postgraphql) tackle the problem (although I don’t recall at the moment if they are as careful about avoiding the possibility of an unprotected SET ROLE as Tomas is in the above article).
Best,
Alex
On Mon, 9 Jan 2017 23:05:47 -0800, Guyren Howe <guyren@gmail.com> wrote: >For my Love Your Database Project: > >https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.8g1ezwx6r <https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.8g1ezwx6r> > >Im trying to see how a typical web developer might use Postgres >roles and row-level security to implement their authorization. > >What Im struggling with is that connection pooling seems to make >straightforward use of the roles to enforce access impossible. > >If Im using a connection pool, then Im not re-connecting to >Postgres with the user for the current transaction. But then my >only option is to use SET ROLE. But that is not much security at >all, because the current user can just do SET ROLE back to the >(presumably privileged) default, or to any other users role. > >What am I missing here? That middleware can control what a user is permitted to do. YMMV, but to me "web application" means there is a server-side program sitting in front of the database and controlling access to it. I grudgingly will permit *compiled* clients direct connection to an Internet facing database, but I am dead set against allowing direct connection from any browser hosted code because - regardless of any "shrouding" that might be done - browser code is completely insecure, accessible to anyone who can right-click on the page. George