Обсуждение: Thoughts on row-level security for webapps?
Hi all,
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?
Siggy Bilstein
On 12/31/18 8:57 AM, Siegfried Bilstein wrote: > Hi all, > > 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. So there is still user information being passed in, correct? > > 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? The ROLE will be determined by the user name used to make the connection. Is it possible to change the connection login depending on the website user privileges? > > -- > Siggy Bilstein > CTO ofAyuda Care <https://www.ayudacare.com> > Book some time <https://calendly.com/siggy-cto> with me! -- Adrian Klaver adrian.klaver@aklaver.com
On 31/12/18 6:57 μ.μ., Siegfried Bilstein wrote:
Hi all,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.
Why?
Is this a common usage pattern for SQL security? Any gotchas relying on RLS?--Siggy Bilstein
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Greetings, * Siegfried Bilstein (sbilstein@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
Вложения
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@snowman.net> wrote:
Greetings,
* Siegfried Bilstein (sbilstein@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