Fwd: pros and cons of two security models

On Wed, Oct 3, 2012 at 6:17 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Chris,
>
> * Chris Travers (chris(dot)travers(at)gmail(dot)com) 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..?
>

Well, that's the tradeoff I see. It can be handled using a bunch of
different means. One that I have suggested is two-factor auth, where you
require a client-side SSL cert with a specific issuing authority and a cn
of the username that comes in under basic auth. We don't support that as a
matter of course yet, but, the other option is to use kerberos.

I guess the what I am wondering is whether some of the pushback we get from
some developers is really a different aspect of the "databases should be
dumb information stores" mentality, which isn't really the way we are going
or if there really are issues here we haven't considered.

My general feeling is that centralizing the security in the database means
a narrower security perimeter in the areas that matter, and that this
mostly comes at the expense of easy multi-tenant hosting.

>
> 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.
>

Ok, that makes sense. I don't think it would be right for us, but it
certainly makes sense in the right time and place.

>
> 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.
>

Out of curiosity, since you are using krb5/gssapi, why do you go through
the set role? Why not just pass krb5 tickets around, since this represents
a re-usable auth method itself?

>
> > 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..
>
>
Yeah. Of course AD integration with anything on Linux is not as simple as
it looks, but it still isn't that painful once you get used to it.

I will keep that in mind regarding a possible alternative for those who
really need something different.

Best Wishes,
Chris Travers