Re: Not clear how to switch role without permitting switch back

["Alexander M. Sauer-Budge" <ambudge@xxxxxxxxxxxx>]

On Jan 10, 2017, at 2:05 AM, Guyren Howe <guyren@xxxxxxxxx> wrote:

For my Love Your Database Project:

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/

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