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 <sfr...@snowman.net> wrote: > Greetings, > > * Siegfried Bilstein (sbilst...@gmail.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!
