Data encryption

Andrey Aksenov

Contents

Overview
Register pgcrypto
General hashing functions
- Example: verify data integrity
Password hashing functions
- Example: verify password
PGP symmetric encryption
- Example: encrypt columns with symmetric keys
PGP public-key encryption
- Example: encrypt columns with public-key cryptography
- Generate PGP key pairs with GnuPG
Reference
- pgcrypto functions
- Options for PGP functions

The pgcrypto module provides cryptographic functions for Greengage DB, allowing you to store selected table columns in encrypted form. Column-level encryption adds an extra layer of protection for sensitive data: values cannot be read without the correct encryption key and remain unreadable even if accessed directly from disk.

Security note

The examples in this topic are for demonstration purposes only. They use hardcoded passwords, encryption keys, and other sensitive information for clarity.

Do not use hardcoded secrets in production. In production environments, always follow secure practices:

Store secrets in dedicated secret management systems (for example, HashiCorp Vault).
Use environment variables or secured configuration files with restricted access.
Implement proper key rotation and strong password policies.
Ensure private keys are never committed to version control or embedded in application code.

Following these practices helps protect sensitive data and prevent accidental exposure.

Prerequisites

To follow the examples in this topic, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql. Create a new database and connect to it:

CREATE DATABASE marketplace;
\c marketplace

The table below lists the cryptographic algorithms and features supported by pgcrypto with built-in providers and with OpenSSL.

Functionality	Built-in	With OpenSSL
MD5	yes	yes
SHA-1	yes	yes
SHA-224/256/384/512	yes	yes
Other digest algorithms	no	yes (see the note below)
Blowfish	yes	yes
AES	yes	yes
DES/3DES/CAST5	no	yes
Raw encryption	yes	yes
PGP symmetric encryption	yes	yes
PGP public-key encryption	yes	yes

Functionality

Built-in

With OpenSSL

MD5

yes

SHA-1

yes

SHA-224/256/384/512

yes

Other digest algorithms

yes (see the note below)

Blowfish

yes

AES

yes

DES/3DES/CAST5

yes

Raw encryption

yes

PGP symmetric encryption

yes

PGP public-key encryption

yes

NOTE

Any digest algorithm supported by OpenSSL is automatically available in pgcrypto.

All pgcrypto functions execute on the database server. Data and passwords are transmitted in plaintext between client applications and the server unless the connection is encrypted. For this reason, use SSL connections or connect locally. Alternatively, perform encryption and decryption directly within the client application.

Data protected with pgcrypto may be vulnerable to side-channel attacks. This does not imply that the ciphertext itself is weak. The risk arises from the execution of cryptographic operations, such as decryption, where runtime characteristics may vary depending on the input data. For example, the time required for a pgcrypto decryption function to complete varies among ciphertexts of a given size.

pgcrypto functions follow common SQL behavior: they return NULL if any argument is NULL. Careless use of these functions may therefore introduce security risks.

Encryption introduces additional processing overhead and can affect database performance. For this reason, avoid encrypting all data unnecessarily. It is generally recommended to encrypt only small amounts of highly sensitive data that is accessed frequently, such as credit card numbers or social security numbers.

Large data values should be encrypted only when they are accessed infrequently, where the security benefits outweigh the performance cost.

Connect to the target database and register the pgcrypto extension:

CREATE EXTENSION pgcrypto;

General hashing functions are used to verify data integrity and authenticate messages. They allow you to generate a fixed-size representation of data that cannot be reversed to reveal the original value. Common use cases include detecting data modifications and creating hash-based message authentication codes (HMAC).

The digest() function generates one-way hashes for arbitrary data. When Greengage DB is compiled with the --with-openssl option, a wider range of hashing algorithms becomes available.

The hmac() function uses a secret key with a hash to protect data integrity and authenticity. It allows recipients to verify that a message has not been modified and originates from a trusted sender.

The following example generates a SHA-256 hash of a string using digest():

SELECT digest('Top secret', 'sha256');

The result looks like this:

                               digest
--------------------------------------------------------------------
 \xad8b354a65533f41288aaabf615826869c70b42815c387899130462ef6068b92
(1 row)

In this example, a SHA-384 hash of the same string is generated:

SELECT digest('Top secret', 'sha384');

The result:

                                               digest
