Re: [GENERAL] LYDB: Feasible to use PG roles instead ofapplication-level security? - Mailing list pgsql-general
From | Alban Hertroys |
---|---|
Subject | Re: [GENERAL] LYDB: Feasible to use PG roles instead ofapplication-level security? |
Date | |
Msg-id | 5155877D-016E-47E9-A6FA-97204D17165C@gmail.com 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 |
> On 30 Dec 2016, at 4:19, Guyren Howe <guyren@gmail.com> wrote: > > Further to my attempts to enlighten application developers about what they might better do in the database: > > https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.4jngp2rcb > > 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. > > 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. > > If I have a system with many thousands of users, is it practical to manage these users’ authentication and authorizationusing *just* Postgres? Postgres roles are global to the cluster, so you would end up with multiple thousands of roles if you have multiple databasesin your cluster with different users on each. Which roles each user is allowed to have becomes quite the nightmarefor the administrators, I suspect. For a web-application facing the internet, I'd say no, don't do that. You're dealing with far too many users to be maintainable. For an intranet database in a not-too-large company with a fixed set of users, it could be a good solution, especially ifthose roles can be linked to the company's LDAP server (assuming that's possible, I don't know). Multiple intranet applicationson that same database can use the same users and roles. Someone needs to do the administration though; with volumes (of users) like that and the database knowledge level of theaverage system administrator, a GUI seems preferable. IMHO, pgadmin provides too many features to be practical for someonelike that, you would probably prefer something that only does user administration. I don't know of anything that doesthat though (not a GUI user myself)... > 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? > > 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. With a few changes, that could work very well. First, create roles for the different types of users that you expect. In a company, that could be by division, distinguishingdivision-heads, interns, etc. Secondly, have a table with the users and their attributes like you describe. Include an attribute for their database rolethere. Only administrator users should have access to that table. Finally, create a stored procedure that looks up a user name in that table and sets the accompanying role. If a user is notfound, set the role to some default 'unprivileged' user. Make that procedure a SECURITY DEFINER with according permissions. That role stays active the entire session, so unless youclose the connection, create a new one or change the user's role, this procedure doesn't need calling again. > Is this practical? Has anyone here done it? What might the caveats be? It's a fairly common practice, the ML archives should contain plenty of examples. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
pgsql-general by date: