[PATCH] user mapping extension to pg_ident.conf

Hi all,

this patch adds the possibility to map the login-rolename to a different
rolename actually used for permissions.

What is it used for?

I'm working with smartcard based TLS-authentication to connect to the PG
server. Authentication is done with the keys and certificates from the card
within the TLS handshake. Certificate-CN and login-username have to be the
same or have to match by the "pg_ident.conf". The role actually used for
permissions is always the login-username. This patch allowes, to change the
actually permissions to a role based on the certificate-CN. It is realised by
an additional column in "pg_ident.conf".

When using ODBC, you have to setup a fixed username which is used for login.
Different permissions depending on the CN of the certificate on the current
smartcard could be achieved by the following line:

# MAPNAME SYSTEM-USERNAME PG-USERNAME EFFECTIVE-USERNAME
ssl-user /(.*) dummy \1

The extension could be similar used for kerberos authentication, too.

Bytheway I refactored the pg_ident-code a little bit, to avoid duplicated code
and to allow substitution of more than one match (\2, \3 etc).

Questions (I'm quite new to the PG-sources and used to write Ruby code):
- Is this something useful - or is there a much easier way?
- Are there any implementation shortcomings?

regards
Lars Kanis

Lars Kanis <kanis(at)comcard(dot)de> writes:
> this patch adds the possibility to map the login-rolename to a different
> rolename actually used for permissions.

This seems like an ugly addition with a very narrow use case. Can't
you accomplish what you want with the existing usermap facility?

regards, tom lane

Am Montag, 29. Juni 2009 16:26:56 schrieben Sie:
> Lars Kanis <kanis(at)comcard(dot)de> writes:
> > this patch adds the possibility to map the login-rolename to a different
> > rolename actually used for permissions.
>
> This seems like an ugly addition with a very narrow use case. Can't
> you accomplish what you want with the existing usermap facility?

You're right, my description is a bit incomplete.

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.

It works fine so far, as long as I want to work with the sigle role given by
the fixed username. I could map any cn-contents to this one user by writing:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
ssl-user /.* fixed_user

The db internal role is always that given by the application. But I need to
work with the role of the certificate-cn of the current smartcard, the
application doesn't know about. Because the username is stored within the
applications own binary data file I'm not able to change it according to the
pluged in card.

I think the same problem occurs with kerberos authentication. You can't get
the role based on your kerberos ticket, when the username is not set
likewise.

So it seemed to me quite useful, to not just set which external name matches
which login-username, but also to set which userrole is actually used for
granted privilegs. This is done by an additional column with the same
characteristic as column PG-USERNAME.

Another way could be to add an parameter to the hba line which tells the
server to not care about the login username, but to only use the external
(CN) name. But this wouldn't have the flexibility of regexps like in
pg_ident.conf.

Hope this clarifies a bit.

regards
Lars Kanis

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. Have you even considered the
potential for security problems arising from this?

regards, tom lane

Lars,

* Lars Kanis (kanis(at)comcard(dot)de) wrote:
> 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.

To be honest, I think the problem as described above makes it a no-go.
What you're asking for is essentially "I want the server to ignore the
username passed in" which just doesn't make sense. It's unfortunate
that this Windows application using ODBC doesn't allow for the username
to be changed, but that's not something PG is going to solve for you. I
would recommend you work on fixing the application.

> It works fine so far, as long as I want to work with the sigle role given by
> the fixed username. I could map any cn-contents to this one user by writing:
> # MAPNAME SYSTEM-USERNAME PG-USERNAME
> ssl-user /.* fixed_user

What you really want is:
ssl-user user1_cn user1
ssl-user user2_cn user2
ssl-user user3_cn user3

or so..

And then have your application accept and use a username provided to it
when connecting to ODBC. Having your application pass some hard-coded
username to PG all the time just isn't going to work.

> I think the same problem occurs with kerberos authentication. You can't get
> the role based on your kerberos ticket, when the username is not set
> likewise.

With Kerberos you do pass in the username (or possibly the username is
"figured out" from the principal, but it's the same to the server side)
you actually want to be in the database as part of the connection and then
the validation is done against the Kerberos credentials. Perhaps your
Windows application also wouldn't work with Kerberos, but in the general
case people are logging in with their username, not with some fixed
username.

Thanks,

Stephen

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

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

My fault. The lower lines should be:
gssapi-user /^user_a(at)domain\(dot)com$ super-role super_user_a
gssapi-user /^user_c(at)domain\(dot)com$ super-role super_user_c

regards
Lars Kanis

Sorry for jumping late into this discussion, but I've been out sailing
for a couple of weeks and stayed away from email :-)

On Mon, Jun 29, 2009 at 21:01, Stephen Frost<sfrost(at)snowman(dot)net> wrote:

> * Lars Kanis (kanis(at)comcard(dot)de) wrote:
>> 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.
>
> To be honest, I think the problem as described above makes it a no-go.
> What you're asking for is essentially "I want the server to ignore the
> username passed in" which just doesn't make sense.  It's unfortunate
> that this Windows application using ODBC doesn't allow for the username
> to be changed, but that's not something PG is going to solve for you.  I
> would recommend you work on fixing the application.

I think it's a reasonable expectation for there to be a way to pick up
the username from an external authentication system, such as Kerberos,
SSPI or certificate. Other databases that support them do - at least
MSSQL.

That said, if there is a username specified it should not be ignored.
But if there is none specified, it should work. This works "reasonably
well" today, in that we pick the username up from the environment. But
I can see cases where it would be a lot more useful to have it instead
pick up the username from the authentication system, since they may
differ.

But in no case should this override what the user put in to the system
- but it could change the default.

>> It works fine so far, as long as I want to work with the sigle role given by
>> the fixed username. I could map any cn-contents to this one user by writing:
>> # MAPNAME     SYSTEM-USERNAME    PG-USERNAME
>> ssl-user      /.*                fixed_user
>
> What you really want is:
> ssl-user      user1_cn     user1
> ssl-user      user2_cn     user2
> ssl-user      user3_cn     user3
>
> or so..

Well, you'd write that too with a regexp map with a back reference.
That's the original reason I implemented regexps for pg_ident :-)

> And then have your application accept and use a username provided to it
> when connecting to ODBC.  Having your application pass some hard-coded
> username to PG all the time just isn't going to work.

I think the issue is "provided to it". There should be no need for the
user to know his username since he's already logged in.

I agree that the hard-coded-username method is broken.

--
Magnus Hagander
Self: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> That said, if there is a username specified it should not be ignored.
> But if there is none specified, it should work. This works "reasonably
> well" today, in that we pick the username up from the environment. But
> I can see cases where it would be a lot more useful to have it instead
> pick up the username from the authentication system, since they may
> differ.

Are you not describing a behavior that you yourself removed in 8.4,
ie the libpq code that looked aside at Kerberos for a username?

regards, tom lane

On Tue, Jul 21, 2009 at 15:58, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> That said, if there is a username specified it should not be ignored.
>> But if there is none specified, it should work. This works "reasonably
>> well" today, in that we pick the username up from the environment. But
>> I can see cases where it would be a lot more useful to have it instead
>> pick up the username from the authentication system, since they may
>> differ.
>
> Are you not describing a behavior that you yourself removed in 8.4,
> ie the libpq code that looked aside at Kerberos for a username?

Yes, partially I am :-)

