Re: [GENERAL] LYDB: Feasible to use PG roles instead ofapplication-level security? - Mailing list pgsql-general
From | Stephen Frost |
---|---|
Subject | Re: [GENERAL] LYDB: Feasible to use PG roles instead ofapplication-level security? |
Date | |
Msg-id | 20161230152344.GW18360@tamriel.snowman.net Whole thread Raw |
In response to | [GENERAL] LYDB: Feasible to use PG roles instead of application-level security? (Guyren Howe <guyren@gmail.com>) |
Responses |
Re: [GENERAL] LYDB: Feasible to use PG roles instead ofapplication-level security?
|
List | pgsql-general |
Greetings, * Guyren Howe (guyren@gmail.com) wrote: > it occurs to me to wonder whether it is practical to use PG’s own roles and security model in lieu of using an application-levelone. The short answer is yes. > It seems that the role system in PG is sufficient for most general purposes. One could presumably also have a table withrole names and associated metainformation (email address etc) as needed. Yup. That can get a bit awkward if you have multiple databases inside of a single cluster, as you would have to pick which database to put that metainformation in, but that isn't a very big issue. > If I have a system with many thousands of users, is it practical to manage these users’ authentication and authorizationusing *just* Postgres? For this, it really depends on if the PG authorization model matches the requirements you have. The PG auth model, particularly with RLS, is extremely flexible but you would really need to evaluate what the exact requirements are and how you would handle that with the PG auth model. Of course, if there are just a few exceptions or complicated cases that can't be satisfied directly with PG today, you could use security definer functions. One area that isn't fully addressed with the PG auth model today is partial access to a certain column. Consider a table where you want users to have access to all of the rows and all of the columns *except* for column X for rows where ID is > 1000. The PG auth model today can be used to say "you can't access column X" or to say "you can't access rows where ID > 1000" but you can't combine those, yet. I'm hopeful that we'll get there as there are definitely use-cases for that kind of access control, but it's unlikely to happen for PG10. > It occurs to me that some client frameworks might have issues with their connection pools if those connections keep switchingusers, assuming they even can, but let’s set that aside for now. Or perhaps every connection could immediately doa SET USER before executing its connection? Again, yes, connection poolers can be an issue, but it's possible to use the role system and do a 'set role X' after having connected as some user that has very little access. The issue here is controlling that role change- there's no direct way in PG today to require a password to be provided when doing the role change, which is unfortunate. One approach to solving that with RLS is to use a security definer function to change a record in a table that is then used in all RLS policies. It's a bit complicated and doesn't involve doing 'set role' though, so there are some trade-offs there. If you really want connection pooling and independent users in PG's role system then you'll end up having to have the app code do the authentication (or maybe auth to PG as the user and, if successful, reconnect as the regular user and set role... that's pretty awkward though) and then connect and do the 'set role'. One big question here, however, is if you're going to have thousands of *concurrently connected* users. Thousands of users shouldn't be too much of an issue, but if they're all connected using PG's main auth system then you'll have thousands of backend processes running. That'll end up causing some amount of overhead even if they're mostly idle. If your application can handle connecting/disconnecting pretty easily and you have a relativly short timeout (though, ideally, not too short) then perhaps your number of concurrent connections won't be too bad. > This seems an attractive proposition from a security standpoint: if I use row-level security pervasively, I can have asecurity system that’s nestled nice and close to the data and presumably tricky to work around from a hacker given directaccess only to the client application. If that's the threat model you want to address then you'll have to work out the concurrent connections question. One thing which can help is to use a common user for 'read-only/public-access (or at least low-value)' queries from the app, if there are such. > Is this practical? Has anyone here done it? What might the caveats be? Yes, yes, see above. Thanks! Stephen
Attachment
pgsql-general by date: