[SQL] 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
[SQL] PGCrypto with Integers and DATE types -- how to?
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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Funtions + 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 for 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 4: Don't 'kill -9' the postmaster
Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
Thank you, Jim. You rock!
This worked although I'm a bit confused:
Your function below says it returns VARCHAR, yet the variable that holds the
contents of my SELECT which we ultimately return is of type TEXT.
When I return the TEXT variable, it works. Does this mean TEXT = VARCHAR in
plpgsql Functions?
Yet if you cast the TEXT to VARCHAR in your RETURN line below (because, it
seems logical to try to match the declared return type)... it fails
So, this works:return c;
This doesn't: return c::VARCHAR;
I always thought matching my return type to my funtion's RETURN declaration
is logical... but now I know that if want a VARCHAR, I gotta manipulate it
as a TEXT within my function when using PGCrypto. Any idea why?
Thank again, Jim!
_
From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
Sent: Thu 3/24/2005 9:14 AM
To: Moran.Michael; pgsql-sql@postgresql.org
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
give this a try
CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
RETURNS VARCHAR
AS '
DECLARE
_pid ALIAS FOR $1;
c text;
BEGIN
SELECT decrypt(crypted_content, decode(''password''::text,
''escape''::text), ''aes''::text) into c
FROM crypto
WHERE pid = _pid;
RETURN c;
END;
' LANGUAGE 'plpgsql';
-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thu, 24 Mar 2005 08:41:34 -0800
Subject: [SQL] Funtions + 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 for 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 4: Don't 'kill -9' the postmaster
--- End of Original Message ---
---(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: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
Jim,
I was thinking... In your excellent solution below, we select/decrypt and
return the crypto column based on an input Integer ID. This is good. But
what if we wanted to do the reverse?
That is, what if I want to select an ID based on input crypto data?
i.e., given my original table data way below:
1. Pass into the function the decrypted string ''BCDE'
2. The function looks up the ID that matchines the encrypted value of
'BCDE' ...
3. Finally: the matching ID of 2 is returned.
How would that change your solution below?
Thanks again in advance.
-Michael Moran
_
From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
Sent: Thu 3/24/2005 9:58 AM
To: Moran.Michael; Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto
= ??
I would change the return type to TEXT, I believe your original example had
it as a varchar and I didn't change it.
Also, I believe that "under the hood" text does equal varchar.
Glad I could help
Jim
-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], pgsql-sql@postgresql.org
Sent: Thu, 24 Mar 2005 09:43:18 -0800
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
> Thank you, Jim. You rock!
>
> This worked although I'm a bit confused:
>
> Your function below says it returns VARCHAR, yet the variable that holds
the
> contents of my SELECT which we ultimately return is of type TEXT.
>
> When I return the TEXT variable, it works. Does this mean TEXT = VARCHAR
in
> plpgsql Functions?
>
> Yet if you cast the TEXT to VARCHAR in your RETURN line below (because, it
> seems logical to try to match the declared return type)... it fails
>
> So, this works:return c;
> This doesn't: return c::VARCHAR;
>
> I always thought matching my return type to my funtion's RETURN
declaration
> is logical... but now I know that if want a VARCHAR, I gotta manipulate it
> as a TEXT within my function when using PGCrypto. Any idea why?
>
> Thank again, Jim!
>
> _
>
> From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]> ]
> Sent: Thu 3/24/2005 9:14 AM
> To: Moran.Michael; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
>
> give this a try
>
> CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
> RETURNS VARCHAR
> AS '
> DECLARE
>_pid ALIAS FOR $1;
>c text;
>
> BEGIN
>
> SELECT decrypt(crypted_content, decode(''password''::text,
> ''escape''::text), ''aes''::text) into c
> FROM crypto
> WHERE pid = _pid;
>
>RETURN c;
> END;
> ' LANGUAGE 'plpgsql';
>
> -- Original Message ---
> From: "Moran.Michael" <[EMAIL PROTECTED]>
> To: pgsql-sql@postgresql.org
> Sent: Thu, 24 Mar 2005 08:41:34 -0800
> Subject: [SQL] Funtions + 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
Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
I figured it out:
Here's how to get an ID from the table below, based on input crypto data
(this essentially is the reverse of Jim's excellent cryto-to-ID solution
below):
--
-- selectFromCrypto.sql
--
-- Purpose: select from crypto table based on input crypto data in VARCHAR
format.
-- Returns the corresponding ID (INTEGER) value for the crypto data.
--
CREATE OR REPLACE FUNCTION selectFromCrypto2(VARCHAR)
RETURNS INTEGER
AS '
DECLARE
_crypt_data ALIAS FOR $1;
ret INTEGER;
BEGIN
SELECT id INTO ret FROM crypto WHERE encode( decrypt(crypted_content,
decode(''password''::text, ''escape''::text), ''aes''::text),
''escape''::text ) = _crypt_data;
RETURN ( ret );
END;
' LANGUAGE 'plpgsql';
Thanks, all. This postgresql forum rocks!
Michael Moran
_
From: Moran.Michael
Sent: Thu 3/24/2005 10:59 AM
To: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
Jim,
I was thinking... In your excellent solution below, we select/decrypt and
return the crypto column based on an input Integer ID. This is good. But
what if we wanted to do the reverse?
That is, what if I want to select an ID based on input crypto data?
i.e., given my original table data way below:
1. Pass into the function the decrypted string ''BCDE'
2. The function looks up the ID that matchines the encrypted value of
'BCDE' ...
3. Finally: the matching ID of 2 is returned.
How would that change your solution below?
Thanks again in advance.
-Michael Moran
_
From: Jim Buttafuoco [mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
]
Sent: Thu 3/24/2005 9:58 AM
To: Moran.Michael; Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto
= ??
I would change the return type to TEXT, I believe your original example had
it as a varchar and I didn't change it.
Also, I believe that "under the hood" text does equal varchar.
Glad I could help
Jim
-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], pgsql-sql@postgresql.org
Sent: Thu, 24 Mar 2005 09:43:18 -0800
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
> Thank you, Jim. You rock!
>
> This worked although I'm a bit confused:
>
> Your function below says it returns VARCHAR, yet the variable that holds
the
> contents of my SELECT which we ultimately return is of type TEXT.
>
> When I return the TEXT variable, it works. Does this mean TEXT = VARCHAR
in
> plpgsql Functions?
>
> Yet if you cast the TEXT to VARCHAR in your RETURN line below (because, it
> seems logical to try to match the declared return type)... it fails
>
> So, this works:return c;
> This doesn't: return c::VARCHAR;
>
> I always thought matching my return type to my funtion's RETURN
declaration
> is logical... but now I know that if want a VARCHAR, I gotta manipulate it
> as a TEXT within my function when using PGCrypto. Any idea why?
>
> Thank again, Jim!
>
> _
>
> From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>
<mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > ]
> Sent: Thu 3/24/2005 9:14 AM
> To: Moran.Michael; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
>
> give this a try
>
> CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
> RETURNS VARCHAR
> AS '
> DECLARE
>_pid ALIAS FOR $1;
>c text;
>
> BEGIN
>
> SELECT decrypt(crypted_content, decode(''password''::text,
> ''escape''::text), ''aes''::text) into c
> FROM crypto
> WHERE pid = _pid;
>
>RETURN c;
> END;
> ' LANGUAGE 'plpgsql';
>
> -- Original Message ---
> From: "Moran.Michael" <[EMAIL PROTECTED]>
> To: pgsql-sql@postgresql.org
> Sent: Thu, 24 Mar 2005 08:41:34 -0800
> Subject: [SQL] Funtions + 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',
>
[SQL] Calling functions from Delete View (+ 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:
s
Re: [SQL] Calling functions from Delete View (+ contrib/pgcrypto)
Jim,
Once again, your solution has worked wonders for me. I can now call the
Function via the DELETE RULE. Thank you very much. You rock!
By the way, as you may have noticed, I could not find ANY online
documentation on using PGCrypto + VIEWS + RULES + FUNCTIONs, all combined;
nor does the "purple book" (PostgreSQL by Korry Douglas) go into these
details... So, if I may ask, how did you acquire such extensive knowledge
on these topics used in conjunction? Online via the forum, like me?
Anyway, by far, this Posgres SQL forum is the best forum I've encountered
for finding answers to questions like mine below. Much better than the
Hackers or Admin forums it seems.
Thanks all!
-Michael Moran
_
From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
Sent: Thu 3/24/2005 7:44 PM
To: Moran.Michael; pgsql-sql@postgresql.org
Subject: Re: [SQL] Calling functions from Delete View (+ contrib/pgcrypto) =
madness ??
Mike,
I posted this RULE also on hackers
CREATE or replace RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD
(
select func_delFromCrypto( OLD.id,OLD.crypted_content);
);
Jim
-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thu, 24 Mar 2005 17:30:33 -0800
Subject: [SQL] Calling functions from Delete View (+ 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 encryp
[SQL] Securing VIEWS that use contrib/pgcrypto -- how?
Hello there,
How do you secure a VIEW so that only certain users may use it?
Let's say you have a table that has a BYTEA column that holds (pgcrypto)
encrypted data; and you have a VIEW on the aforementioned table that returns
the decrypted contents of the BYTEA column.
How do you secure this VIEW so that only certain users may use it (to get
the decrypted data)?
For example:
1. Here is a simple table that will hold a pgcrypto encrypted BYTEA value:
CREATE TABLE crypto (
id SERIAL PRIMARY KEY,
title VARCHAR(50),
crypted_content BYTEA
);
2. Let's enter (and encrypt via pgcrypto) some rows of test data:
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') );
3. Let's SELECT directly from the table (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
(3 rows)
4. Let's create a VIEW that automatically decrypts the (pgcrypto encrypted)
BYTEA column, returning its decrypted contents as type TEXT:
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;
5. Now let's select from this VIEW (note the decrypted values):
# select * from crypto_view;
id | title | crypted_content
+---+-
1 | test1 |
2 | test2 |
3 | test3 |
(3 rows)
Pretty straight forward, right?
So how can I secure this nifty VIEW? If it can't be secured, then everyone
has access to the data by simply calling the VIEW.
By extention, if you have RULES on a VIEW, when you secure the VIEW, do the
RULES inherit their underlying VIEW's security rights as well?
Thank you,
Michael Moran
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] PGCrypto: Realworld scenario and advice needed
Hello all, I'm looking for advice on real-world PGCrypto usage. I understand how to programmatically encrypt/decrypt data with PGCrypto -- no problem. My question is: What is the best way to update massive amounts of *existing* encrypted data with a new encryption passphrase, assuming you know the old passphrase? For example: Let's say that periodically there must be a one-time change of the encryption passphrase and ALL existing encrypted data has to be encrypted with the new passphrase. My initial attack plan was to do the following: 1. Call decrypt() with the old-passphrase to decrypt each table's existing data. 2. Temporarily store the decrypted data in temp tables. 3. Delete all rows of encrypted data from the original tables -- thereby clearing the tables of all data encrypted with the old passphrase. 4. Call encrypt() with the new passphrase to encrypt all data in the temp tables -- thereby repopulating the production tables with data encrypted with the new passphrase. 5. Blow away the temp tables. But this seems like a tedious procedure. Is there any simple way to update ALL existing encrypted data with a new passphrase, assuming you know the old passphrase and encryption type (i.e. AES, Blowfish, etc.) without having to go through the 5-step process mentioned above? Thank you and best regards, Michael Moran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PGCrypto: Realworld scenario and advice needed
Thank you, Joe. Your solution provided below works great. Much better/simpler than my original approach. You rock! -Michael _ From: Joe Conway [mailto:[EMAIL PROTECTED] Sent: Mon 4/11/2005 3:26 PM To: Moran.Michael Cc: PostgreSQL Subject: Re: [SQL] PGCrypto: Realworld scenario and advice needed Moran.Michael wrote: > My initial attack plan was to do the following: > > 1. Call decrypt() with the old-passphrase to decrypt each table's existing > data. > 2. Temporarily store the decrypted data in temp tables. > 3. Delete all rows of encrypted data from the original tables -- thereby > clearing the tables of all data encrypted with the old passphrase. > 4. Call encrypt() with the new passphrase to encrypt all data in the temp > tables -- thereby repopulating the production tables with data encrypted > with the new passphrase. > 5. Blow away the temp tables. > > But this seems like a tedious procedure. > > Is there any simple way to update ALL existing encrypted data with a new > passphrase, assuming you know the old passphrase and encryption type (i.e. > AES, Blowfish, etc.) without having to go through the 5-step process > mentioned above? Why not use a single UPDATE command, e.g. something like: UPDATE tbl SET f1 = encrypt(decrypt(f1, 'oldkey', 'aes'), 'newkey', 'aes'); Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
