3 ms·
Regarding Row Security, yes you can use it with a web application and still use connection pooling. From web server, connect to db as one user then SET ROLE to
by elchief 11y ago
Regarding Row Security, yes you can use it with a web application and still use connection pooling.
From web server, connect to db as one user then SET ROLE to the database user. This gives you Column Security and easier auditing as well. See http://stackoverflow.com/questions/2998597/switch-role-after-connecting-to-database http://stackoverflow.com/questions/2998597/switch-role-after...
- jimktrains2 11y agoThe thing is that each application user now needs a corresponding DB user to use RLS. While this isn't a huge problem, it's different than how most (if not 99%?) of applications work.
- elchief 11y agoYes, but it's not harder to have many db users vs many app users. There's even an extension to sync pg users with ldap
- jimktrains2 11y agoI'm not saying it's _hard_, it's just _different_ and would be a challenging migration for established apps as I don't know of any framework's authentication system that works like that. Also, why would you sync pg users in ldap? pg can auth against ldap.
- jimktrains2 11y ago> Also, why would you sync pg users in ldap? pg can auth against ldap. I figured out why: To pull roles/groups into the database from ldap.
- anarazel 11y agoNo, RLS does not necessarily require separate database users. Using database users is one relatively obvious way to use the feature, but you can very well do something like 'SELECT myapp_set_current_user(...)' or something, and use a variable securely set therein for the row restrictions.
- jimktrains2 11y agoInteresting. I didn't think about doing that. 9.2 makes that much easier to do http://dba.stackexchange.com/questions/97095/set-session-custom-variable-to-store-user-id http://dba.stackexchange.com/questions/97095/set-session-cus...
- Jweb_Guru 11y agoYep. We do this where I work (the 9.4 equivalent using SECURITY BARRIER views) and it is extremely useful.
- nickpeterson 11y agoIt's probably unsuitable for public facing sites, but for line of business applications it could be a real win. I would much rather have enforcement down to the data level.
- ownagefool 11y agoIt's probably is sutible for public facing sites, just not globablly. Little bobby tables doesn't need the ability to dump your users or credit cards, even if he does need access to all the blog posts. I have to read up on the feature a bit more, but it sounds a potentially massive win if you build the support into an orm / framework.