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 log in to the {{ch}} 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 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;