Folks,

Jeremy handed me an interesting feature proposal at last night's SFPUG 
meeting.

PG authentication methods ought to have drop-downs to other authentication 
methods, in the same manner as SSH and PAM.

The idea would be this, if you had the following in your pg_hba.conf:

somedb	jeremy	23.165.22.198	255.255.255.255	kerberos
somedb	jeremy	23.165.22.198	255.255.255.255	md5

Then, when jeremy tries to connect to somedb from 23.165.22.198, the system 
would first try kerberos authentication, and if that fails offer an md5 
password login.   Only when the system ran out of applicable lines in 
pg_hba.conf would the user be rejected.

Any reason why this is a bad idea?   It would improve the lives of a lot of 
kerberos and SSL users who have to deal with flaky authentication issues.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Thu, 18 Mar 2004 13:39:07 -0800
Josh Berkus <josh@agliodbs.com>
Authentication drop-down?
On Thu, 18 Mar 2004, Josh Berkus wrote:

> Jeremy handed me an interesting feature proposal at last night's SFPUG 
> meeting.
> 
> PG authentication methods ought to have drop-downs to other authentication 
> methods, in the same manner as SSH and PAM.
> 
> The idea would be this, if you had the following in your pg_hba.conf:
> 
> somedb	jeremy	23.165.22.198	255.255.255.255	kerberos
> somedb	jeremy	23.165.22.198	255.255.255.255	md5
> 
> Then, when jeremy tries to connect to somedb from 23.165.22.198, the system 
> would first try kerberos authentication, and if that fails offer an md5 
> password login.   Only when the system ran out of applicable lines in 
> pg_hba.conf would the user be rejected.

The case I ran into this morning where such an optional behavior would've
been handy is when I want to allow the "postgres" OS user to connect as Pg
user "postgres" without a password via ident checking, but allow anyone to
connect as Pg user "postgres" with a password, e.g.:

local   all     postgres                  ident  sameuser
local   all     all                       md5
host    all     all     0.0.0.0  0.0.0.0  md5

What that makes easy is cron-driven vacuumdb or other maintenance calls
(where I can't give a password), or letting the root user su to postgres
and connect without needing to know the password, while still allowing
others to connect with a password.

Is there some other way to do what I'm looking for here without the 
authentication method fallthrough Josh proposes?

Jon

Thu, 18 Mar 2004 22:58:46 +0000 (UTC)
Jon Jensen <jon@endpoint.com>
Re: Authentication drop-down?
On Thu, Mar 18, 2004 at 22:58:46 +0000,
  Jon Jensen <jon@endpoint.com> wrote:
> 
> Is there some other way to do what I'm looking for here without the 
> authentication method fallthrough Josh proposes?

Assuming people aren't sharing accounts, you could let any authorized
postgres user connect using ident authentication as postgres. This
should be usable any place you are willing to let the postgres user
authenticate using ident.

Thu, 18 Mar 2004 19:54:07 -0600
Bruno Wolff III <bruno@wolff.to>
Re: Authentication drop-down?
On Thu, 18 Mar 2004, Bruno Wolff III wrote:

> On Thu, Mar 18, 2004 at 22:58:46 +0000, Jon Jensen <jon@endpoint.com> wrote:
> > 
> > Is there some other way to do what I'm looking for here without the 
> > authentication method fallthrough Josh proposes?
> 
> Assuming people aren't sharing accounts, you could let any authorized
> postgres user connect using ident authentication as postgres. This
> should be usable any place you are willing to let the postgres user
> authenticate using ident.

That's true, but that doesn't satisfy the need. I want an automated
process running as OS user "postgres" to authenticate with ident, but I'd
also like to be able have, say, phpPgAdmin (running as user "apache")
connect as Pg user "postgres" via the UNIX socket using an MD5 password.  
There's currently no way to do both AFAICT. I can create another superuser
with a different name so each user has different pg_hba settings, but
that's about it.

Jon

Fri, 19 Mar 2004 02:01:40 +0000 (UTC)
Jon Jensen <jon@endpoint.com>
Re: Authentication drop-down?
On Fri, Mar 19, 2004 at 02:01:40 +0000,
  Jon Jensen <jon@endpoint.com> wrote:
> 
> That's true, but that doesn't satisfy the need. I want an automated
> process running as OS user "postgres" to authenticate with ident, but I'd
> also like to be able have, say, phpPgAdmin (running as user "apache")
> connect as Pg user "postgres" via the UNIX socket using an MD5 password.  
> There's currently no way to do both AFAICT. I can create another superuser
> with a different name so each user has different pg_hba settings, but
> that's about it.

You can treat domain socket and internet socket connections differently.
This is still a kludge but might solve your particular problem.

Thu, 18 Mar 2004 20:43:16 -0600
Bruno Wolff III <bruno@wolff.to>
Re: Authentication drop-down?
Josh Berkus <josh@agliodbs.com> writes:
> Any reason why this is a bad idea?

It breaks client compatibility --- I don't think any existing clients
are prepared to be challenged multiple times, and indeed the protocol
spec specifically advises clients to drop the connection if they can't
handle the first challenge method.  We'd need a protocol extension
by which a client could respond "I can't do that Dave".

We could put it on the to-do list for the next protocol version bump,
perhaps.  I'm not in a hurry for one though...

			regards, tom lane

Fri, 19 Mar 2004 02:16:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Authentication drop-down? 
On Friday 19 March 2004 02:01, Jon Jensen wrote:
> On Thu, 18 Mar 2004, Bruno Wolff III wrote:
> > On Thu, Mar 18, 2004 at 22:58:46 +0000, Jon Jensen <jon@endpoint.com> 
wrote:
> > > Is there some other way to do what I'm looking for here without the
> > > authentication method fallthrough Josh proposes?
> >
> > Assuming people aren't sharing accounts, you could let any authorized
> > postgres user connect using ident authentication as postgres. This
> > should be usable any place you are willing to let the postgres user
> > authenticate using ident.
>
> That's true, but that doesn't satisfy the need. I want an automated
> process running as OS user "postgres" to authenticate with ident, but I'd
> also like to be able have, say, phpPgAdmin (running as user "apache")
> connect as Pg user "postgres" via the UNIX socket using an MD5 password.
> There's currently no way to do both AFAICT. I can create another superuser
> with a different name so each user has different pg_hba settings, but
> that's about it.

How about a .pgpass file for OS-user postgres, and just set all logins for 
PG-user postgres to use password?

-- 
  Richard Huxton
  Archonet Ltd

Fri, 19 Mar 2004 07:47:14 +0000
Richard Huxton <dev@archonet.com>
Re: Authentication drop-down?
Tom,

> We could put it on the to-do list for the next protocol version bump,
> perhaps.  I'm not in a hurry for one though...

That would be fine with me.   It's an annoyance rather than crippling for 
anybody, but seems like it would be easy to do ....

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Fri, 19 Mar 2004 08:41:45 -0800
Josh Berkus <josh@agliodbs.com>
Re: Authentication drop-down?