BUG #9337: SSPI/GSSAPI with mismatched user names

brian@fluggo.com writes:

The short version is that Postgres requires two user names when using
GSSAPI/SSPI: one from the startup packet, and one from the Kerberos ticket,
and if these don't match exactly, the login fails. It's generally impossible
to determine the correct user name to send in the startup packet.

I think Postgres should either not require or ignore the user name in the
startup packet for these two login types.

If we did that, wouldn't it mean that anyone with a working Kerberos login
could log in as *any* database user? Even a superuser?

I'm prepared to grant that we might need to change the behavior somehow,
but it seems like not requiring any connection at all between the Kerberos
principal name and the database user name would be entirely unsafe.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, Feb 24, 2014 at 12:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

If we did that, wouldn't it mean that anyone with a working Kerberos login
could log in as *any* database user? Even a superuser?

I'm prepared to grant that we might need to change the behavior somehow,
but it seems like not requiring any connection at all between the Kerberos
principal name and the database user name would be entirely unsafe.

I don't think I'm suggesting what you're thinking. I'm saying that if
the Postgres user name *has* to match the Kerberos principal name
anyways, why not just take the Kerberos principal name and save us the
trouble of sending a Postgres user name?

Right now, I'm seeing log entries like this:

2014-02-24 11:30:40 CST LOG: provided user name (Brian) and
authenticated user name (BCrowell@REALM.COM) do not match

But the Kerberos ticket is perfectly valid, and matches a Postgres
user. In this case, the program attempting to log in is incapable of
determining the correct Postgres user name to send (see Npgsql bug for
the dirty details), so why not just accept the Kerberos principal
name?

--Brian

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, Feb 24, 2014 at 12:50 PM, Brian Crowell <brian@fluggo.com> wrote:

2014-02-24 11:30:40 CST LOG: provided user name (Brian) and
authenticated user name (BCrowell@REALM.COM) do not match

But the Kerberos ticket is perfectly valid, and matches a Postgres
user. In this case, the program attempting to log in is incapable of
determining the correct Postgres user name to send (see Npgsql bug for
the dirty details), so why not just accept the Kerberos principal
name?

Or in other words, I'm trying to log in as the Postgres user
"BCrowell@REALM.COM" (which is in the Kerberos ticket), and not as
"Brian" (which is in the startup packet, because Npgsql doesn't know
what else to do).

--Brian

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

* Brian Crowell (brian@fluggo.com) wrote:

Right now, I'm seeing log entries like this:

2014-02-24 11:30:40 CST LOG: provided user name (Brian) and
authenticated user name (BCrowell@REALM.COM) do not match

But the Kerberos ticket is perfectly valid, and matches a Postgres
user. In this case, the program attempting to log in is incapable of
determining the correct Postgres user name to send (see Npgsql bug for
the dirty details), so why not just accept the Kerberos principal
name?

This is what the mapping logic in pg_ident was written to address...

Thanks,

Stephen

On Mon, Feb 24, 2014 at 7:56 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Brian Crowell (brian@fluggo.com) wrote:

Right now, I'm seeing log entries like this:

2014-02-24 11:30:40 CST LOG: provided user name (Brian) and
authenticated user name (BCrowell@REALM.COM) do not match

But the Kerberos ticket is perfectly valid, and matches a Postgres
user. In this case, the program attempting to log in is incapable of
determining the correct Postgres user name to send (see Npgsql bug for
the dirty details), so why not just accept the Kerberos principal
name?

This is what the mapping logic in pg_ident was written to address...

There is also a parameter called include_realm, specifically for Kerberos,
which will remove the @REALM.COM part. But I believe it does that by
default.

Specifically see
http://www.postgresql.org/docs/9.3/static/auth-methods.html#GSSAPI-AUTH,
which deals with both those.

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

* Brian Crowell (brian@fluggo.com) wrote:

On Mon, Feb 24, 2014 at 12:50 PM, Brian Crowell <brian@fluggo.com> wrote:

2014-02-24 11:30:40 CST LOG: provided user name (Brian) and
authenticated user name (BCrowell@REALM.COM) do not match

But the Kerberos ticket is perfectly valid, and matches a Postgres
user. In this case, the program attempting to log in is incapable of
determining the correct Postgres user name to send (see Npgsql bug for
the dirty details), so why not just accept the Kerberos principal
name?

Or in other words, I'm trying to log in as the Postgres user
"BCrowell@REALM.COM" (which is in the Kerberos ticket), and not as
"Brian" (which is in the startup packet, because Npgsql doesn't know
what else to do).

To PG, you're trying to log in as PG user 'Brian' and there's no mapping
which allows the kerb princ "BCrowell@REALM.COM" to log in as that user.
Also, is the PG user really "BCrowell@REALM.COM", or is it actually
'bcrowell', in which case you need a mapping for that (unless you tell
PG to just strip the realm off, but I generally recommend against such
since you can end up with cross-realm issues if you ever define a trust
relationship to another realm with different users who might have the
same princs as your local users).

Thanks,

Stephen

Brian Crowell <brian@fluggo.com> writes:

Right now, I'm seeing log entries like this:

2014-02-24 11:30:40 CST LOG: provided user name (Brian) and
authenticated user name (BCrowell@REALM.COM) do not match

But the Kerberos ticket is perfectly valid, and matches a Postgres
user. In this case, the program attempting to log in is incapable of
determining the correct Postgres user name to send (see Npgsql bug for
the dirty details), so why not just accept the Kerberos principal
name?

Why exactly doesn't Npgsql know what the Kerberos principal name is?
How did it obtain the ticket without knowing that?

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, Feb 24, 2014 at 1:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Why exactly doesn't Npgsql know what the Kerberos principal name is?
How did it obtain the ticket without knowing that?

Windows obtained the ticket, not Npgsql. It's attached to my logon
token without Npgsql's help. If I'm on the domain, I _might_ have
access to that information through a call to LsaGetLogonSessionData or
similar. If I'm not on the domain, I definitely don't.

Npgsql is just asking Windows to do GSSAPI auth on its behalf, so it
never really touches that info.

--Brian

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Brian Crowell <brian@fluggo.com> writes:

On Mon, Feb 24, 2014 at 1:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Why exactly doesn't Npgsql know what the Kerberos principal name is?
How did it obtain the ticket without knowing that?

Windows obtained the ticket, not Npgsql. It's attached to my logon
token without Npgsql's help. If I'm on the domain, I _might_ have
access to that information through a call to LsaGetLogonSessionData or
similar. If I'm not on the domain, I definitely don't.