But it was not documented, and done in a fairly hackish way. If we
want it, it should work the same for *all* external authentication
methods (where it would be possible).

Doing it on the client presents a certain challenge when it comes to
certificates for example - or really in any case where you need to map
the username to something else. It would be quite convenient to have
that ability controlled from the server side. We'd have to have some
way to communicate down that the username specified was the default
one and not a user-specified one (or we're back at overriding), but if
the actual mapping could be controlled server-side it would be a lot
more convenient.

--
Magnus Hagander
Self: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Tue, Jul 21, 2009 at 15:58, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Are you not describing a behavior that you yourself removed in 8.4,
>> ie the libpq code that looked aside at Kerberos for a username?

> Yes, partially I am :-)

> But it was not documented, and done in a fairly hackish way. If we
> want it, it should work the same for *all* external authentication
> methods (where it would be possible).

Well, the problem with it of course was that it happened even when the
selected auth method was not Kerberos.

> Doing it on the client presents a certain challenge

Yup, you would need a protocol change that would allow the client to
change its mind about what the username was after it got the auth
challenge. And then what effects does that have on username-sensitive
pg_hba.conf decisions? We go back and change our minds about the
challenge type, perhaps? The whole thing seems like a nonstarter to me.

regards, tom lane

Am Dienstag, 21. Juli 2009 16:01:01 schrieben Sie:
> Doing it on the client presents a certain challenge when it comes to
> certificates for example - or really in any case where you need to map
> the username to something else. It would be quite convenient to have
> that ability controlled from the server side. We'd have to have some
> way to communicate down that the username specified was the default
> one and not a user-specified one (or we're back at overriding), but if
> the actual mapping could be controlled server-side it would be a lot
> more convenient.

I thought about doing it on the client side too, but server side mapping
seemed to me more flexible. In fact one could do a client side mapping
(without knowledge of the auth method), by greping the username of the
external system out of the error text from the server and doing a second auth
with it. Just I don't like this ugly hack.

There was another mail, where I described the use of the mapping patch:
http://archives.postgresql.org/pgsql-hackers/2009-06/msg01496.php
Please have a look on it. One can give different mappings for combinations of
internal and external username. This way you could easy use different roles
within the database with differnent applications, although the external auth
system gives the same username. The "dummy"-user of the first mail was not
the best example.

I'm not the expert with PGs internals. So if you have a better way to get an
usermapping based on the external auth, I could do a bit of research in this
area, because I need something like this.

regards
Lars Kanis

On Tue, Jul 21, 2009 at 16:06, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> On Tue, Jul 21, 2009 at 15:58, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Are you not describing a behavior that you yourself removed in 8.4,
>>> ie the libpq code that looked aside at Kerberos for a username?
>
>> Yes, partially I am :-)
>
>> But it was not documented, and done in a fairly hackish way. If we
>> want it, it should work the same for *all* external authentication
>> methods (where it would be possible).
>
> Well, the problem with it of course was that it happened even when the
> selected auth method was not Kerberos.

