[SQL] MAY I HAVE YOUR ASSISTANCE
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 | [EMAIL PROTECTED]&&^*8 0003 | IT-MANAGER | ^8Y3U(-43873GDG? I appriciate for your assistance in advance, Sincerely yours, James Kitambara, PostgreSQL user.
Re: [SQL] MAY I HAVE YOUR ASSISTANCE
On Mon, Sep 1, 2008 at 1:03 AM, James Kitambara <[EMAIL PROTECTED]> 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] MAY I HAVE YOUR ASSISTANCE
On Mon, 1 Sep 2008 07:03:51 + (GMT) James Kitambara <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> | 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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
/* Nothing fancy and nothing Microsoft! */
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] MAY I HAVE YOUR ASSISTANCE
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Gary Chambers
> Enviado el: Lunes, 01 de Septiembre de 2008 11:31
> Para: D'Arcy J.M. Cain
> CC: [EMAIL PROTECTED]; 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] MAY I HAVE YOUR ASSISTANCE
> 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! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