Hm, so how did Windows know what ticket to get? *Somewhere* there's
got to be a mapping from "Brian" to "BCrowell". It might not be
readily accessible to you though :-(

As noted upthread, we can't really do what you're suggesting without
a fundamental rearchitecting of our authentication scheme, which aside
from being a lot of work would probably break at least as many use-cases
as it improves. To take one example, it's not unreasonable at all that
people might want database superusers to have to use a different auth
method from ordinary users --- so just taking the username out of the
auth method selection process doesn't sound workable.

It's unfortunate that this doesn't fit well with the architecture you
find yourself dealing with on the client side, but I doubt we can do
anything to help you.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

* Brian Crowell (brian@fluggo.com) wrote:

On Mon, Feb 24, 2014 at 1:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Why exactly doesn't Npgsql know what the Kerberos principal name is?
How did it obtain the ticket without knowing that?

Windows obtained the ticket, not Npgsql. It's attached to my logon
token without Npgsql's help. If I'm on the domain, I _might_ have
access to that information through a call to LsaGetLogonSessionData or
similar. If I'm not on the domain, I definitely don't.

Npgsql is just asking Windows to do GSSAPI auth on its behalf, so it
never really touches that info.

I seem to recall that, at one point, we actually we doing this
automatically in libpq- that is, grabbing the Kerberos princ and then
using it to auth. That was too constrained though, as we wanted to be
able to have users with names other than their princs, but perhaps we
should have just made it optional instead, perhaps using an environment
variable. Would that work for you and the general users? I'm on the
fence about making that the default again since it's possible we would
break things for existing users...

Thanks,

Stephen

On Mon, Feb 24, 2014 at 1:01 PM, Stephen Frost <sfrost@snowman.net> wrote:

To PG, you're trying to log in as PG user 'Brian' and there's no mapping
which allows the kerb princ "BCrowell@REALM.COM" to log in as that user.

Yes, that's the problem. There will not be a mapping.

Also, is the PG user really "BCrowell@REALM.COM", or is it actually
'bcrowell', in which case you need a mapping for that (unless you tell
PG to just strip the realm off, but I generally recommend against such
since you can end up with cross-realm issues if you ever define a trust
relationship to another realm with different users who might have the
same princs as your local users).

The PG user is "BCrowell@REALM.COM". include_realm is on because we
have a forest, and I don't want any crossed wires between domains.

Really, this is all what I want to happen, and everything about it
works. The only problem is that PG wants a user name that, in a few
cases, I just don't have.

I'm starting to see that this appears very differently to Postgres
people. I'm coming here from SQL Server, where in our company we've
now got it set up that each user's SQL Server login _is_ their domain
login. Not just named the same--SQL Server understands the domain, and
each user is coming in as their Windows identity.

However, to Postgres, Kerberos is not about identities at all, it's
just a fancy password mechanism. Really you just want to know a
Postgres user, and it's never been a problem for users to specify
that. I guess what I'm asking is if Kerberos can be used to specify my
Postgres username as well.

--Brian

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, Feb 24, 2014 at 1:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Hm, so how did Windows know what ticket to get? *Somewhere* there's
got to be a mapping from "Brian" to "BCrowell". It might not be
readily accessible to you though :-(

Yes. These are tied together on the user's login token, but I can't
get to the tied information. It can even be specified before my
program starts (via runas /netonly).

As noted upthread, we can't really do what you're suggesting without
a fundamental rearchitecting of our authentication scheme, which aside
from being a lot of work would probably break at least as many use-cases
as it improves. To take one example, it's not unreasonable at all that
people might want database superusers to have to use a different auth
method from ordinary users --- so just taking the username out of the
auth method selection process doesn't sound workable.

Well rats. I can see that would require a change at the protocol
level. You'd need to accept a ticket or password from me without
knowing beforehand if that matches the auth method specified for that
user.

It's unfortunate that this doesn't fit well with the architecture you
find yourself dealing with on the client side, but I doubt we can do
anything to help you.

Luckily, I know we can architect a workaround for our organization,
but I was trying to get it as clean as I could for future Npgsql
users.

Thanks for taking the time to talk it over with me anyhow :P

--Brian

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, Feb 24, 2014 at 1:35 PM, Brian Crowell <brian@fluggo.com> wrote:

Well rats. I can see that would require a change at the protocol
level. You'd need to accept a ticket or password from me without
knowing beforehand if that matches the auth method specified for that
user.

Er, well, one last comment, and I promise I'll leave. You could accept
these in the startup packet.

--Brian

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

* Brian Crowell (brian@fluggo.com) wrote:

Also, is the PG user really "BCrowell@REALM.COM", or is it actually
'bcrowell', in which case you need a mapping for that (unless you tell
PG to just strip the realm off, but I generally recommend against such
since you can end up with cross-realm issues if you ever define a trust
relationship to another realm with different users who might have the
same princs as your local users).

The PG user is "BCrowell@REALM.COM". include_realm is on because we
have a forest, and I don't want any crossed wires between domains.

Ah, makes sense. Again, you could have different usernames in PG if you
wanted to keep things simpler, by using pg_ident.conf, but if useing the
full princ works for you then that's certainly fine too.

Really, this is all what I want to happen, and everything about it
works. The only problem is that PG wants a user name that, in a few
cases, I just don't have.

It really should be possible for you to get it. I'm in flight at the
moment and so the interwebs are a bit lagged or I'd go figure out what
the right GSSAPI calls are, though I can understand if you'd rather just
be able to ask libpq to handle that or maybe pass back what the princ
is, so you don't have to deal with the Kerberos calls directly.

I'm starting to see that this appears very differently to Postgres
people. I'm coming here from SQL Server, where in our company we've
now got it set up that each user's SQL Server login _is_ their domain
login. Not just named the same--SQL Server understands the domain, and
each user is coming in as their Windows identity.

I'm familiar with SQL Server and how it works there and in a lot of ways
it's very similar to what happens in PG, and it has similar options for
doing mapping too, as I recall, and if you want to be able to have such
a mapping then you have to have both the log-me-in-as username and the
Kerberos princ.

However, to Postgres, Kerberos is not about identities at all, it's
just a fancy password mechanism. Really you just want to know a
Postgres user, and it's never been a problem for users to specify
that. I guess what I'm asking is if Kerberos can be used to specify my
Postgres username as well.

This is overstating it, imv. The exact same issue happens if, for
example, you want to ssh to a server- you have to provide the Unix
username that you want to log into the system as, along with the
Kerberos ticket. Those can then be different too, by using a .k5login
file. If you'd like to complain about something in this regard, it
would be that we don't have any way to link PG users in directly with
LDAP in the way that AD does, where the group membership is doing
through LDAP. That would certainly be accurate but would be quite a bit
of work to allow ad we don't get many requests for such capability.

Thanks,

Stephen

Brian Crowell <brian@fluggo.com> writes:

I'm starting to see that this appears very differently to Postgres
people. I'm coming here from SQL Server, where in our company we've
now got it set up that each user's SQL Server login _is_ their domain
login. Not just named the same--SQL Server understands the domain, and
each user is coming in as their Windows identity.

However, to Postgres, Kerberos is not about identities at all, it's
just a fancy password mechanism. Really you just want to know a
Postgres user, and it's never been a problem for users to specify
that. I guess what I'm asking is if Kerberos can be used to specify my
Postgres username as well.

Hm. That's an interesting way of putting it, and you're right --- we
don't expect Kerberos to be a source of identity. Which is not the
world view Kerberos users have.

I wonder whether there would be any value in an option for SSPI (and
maybe other auth methods) to say "after authentication is complete,
substitute the authenticated principal name for the database user
name" (possibly after realm-stripping, case-folding, etc).

The usage pattern would be that you'd pass say "use_kerberos_username"
as the user name in the startup packet, and that would select a
pg_hba.conf entry along the lines of

host use_kerberos_username all samenet sspi use_principal=without_realm

I'm not sure if that'd solve Npgsql's problem or not, though. As sketched
here, the existence of such an auth entry, as well as the spelling of the
magic username, would be at the whim of the Postgres installation's DBA.
That might not be stable enough for you; I'm betting you're wishing you
could hard-wire a dummy name. On the other hand, you weren't complaining
about the fact that you can't use Kerberos at all without a suitable
pg_hba entry; so maybe some additional constraints on its form would be OK
for your use-case.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, Feb 24, 2014 at 1:47 PM, Stephen Frost <sfrost@snowman.net> wrote:

The PG user is "BCrowell@REALM.COM". include_realm is on because we
have a forest, and I don't want any crossed wires between domains.

Ah, makes sense. Again, you could have different usernames in PG if you
wanted to keep things simpler, by using pg_ident.conf, but if useing the
full princ works for you then that's certainly fine too.

We'd be mapping several dozen users. I'd rather keep things simple :P

It really should be possible for you to get it. I'm in flight at the
moment and so the interwebs are a bit lagged or I'd go figure out what
the right GSSAPI calls are, though I can understand if you'd rather just
be able to ask libpq to handle that or maybe pass back what the princ
is, so you don't have to deal with the Kerberos calls directly.

If it is possible, I'd be happy to find out. Note that I'm trying to
solve the LOGON32_LOGON_NEW_CREDENTIALS case, which ADO.NET handles
just fine. This discussion does not give me much hope:

https://groups.google.com/forum/#!topic/microsoft.public.platformsdk.security/5L7ugO0Fc90

I'm familiar with SQL Server and how it works there and in a lot of ways
it's very similar to what happens in PG, and it has similar options for
doing mapping too, as I recall, and if you want to be able to have such
a mapping then you have to have both the log-me-in-as username and the
Kerberos princ.

I'm not sure what you mean. Our connection strings look like this:

server=productiondb.realm.com;Integrated
Security=SSPI;database=OURDB;Connect Timeout=500;Application Name=w00t

Most of our users do not even have SQL Server logins. They get in by
group permissions. They're mapped to application data by their SID.

This is overstating it, imv. The exact same issue happens if, for
example, you want to ssh to a server- you have to provide the Unix
username that you want to log into the system as, along with the
Kerberos ticket. Those can then be different too, by using a .k5login
file. If you'd like to complain about something in this regard, it
would be that we don't have any way to link PG users in directly with
LDAP in the way that AD does, where the group membership is doing
through LDAP. That would certainly be accurate but would be quite a bit
of work to allow ad we don't get many requests for such capability.

Sorry, I didn't mean for it to sound like a complaint.

But yes, I am looking for the Postgres account to be "tied" to the
Kerberos account. It doesn't have to be through LDAP; the supplied
Kerberos user name is enough. I realize I will still have to set up a
mapping for each user, but that's small potatoes.

--Brian

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, Feb 24, 2014 at 1:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wonder whether there would be any value in an option for SSPI (and
maybe other auth methods) to say "after authentication is complete,
substitute the authenticated principal name for the database user
name" (possibly after realm-stripping, case-folding, etc).

I humbly resubmit my ticket-in-the-startup-packet suggestion, which
I'd hope would be easier, especially since any program not supplying
it would fall back to the standard challenge auth mechanism.

Like:

1. client -> server startup packet + GSSAPI="here's my ticket"
2. server -> client AuthenticationGSSContinue
3. client -> server password packet
4. server -> client AuthenticationOK

But then I don't know what I'm talking about really :P

(goes to read the protocol specs)

--Brian

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, Feb 24, 2014 at 2:09 PM, Brian Crowell <brian@fluggo.com> wrote:

I humbly resubmit my ticket-in-the-startup-packet suggestion, which
I'd hope would be easier, especially since any program not supplying
it would fall back to the standard challenge auth mechanism.

Tell you what.

Our company is not to the point of needing anything like this, I was
just helping out the Npgsql people. But if we should get to the point
that we'd want it, would you accept patches that implemented this sort
of shortcut authentication?

--Brian

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Brian Crowell <brian@fluggo.com> writes:

On Mon, Feb 24, 2014 at 2:09 PM, Brian Crowell <brian@fluggo.com> wrote:

I humbly resubmit my ticket-in-the-startup-packet suggestion, which
I'd hope would be easier, especially since any program not supplying
it would fall back to the standard challenge auth mechanism.

Tell you what.

Our company is not to the point of needing anything like this, I was
just helping out the Npgsql people. But if we should get to the point
that we'd want it, would you accept patches that implemented this sort
of shortcut authentication?

As a new feature it would need discussion, and I'm not real sure that
it's sensible even in theory. In our model, until you've identified the
relevant pg_hba.conf entry you don't know which Kerberos server to talk to
to validate the ticket.

If it's possible to extract a principal name from a ticket without
contacting the server, then that objection fails ... but if that were
the case then I suppose you'd not be here looking for a solution.

I suppose you could propose some additional configuration settings
that would be looked at in advance of the pg_hba.conf lookup to allow
extraction of a user name from a supplied ticket. However, that would
greatly expand the potential for DOS attacks based on launching bogus
startup packets at a postmaster (since the computation needed before
rejecting a packet is greatly increased if we have to contact some
Kerberos server). Not sure if that objection is fatal or not.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs