Encrypt a column using PostgreSQL

Question

I want to encrypt a column using PostgreSQL. But I don't want the data in DB/Table to be encrypted. While querying/extracting the data from DB, I just want 'adsid' column to be encrypted in the output. Data in 'adsid' column is like a123456-111-dd43-123r-xdf1we3456z999. How can I do that?

You can do it within Postgres with pgcrypto. Or you can do it in your application using whatever encryption functions your programming language and framework provides. — Schwern
– Schwern, Commented Feb 24, 2021 at 19:21
See "Column-level encryption" in this blog post. enterprisedb.com/postgres-tutorials/… — Schwern
– Schwern, Commented Feb 25, 2021 at 7:21

Mahesh G · Accepted Answer · 2021-02-27 12:30:51Z

2

CREATE EXTENSION pgcrypto;

SELECT 'column_name', crypt('column_name', gen_salt('md5')) AS hashed_value
FROM 'table_name';

Also, you can encrypt using 'sha1' instead of 'md5' as well. It is as below:

SELECT 'column_name', encode(digest("column_name",'sha1'),'hex') AS hashed_value
FROM 'table_name';

edited Feb 27, 2021 at 12:30

user330315

answered Feb 27, 2021 at 12:19

Mahesh G

358 bronze badges

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

1 Comment

yurenchen Over a year ago

have to say, crypt() is something like man 3 crypt: irreversibly “hash” phrase

Collectives™ on Stack Overflow

Encrypt a column using PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related