Database column encryption postgres

Question

How to encrypt column in postgres database using pgcrypto addon ?

I am using postgres 9.3 and i need to encrypt one of my column , does postgres also support Aes encryption or by any mean i can achieve it ?

Spitball

You also need to consider which servers are allowed to see the decryption key. If you have the key in the same place as encrypted data then, well, it doesn't improve the security by much. – Dement

Answer 1

Yes, Postgres pgcrypto module does support AES. All details with examples can be found here. As for the sample usage:

-- add extension
CREATE EXTENSION pgcrypto;

-- sample DDL
CREATE TABLE test_encrypt(
  value TEXT
);
INSERT INTO test_encrypt VALUES ('testvalue');

-- encrypt value
WITH encrypted_data AS (
  SELECT crypt('PasswordToEncrypt0',gen_salt('md5')) as hashed_value
)
UPDATE test_encrypt SET value = (SELECT hashed_value FROM encrypted_data);

Validate password:

SELECT (value = crypt('PasswordToEncrypt0', value)) AS match FROM test_encrypt;

Returns:

 match 
-------
 t
(1 row)

Reginaldreginauld

This means that i need to make changes in my application code too , is it possible to achieve it without making any changes at application level. – Spitball

@Spitball - well, it's hard to tell you due to the fact that I have not seen your auth implementation.. – Reginaldreginauld

This is not (column) encryption. Encryption is reversible, and hashing is not. Example in SQL Server learn.microsoft.com/en-us/sql/relational-databases/security/… – Haha

@Henrik, very good feature, I didn't know that.. Have you found such feature for Postgre? If so, let me know please... thanks – Phono

Answer 2

The above is not encryption as encryption is reversible which means if you encrypt some secret text or value, you should be able to know what that secret value or text was unlike hashing where you want to verify if the user-provided value matches the hashed value or not.

This is how you would encrypt column data using pgcrypto module.

create extension if not exists pgcrypto; -- this will install the module if not installed 

CREATE TABLE agents (
id serial primary key,
name varchar not null
);
 
INSERT INTO agents (name) values
(pgp_sym_encrypt('Johny Smith', 'longsecretencryptionkey')),
(pgp_sym_encrypt('Bob Marley', 'longsecretencryptionkey'));

longsecretencryptionkey 

is your encryption key. You can generate encryption key from here encryption key generator and choose the bit of your choice. The recommendation would be to choose min 256 bit.

Remember to keep the encryption key somewhere safe saved somewhere. If you lose your encryption key, you will not be able to decrypt it anymore. This is very crucial to understand.

This is how you would query them

SELECT pgp_sym_decrypt(name::bytea, 'longsecretencryptionkey') FROM users WHERE pgp_sym_decrypt(name::bytea, 'longsecretencryptionkey') ILIKE 'johny%'; -- querying for agents whose name start with johny

You can checkout this blog article which helped me https://blog.andreiavram.ro/encrypt-postgresql-column/

Note: Instead of keeping the name column in varchar, you can use bytea data type for the column. So the table becomes

CREATE TABLE agents (
id serial primary key,
name bytea not null
);

So, when querying you don't need to cast every time, you can do just this

SELECT pgp_sym_decrypt(name, 'longsecretencryptionkey') FROM users WHERE pgp_sym_decrypt(name::bytea, 'longsecretencryptionkey') ILIKE 'johny%'; -- querying for agents whose name start with johny

Tetrastichous

This type of encryption is pointless. Why? First of all it is some sort of a client side encryption which relies on a symmetric algorithm. I won't talk about the client side aspect but the symmetric algorithm has one big security flaw since your are dealing with a database. The client needs to know the symmetric key to encrypt data which is used for both and encryption. This means that if a hacker steals your data, it will be extremely easy for the hacker to extract the key from the client application wherever it runs. I know everyone thinks asymmetric algorithms are not suitable and are .... – Barrybarrymore

only good for key exchange scenarios. But if you use RSA in this case since the column has a length, then the public key will be only known to the client side, and the private key can be fully kept secret elsewhere. Then any comparison can be done by comparing hashes of the encrypted data. It is a little troublesome, but your data will be completely safe at rest. – Barrybarrymore

Answer 3

edit queue is full,
just put some description about crypt here:

一. crypt()

Password Hashing Functions
https://www.postgresql.org/docs/current/pgcrypto.html#id-1.11.7.35.7

The functions crypt() and gen_salt() are specifically designed for hashing passwords.
crypt() does the hashing and gen_salt() prepares algorithm parameters for it.

crypt(password text, salt text) returns text

Calculates a crypt(3)-style hash of password. When storing a new password, you need to use gen_salt() to generate a new salt value. To check a password, pass the stored hash value as salt, and test whether the result matches the stored value.

man 3 crypt
https://manpages.ubuntu.com/manpages/jammy/en/man3/crypt.3.html

The crypt, crypt_r, crypt_rn, and crypt_ra functions irreversibly “hash” phrase for storage in the system password database (shadow(5)) using a cryptographic “hashing method.”

二. pgp_sym_encrypt

PGP Encryption Functions
https://www.postgresql.org/docs/current/pgcrypto.html#id-1.11.7.35.8

The functions here implement the encryption part of the OpenPGP (RFC 4880) standard. Supported are both symmetric-key and public-key encryption.

pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea

Encrypt data with a symmetric PGP key psw.

pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text

Decrypt a symmetric-key-encrypted PGP message.

Still