Re: Password type ?

> > > > I don't seem to be able to find a built-in way in PostgreSQL for
> > > > password encryption of a field?
> > > >
> > > > is there something like this and i just dont find it because of
> > > > the late hour ?
> > >
> > > We don't have that feature.
> >
> > Eh... what about the pgcrypto package?
> >
> > CREATE VIEW "user_md5_auth" AS
> > SELECT username, ENCODE(DIGEST(password, 'md5'), 'hex') as password
> > FROM passwd;
>
> Uh, yes. I am sorry. I thought the user wanted a field that could
> only be accessed via a password. I suppose it could be built using
> the pgcrypto routines, but I question how secure it would be because
> the password would have to pass over the network in plantext as part
> of the query. You could do something similar to what we do with
> wire encryption now by encrypting on the client side with a random
> salt supplied by the server and comparing that, but that doesn't
> sound secure.

Or you could MD5 digest the password with a shared and rotated secret
that's apart of the view... or even just hash the md5 the password on
the client and send the md5 over the wire to be compared with the
value generated by the view. This is what I'm doing with mod_auth_pg.
Raw password in the database, but the md5s are being generated via a
view and the md5 of the password is the only thing going over the wire.
-sc

--
Sean Chittenden