Fwd: pros and cons of two security models - Mailing list pgsql-general

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