[HACKERS] Functions + plpgsql + contrib/pgcrypto = ??
Hello there,
What's the preferred and most efficient way to obtain PGCrypto encrypted
data from a plpgsql function?
1. Imagine the following simple table:
CREATE TABLE crypto (
pid SERIAL PRIMARY KEY,
title VARCHAR(50),
crypted_content BYTEA
);
2. Now insert the following 3 rows of data:
INSERT INTO crypto VALUES (1, 'test1', encrypt( 'ABCD', 'password',
'aes'));
INSERT INTO crypto VALUES (2, 'test2', encrypt( 'BCDE', 'password',
'aes'));
INSERT INTO crypto VALUES (3, 'test3', encrypt( 'CDEF', 'password',
'aes'));
3. Using the psql tool, selecting * from the crypto table yields the
following:
# select * from crypto;
id | title |crypted_content
+---+
1 | test1 | \307\266xI\235\210a\363=\201\222\247\2660\215
2 | test2 | D\211\256E\372^Y\0259\341\215\271\030]\234\017
3 | test3 | 4\263\251y\006_N\343\0270I\207;\370\270\266
Pretty straight forward, right?
Now how about doing this in a simple plpgsql Function. That's where we
encounter problems. I want to get DECRYPTED data based on an input ID. So...
4. Imagine the following simple plpgsql function (note I'm trying to decrypt
the table's encrypted BYTEA column into a decrypted VARCHAR upon return):
CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
RETURNS VARCHAR
AS '
DECLARE
crypto_cursor CURSOR (input INTEGER) FOR SELECT
encode(decrypt(crypted_content, decode(''password''::text,
''escape''::text), ''aes''::text), ''escape''::text) FROM crypto WHERE id =
input;
crypto crypto.crypted_content%TYPE;
tid ALIAS FOR $1;
BEGIN
OPEN crypto_cursor( tid );
LOOP
FETCH crypto_cursor INTO crypto;
EXIT WHEN NOT FOUND;
END LOOP;
CLOSE crypto_cursor;
RETURN ( encode(crypto, ''escape''::text)::VARCHAR );
END;
' LANGUAGE 'plpgsql';
5. When I use the above function (in the tool, psql) to get the decrypted
contents for ID = 2, it says I get 1 row returned, but the contents are
blank:
# select * from selectFromCrypto(1);
selectfromcrypto1
---
(1 row)
Notice the blank row returned... So what am I doing wrong?
I suspect it has something to do with
converting/encoding/decoding/decrypting the BYTEA column for return... but
what is the problem with the above Function?
I'm using PostgreSQL 7.4.6, on a Fedora Core 1 system.
Best regards and thank you very much in advance,
Michael Moran
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Calling Functions from Delete Rule (+ contrib/pgcrypto) = madness ??
Hello there,
I have a View with a Delete rule and I would like the Delete rule to call a
function (and pass-in a few of the the underlying View's/Table's column
values). How do you do this?
When I do it, I keep getting the following error:
ERROR: function expression in FROM may not refer to other relations of same
query level
This is my new Delete View that attempts to call a Function but yields the
above-mentioned error:
CREATE RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD
--Original (working) code:
-- DELETE FROM crypto
-- WHERE id = OLD.id;
--NEW (non-working) code:
select * from func_delFromCrypto( crypto.id, encode( decrypt(
crypto.crypted_content, decode ('password'::text, 'escape'::text),
'aes'::text), 'escape'::text) );
The above may seem complicated, but the crypto portion DOES work (as you'll
see below), and I've broken down the steps below so that it's easy to see
what's going on:
1. Imagine the following simple table:
CREATE TABLE crypto (
id SERIAL PRIMARY KEY,
title VARCHAR(50),
crypted_content BYTEA
);
2. Imagine the following simple working View:
CREATE VIEW crypto_view AS
SELECT
id,
title,
--Decrypt the BYTEA column and convert result to TEXT type:
encode(decrypt(crypted_content, decode('password','escape'::text),
'aes'::text), 'escape'::text) as crypted_content
FROM
crypto;
3. Imagine my original, simple Delete Rule (that works -- it was my original
version prior to changing it to call the Function):
CREATE RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD
DELETE FROM crypto
WHERE id = OLD.id;
4. Let's load (and encrypt) some test data into the above-mentioned table:
insert into crypto VALUES (1, 'test1', encrypt('', 'password',
'aes') );
insert into crypto VALUES (2, 'test2', encrypt('', 'password',
'aes') );
insert into crypto VALUES (3, 'test3', encrypt('', 'password',
'aes') );
insert into crypto VALUES (4, 'test4', encrypt('', 'password',
'aes') );
insert into crypto VALUES (5, 'test5', encrypt('', 'password',
'aes') );
5. Let's SELECT from the table to see its contents (note the encrypted
values):
select * from crypto;
id | title | crypted_content
+---+
1 | test1 | \026\206I93\327\315\376t\243\006~J\177{\301
2 | test2 | \257\212\260\321\217\274c\210d \361\225\212\327\317\206
3 | test3 | 6\345:\224dp\002\206\007k\344\302\347V\214
4 | test4 | VH)\023\303\0239\363\323\362\22734\204R\357
5 | test5 | \216Np\235\026\362\277\246\026\027\221\266\021\361\224\256
(5 rows)
6. Let's run the View (which nicely decrypts the encrypted columns):
# select * from crypto_view;
id | title | crypted_content
+---+-
1 | test1 |
2 | test2 |
3 | test3 |
4 | test4 |
5 | test5 |
(5 rows)
7. Let's test the old DELETE RULE (the one that doesn't call a function,
hence works):
delete from crypto_view where crypted_content = ;
DELETE 1
Check that the DELETE RULE worked (there is one less row of data now):
# select * from crypto_view;
id | title | crypted_content
+---+-
1 | test1 |
2 | test2 |
3 | test3 |
4 | test4 |
(4 rows)
It works! Pretty straight forward, right?
Now let's make things a little more interesting...
8. Now here is the following (working) function that I want my rule to call:
CREATE OR REPLACE FUNCTION func_delFromCrypto(INTEGER, VARCHAR)
RETURNS INTEGER
AS '
DECLARE
_id ALIAS FOR $1;
_crypto_dataALIAS FOR $2;
ret INTEGER:=0;
BEGIN
DELETE FROM crypto WHERE id = _id AND encode( decrypt( crypted_content,
decode(''password''::text, ''escape''::text), ''aes''::text),
''escape''::text) = _crypto_data;
GET DIAGNOSTICS ret = ROW_COUNT;
RETURN ret;
END;
' LANGUAGE 'plpgsql';
9. The function works, stand-alone, as you can see below:
# select * from func_delFromCrypto(4, '');
func_delfromcrypto
1
(1 row)
Check the output (there is one less row now):
# select * from crypto_view;
id | title | crypted_content
+---+-
1 | test1 |
2 | test2 |
3 | test3 |
(3 rows)
So the function works, stand-alone. But how can I get my Delete Rule to call
it?
I want my Delete Rule to pass in the underlying ID (the primary key) and the
crypted_content into the Function (like you can when calling the Function
stand-alone).
Once again, here is my new DELETE RULE that calls the function (and fails):
CREATE RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD
--Original (working) code:
-- DELETE FROM crypto
-- WHERE id = OLD.id;
--NEW (non-working) code:
[HACKERS] contrib/pgcrypto
Hello, How do you encrypt() decrypt() data of types INT4 or DATE? The PGCrypto methods encrypt() and decrypt() each take BYTEA as input: i.e., encrypt( data::bytea, key::bytea, type::text) decrypt( data::bytea, key::bytea, type::text) So how do you convert INT4 and DATE data into BYTEA so that they can be passed as input into the PGCrypto methods encrypt() decrypt()? Thank you, Michael Moran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] How to cast VARCHAR to BYTEA and vice-versa?
Hello all, I have a table with a VARCHAR column that I need to convert to a BYTEA. How do I cast VARCHAR to BYTEA? The following doesn't seem to work as it yields the 'cannot cast varchar to bytea' error message: varchar_data::bytea On the same topic, how do I do the reverse, that is, how to I cast from BYTEA back to VARCHAR? Thanks, -Michael Moran ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] How to cast VARCHAR to BYTEA and vice-versa?
Thank you, Tom. Yes, the exact bytes in the varchar datum (without encoding) is what I would like to become the bytes in the BYTEA. So, how do I create a cast WITHOUT FUNCTION as you mention below? I assume plpgsql is required, right? Is there anyway this can be done in a VIEW without having to use plpgsql in a Function (a.k.a. stored procedure)? Is there no simple cast or conversion method I can call that does this for me? Best Regards, Michael Moran _ From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tue 3/8/2005 9:18 AM To: Moran.Michael Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] How to cast VARCHAR to BYTEA and vice-versa? Moran.Michael [EMAIL PROTECTED] writes: How do I cast VARCHAR to BYTEA? I think it would work to create a cast WITHOUT FUNCTION, assuming that the semantics you want is that the exact bytes in the varchar datum become the bytes in the bytea (no encoding or backslashing conversions). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
