Appendix A: Column-level encryption with pgcrypto

This appendix details the implementation of column-level encryption within PostgreSQL databases using the pgcrypto extension. When paired with Transparent Data Protection (TDP), this approach allows for granular security controls where specific database directories can be selectively encrypted. By combining these complementary encryption methodologies, you can establish a multi-layered defense strategy that protects sensitive data both at the application level and within the physical storage architecture.

Sample operations and tasks

This section provides instructions to complete some common pgcrypto tasks.

Connect to PostgreSQL

Perform the following steps to connect to PostgreSQL with PGAdmin:

Launch the PGAdmin application.

To add a server connection, perform the following steps:

Right-click Servers in the browser panel.
Select Register > Server.

Enter the following configuration details:

On the General tab: Enter a name for the connection
In the Connection tab, fill in:
Host: Server IP or hostname (use “localhost” for local connections)
Port: 5432 (default)
Username: Enter postgres (or your specific username)
Password: Your password

Select [ Save ].

Create a database

Perform the following steps to create a new database:

Right-click Databases under your server.

Select Create > Database.

Enter a name for your database (such as TestDB).

Select [ Save ].

Enable pgcrypto

The pgcrypto extension enables you to use cryptographic functions like pgp_sym_encrypt, pgp_sym_decrypt, crypt, and others in your PostgreSQL instance. Perform the following steps to enable pgcrypto:

To open the SQL Query tool, right-click on your new database name and select Query Tool.

To enable the pgcrypto extension, in the query window, run the following command: CREATE EXTENSION pgcrypto;

Select [ Execute/Refresh ].

You should see a Query returned successfully message.

To verify the installation, run the following query:SELECT * FROM pg_extension WHERE extname = 'pgcrypto';

A row with pgcrypto details confirms successful installation.

Encrypt at the column level

Run the following SQL query to create a sample table and encrypt all data stored in the pgp_sym_encrypt column:

CREATE TABLE sensitive_data (
      id serial PRIMARY KEY,
      name text,
      ssn bytea
  );

  INSERT INTO sensitive_data (name, ssn)
  VALUES
  ('John Doe', pgp_sym_encrypt('123-45-6789', 'encryption_key'));

View unencrypted data

To view the information in the table unencrypted, without column-level encryption applied, run the following command:

SELECT name, pgp_sym_decrypt(ssn, 'encryption_key') FROM sensitive_data;

View encrypted data

To see the table with column-level encrypted data, run the following command:

SELECT id, name, ssn
  FROM sensitive_data;

View encrypted data in hex

To see column-level encrypted data in binary or hexadecimal format, run the following command:

SELECT id, name, encode(ssn, 'hex') AS encrypted_ssn
  FROM sensitive_data;

Find the DB files

Perform the following steps to see where your physical database files are stored:

Run the following query:

SELECT oid, datname FROM pg_database WHERE datname = 'TestDB';

This displays the OID for the TestDB files (such as oid = 16387).

Now, go to the directory where your PostgreSQL is installed (such as C:\PostgreSQL\data\base\) and find the folder marked with the same OID to find the database files.

Add a new path rule inside your PostgreSQL service on the CryptoHub

The following steps apply the Futurex TDP solution on these files to ensure they are encrypted on the disk, with the symmetric key stored on the Cryptohub appliance:

Go to the Path Rules menu in your PostgreSQL service.

Select [ Add Path Rule ].

Enter Active Directory, individual group, or user names.

Add an Advanced Path Rule to where the database files are stored for the TestDB you created. For example: C:\PostgreSQL\data\base\16387\**

This rule encrypts all unstructured data in the folder.

The files in the directory C:\PostgreSQL\data\base\16387 (or similar directories) include the actual data of your PostgreSQL database. These files store the contents of tables, indexes, and other database objects in a binary format optimized for the PostgreSQL storage engine.

Summary of the Symmetric Keys used for these operations

The symmetric key inpgcrypto is derived from the passphrase you provide. The key is typically an AES key (usually 256-bit, but it depends on the implementation). The key is used only in memory for encryption and is not stored. The symmetric key algorithm and length depend on the encryption function, but this information is not stored in the database. Rather, it is used temporarily during the encryption or decryption process.

Encryption with a key stored in a text file

You can create a database on PostgreSQL by using the following SQL query:

CREATE DATABASE encrypted_db;

  CREATE TABLE sensitive_data (
  id SERIAL PRIMARY KEY,
  name TEXT,
  address TEXT
  );

  INSERT INTO sensitive_data (name, address)
  VALUES
  ('Alice', '123 Main St'),
  ('Bob', '456 Oak Ave');

  -- Enable pgcrypto
  CREATE EXTENSION pgcrypto;

  -- You then refer to the key stored in a text file by using:
  UPDATE sensitive_data
  SET address = pgp_sym_encrypt(address, pg_read_file('C:\PostgreSQL\data\DEK.txt')::text);

  -- To view the data in encrypted format, send:
  SELECT * FROM sensitive_data;

​Sample operations and tasks

​Connect to PostgreSQL

​Create a database

​Enable pgcrypto

​Encrypt at the column level

​View unencrypted data

​View encrypted data

​View encrypted data in hex

​Find the DB files

​Add a new path rule inside your PostgreSQL service on the CryptoHub

​Summary of the Symmetric Keys used for these operations

​Encryption with a key stored in a text file

Sample operations and tasks

Connect to PostgreSQL

Create a database

Enable pgcrypto

Encrypt at the column level

View unencrypted data

View encrypted data

View encrypted data in hex

Find the DB files

Add a new path rule inside your PostgreSQL service on the CryptoHub

Summary of the Symmetric Keys used for these operations

Encryption with a key stored in a text file