Re: [PATCH] user mapping extension to pg_ident.conf

Am Montag, 29. Juni 2009 18:12:13 schrieben Sie:
> Lars Kanis <kanis(at)comcard(dot)de> writes:
> > The problem I have, is that I want to use an ordinary windows
> > application, which connects to an arbitrary ODBC data source. This
> > application stores a fixed username und password for the connection
> > within it's own binary data file. It doesn't know anything about
> > TLS-connection nor smartcard based authentication. All this is done in
> > the libpg.dll.
>
> This seems to boil down to "I'm willing to damage PG's authentication
> mechanisms to an unlimited extent to work around one broken proprietary
> windows app". I'm not excited about it.

Oh no! You totally misunderstood me. So please give me another try to explain
the addition: It's NOT about one stupid or broken (it isn't) application that
has to be corrected. It's NOT about a specific problem with ODBC connections.
It's a much more general thing, regardless which application/programming
language/connection-library you use. I wouldn't post a quick an dirty hack to
work around one broken proprietary windows app!

The situation with any external single sign-on authentication method (GSSAPI,
SSPI, Kerberos, Certificate authentication) is that you always have to
provide a username. It is differentiated between the SYSTEM-USERNAME and the
PG-USERNAME. The SYSTEM-USERNAME is the name the user is identified to by the
external authentication. It is usually done with cryptographic validation of
some tickets or certificates. So this value is the important one in terms of
security. The PG-USERNAME is what the application tells the server the user
wants to act as, in the database. It has no value at all in terms of
security - it's just a wish. It is verified through the mapping of
pg_ident.conf whether the wish can be granted with the given SYSTEM-USERNAME.

The problem arises when you want to work with 1:1 mapping of SYSTEM-USERNAME
to PG-USERNAME. You don't want to let the user type in the PG-USERNAME he
wants to act as (I have never seen an kerberos-enabled application to do so).
So the application has to take into account which authentication method you
actually use, to extract the exact username from credentials/certificate.
That means you have to open a second channel to your SSO-System to provide
the correct username to the PG-server. And it means you have to do some sort
of duplicated code in the application, which is already realised within
libpq. Currently you can not say: "Dear libpq, please identify the current
user and authenticate to the PG-server (by whatever authentication methods
you're allowed to by pg_hba.conf), and log in to the own account of the
user."

My addition allows to abstract the application from the used authentication
method. It adds an level of indirection between PG-USERNAME (given by the
application) and the EFFECTIVE-USERNAME (for actual db-role) based on the
SYSTEM-USERNAME (given by the SSO-system). And it adds it in a way that fits
nicely into the already existend mapping-file pg_ident.conf.

Let's show the following example-table in pg_ident.conf:
# MAPNAME SYSTEM-USERNAME PG-USERNAME EFFECTIVE-USERNAME
gssapi-user /^(.*)@domain\.com$ simple-role \1
gssapi-user /^user_a(at)domain\(dot)com$ super-role user_a
gssapi-user /^user_c(at)domain\(dot)com$ super-role user_c

The first line says: Any user with an account on the PG-server and valid
GSSAPI-credentials can log in to it's own PG-account. The application doesn't
need to care about which user is actually working with it. It simply needs to
say to server to use the users 'simple-role'. You can write the application
completely independent to the authentication method to use.

For whatever security relevant action in this or another application, you can
log in to the 'super-role' of the user. But only user_a and user_c are able
to do so. Likewise the application don't need to care about the actually used
SSO-method or system.

Also to mention: The addition is fully backward compatible.

> Have you even considered the
> potential for security problems arising from this?

Yes, of course. The primary goal of the addition is to get more security. Some
questions I considered:

Can I go around permission checks which are carried out by PG?

That's the most important question, I think. As I stated above, the
PG-USERNAME always has an informative nature. It's just a wish with no value
in terms of security. So it is safe to change the name, as long as all usual
permission checks are done on the changed one. In fact they are done. The
identification and validation of users credentials are completed, so we can
trust the SYSTEM-USERNAME. Then the pg_ident.conf comes in action and
determines the EFFECTIVE-USERNAME. Any further permission checks are done
afterwards. So the EFFECTIVE-USERNAME is checked against login-permission and
so on.

Does it get more security to give every application user it's own account
within the database?

Yes. Currently it's easy to setup an accumulative account for more than one
user. For this case the application don't need to care about the
authentication already with the current state. But you can not set fine
granted access rights. Often equal important, the internal and external
logging of the database doesn't show the exact usernames. My addition makes
it more easy to setup fine granted permissions, so it can give more security.
If the application wants to do some client side logging, too, with the exact
username, it can simply "SELECT user;".

Are there any security implementation flaws in the patch?

I don't think so. All the string operations are done on palloc'ed memory, so
there should be not buffer over/underrun. But it's on you to get an eye on
it.

Is the place suitable where you configure the usermapping?

It's in the same files of which are central parts in the authentication
system. So, in any way the admin has to care exactly about what
authentication methods and mappings he allowes.

Well, I don't like to write more text in my broken English than code I
produce :). But I think it's worth to do so...

regards
Lars Kanis