Re: [pgsql-ayuda] Campo que contenga password

> dentro de mi base de datos quiero tener un campo en una tabla de datos
> personales en el que se guarden passwords, como puede encriptar dicho campo?
> esto puede resultar inseguro? es mejor ponerlo todas las claves en una tabla
> diferente?

>>From ceyusa(at)linux1(dot)coral(dot)com(dot)mx Tue Dec 5 09:34:58 2000
Return-Path: <ceyusa(at)linux1(dot)coral(dot)com(dot)mx>
Delivered-To: ceyusa(at)coral(dot)com(dot)mx
Received: (qmail 6427 invoked from network); 5 Dec 2000 15:34:58 -0000
Received: from linux1.coral.com.mx (148.245.41.15)
by coral.com.mx with SMTP; 5 Dec 2000 15:34:58 -0000
Received: from localhost (ceyusa(at)localhost)
by linux1.coral.com.mx (8.9.3/8.9.3) with SMTP id KAA32022
for <ceyusa(at)coral(dot)com(dot)mx>; Tue, 5 Dec 2000 10:19:21 -0600
Date: Tue, 5 Dec 2000 10:19:21 -0600 (CST)
From: Victor Manuel Jaquez Leal <ceyusa(at)linux1(dot)coral(dot)com(dot)mx>
To: ceyusa(at)coral(dot)com(dot)mx
Subject: Re: [GENERAL] Functions in postgres (fwd)
Message-ID: <Pine(dot)LNX(dot)3(dot)96(dot)1001205101915(dot)31935A-200000(at)linux1(dot)coral(dot)com(dot)mx>
MIME-Version: 1.0
Content-Type: MULTIPART/MIXED; BOUNDARY=pf9I7BMVVzbSWLtt
Content-ID: <Pine(dot)LNX(dot)3(dot)96(dot)1001205101915(dot)31935B(at)linux1(dot)coral(dot)com(dot)mx>

This message is in MIME format. The first part should be readable text,
while the remaining parts are likely unreadable without MIME-aware tools.
Send mail to mime(at)docserver(dot)cac(dot)washington(dot)edu for more info.

--pf9I7BMVVzbSWLtt
Content-Type: TEXT/PLAIN; CHARSET=us-ascii
Content-ID: <Pine(dot)LNX(dot)3(dot)96(dot)1001205101915(dot)31935C(at)linux1(dot)coral(dot)com(dot)mx>

---------- Forwarded message ----------
Date: Wed, 5 Apr 2000 14:20:52 -0500
From: "Ross J. Reedstrom" <reedstrm(at)wallace(dot)ece(dot)rice(dot)edu>
To: Victor Manuel Jaquez Leal <ceyusa(at)linux1(dot)coral(dot)com(dot)mx>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Functions in postgres

On Wed, Apr 05, 2000 at 12:04:49PM -0500, Victor Manuel Jaquez Leal wrote:
>
> Hi!

Hi back at ya.

>
> I know that with \df you can see the functions available in postgres, but
> there must be others not documented just like getpgusername().
>
> My question is if are there a more complete list of postgres'
> functions. To be more specific I'm looking for a crypt function.
>

Then you're in luck. Not as much luck as if there was a built in, but
I've attached my implementation below. I stole a general boiler plate
function from someone else, and modified it to call crypt. The trickiest
part was generating random salt. I use it with these SQL statements:

CREATE FUNCTION "sqlcrypt" (text,text ) RETURNS text AS
'/usr/local/pgsql/data/sqlcrypt.so' LANGUAGE 'C';

CREATE FUNCTION "sqlcrypt" (text ) RETURNS text AS 'select
sqlcrypt($1,'''')' LANGUAGE 'SQL';

That way, I can say sqlcrypt('somestring') and it'll return a crypted
version of the string, with a randomly selected salt. I use it for
storing passwords for a web based login: for that, we check logins as
so:

SELECT * FROM "Personnel" WHERE "PerUsername" = 'RJReedstrom' AND
"PerPassword" = sqlcrypt('password',substr("PerPassword",1,2))

That will only return results if the password hashes match. It does expose
the cleartext of the password between the web server and postgres db:
That's not a problem for us, since they're on the same machine.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

--pf9I7BMVVzbSWLtt
Content-Type: TEXT/X-CSRC; CHARSET=US-ASCII
Content-ID: <Pine(dot)LNX(dot)3(dot)96(dot)1001205101915(dot)31935D(at)linux1(dot)coral(dot)com(dot)mx>
Content-Description:

#define _XOPEN_SOURCE
#include <postgres.h>
#include <unistd.h>
#include <string.h>
#include <stdlib.h>
#include <sys/time.h>

text *sqlcrypt(text *key, text *salt);
/*sql create function sqlcrypt(text,text) returns text as 'DESTLIB' language 'c'*/

char *crypt(const char *key, const char *salt);
int rand(void);
void srand(unsigned int seed);

text *sqlcrypt(text *key, text *salt)
{
text *ret;
char pass[] = "123456789";
char s[] = "...";
char salts[] = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789./";
int j,k;
struct timeval tv;

s[2]=0;
bzero(pass,9);
if ((VARSIZE(salt)-VARHDRSZ) < 2)
{
gettimeofday(&tv,0);
srand((unsigned int)(tv.tv_usec));
j=(rand() % 64);
k=(rand() % 64);
s[0]=salts[j];
s[1]=salts[k];

}
else
{
memcpy(s,VARDATA(salt),2);
}
ret = palloc(VARHDRSZ + 13);
bzero(ret,VARHDRSZ + 13);
VARSIZE(ret) = (VARHDRSZ + 13);
if ((VARSIZE(key)-VARHDRSZ) < 8)
{
memcpy(pass,VARDATA(key),VARSIZE(key)-VARHDRSZ);
}
else
{
memcpy(pass,VARDATA(key),8) ;
}

memcpy(VARDATA(ret), crypt(pass,s),13);

return ret;
}

--pf9I7BMVVzbSWLtt--

------------------------------
V�tor Manuel J�uez Leal
http://www.coral.com.mx/ceyusa
--------- Pie de mensaje -------------------------------------------
Archivo historico: http://tlali.iztacala.unam.mx/maillist/pgsql-ayuda
Cancelar inscripcion:
mail to: majordomo(at)tlali(dot)iztacala(dot)unam(dot)mx
text : unsubscribe pgsql-ayuda