Thread: Pgcrypto install (I've tried multiple ways)
I’m not sure what I’m doing wrong here. I’ve tried to install the pgcrypto module from:
- psql shell: i\ path/to/pgcrypto.sql
- pgAdmin: execute pgScript pgcrypto.sql
Both showed me multiple lines of CREATE FUNCTION with no other errors. However, when I try to test the decrypt() function it says it doesn’t exist. Encrypt() has worked fine, even before the module install. Stopping and starting the service doesn’t help.
I’m running pgAdmin 1.12 with Postgresql 9.0 installed on Windows 2003.
David Patricola | Senior Cold Fusion Developer | Web Applications & Services | Jefferson Information Technologies
Thomas Jefferson Universtiy | Philadelphia, PA | 215.503.1715 (Office)
"David Patricola" <david.patricola@jefferson.edu> writes:
> I'm not sure what I'm doing wrong here. I've tried to install the pgcrypto
> module from:
> 1. psql shell: i\ path/to/pgcrypto.sql
> 2. pgAdmin: execute pgScript pgcrypto.sql
> Both showed me multiple lines of CREATE FUNCTION with no other errors.
> However, when I try to test the decrypt() function it says it doesn't exist.
Hm, are you sure you're invoking the decrypt function with the right
arguments? Can you see it with "\df decrypt" in psql?
A different possibility is that you ran the install script in some other
database of the installation. The functions need to be installed in
each database you want to use them in.
regards, tom lane
I ran the script under database 'testdb', and that's the database I'm
viewing when in pgAdmin. Here's what \df decript gave me:
Schema | Name | Result data type | Argument data types | Type
-------+---------+------------------+---------------------+--------
public | decrypt | bytea | bytea, bytea, text | normal
(it finds all of the functions, in fact)
However, when I run this query it says it can't find the function with the
given name and datatypes.
select decrypt(thenotes, 'thekey', 'aes') AS theoutput
from footable
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, January 27, 2011 4:45 PM
To: David Patricola
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pgcrypto install (I've tried multiple ways)
"David Patricola" <david.patricola@jefferson.edu> writes:
> I'm not sure what I'm doing wrong here. I've tried to install the
pgcrypto
> module from:
> 1. psql shell: i\ path/to/pgcrypto.sql
> 2. pgAdmin: execute pgScript pgcrypto.sql
> Both showed me multiple lines of CREATE FUNCTION with no other errors.
> However, when I try to test the decrypt() function it says it doesn't
exist.
Hm, are you sure you're invoking the decrypt function with the right
arguments? Can you see it with "\df decrypt" in psql?
A different possibility is that you ran the install script in some other
database of the installation. The functions need to be installed in
each database you want to use them in.
regards, tom lane
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
"David Patricola" <david.patricola@jefferson.edu> writes:
> I ran the script under database 'testdb', and that's the database I'm
> viewing when in pgAdmin. Here's what \df decript gave me:
> Schema | Name | Result data type | Argument data types | Type
> -------+---------+------------------+---------------------+--------
> public | decrypt | bytea | bytea, bytea, text | normal
> (it finds all of the functions, in fact)
> However, when I run this query it says it can't find the function with the
> given name and datatypes.
> select decrypt(thenotes, 'thekey', 'aes') AS theoutput
> from footable
So is "thenotes" of type bytea?
regards, tom lane
Actually, what is bytea in terms of type, or rather, what types of database
fields will this work with? The field I'm calling from the table is varchar
(the encrypt function worked find for this).
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, January 27, 2011 6:15 PM
To: David Patricola
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pgcrypto install (I've tried multiple ways)
"David Patricola" <david.patricola@jefferson.edu> writes:
> I ran the script under database 'testdb', and that's the database I'm
> viewing when in pgAdmin. Here's what \df decript gave me:
> Schema | Name | Result data type | Argument data types | Type
> -------+---------+------------------+---------------------+--------
> public | decrypt | bytea | bytea, bytea, text | normal
> (it finds all of the functions, in fact)
> However, when I run this query it says it can't find the function with the
> given name and datatypes.
> select decrypt(thenotes, 'thekey', 'aes') AS theoutput
> from footable
So is "thenotes" of type bytea?
regards, tom lane
"David Patricola" <david.patricola@jefferson.edu> writes:
> Actually, what is bytea in terms of type, or rather, what types of database
> fields will this work with? The field I'm calling from the table is varchar
> (the encrypt function worked find for this).
bytea is for arbitrary byte strings, ie, non-textual data. Typically
you'd store the output of encrypt() (which yields bytea) in the database
and then apply decrypt() to that. Not sure what you're trying to do
above.
BTW, according to the pgcrypto man page, use of the PGP functions is
preferred over directly using encrypt/decrypt.
regards, tom lane
What I have to do is store multiple fields encrypted from user input, them
decrypt them for retrieval to the browser. Is there a preferred way to do
this?
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, January 27, 2011 6:34 PM
To: David Patricola
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pgcrypto install (I've tried multiple ways)
"David Patricola" <david.patricola@jefferson.edu> writes:
> Actually, what is bytea in terms of type, or rather, what types of
database
> fields will this work with? The field I'm calling from the table is
varchar
> (the encrypt function worked find for this).
bytea is for arbitrary byte strings, ie, non-textual data. Typically
you'd store the output of encrypt() (which yields bytea) in the database
and then apply decrypt() to that. Not sure what you're trying to do
above.
BTW, according to the pgcrypto man page, use of the PGP functions is
preferred over directly using encrypt/decrypt.
regards, tom lane
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
I did read about the pgp functions but they are on another level right now.
I just want to be able to encrypt and decrypt data to a database
competently. From there I'll learn about the more advanced functions.
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, January 27, 2011 6:34 PM
To: David Patricola
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pgcrypto install (I've tried multiple ways)
"David Patricola" <david.patricola@jefferson.edu> writes:
> Actually, what is bytea in terms of type, or rather, what types of
database
> fields will this work with? The field I'm calling from the table is
varchar
> (the encrypt function worked find for this).
bytea is for arbitrary byte strings, ie, non-textual data. Typically
you'd store the output of encrypt() (which yields bytea) in the database
and then apply decrypt() to that. Not sure what you're trying to do
above.
BTW, according to the pgcrypto man page, use of the PGP functions is
preferred over directly using encrypt/decrypt.
regards, tom lane
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
On 28 January 2011 02:06, David Patricola <david.patricola@jefferson.edu> wrote: > What I have to do is store multiple fields encrypted from user input, them > decrypt them for retrieval to the browser. Is there a preferred way to do > this? Try creating a column with type "bytea" and store the results of the encrypt() in there. Then use that column with decrypt(). (Or alter the type of your existing "thenotes" column from varchar to bytea.) See also: http://www.postgresql.org/docs/9.0/static/datatype-binary.html Note: I've never used pgcrypt. -- Michael Wood <esiotrot@gmail.com>
This put me on the right track, and I did find a solution. select encode(decrypt(testfield2, 'key', 'aes'), 'escape') AS foo from table I needed to add encode() when outputting to the browser (using ColdFusion), but converting the field to bytea did the trick. Thanks to both of you! -----Original Message----- From: Michael Wood [mailto:esiotrot@gmail.com] Sent: Friday, January 28, 2011 8:56 AM To: David Patricola Cc: Tom Lane; pgsql-novice@postgresql.org Subject: Re: [NOVICE] Pgcrypto install (I've tried multiple ways) On 28 January 2011 02:06, David Patricola <david.patricola@jefferson.edu> wrote: > What I have to do is store multiple fields encrypted from user input, them > decrypt them for retrieval to the browser. Is there a preferred way to do > this? Try creating a column with type "bytea" and store the results of the encrypt() in there. Then use that column with decrypt(). (Or alter the type of your existing "thenotes" column from varchar to bytea.) See also: http://www.postgresql.org/docs/9.0/static/datatype-binary.html Note: I've never used pgcrypt. -- Michael Wood <esiotrot@gmail.com>