Обсуждение: Password type ?

HI

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 ?

Thanks
Urs

--
    "A little rebellion now and then is a good thing." -Jefferson

Urs Steiner wrote:
> HI
>
> 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.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

-sc

--
Sean Chittenden

Sean Chittenden wrote:
> > > 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.

I think your best bet is to do encryption/decryption on the client side
and store only the encrypted part in the database.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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