pros and cons of two security models

Hi all;

I wanted to get opinions of folks who do a lot of this as well.

LedgerSMB uses database user accounts and role grants to restrict access of
the front-end application. This means:

1) The database does not trust the application. The application has no
access to the db in the absence of user-supplied credentials.

2) Permissions are granted on functions and tables, and the application is
aware of granted roles, and so can make informed decisions about what
options to give the user.

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:

1) access to every method of authentication Pg supports for non-web apps,
and

2) access to every method of password auth that Pg supports for web apps

3) Centralized security logic which means consistent access enforced
through a variety of clients.

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.

Best Wishes,
Chris Travers

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

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

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

Chris,

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

Is it possible to proxy those client-side SSL credentials..? I've not
seen that, personally, but I've heard of people doing it.. Would be
very interested in that possibility.

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

If they're asking about specific authentication mechanisms, I don't
think they're feeling like the database is a dumb data store... I'll
admit that I could be wrong there though.

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

I wouldn't compromise on having one central authority for security and
access control, particularly for a product such as LedgerSMB. Just my
2c though, I'm sure others have differing opinions.

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

When we're using krb5/gssapi, we *don't* use the set role approach, we
just proxy the credentials, as you'd expect. We use the set role
approach when we *can't* use krb5/gssapi (we're supporting users outside
of our AD infrastructure- clients, subcontractors, etc). In those
cases, we still want a strong auth system, so we go to a solution such
as client-side certificates or hardware tokens, but those aren't
proxyable credentials (that I've seen anyway.. maybe there's a way to
do it now), and so, in those cases, we use the SET ROLE approach.

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

Yeah, it really isn't that hard once you know the correct
'incarnations', which are actually in a few briefings/trainings I've
given and are online in various places...

Thanks!

Stephen

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

> Chris,
>
> * Chris Travers (chris(dot)travers(at)gmail(dot)com) wrote:
> > 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.
>
> Is it possible to proxy those client-side SSL credentials..? I've not
> seen that, personally, but I've heard of people doing it.. Would be
> very interested in that possibility.
>
> I wonder how you'd validate the certificate without being able to ask for
something to be encrypted via a private key.

> > 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.
>
> If they're asking about specific authentication mechanisms, I don't
> think they're feeling like the database is a dumb data store... I'll
> admit that I could be wrong there though.
>

Well, that hasn't been the request. The request has been for one role for
the application to use in the db. I was going through the tradeoffs that I
had seen. The arguments I have heard are:

1) If you are giving access to your db, then people can do things that
aren't possible in the application itself (however here a lot of the
business logic is contained in the database, and so I don't think this
applies), and
2) A simple assertion that that doing things this way is "a security flaw"
and that the abstraction of user accounts in the web application is a
"necessary abstraction." The concern appears to be some sort of
architectural concern and I don't really understand it, hence one reason
for asking around a bit.

My thinking is that security needs to follow business logic and it is a
mistake to have them on separate layers.

>
> > 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.
>
> I wouldn't compromise on having one central authority for security and
> access control, particularly for a product such as LedgerSMB. Just my
> 2c though, I'm sure others have differing opinions.
>
> > 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?
>
> When we're using krb5/gssapi, we *don't* use the set role approach, we
> just proxy the credentials, as you'd expect. We use the set role
> approach when we *can't* use krb5/gssapi (we're supporting users outside
> of our AD infrastructure- clients, subcontractors, etc). In those
> cases, we still want a strong auth system, so we go to a solution such
> as client-side certificates or hardware tokens, but those aren't
> proxyable credentials (that I've seen anyway.. maybe there's a way to
> do it now), and so, in those cases, we use the SET ROLE approach.
>

They had better not be proxyiable... However one thing I have considered
is this:

1) Password auth using HTTP basic and an SSL connection, and
2) The web server checks the SSL client cert to ensure that the cn on the
certificate matches the supplied username. If they don't match you just
ask for new credentials. In this regard, basically a username/password
combo is unlocked by a certificate and you don't get to use a password
unless you have a certificate for the same username. The password itself
is proxyable, and so you get essentially two perimeters:
a) The web app then can enforce two factor auth, and then
b) The web app can then use the username/password as proxyable
credentials.

>
> > 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.
>
> Yeah, it really isn't that hard once you know the correct
> 'incarnations', which are actually in a few briefings/trainings I've
> given and are online in various places...
>

I wrote a paper on doing AD integration for OpenSSL on Linux. I was
rather... amused.... by the differences in in how service accounts are
handled in AD (at least in 2007) vs MIT Kerberos.

Best Wishes,
Chris Travers

On 10/03/2012 07:06 PM, Chris Travers wrote:
> Hi all;
>
> I wanted to get opinions of folks who do a lot of this as well.
>
> LedgerSMB uses database user accounts and role grants to restrict access
> of the front-end application.

Also posted to
http://dba.stackexchange.com/questions/25357/choice-of-authentication-approach-for-financial-app-on-postgresql

When you cross-post, please link.

--
Craig Ringer