Thread: 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.travers@gmail.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
Attachment
On Wed, Oct 3, 2012 at 6:17 AM, Stephen Frost <sfrost@snowman.net> wrote:
Chris,If I'm following along correctly, the issue is that the web server tier
* Chris Travers (chris.travers@gmail.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:
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.This is certainly the big win for us, but we've settled on
> 3) Centralized security logic which means consistent access enforced
> through a variety of clients.
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?
It seems like the SET ROLE option would be reasonably straight-forward
> 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.
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.travers@gmail.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
Attachment
On Wed, Oct 3, 2012 at 6:25 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Well, that's the tradeoff I see. It can be handled using a bunch ofIs it possible to proxy those client-side SSL credentials..? I've not
> 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.
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 fromIf they're asking about specific authentication mechanisms, I don't
> 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.
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.
I wouldn't compromise on having one central authority for security and
> 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.
access control, particularly for a product such as LedgerSMB. Just my
2c though, I'm sure others have differing opinions.When we're using krb5/gssapi, we *don't* use the set role approach, we
> 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?
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, it really isn't that hard once you know the correct
> 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.
'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