creating a function in psql

Hi
I've just done the very same thing today and found how to add plpgsql
into the pg_languages.
Firstly I located where plpgsql was on our server.
In that directory there was a file called install which gives
instructions using make file and then adding the language to your
pg_language table.
It also gives instructions on how to add it to the template1 so all
created databases will include the language.
I found that I didn't need to use makefile and was able to just do the
psql dbname <mklang.sql

This was the instruction and location of our plpgsql:-

psql test
</usr/ports/databases/postgresql/work/postgresql-6.5.2/src/pl/plpgsql/src/mklang.sql

Once this is created successfully, then you can write the functions
using plpgsql.

Hope this helps

Regards,
Julie

rachel cox wrote:

I use set datestyle to 'iso';
or set datestyle to 'European';
with success.

rachel cox wrote:

Hi!

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.

BTW

How do I implement a function in C that returns a varchar...

create function pgcrypt(opaqe) returns varchar....???

char *pgcrypt(char *text) ????

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@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

Ah, I forget to mention how to compile the code I sent. I use:

gcc -fPIC -shared -I /usr/local/pgsql/include -L /usr/local/pgsql/lib
-o sqlcrypt.so sqlcrypt.c

then move the sqlcrypt.so file into my pgsql storage space. This is
on Linux, if it matters.

Ross

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
On Wed, Apr 05, 2000 at 12:04:49PM -0500, Victor Manuel Jaquez Leal wrote:

On Thu, Apr 06, 2000 at 04:48:02PM -0500, Jeff Gerhart wrote:

Ross,
Thats for the input. Have a couple of additional questions:

1- I have the code running and it appears that I get a different result each
time I sqlcrypt the same string e.g. select sqlcrypt('xyz') gives me a
different encryption each time I execute it. Pardon my ignorance, but how
does unix validate a password i.e. compare the results of crypt'd inout
password against the previously crypt'd password.

It's selecting a random 'salt' each time, since you're not passing it
one. The first two characters of the hash are the salt, so, for example:

reedstrm=> select sqlcrypt('secret');
sqlcrypt
-------------
PfB9b6nH6QgbA
(1 row)

reedstrm=> select sqlcrypt('secret');
sqlcrypt
-------------
acI.WsXmTid6k
(1 row)

reedstrm=> select sqlcrypt('secret','Pf');
sqlcrypt
-------------
PfB9b6nH6QgbA
(1 row)

So, if we pass in the salt, we get the same hash back. That's what the
example select from my original post was for: it includes the salt:

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

This will return the row IFF the the submitted password 'secret' is
the same as the password that was originally hashed in. Note that any
encryption strategy that relys on the backend DB to do the encryption
is on the wrong end of the network link! The password still goes clear
text to the database, and is visible in the postgresql logs, if you
log queries. But it _does_ keep me from seeing all the bad passwords
people chose actually in my tables!

2- Is there something simple process I can use to encrypt text for storage
in the database and then de-encrypt it later. I would assume I would need to
maintain the key or seed I used for encryption of the string to de-encrypt
it later.

I'm not really an encryption expert, but what your looking for is a
reversible encryption algorithm. Some suggestions of the top of my head:
SHA, Blowfish, Hmm, can't think of any more. Note my caveat above,
about what's on the wire. You _really_ want the client app to do the
encrypting/decrypting, not the backend.

Ross
P.S. I copied GENERAL on this, just so there's a record with the
original post.

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