Thread: Postgresql 8.4 GSSAPI auth with fallback to password prompting?

Postgresql 8.4 GSSAPI auth with fallback to password prompting?

From
Tim Watts
Date:
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): +44 (0)1580 848360
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



Re: Postgresql 8.4 GSSAPI auth with fallback to password prompting?

From
Stephen Frost
Date:
Tim,

* Tim Watts (tim.j.watts@kcl.ac.uk) wrote:
> 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

Attachment

Re: Postgresql 8.4 GSSAPI auth with fallback to password prompting?

From
Tim Watts
Date:
On 24/03/13 18:47, Stephen Frost wrote:
> Tim,
>
> * Tim Watts (tim.j.watts@kcl.ac.uk) wrote:
>> 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): +44 (0)1580 848360
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



Re: Postgresql 8.4 GSSAPI auth with fallback to password prompting?

From
Stephen Frost
Date:
Tim,

* Tim Watts (tim.j.watts@kcl.ac.uk) wrote:
> 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).

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

> 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

Attachment

Re: Postgresql 8.4 GSSAPI auth with fallback to password prompting?

From
Tom Lane
Date:
Stephen Frost <sfrost@snowman.net> writes:
> * Tim Watts (tim.j.watts@kcl.ac.uk) wrote:
>> 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


Re: Postgresql 8.4 GSSAPI auth with fallback to password prompting?

From
Tim Watts
Date:
On 25/03/13 13:25, Stephen Frost wrote:
> Tim,
>
> * Tim Watts (tim.j.watts@kcl.ac.uk) wrote:
>> 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".

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

>> 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): +44 (0)1580 848360
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."



Re: Postgresql 8.4 GSSAPI auth with fallback to password prompting?

From
Tim Watts
Date:
On 25/03/13 14:31, Tom Lane wrote:
> Stephen Frost <sfrost@snowman.net> writes:
>> * Tim Watts (tim.j.watts@kcl.ac.uk) wrote:
>>> 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): +44 (0)1580 848360
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."



Re: Postgresql 8.4 GSSAPI auth with fallback to password prompting?

From
Stephen Frost
Date:
Tim,

* Tim Watts (tim.j.watts@kcl.ac.uk) wrote:
> 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

Attachment

Re: Postgresql 8.4 GSSAPI auth with fallback to password prompting?

From
Tim Watts
Date:
On 25/03/13 15:17, Stephen Frost wrote:
> Tim,
>
> * Tim Watts (tim.j.watts@kcl.ac.uk) wrote:
>> 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 :)

> 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): +44 (0)1580 848360
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."



Re: Postgresql 8.4 GSSAPI auth with fallback to password prompting?

From
Stephen Frost
Date:
* Tim Watts (tim.j.watts@kcl.ac.uk) wrote:
> 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. :)

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

> 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

Attachment