Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

postgresql encrypt / decrypt

Tags:

postgresql

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

like image 406
KIM Avatar asked Sep 26 '12 09:09

KIM


People also ask

Does Postgres encrypt?

PostgreSQL offers encryption at several levels, and provides flexibility in protecting data from disclosure due to database server theft, unscrupulous administrators, and insecure networks. Encryption might also be required to secure sensitive data such as medical records or financial transactions.

How do I encrypt passwords with PostgreSQL?

When creating a new user, we can use the crypt function to encrypt the password. INSERT INTO users (email, password) VALUES ( '[email protected]', crypt('johnspassword', gen_salt('bf')) ); The crypt function accepts two arguments: The password to encrypt.

Is Postgres data encrypted at rest?

While there are options such as Crunchy Hardened PostgreSQL that offer TDE solutions, you can still encrypt your PostgreSQL data at rest today by doing so at the disk level. In a Kubernetes environment, this is done by using a storage class that supports encryption.

Does PostgreSQL have TDE?

Transparent Data Encryption (TDE) is a CYBERTEC encryption patch for PostgreSQL. It is currently the only implementation that supports transparent and cryptographically safe data (cluster) level encryption, independent of operating system or file system encryption.


2 Answers

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

like image 108
Gary Avatar answered Oct 20 '22 00:10

Gary


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

like image 33
jkilgrow Avatar answered Oct 20 '22 02:10

jkilgrow