how to encrypt integer and date time data type in postgresql using pgcrypto

Question

Here is my code to encrypt the column:

UPDATE users 
SET  (userid, modifieddate) = 
(
    PGP_SYM_ENCRYPT('0', 'AES_KEY'),
    PGP_SYM_ENCRYPT('2018-06-19 08:40:23', 'AES_KEY')
) 
WHERE id='3';

but its throwing error:

column "userid" is of type integer but expression is of type byte

C.C. Hsu · Accepted Answer · 2019-01-01 06:39:47Z

1

Your table columns users.userid and users.modifieddate should be type BYTEA, because pgcrypto module generates encrypted result with BYTEA type.

answered Jan 1, 2019 at 6:39

C.C. Hsu

3091 silver badge3 bronze badges

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

2 Comments

adams Over a year ago

i cannot alter my table data types

Gadhia Reema Over a year ago

@adams I am facing a similar issue, did you find a solution such that you can encrypt numeric data and persist the datatype as well?

SABER · Accepted Answer · 2019-01-01 09:28:02Z

0

You cannot encrypt only one user's id and put rest of it as it is , if you want to achieve that you need to alter table as mention by @C.C. Hsu , i.e. to converting data type of userid , modifieddate into bytea or text which is capable of storing both encrypted data and your normal data.

alter table users alter column userid type bytea using PGP_SYM_ENCRYPT(userid::text, 'AES_KEY');
alter table users alter column modifieddate type bytea using PGP_SYM_ENCRYPT(modifieddate::text, 'AES_KEY');

answered Jan 1, 2019 at 9:28

SABER

4737 silver badges17 bronze badges

Collectives™ on Stack Overflow

how to encrypt integer and date time data type in postgresql using pgcrypto

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related