PostgreSQL Data Encryption (How to Configure Transport and Storage Encryption)

postgresql Data Encryption Configuration

Data encryption is a technology used to protect data security. The Postgres database supports various encryption protocols, including transmission encryption and storage encryption.

Transmission Encryption

Transmission encryption refers to encrypting data when it is sent from the client to the server to prevent man-in-the-middle eavesdropping. To implement transmission encryption, we need to configure the following steps:

• Generate Certificates: Generate certificates for both the Server (peer) and Client (trusted party). These certificates are issued by a CA authority and contain public keys and private keys.
• Configure Transmission Encryption: Enable transmission encryption by setting values for SSLMode and SSLCertFile.

    ALTER SYSTEM SET SSLMode = 'require';
    ALTER SYSTEM SET SSLCertFile = '/path/to/client.crt';
    ALTER SYSTEM SET SSLKeyFile = '/path/to/client.key';

• Verify Certificates: Verify the security of transmission encryption using the pg_stat_ssl system view. It includes information such as the encryption mode used for connections, whether authentication is required, and the current encryption mode.

Storage Encryption

Storage encryption refers to encrypting data when it is saved in a file system to prevent theft or leakage. To implement storage encryption, we can use PostgreSQL's built-in openssl extension.

CREATE EXTENSION IF NOT EXISTS "openssl";

Then we need to create an encryption function, for example:

CREATE OR REPLACE FUNCTION encrypt_data(p_data text)
RETURNS bytea AS $$
DECLARE
    encrypted_data bytea;
BEGIN
    SELECT openssl_encrypt(p_data, 'aes-256-cbc', 
        'your_secret_key_here', 
        128) INTO encrypted_data;
    RETURN encrypted_data;
END;
$$ LANGUAGE plpgsql;

We also need to create a function that can decrypt the data.

CREATE OR REPLACE FUNCTION decrypt_data(p_encrypted_data bytea)
RETURNS text AS $$
DECLARE
    decrypted_text text;
BEGIN
    SELECT openssl_decrypt(p_encrypted_data, 'aes-256-cbc', 
        'your_secret_key_here', 
        128) INTO decrypted_text;
    RETURN decrypted_text;
END;
$$ LANGUAGE plpgsql;

Finally, we need to configure items such as data_directory, ssl, wal_level, and archive_mode in the PostgreSQL configuration to achieve data encryption.

CREATE DATABASE mydatabase WITH ENCRYPTION;

Or

ALTER SYSTEM SET data_directory = '/path/to/mydatadirectory';
ALTER SYSTEM SET ssl = 'on';
ALTER SYSTEM SET wal_level = 'hotstandby';
ALTER SYSTEM SET archive_mode = 'on';

postgresql Example of Encrypting Data

The following is an example using encryption functions to store and transmit data.

-- Generate a random KEY for encryption.
SELECT random_bytes() INTO mykey;
-- Convert mykey to bytea format
SELECT * FROM pg_random_bytes_to_hex(mykey);

-- Generate encrypted text.
CREATE TABLE mytable (
    id serial PRIMARY KEY,
    data text NOT NULL
) WITH (OIDS=FALSE);
INSERT INTO mytable (data)
VALUES ('Hello, world!');

-- Generate an encryption function to encrypt data in mytable
CREATE OR REPLACE FUNCTION encrypt_data(p_data text)
RETURNS bytea AS $$
DECLARE
    encrypted_data bytea;
BEGIN
    SELECT openssl_encrypt(p_data, 'aes-256-cbc', 
        'your_secret_key_here'::bytea, 
        128) INTO encrypted_data;
    RETURN encrypted_data;
END;
$$ LANGUAGE plpgsql;

-- Use the encryption function to encrypt data in mytable
SELECT encrypt_data(data) INTO mydata;

-- Store the encrypted text into mydatabase.
INSERT INTO mytable (data)
VALUES ('Hello, world!');

-- Generate a decryption function to decrypt data in mytable
CREATE OR REPLACE FUNCTION decrypt_data(p_encrypted_data bytea)
RETURNS text AS $$
DECLARE
    decrypted_text text;
BEGIN
    SELECT openssl_decrypt(p_encrypted_data, 'aes-256-cbc', 
        'your_secret_key_here'::bytea, 
        128) INTO decrypted_text;
    RETURN decrypted_text;
END;
$$ LANGUAGE plpgsql;

-- Use the decryption function to decrypt mydata
SELECT decrypt_data(mydata) INTO mydecrypted;

-- In myql, use encrypted data from mytable.
SELECT * FROM mytable WHERE data = 'mydecrypted';

If you need to ensure your data security, you can use PostgreSQL's openssl extension for encryption.