Postgresql 8.4 GSSAPI auth with fallback to password prompting?

[Tim Watts]

Hi,

Pretty sure this has a yes or no answer (and google+postgres docs is
suggesting "no", but I thought it worth asking the experts )...



Is it possible to specify GSSAPI auth (with MIT kerberos as the backend)
but get Postgresql to fallback to prompting for a password if a kerberos
ticket cannot be supplied by the client - eg because the client cannot
do GSSAPI or because the client is not part of the kerberos realm?

A bit like how OpenSSH server can try multiple auth methods
transparantly until one works,

eg GSSAPI->PubKey->Password-interactive->FAIL



Snippet from my pg_hba.conf:

#1# host all +role_users 0/0 gss
#2# host all +role_users 0/0 pam
host all +role_apps 0/0 md5
host all all 0/0 reject

#1# and #2# both work independently when uncommented. "role_users" is
used as a grouping for real user accounts vs application/script accounts
which are in "role_apps" and will always use local Postgresql
authentication.

It would be really nice if the gss method could fallback to asking for a
password or if it were possible to try gss then pam.

Maybe it is but I missed something?


Any answers, even a definitive negative, would be most welcome :)

Cheers!

Tim
--
Tim Watts Tel (VOIP): [phone]
Systems Manager Digital Humanities, King's College London

Systems Messages and Notifications: https://systemsblog.cch.kcl.ac.uk/
Personal Blog: http://squiddy.blog.dionic.net/

http://www.sensorly.com/ Crowd mapping of 2G/3G/4G mobile signal coverage

[Stephen Frost]

Tim,

Post by Tim Watts
Is it possible to specify GSSAPI auth (with MIT kerberos as the
backend) but get Postgresql to fallback to prompting for a password
if a kerberos ticket cannot be supplied by the client - eg because
the client cannot do GSSAPI or because the client is not part of the
kerberos realm?

You're right- it's a 'no'. It would also really degrade the security
which you get with Kerberos as you'd undoubtably end up with clients
storing those passwords and using them routinely instead of using
Kerberos.

Thanks,

Stephen

[Tim Watts]

Post by Stephen Frost
Tim,

Post by Tim Watts
Is it possible to specify GSSAPI auth (with MIT kerberos as the
backend) but get Postgresql to fallback to prompting for a password
if a kerberos ticket cannot be supplied by the client - eg because
the client cannot do GSSAPI or because the client is not part of the
kerberos realm?

You're right- it's a 'no'. It would also really degrade the security
which you get with Kerberos as you'd undoubtably end up with clients
storing those passwords and using them routinely instead of using
Kerberos.
Thanks,
Stephen

Thank you Stephen, for confirming that.

I would have to respectfully take another point of view: that that
particular judgement is probably better placed with the sysadmin rather
than a blanket decision by the devs.

Reason: Whilst the argument is solid in an ideal world (all clients are
part of the kerberos realm), in reality it means that I cannot gain
partial security improvements and I have to leave it running with PAM
auth which ensures that passwords are chucked around 100% of the time.

My solution regarding scripts is that those *MUST* have a separate user
account that is a member of "role_apps" and uses Postgresql local
authentication. Those are expect to have passwords in config files, so
the domain of attack is limited of the password leaks (one database is
at risk typically).

I definitely do not want user account passwords in config files.

But it would be nice to be able to use kerberos tickets *where
available* and fallback to password-interactive login where not. On
about about 50% of the connections (those where a dev has ssh-ed into a
server that is kerberized) they would not need to retype passwords, so
the temptation to stuff them into .pgpass files would be much reduced :)

Cheers :)

Tim
--
Tim Watts Tel (VOIP): [phone]
Systems Manager Digital Humanities, King's College London

Systems Messages and Notifications: https://systemsblog.cch.kcl.ac.uk/
Personal Blog: http://squiddy.blog.dionic.net/

http://www.sensorly.com/ Crowd mapping of 2G/3G/4G mobile signal coverage

[Stephen Frost]

Tim,

Post by Tim Watts
I would have to respectfully take another point of view: that that
particular judgement is probably better placed with the sysadmin
rather than a blanket decision by the devs.

It's not a blanket decision by any means- the current situation is that
such an option doesn't exist. It's not "it exists, but we disabled it
because we felt like it."

