Postgres Pro
Downloads
Home   >   mailing lists

Thread: [general] Error while decrypting using pgp

[general] Error while decrypting using pgp

From
VENKTESH GUTTEDAR
Date:
Hello,

    I am running postgresql 9.3.5 on Ubuntu14.04.

    i have the table two fields of varchar type. while inserting the data in the following way.

    INSERT INTO testusers(username, phone_no)
    SELECT pgp_sym_encrypt(test.username, keys.pubkey),
    pgp_sym_encrypt(test.phone_no, keys.pubkey) As cc
    FROM (VALUES ('abcd', '9876543210')
    ) As test(username, cc)
    CROSS JOIN (SELECT armor('DC35GT67'') As pubkey) As keys;

    it is encrypting the data.
    when i say SELECT * FROM testusers;
    i get these values.
    
    username = \xc30d04070302624fc5146ba1304a63d2360165b2c8c7e5d48cea7673c66f0ddb5ecef4eafe146797b70f4df028a257847e9de6b1eccaf974639b27a01fbb5b42e24f14fb17eba5
    phone_no =
\xc30d0407030284f6b57ae1c2d57369d23701cd705d5c6da32570c3f0ca9aaf2625a5bfbdcb439959674c6c5be879f8c42fd24ac2eba99648b853e6b37977560f52118c9ee7bb0d49      

    But when i say
    SELECT pgp_sym_decrypt(phone_no, keys.privkey), pgp_sym_decrypt(username, keys.privkey) As Phonedecrypt FROM testusers CROSS JOIN (SELECT armor('DC35GT67'') As privkey) As keys;

    i am getting this error

    ERROR:  function pgp_sym_decrypt(character varying, text) does not exist
    LINE 1: SELECT pgp_sym_decrypt(phone_no, keys.privkey), pgp_sym_decrypt(us...
                                                  ^
    HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

   Can anyone tell me where i am going wrong. and if not then what explicit type casts i have to add.?

    please help thanks in advance.

--
Regards :
Venktesh Guttedar.

Re: [general] Error while decrypting using pgp

From
Jeff Janes
Date:
On Wed, Nov 19, 2014 at 2:49 AM, VENKTESH GUTTEDAR <venkteshguttedar@gmail.com> wrote:

    i am getting this error

    ERROR:  function pgp_sym_decrypt(character varying, text) does not exist
    LINE 1: SELECT pgp_sym_decrypt(phone_no, keys.privkey), pgp_sym_decrypt(us...

The encrypted message needs to be binary data, bytea.  You can cast it to bytea, like: 

pgp_sym_decrypt(phone_no::bytea, keys.privkey)

But really the column phone_no should be of type bytea to start with.

Cheers,

Jeff