> On Jan 10, 2017, at 2:05 AM, Guyren Howe <guy...@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> > > 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: http://blog.2ndquadrant.com/application-users-vs-row-level-security/ <http://blog.2ndquadrant.com/application-users-vs-row-level-security/> You can also look at how projects like PostgREST (http://postgrest.com/ <http://postgrest.com/>) and PostGaphQL (https://github.com/calebmer/postgraphql <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