Were someone to write the code to support such an option, it's entirely
possible it'd get committed (though likely with strong caveats about its
use in the documentation).

Post by Tim Watts
Reason: Whilst the argument is solid in an ideal world (all clients
are part of the kerberos realm), in reality it means that I cannot
gain partial security improvements and I have to leave it running
with PAM auth which ensures that passwords are chucked around 100%
of the time.

The pg_hba.conf allows you to migrate users or sets of users at a time.
Having a fall-back mechanism if Kerberos doesn't work is a different
thing. My experience has been that all clients (or at least, all in a
given IP range or for a set of users) *are* part of the Kerberos realm
because they're coming from Active Directory or another entrenched
Kerberos installation. That's specifically because that's how
Kerberos is intended to work and how it provides a strong
authentication mechanism.

Post by Tim Watts
But it would be nice to be able to use kerberos tickets *where
available* and fallback to password-interactive login where not.

And I continue to contend that this is a very bad idea.

Thanks,

Stephen

[Tom Lane]

Post by Stephen Frost

Post by Tim Watts
I would have to respectfully take another point of view: that that
particular judgement is probably better placed with the sysadmin
rather than a blanket decision by the devs.

It's not a blanket decision by any means- the current situation is that
such an option doesn't exist. It's not "it exists, but we disabled it
because we felt like it."
Were someone to write the code to support such an option, it's entirely
possible it'd get committed (though likely with strong caveats about its
use in the documentation).

I'm not sure it would. Allowing a fallback would amount to a protocol
change, meaning that old clients might fail in strange ways. You'd
need a lot stronger case than has been made here to justify dealing
with that.

regards, tom lane

[Tim Watts]

Post by Tom Lane

Post by Stephen Frost

Post by Tim Watts
I would have to respectfully take another point of view: that that
particular judgement is probably better placed with the sysadmin
rather than a blanket decision by the devs.

It's not a blanket decision by any means- the current situation is that
such an option doesn't exist. It's not "it exists, but we disabled it
because we felt like it."
Were someone to write the code to support such an option, it's entirely
possible it'd get committed (though likely with strong caveats about its
use in the documentation).

I'm not sure it would. Allowing a fallback would amount to a protocol
change, meaning that old clients might fail in strange ways. You'd
need a lot stronger case than has been made here to justify dealing
with that.

Just had a look at a non SSL psql connection with wireshark:

The username is offered. Then the server comes back with:

"Type: Authentication request"
"Authentication type: Plaintext password (3)"

So clearly it's not as simple as the client offering what it feels like.
And whilst I assume it might be possible for the server to have a new
code for

"Authentication type: GSSAPI with Password-Interactive-Fallback"

that's not going to be implicitly backwardly compatible.

Tricky I agree...

I presume the protocol does not allow the server to send a succession of
"Type: Authentication request" packets with different Authentication
types until it deems that one is acceptable?

BTW - I am not seriously proposing this - just for a bit of idea banter
and better understanding by me. If you've all got better things to do,
ignore me :-o

Cheers,

Tim
--
Tim Watts Tel (VOIP): [phone]
Systems Manager Digital Humanities, King's College London

Systems Messages and Notifications: https://systemsblog.cch.kcl.ac.uk/
Personal Blog: http://squiddy.blog.dionic.net/

"A fanatic is one who can't change his mind and won't change the subject."

[Stephen Frost]

Tim,

Post by Tim Watts
I presume the protocol does not allow the server to send a succession of
"Type: Authentication request" packets with different Authentication
types until it deems that one is acceptable?

Even if it did, existing clients would very likely be confused by it..

To be honest, I don't have a solution in mind for how to make this
happen, I was really just pointing out that there's a difference between
"we won't do that because we don't trust the sysadmin" and "that's not
an option due to how the system works today". Perhaps one option would
be to look at the Negotiate protocol which mod_auth_kerb and friends use
and perhaps have that as an explicitly new auth mechanism. A server set
up to provide that would, of course, have to consider if its users
supported it or not but that's true already- you can have situation
already though, a given client might not support gssapi, for example.

Thanks,

Stephen

[Tim Watts]

Post by Stephen Frost
Tim,

Post by Tim Watts
I presume the protocol does not allow the server to send a succession of
"Type: Authentication request" packets with different Authentication
types until it deems that one is acceptable?

