Asymmetric and symmetric keys in PostgreSQL

A little about asymmetric and symmetric encryption

Asymmetric encryption uses a key pair to encrypt and decrypt data: public and private keys.

• Public Key: used to encrypt data and is available to all users.

• Private key: used to decrypt data and is kept safe by the owner. This key must not be disclosed to another user.

The encryption process using asymmetric encryption is as follows: the sender uses the recipient's public key to encrypt the message, and then the recipient uses their private key to decrypt the message. That is, even if the public key is known to everyone, the data remains protected because the private key is required to decrypt it.

And int symmetric encryption uses the same key to encrypt and decrypt data. This key must be available to both the sender and the recipient of the message.

The encryption process using symmetric encryption is simpler: the sender and recipient both use the same key to encrypt and decrypt data. But here it is important to ensure the secure distribution and storage of this key.

Symmetric key encryption in PostgreSQL

First, you need to create a symmetric key and then you can use it to encrypt and decrypt data.

To create a symmetric key in PostgreSQL, the module is often used pgcrypto, which provides functions for working with cryptography. An example of creating a symmetric key using pgcrypto:

-- создание симметричного ключа
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- генерация симметричного ключа
SELECT gen_random_bytes(16) AS symmetric_key;

The code creates a 16-byte symmetric key that will be used to encrypt and decrypt data.

After creating the key, you can begin encrypting the data. For this purpose the functions are used pgcrypto:

-- шифрование данных с использованием симметричного ключа
SELECT pgp_sym_encrypt('Secret message', 'AES_KEY');

-- дешифрование данных с использованием симметричного ключа
SELECT pgp_sym_decrypt('Encrypted message', 'AES_KEY');

We encrypt the message “Secret message” using a symmetric key AES_KEYand then decrypt it back.

For example, you need to store confidential information about users in the database, for example, their passwords:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    encrypted_password TEXT
);

-- вставка данных с зашифрованным паролем
INSERT INTO users (username, encrypted_password)
VALUES ('user1', pgp_sym_encrypt('password123', 'AES_KEY'));

When a new user is inserted, we encrypt their password using a symmetric key AES_KEY.

Encryption with asymmetric keys in PostgreSQL

The first step in such encryption is the creation of a pair of keys: public and private.

To generate a key pair in PostgreSQL, you can use the function pgcrypto.gen_rsa_keypairwhich creates an RSA key pair:

-- создание пары ключей RSA
SELECT pgp_pub_encrypt('Super secret data', rsa_pub) AS encrypted_data
FROM (SELECT pgcrypto.gen_rsa_keypair(2048) AS rsa_pub) AS keys;

The request creates a new RSA key pair and encrypts the string Super secret data using a public key.

Once the key pair has been generated, the public key can be used to encrypt the data. To do this, they often use the function pgp_pub_encrypt:

SELECT pgp_pub_encrypt('Super secret data', rsa_pub) AS encrypted_data
FROM (SELECT '-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: PGP ..

mQENBE+zv4UBCAC2Xljsn1FTmLX2zW/KG1QVpukDtJZIKAFZlCz8PrQkg/hfpxiH
d4dgbUzVWg3Ew0yGmLoImf+PQK7Cf5Nvl5feoX9P9pUJ4FZfSYBF5FjS2zlbjcG+
q2K1L9zP7vIlpZ5OYd9r3HyLhKThyP2g95DWf9INtC7nFpT8Uw9+7HK20W/buyPj
...
-----END PGP PUBLIC KEY BLOCK-----' AS rsa_pub) AS keys;

The request encrypts the string “Super secret data” using an RSA public key.

To decrypt data encrypted with asymmetric keys, the corresponding private key is used. There is a function for thispgp_priv_decrypt:

SELECT pgp_priv_decrypt(encrypted_data, rsa_priv) AS decrypted_data
FROM (SELECT pgp_pub_encrypt('Super secret data', rsa_pub) AS encrypted_data,
             pgcrypto.gen_rsa_keypair(2048) AS rsa_priv) AS keys;

This request encrypts the string “Super secret data” using the RSA public key and then decrypts it using the corresponding private key.

For example, you need to ensure the secure transfer of confidential information between two users:

CREATE TABLE encrypted_messages (
    id SERIAL PRIMARY KEY,
    sender_username VARCHAR(50),
    recipient_username VARCHAR(50),
    encrypted_data TEXT
);

-- шифрование и сохранение сообщения для отправителя
INSERT INTO encrypted_messages (sender_username, recipient_username, encrypted_data)
VALUES ('sender', 'recipient', pgp_pub_encrypt('Secret message', rsa_pub));

When we insert a new message, we encrypt it using the recipient's public key.

Comparison

So, use asymmetric keys when you need to exchange data between different systems or users, and when you need to provide a greater degree of security through a public/private key. But in cases where data is stored and processed within the same system or application, symmetric keys may be preferable due to their ease of manageability and higher performance.

And more practical skills can traditionally be obtained within practical online courses from leading market experts.