Re: Using both ident and password in pg_hba.conf

On Mon, May 9, 2016 at 5:42 PM, D'Arcy J.M. Cain <darcy(at)druid(dot)net> wrote:

> On Mon, 09 May 2016 17:12:22 -0400
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > If the same user id + database combinations might be valid in both
> > cases (from both PHP and manual connections) I think your only other
> > option for distinguishing which auth method to use is to make them
> > come in on different addresses. Can you set up a secondary IP
> > interface that only the PHP server uses, for example?
>
> I did think of that but how do I define that in pg_hba? The host field
> only specifies the remote IP, not the local one.
>
> > There's no provision for saying "try this auth method, but if it
> > fails, try subsequent hba lines". It might be interesting to have
> > that, particularly for methods like ident that don't involve any
> > client interaction. (Otherwise, you're assuming that the client can
> > cope with multiple challenges, which seems like a large assumption.)
> > I don't have much of a feeling for how hard it would be to do in the
> > server.
>
> I had an idea that that wouldn't be so easy else we would have had it
> by now. However, I am not sure that that is what is needed. I was
> thinking of something like this:
>
> host all joe(at)nobody 192.168.151.75/32 password
> host all all 192.168.151.75/32 ident
>
> The "all(at)nobody" field is meant to specify that the remote user is
> nobody but that they are connecting as user joe. You would be able to
> use "all" as well. You don't even need to do an ident check unless the
> auth method is "trust" which would be silly anyway. In fact "password"
> is the only method that even makes any sense at all.
>

So, at a high-level, you want:

- Users deploying php scripts in apache to require a password ( btw -- use
md5, not password)
- Users running php scripts from their shell accounts to connect with no
password to the database

Is that correct?

Why not just require that everyone use an (again: *md5*) to connect? It
would be significantly more secure. Is their a requirement that shell
account users be able to connect without providing a password?

(NB:
http://www.postgresql.org/docs/9.4/static/auth-methods.html#AUTH-PASSWORD
password will send the password in cleartext, md5 will tell libpq to hash
the password for you. No client-level change).

> --
> D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
> http://www.druid.net/darcy/ | and a sheep voting on
> +1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner.
> IM: darcy(at)Vex(dot)Net, VoIP: sip:darcy(at)druid(dot)net
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>