Even if it did, existing clients would very likely be confused by it..
To be honest, I don't have a solution in mind for how to make this
happen, I was really just pointing out that there's a difference between
"we won't do that because we don't trust the sysadmin" and "that's not
an option due to how the system works today".

No no - fully understood :)

I appreciate the candid and reasoned arguments :)

I wish I could help - but I more of a sysamdin and less of a developer.

But it is *very* helpful to know that something *is not possible* and
*is likely to not be possible for a long time, if ever*. That allows me
as a humble user of the software to plan deployment :)

Post by Stephen Frost
Perhaps one option would
be to look at the Negotiate protocol which mod_auth_kerb and friends use
and perhaps have that as an explicitly new auth mechanism. A server set
up to provide that would, of course, have to consider if its users
supported it or not but that's true already- you can have situation
already though, a given client might not support gssapi, for example.

A "negotiate" option would be very cool. I will expect nothing (on the
basis it's free software, I have no rights ;-> ).

Save to say I think Postgresql is very cool already and has been for the
last 12 years I've been using it...


All the best,

Tim
--
Tim Watts Tel (VOIP): [phone]
Systems Manager Digital Humanities, King's College London

Systems Messages and Notifications: https://systemsblog.cch.kcl.ac.uk/
Personal Blog: http://squiddy.blog.dionic.net/

"She got her looks from her father. He's a plastic surgeon."

[Stephen Frost]

Post by Tim Watts
I wish I could help - but I more of a sysamdin and less of a developer.

So was I, until I ran into this annoying issue where a table could only
have one owner.. Then I added support for roles. :)

Post by Tim Watts
A "negotiate" option would be very cool. I will expect nothing (on
the basis it's free software, I have no rights ;-> ).

I'm open to possibly working on adding support for Negotiate (which is
actually a specific protocol..), if there's really interest in it. To
be honest, we've not heard this request very much and so it hasn't
exactly been on the radar.

Post by Tim Watts
Save to say I think Postgresql is very cool already and has been for
the last 12 years I've been using it...

Thanks!

Stephen

[Tim Watts]

Post by Stephen Frost
Tim,

Post by Tim Watts
I would have to respectfully take another point of view: that that
particular judgement is probably better placed with the sysadmin
rather than a blanket decision by the devs.

It's not a blanket decision by any means- the current situation is that
such an option doesn't exist. It's not "it exists, but we disabled it
because we felt like it."
Were someone to write the code to support such an option, it's entirely
possible it'd get committed (though likely with strong caveats about its
use in the documentation).

That's totally fair... Not sure if I could. I hacked an option into
Samba from a cold start once. On an equal footing, OpenLDAP's source
code totally defeated me ;-> I might have a look to see if it looks
"trivial" or "hard".

Post by Stephen Frost

Post by Tim Watts
Reason: Whilst the argument is solid in an ideal world (all clients
are part of the kerberos realm), in reality it means that I cannot
gain partial security improvements and I have to leave it running
with PAM auth which ensures that passwords are chucked around 100%
of the time.

The pg_hba.conf allows you to migrate users or sets of users at a time.
Having a fall-back mechanism if Kerberos doesn't work is a different
thing. My experience has been that all clients (or at least, all in a
given IP range or for a set of users) *are* part of the Kerberos realm
because they're coming from Active Directory or another entrenched
Kerberos installation. That's specifically because that's how
Kerberos is intended to work and how it provides a strong
authentication mechanism.

I think that laptops[1] and "BYOD" (Bring your own device, eg *pads) are
going to make that scenario less common.

[1] OK - it is perfectly possible to have a managed laptop. But it's
harder than a managed desktop so I've not seen it outside of very large
corporations with draconian policies on using their and only their devices.

Post by Stephen Frost

Post by Tim Watts
But it would be nice to be able to use kerberos tickets *where
available* and fallback to password-interactive login where not.

And I continue to contend that this is a very bad idea.

But less bad than not using kerberos for anything...

Cheers

Tim
--
Tim Watts Tel (VOIP): [phone]
Systems Manager Digital Humanities, King's College London

Systems Messages and Notifications: https://systemsblog.cch.kcl.ac.uk/
Personal Blog: http://squiddy.blog.dionic.net/

"She got her looks from her father. He's a plastic surgeon."