> ## Documentation Index
> Fetch the complete documentation index at: https://docs.futurex.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Appendix A: Column-level encryption with pgcrypto
> Implementation guide for column-level encryption using pgcrypto with examples.
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:
1. Right-click **Servers** in the browser panel.
2. 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:
```Plaintext wrap theme={null}
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:
```Plaintext wrap theme={null}
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:
```Plaintext wrap theme={null}
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:
```Plaintext wrap theme={null}
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:
```Plaintext wrap theme={null}
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:
Log in to the CryptoHub with a role that has permissions to use your PostgreSQL service.
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 in**pgcrypto** 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:
```Plaintext wrap theme={null}
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;
```