From: | "Ross J(dot) 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: Functions in postgres |
Date: | 2000-04-05 19:20:52 |
Message-ID: | 20000405142052.A2607@rice.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
Attachment | Content-Type | Size |
---|---|---|
sqlcrypt.c | text/x-csrc | 1.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Ross J. Reedstrom | 2000-04-05 19:22:56 | Re: Functions in postgres |
Previous Message | Paulo Jan | 2000-04-05 19:00:11 | System tables |