postgresql encrypt / decrypt

Question

I'm working on field encrypt / decrypt.

What I choose is

select encrypt('123456789012345','1234','aes');
              encrypt               
------------------------------------
 \x34591627f9c8eae417fc7cbbf458592c
(1 row)

I got my data encrypted though, the other string is there after decrypt like below...

postgres=# select decrypt('\x34591627f9c8eae417fc7cbbf458592c','1234','aes');
             decrypt              
----------------------------------
 \x313233343536373839303132333435
(1 row)

Have I made wrong way? (I know this kind of asking could be stupid... )

What I have to do is just getting a most simple way and encrypted data has small size....

Thanks in advance...

If this is for passwords, don't encrypt them, salt them and one-way hash them. You should not usually need to be able to recover the plaintext of a password, only determine if its hash matches one that was supplied to you. — Craig Ringer
– Craig Ringer, Commented Sep 27, 2012 at 3:37

Gary - Stand with Ukraine · Accepted Answer · 2012-09-26 10:44:13Z

30

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)

Postgresql string functions

edited Sep 26, 2012 at 10:44

answered Sep 26, 2012 at 9:49

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jkilgrow · Accepted Answer · 2018-08-15 02:42:42Z

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'.

Collectives™ on Stack Overflow

postgresql encrypt / decrypt

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related