0

The other posts I've seen for this issue say to make sure the encrypted field is of type bytea, which mine already is. So, I'm not really sure what I'm doing wrong here. Please excuse the silly passwords, this is just testing proof of concept. Basically, I encrypt the "email" field, then later retrieve and decrypt it, but the value comes back as '\x66616b65406578616d706c652e636f6d'. I've tried adding a ::text cast to the returned value, but it did not change the result.

psql (9.6.8)

CREATE EXTENSION IF NOT EXISTS pgcrypto;

create table IF NOT EXISTS test1
(
    username varchar(32) PRIMARY KEY,
    passhash varchar(255),
    email bytea
);

insert into test1 values (
    'amazar', 
    crypt('fakepass1', gen_salt('bf')),
    pgp_sym_encrypt_bytea('fake@example.com', 'cryptopass123')
);

select 
username, 
crypt('fakepass1', passhash)=passhash as match,
pgp_sym_decrypt_bytea(email, 'cryptopass123') as email
from test1 where username='amazar';

-- This is the value returned, rather than the original email

amazar   | t     | \x66616b65406578616d706c652e636f6d
Anwinity
  • 65
  • 1
  • 7

1 Answers1

0

Nevermind, I figured it out. Just needed to use pgp_sym_decrypt in the select statement instead of pgp_sym_decrypt_bytea.

Anwinity
  • 65
  • 1
  • 7
  • Correct. The presence or absence of `_bytea` tells you what the type of the cleartext is. The cyphertext must be bytea, but the cleartext can be either type and you have to keep track of that yourself. – jjanes May 04 '18 at 20:58