Skip site navigation (1) Skip section navigation (2)

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

From: Lars Kanis <kanis(at)comcard(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Magnus Hagander <magnus(at)hagander(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [PATCH] user mapping extension to pg_ident.conf
Date: 2009-06-30 09:07:03
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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 

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

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

Lars Kanis

In response to


pgsql-hackers by date

Next:From: Lars KanisDate: 2009-06-30 10:18:42
Subject: Re: [PATCH] user mapping extension to pg_ident.conf
Previous:From: Simon RiggsDate: 2009-06-30 08:02:42
Subject: Re: Query progress indication - an implementation

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group