----------------------------------------------------------------------------------------------------
 \x477187c44fd737c2599da5550b3ebe9e9b0a519f8ed7de1718167b0ca129dd2696385f3fb834dd0b259f383681b0a77f
(1 row)

This command generates an HMAC using a secret key:

SELECT hmac('Top secret', 'secret_key', 'sha256');

The binary output of digest() can be converted to hexadecimal format using encode():

SELECT encode(digest('Top secret', 'sha256'), 'hex') AS hash;

The result looks as follows:

                               hash
------------------------------------------------------------------
 ad8b354a65533f41288aaabf615826869c70b42815c387899130462ef6068b92
(1 row)

This example demonstrates how to use digest() to detect whether sensitive data has been altered. A SHA-256 hash of each personal_id is stored and later compared to the current value to verify integrity.

Create a table to store customer data along with a hash of their personal IDs:

CREATE TABLE customers
(
    customer_id      INTEGER,
    name             TEXT,
    personal_id      TEXT,
    personal_id_hash BYTEA
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

Insert sample data, computing the hash for each personal_id:

INSERT INTO customers (customer_id, name, personal_id, personal_id_hash)
VALUES (1, 'Andrew Fuller', 'ID-001-AF', digest('ID-001-AF', 'sha256')),
       (2, 'Michael Suyama', 'ID-002-MS', digest('ID-002-MS', 'sha256')),
       (3, 'Laura Callahan', 'ID-003-LC', digest('ID-003-LC', 'sha256'));

Verify whether the stored data matches the hash:

SELECT name,
       personal_id,
       personal_id_hash = digest(personal_id, 'sha256') AS is_intact
FROM customers;

All rows should be intact initially:

      name      | personal_id | is_intact
----------------+-------------+-----------
 Laura Callahan | ID-003-LC   | t
 Andrew Fuller  | ID-001-AF   | t
 Michael Suyama | ID-002-MS   | t
(3 rows)

Simulate data corruption by updating a personal ID:

UPDATE customers
SET personal_id = 'CORRUPTED'
WHERE customer_id = 2;

Verify data integrity again to detect the change:

SELECT name,
       personal_id,
       personal_id_hash = digest(personal_id, 'sha256') AS is_intact
FROM customers;

The result:

      name      | personal_id | is_intact
----------------+-------------+-----------
 Laura Callahan | ID-003-LC   | t
 Andrew Fuller  | ID-001-AF   | t
 Michael Suyama | CORRUPTED   | f
(3 rows)

Password hashing functions are used to securely store user passwords in a database. Instead of storing passwords in plain text, a hash of the password is saved, making it computationally infeasible to recover the original password even if the database is compromised.

The crypt() function is specifically designed for password hashing. It includes the salt parameter to ensure that each password generates a unique hash, even if two users have the same password. The gen_salt() function is used to generate this salt.

This command generates a hashed password using crypt() with a generated salt:

SELECT crypt('top_secret_123', gen_salt('bf'));

The result shows the hashed password:

                            crypt
--------------------------------------------------------------
 $2a$06$uJlom0lne.dUlBhX7Z8oSOsoto5eMcft9xWMVhZeHaoSoEgScBERG
(1 row)

This example demonstrates how to store hashed passwords and verify them using crypt() and gen_salt(). The password hashes are stored in the database, and authentication is performed by comparing the stored hash with the result of crypt() applied to the input password.

Create a table to store users and their hashed passwords:

CREATE TABLE users
(
    user_id       SERIAL,
    username      TEXT,
    password_hash TEXT
)
    DISTRIBUTED BY (user_id);

Insert sample users, hashing their passwords with Blowfish (bf) and a generated salt:

INSERT INTO users (username, password_hash)
VALUES ('alice', crypt('alice_secret', gen_salt('bf'))),
       ('bob', crypt('bob_secret', gen_salt('bf'))),
       ('carol', crypt('carol_secret', gen_salt('bf')));

Check authentication for a correct password:

SELECT username,
       (password_hash = crypt('alice_secret', password_hash)) AS is_authenticated
FROM users
WHERE username = 'alice';

Result — password matches:

 username | is_authenticated
----------+------------------
 alice    | t
(1 row)

Check authentication for an incorrect password:

SELECT username,
       (password_hash = crypt('wrong_password', password_hash)) AS is_authenticated
FROM users
WHERE username = 'alice';

Result — password does not match:

 username | is_authenticated
----------+------------------
 alice    | f
(1 row)

PGP symmetric encryption allows data to be securely encrypted using a shared password. The password is processed using a String-to-Key (S2K) algorithm to produce a binary key, which is then used to encrypt data.

In this example, pgp_sym_encrypt() is used to encrypt a message using a password:

SELECT pgp_sym_encrypt(
               'some data to encrypt',
               'top_secret_key'
       ) AS encrypted_message;

You can also encrypt a message with additional options, such as a specific cipher, compression, or Unicode handling:

SELECT pgp_sym_encrypt(
               'some data to encrypt',
               'top_secret_key',
               'cipher-algo=aes256, compress-algo=1, unicode-mode=1'
       ) AS encrypted_message_with_options;

This example demonstrates how to encrypt sensitive data in a table using pgp_sym_encrypt() and decrypt it using pgp_sym_decrypt().

Create a table to store customer data and the encrypted personal IDs:

CREATE TABLE customers
(
    customer_id INTEGER,
    name        TEXT,
    personal_id BYTEA
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

Set a secret key to be used for encryption and decryption:

\set secret_key 'top_secret_123'

Insert customer data, encrypting the personal_id column with the secret key:

INSERT INTO customers (customer_id, name, personal_id)
VALUES (1, 'Andrew Fuller', pgp_sym_encrypt('ID-001-AF', :'secret_key')),
       (2, 'Michael Suyama', pgp_sym_encrypt('ID-002-MS', :'secret_key')),
       (3, 'Laura Callahan', pgp_sym_encrypt('ID-003-LC', :'secret_key'));

Select the raw encrypted data to show how it appears in the table:

SELECT name,
       personal_id
FROM customers;

The result:

      name      |                                                                       personal_id
----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------
 Laura Callahan | \xc30d040703028df998cc672875417fd23a0140342d5d9d0ff2b009502c9afff26256f48b2af213230a7173533f8cd91181794b9c70c065c2877064cbc6e51a0958ac21463881e619ad986d
 Andrew Fuller  | \xc30d04070302cc4a79bf1b48614972d23a017261253469237edb8598d1c8df0e7c2895b268ea6eace7f38dd159740616cafa9d15981e6c22751c9ac3196c19deb1eb3c8cd6d1ba7ef7b076
 Michael Suyama | \xc30d04070302e97127260b161f4062d23a01c412858005b76d946c82f9663c9a1b2b6cdadb72a9876ca01fce4f0987c9a5d0039b493e8c21dc8218ff2a33f3231a5cbdf9cf3176eeb0ff82
(3 rows)

Decrypt the personal_id column to view the original data:

SELECT name,
       pgp_sym_decrypt(personal_id, :'secret_key') AS personal_id
FROM customers;

The result:

      name      | personal_id
----------------+-------------
 Michael Suyama | ID-002-MS
 Andrew Fuller  | ID-001-AF
 Laura Callahan | ID-003-LC
(3 rows)

PGP public-key encryption allows data to be securely encrypted using a recipient’s public key so that only the holder of the corresponding private key can decrypt it.

You can encrypt a message using a recipient’s public key with pgp_pub_encrypt():

SELECT pgp_pub_encrypt(
    'Top secret',
    dearmor('-----BEGIN PGP PUBLIC KEY BLOCK----- <public_key> -----END PGP PUBLIC KEY BLOCK-----')
);

To decrypt the message, provide the corresponding private key to pgp_pub_decrypt():

SELECT pgp_pub_decrypt(
    data::bytea,
    dearmor('-----BEGIN PGP PRIVATE KEY BLOCK----- <private_key> -----END PGP PRIVATE KEY BLOCK-----')
);

The armor() function is useful for importing and exporting keys or messages in a text-based format.

This example demonstrates how to store data encrypted with a recipient’s public key and decrypt it using the corresponding private key. The data is stored in the database as a BYTEA column and can only be decrypted by the holder of the private key.

Create a table to store customer data and the encrypted personal IDs:

CREATE TABLE customers
(
    customer_id INTEGER,
    name        TEXT,
    personal_id BYTEA
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

Load the public and private PGP keys from files into psql variables. In this example, the key files are stored in the examples/data_encryption/certs directory:

\set public_key `cat examples/data_encryption/certs/public.key`
\set private_key `cat examples/data_encryption/certs/secret.key`

TIP

For instructions on how to generate PGP keys, see Generate PGP key pairs with GnuPG.

Insert customer data, encrypting the personal_id column with the public key:

INSERT INTO customers (customer_id, name, personal_id)
VALUES (1, 'Andrew Fuller', pgp_pub_encrypt('ID-001-AF', dearmor(:'public_key'))),
       (2, 'Michael Suyama', pgp_pub_encrypt('ID-002-MS', dearmor(:'public_key'))),
       (3, 'Laura Callahan', pgp_pub_encrypt('ID-003-LC', dearmor(:'public_key')));

Decrypt the personal_id column using the corresponding private key to view the original data:

SELECT name,
       pgp_pub_decrypt(personal_id, dearmor(:'private_key'), 'top_secret_123') AS personal_id
FROM customers;

The result:

      name      | personal_id
----------------+-------------
 Andrew Fuller  | ID-001-AF
 Michael Suyama | ID-002-MS
 Laura Callahan | ID-003-LC
(3 rows)

You can use the GnuPG tool to generate PGP keys.

Generate a new PGP key pair:

$ gpg --full-generate-key

At the prompt, select the key type, key size, expiration period, and user identity. You will also be asked to define a secure passphrase. This passphrase is later used as the psw argument of the pgp_pub_decrypt() function.

List available secret keys:

$ gpg --list-secret-keys

Example output:

sec   dsa2048 2026-02-16 [SC]
      446049EA9E873DB8A526437CAD2C1899311684E7
uid           [ultimate] Greengage DB User <example@greengagedb.org>
ssb   elg2048 2026-02-16 [E]

Export the public key:

$ gpg -a --export <KEYID> > public.key

In the example above, KEYID is 446049EA9E873DB8A526437CAD2C1899311684E7.

Export the private key:

$ gpg -a --export-secret-keys <KEYID> > secret.key

NOTE

This section does not include the raw encryption functions (encrypt(), decrypt(), encrypt_iv(), decrypt_iv()), which operate directly on ciphers but lack integrity checks, automatic padding, and text handling. PGP‑based encryption is preferred over these raw functions. For details on the raw encryption functions, see the pgcrypto section in the PostgreSQL documentation.

digest(data text, type text) returns bytea
digest(data bytea, type text) returns bytea: Computes a binary hash value for the given input data. The type parameter specifies the hash algorithm to use (see Supported algorithms). Common algorithms include md5, sha1, sha224, sha256, sha384, and sha512. If pgcrypto was built with OpenSSL support, additional algorithms are available.

See also: General hashing functions.

hmac(data text, key text, type text) returns bytea
hmac(data bytea, key bytea, type text) returns bytea: Computes a keyed hash (HMAC) of the given data using the specified secret key. The type parameter specifies the hash algorithm to use, the same as for digest().

Unlike digest(), the hash value can only be recomputed by someone who knows the key. This protects against tampering where both the data and its hash could otherwise be modified together.

If the key is longer than the hash algorithm’s block size, it is first hashed, and the result is used as the effective key.

See also: General hashing functions.

crypt(password text, salt text) returns text

Computes a crypt(3)-style hash of the given password. When storing a new password, use gen_salt() to generate a new random salt value. To verify a password, pass the stored hash as the salt parameter and compare the result with the stored value.

The table below lists the algorithms supported by the crypt() function.

Algorithm	Max password length	Adaptive	Salt bits	Output length	Description
bf	72	yes	128	60	Blowfish-based, variant 2a
md5	unlimited	no	48	34	MD5-based crypt
xdes	8	yes	24	20	Extended DES
des	8	no	12	13	Original UNIX crypt

Data encryption

Overview

Supported algorithms

Security limitations

Performance considerations

Register pgcrypto

General hashing functions

Example: verify data integrity

Password hashing functions

Example: verify password

PGP symmetric encryption

Example: encrypt columns with symmetric keys

PGP public-key encryption

Example: encrypt columns with public-key cryptography

Generate PGP key pairs with GnuPG

Reference

pgcrypto functions

Options for PGP functions

Algorithm	Default	Min	Max
xdes	725	1	16777215
bf	6	4	31