Re: Thoughts on row-level security for webapps?

Thank you for the responses.

I did some research and now understand that in my query I'll need to do
something like 'SET LOCAL user_id=5; SET ROLE app_user' and define a policy
that references a 'user_id' variable. I think I have enough info now to get
started.

On Thu, Jan 3, 2019 at 12:49 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Siegfried Bilstein (sbilstein(at)gmail(dot)com) wrote:
> > I'm evaluating using a tool called Postgraphile that generates a GraphSQL
> > server from a postgres setup. The recommended way of handling security is
> > to implement RLS within postgres and simply have the webserver take a
> > cookie or similar and define which user is querying data.
> >
> > I've normally built webapps like this: pull out user id from a session
> > cookie -> the API endpoint verifies the user and whether or not it has
> > access to the given data -> app code mutates the data.
> >
> > With Postgraphile the request specifies the mutation and the server
> > processes the request and relies on Postgres to determine if the user has
> > correct access rights.
> >
> > It seems like I would need to create a ROLE for every single member that
> > signs up for my website which I'm a little concerned about. Is this a
> > common usage pattern for SQL security? Any gotchas relying on RLS?
>
> You don't have to create a role for every member, though depending on
> your expectation you might want to. You could just set a custom GUC
> which is used in the policy, but you then have to trust the web
> application code to always do that correctly and to always properly
> validate the client (without bugs, of course).
>
> RLS has been around for a while now and it works really rather well in
> most cases. There are some corner cases where you're doing some kind of
> filtering that might be able to use an index but the functions aren't
> leakproof and therefore can't be used, causing a performance regression,
> but that's not too hard to test for and only an issue if the policy
> itself isn't very selective.
>
> Thanks!
>
> Stephen
>

--
Siggy Bilstein
CTO of Ayuda Care <https://www.ayudacare.com>
Book some time <https://calendly.com/siggy-cto> with me!