Chris, * Chris Travers (chris.travers@xxxxxxxxx) wrote: > This has a few significant drawbacks. As far as the web application is > concerned, the types of supported authentication are limited to those > which are re-usable, which basically means BASIC and KRB5. This maps to a > much larger number on the web server to db server tier, but often the web > server hop is the most exposed one. This is a significant problem. On the > other hand it buys us: If I'm following along correctly, the issue is that the web server tier can support many more authentication methods, but LedgerSMB doesn't support them because it needs an authentication method which can pass credentials through to the database..? The way that I've addressed this issue in the past (which has allowed us to support client-side SSL-based authentication, RADIUS-based auth before it was in PG, etc), is to have the web server connect to the database with a set of fixed credentials, preferably using encryption and a strong auth method (eg: client-side cert), and then allow the role which the web server logs in as to 'SET ROLE' to all the user accounts in the system. By setting up the web server role as 'noinherit' (or having a role between the web server role and the users which is 'noinherit), this means that the initial web server connection has no rights to anything in the database until that 'SET ROLE' is done, and after that, it only has access to what that role can see. There are down sides to this, of course. It allows an individual who can gain sufficient access on the web server to be able to log in as the web server role and then 'SET ROLE' to any user. When the credentials have to be passed through the web server, an attacker would have to be on the system and monitoring when a user logs in, to be able to steal their credentials, and even they they'd only get the credentials of the user who logged in during that time. It also increases the risk that an SQL injection or similar would allow a 'RESET ROLE; SET ROLE' to be issued by the web server which could change the rights that the web server is logged in as. I've wondered about a way to address that (eg: a GUC that essentially disallows a RESET ROLE), but that make it impossible to use pg_bouncer or similar connection pool in front of PG. > 3) Centralized security logic which means consistent access enforced > through a variety of clients. This is certainly the big win for us, but we've settled on GSSAPI/KRB5 based authentication for our internal systems, which allows for SSO and one set of user credentials across the environment. For external systems, we typically require non-admins to go through the web layer and use the SET ROLE method that I mentioned above and then use client-side SSL certificates or RADIUS-based hardware-tokens. > Which does everyone else prefer? Why? I am asking because this choice has > spawned some controversy around LedgerSMB from time to time, though not as > much as being Pg-only at least looking at public and private discussions of > the software I have seen. It seems like the SET ROLE option would be reasonably straight-forward to implement and would give people the flexibility they're asking for.. I would still try to encourage them to use Kerberos-based authentication which is integrated in with their enterprise systems (eg: Active Directory), which would provide SSO. It's amazing how often I run into environments where people are running AD but they have a completely independent set of user credentials for accessing PG.. Thanks, Stephen
Attachment:
signature.asc
Description: Digital signature
