postgresql encrypt / decrypt
The decrypt function is returning a byte string, not a character string, so its being shown in hex notation. The actual values are the same \x31 = 1, \x32 = 2 etc.
You need to cast the return value back to text.
eg:
select convert_from(decrypt('\x34591627f9c8eae417fc7cbbf458592c','1234','aes'),'SQL_ASCII'); convert_from ----------------- 123456789012345(1 row)
Thank you, Gary!
To add on to that, if you are using decrypt in a table query, you will have to specifically cast the column to a bytea type. For example, if you have the following:
CREATE TABLE public.test_crypto(id bigint NOT NULL DEFAULT nextval('test_crypto_id_seq'::regclass),plain_text text COLLATE pg_catalog."default",crypted_text text COLLATE pg_catalog."default",CONSTRAINT test_crypto_pkey PRIMARY KEY (id))WITH (OIDS = FALSE)TABLESPACE pg_default;
You can encrypt data like this:
insert into public.test_crypto (plain_text, crypted_text)values ('plaintext', encrypt('plaintext', 'salty', 'aes'))
And decrypt it like this:
select id, plain_text,convert_from(decrypt(crypted_text::bytea, 'salty', 'aes'), 'SQL_ASCII')from test_crypto
If you don't use crypted_text::bytea, the SQL parser will yell at you for not being able to find the function you are talking about 'decrypt'.