Postgres Pro
Downloads
Home   >   mailing lists

Thread: MAY I HAVE YOUR ASSISTANCE

MAY I HAVE YOUR ASSISTANCE

From
James Kitambara
Date:
 
Please members of the PGSQL-SQL,
 
I have one problem with the user table. I want to hide the password for the users.
 
The table format is:
 
user ( user_id, user_name, password)
 
But I want the password to be encrypted so that when other users send the query:SELECT * FROM USER; The password must be gabbage.
 
i.e. THE OUTPUT SHOULG LOOK LIKE THIS
 
    user_id      | user_name     |    password
    -------------------------------------------------------------
    0001         |  ADMIN           |   %3g4767teghh890)*
    0002         |  USER            |   #42@rwfs%6&&^*8
    0003         |  IT-MANAGER |   ^8Y3U(-43873GDG?
 
I appriciate for your assistance in advance,
 
Sincerely yours,
 
James Kitambara,
PostgreSQL user.

Re: MAY I HAVE YOUR ASSISTANCE

From
"Scott Marlowe"
Date:
On Mon, Sep 1, 2008 at 1:03 AM, James Kitambara
<jameskitambara@yahoo.co.uk> wrote:
>
> Please members of the PGSQL-SQL,
>
> I have one problem with the user table. I want to hide the password for the
> users.
>
> The table format is:
>
> user ( user_id, user_name, password)
>
> But I want the password to be encrypted so that when other users send the
> query:SELECT * FROM USER; The password must be gabbage.

Store it as an md5sum.  note that this is a one way function.  so
then, when someone logs in you md5 the password and compare it to the
md5 you saved in the db.

Re: MAY I HAVE YOUR ASSISTANCE

From
"D'Arcy J.M. Cain"
Date:
On Mon, 1 Sep 2008 07:03:51 +0000 (GMT)
James Kitambara <jameskitambara@yahoo.co.uk> wrote:
>  
> Please members of the PGSQL-SQL,
>  
> I have one problem with the user table. I want to hide the password for the users.

Look in the contrib directory for the chkpass type.  It uses DES
encryption but if you need MD5 or Blowfish it wouldn't be very hard to
modify the code.

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Re: MAY I HAVE YOUR ASSISTANCE

From
"Gary Chambers"
Date:
>> I have one problem with the user table. I want to hide the password for the users.

Here's what I did, which requires using the contrib/pgcrypto extension:

CREATE FUNCTION encryptpw() RETURNS TRIGGER AS
$encryptpw$
BEGIN       NEW.password = CRYPT(NEW.password, GEN_SALT('md5'));       RETURN NEW;
END;
$encryptpw$

CREATE TRIGGER trg_encryptpw BEFORE INSERT OR UPDATE ON assignees
FOR EACH ROW EXECUTE PROCEDURE encryptpw();

Comments, suggestions, criticisms?

-- Gary Chambers

/* Nothing fancy and nothing Microsoft! */

Re: MAY I HAVE YOUR ASSISTANCE

From
"Fernando Hevia"
Date:
 

> -----Mensaje original-----
> De: pgsql-sql-owner@postgresql.org 
> [mailto:pgsql-sql-owner@postgresql.org] En nombre de Gary Chambers
> Enviado el: Lunes, 01 de Septiembre de 2008 11:31
> Para: D'Arcy J.M. Cain
> CC: jameskitambara@yahoo.co.uk; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] MAY I HAVE YOUR ASSISTANCE
> 
> >> I have one problem with the user table. I want to hide the 
> password for the users.
> 
> Here's what I did, which requires using the contrib/pgcrypto 
> extension:
> 
> CREATE FUNCTION encryptpw() RETURNS TRIGGER AS $encryptpw$ BEGIN
>         NEW.password = CRYPT(NEW.password, GEN_SALT('md5'));
>         RETURN NEW;
> END;
> $encryptpw$
> 
> CREATE TRIGGER trg_encryptpw BEFORE INSERT OR UPDATE ON 
> assignees FOR EACH ROW EXECUTE PROCEDURE encryptpw();
> 
> Comments, suggestions, criticisms?
> 
> -- Gary Chambers
> 

The weakness of this solution is that your password might be send in the
clear through the network as the encription ocurrs in the database. I
suggest the encryption be enforced at the application or secure the
connection with ssl.

Re: MAY I HAVE YOUR ASSISTANCE

From
"Gary Chambers"
Date:
> The weakness of this solution is that your password might be send in the
> clear through the network as the encription ocurrs in the database. I
> suggest the encryption be enforced at the application or secure the
> connection with ssl.

Absolutely -- and understood.  I should have been more specific in my
request for comments since I was only thinking about
PostgreSQL-specific issues related to what I presented to the initial
requestor.

Thanks for your reply!

-- Gary Chambers

/* Nothing fancy and nothing Microsoft! */