column_encrypt

Overview

fixed encrypt schema; create schema encrypt before CREATE EXTENSION; preload column_encrypt;

Version

Build

You can build the RPM / DEB packages for column_encrypt using pig build:

pig build pkg column_encrypt         # build RPM / DEB packages

Install

You can install column_encrypt directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install column_encrypt;          # Install for current active PG version

pig ext install -y column_encrypt -v 18  # PG 18
pig ext install -y column_encrypt -v 17  # PG 17
pig ext install -y column_encrypt -v 16  # PG 16
pig ext install -y column_encrypt -v 15  # PG 15
pig ext install -y column_encrypt -v 14  # PG 14

dnf install -y column_encrypt_18       # PG 18
dnf install -y column_encrypt_17       # PG 17
dnf install -y column_encrypt_16       # PG 16
dnf install -y column_encrypt_15       # PG 15
dnf install -y column_encrypt_14       # PG 14

apt install -y postgresql-18-column-encrypt   # PG 18
apt install -y postgresql-17-column-encrypt   # PG 17
apt install -y postgresql-16-column-encrypt   # PG 16
apt install -y postgresql-15-column-encrypt   # PG 15
apt install -y postgresql-14-column-encrypt   # PG 14

Preload:

shared_preload_libraries = 'column_encrypt';

Create Extension:

CREATE EXTENSION column_encrypt CASCADE;  -- requires: pgcrypto

Usage

Sources: README, v4.0 release, SQL objects

column_encrypt provides transparent column-level encryption for PostgreSQL. It defines encrypted_text and encrypted_bytea types, encrypts values through type input functions, decrypts through output functions, and manages data-encryption keys through the encrypt schema.

Enable

Load the shared library at server start, restart PostgreSQL, then create the schema and extension:

shared_preload_libraries = 'column_encrypt'

CREATE EXTENSION pgcrypto;
CREATE SCHEMA IF NOT EXISTS encrypt;
CREATE EXTENSION column_encrypt;

Add encrypt to search_path or schema-qualify the encrypted types and functions.

Register And Load Keys

SELECT encrypt.register_key('my-secret-data-key', 'my-master-passphrase');
SELECT encrypt.load_key('my-master-passphrase');

SELECT * FROM encrypt.keys();
SELECT * FROM encrypt.status();

The extension uses a two-tier key model with key-encryption keys and data-encryption keys. Ciphertext carries a key-version header so older values can still be decrypted after rotation.

Encrypt Columns

CREATE TABLE secure_data (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ssn encrypt.encrypted_text,
  payload encrypt.encrypted_bytea
);

INSERT INTO secure_data (ssn, payload)
VALUES ('888-999-2045', decode('aabbcc', 'hex'));

SELECT id, ssn FROM secure_data;

Without a loaded key, decrypting encrypted values raises an error.

Key Operations

Common functions include encrypt.activate_key, encrypt.revoke_key, encrypt.rotate, encrypt.verify, encrypt.unload_key, encrypt.loaded_cipher_key_versions, and encrypt.blind_index.

Use blind indexes for lookup patterns that cannot expose plaintext values directly:

SELECT encrypt.blind_index('888-999-2045', 'lookup-hmac-key');

Notes

The extension intentionally rejects binary send/receive for encrypted values. Equality and hash semantics are based on decrypted plaintext; range ordering is not supported. After upgrading from older ciphertext-hash behavior, rebuild hash indexes on encrypted columns.