That was the core problem, yes. IIRC there were some other minor
issues with it as well.

>> Doing it on the client presents a certain challenge
>
> Yup, you would need a protocol change that would allow the client to
> change its mind about what the username was after it got the auth
> challenge.  And then what effects does that have on username-sensitive
> pg_hba.conf decisions?  We go back and change our minds about the
> challenge type, perhaps?  The whole thing seems like a nonstarter to me.

"challenge type"? Not sure I understand what you are referring to here.

--
Magnus Hagander
Self: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Yup, you would need a protocol change that would allow the client to
>> change its mind about what the username was after it got the auth
>> challenge. And then what effects does that have on username-sensitive
>> pg_hba.conf decisions? We go back and change our minds about the
>> challenge type, perhaps? The whole thing seems like a nonstarter to me.

> "challenge type"? Not sure I understand what you are referring to here.

The point is that pg_hba.conf allows the selection of auth method to
depend on username. What happens if, after being told auth method is
(say) Kerberos, the client comes back and wants to use a different
username that should have resulted in a different auth method according
to pg_hba.conf? It's not hard to construct scenarios where that would
be seen as a security breach.

regards, tom lane

On Wed, Jul 22, 2009 at 14:53, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>>> Yup, you would need a protocol change that would allow the client to
>>> change its mind about what the username was after it got the auth
>>> challenge.  And then what effects does that have on username-sensitive
>>> pg_hba.conf decisions?  We go back and change our minds about the
>>> challenge type, perhaps?  The whole thing seems like a nonstarter to me.
>
>> "challenge type"? Not sure I understand what you are referring to here.
>
> The point is that pg_hba.conf allows the selection of auth method to
> depend on username.  What happens if, after being told auth method is
> (say) Kerberos, the client comes back and wants to use a different
> username that should have resulted in a different auth method according
> to pg_hba.conf?  It's not hard to construct scenarios where that would
> be seen as a security breach.

Oh. Now I get it. Good point. Forgot about the username being part of
that. Yeah, that basicalliy says it has to be a client-side
implementation only.

--
Magnus Hagander
Self: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Wed, Jul 22, 2009 at 8:57 AM, Magnus Hagander<magnus(at)hagander(dot)net> wrote:
> On Wed, Jul 22, 2009 at 14:53, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>>>> Yup, you would need a protocol change that would allow the client to
>>>> change its mind about what the username was after it got the auth
>>>> challenge.  And then what effects does that have on username-sensitive
>>>> pg_hba.conf decisions?  We go back and change our minds about the
>>>> challenge type, perhaps?  The whole thing seems like a nonstarter to me.
>>
>>> "challenge type"? Not sure I understand what you are referring to here.
>>
>> The point is that pg_hba.conf allows the selection of auth method to
>> depend on username.  What happens if, after being told auth method is
>> (say) Kerberos, the client comes back and wants to use a different
>> username that should have resulted in a different auth method according
>> to pg_hba.conf?  It's not hard to construct scenarios where that would
>> be seen as a security breach.
>
> Oh. Now I get it. Good point. Forgot about the username being part of
> that. Yeah, that basicalliy says it has to be a client-side
> implementation only.

I believe this means that this patch is rejected, so I am marking it
as such on commitfest.postgresql.org. However, it sounds like there
would be room for a client-side patch offering functionality in this
area, if Lars or someone else wanted to develop such a thing for a
future CommitFest.

Hopefully I've understood the situation correctly...

...Robert