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, organizations can establish a multi layered defense strategy that protects sensitive data both at the application level and within the physical storage architecture connect to postgresql with pgadmin launch the pgadmin application add server connection right click servers in browser panel select register > server enter basic info 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 "postgres" (or your specific username) password your password click \[ save ] create a new database right click on databases under your server select create > database enter a name for your database (e g , "testdb") click \[ save ] enable pgcrypto the pgcrypto extension allows you to use cryptographic functions like pgp sym encrypt , pgp sym decrypt , crypt , and others in your postgresql instance open sql query tool right click on your new database name select query tool enable pgcrypto extension in the query window, type create extension pgcrypto; click the execute/refresh button you should see a "query returned successfully" message verify installation run the following query select from pg extension where extname = 'pgcrypto'; a row with pgcrypto details confirms successful installation create a sample table and apply pgp sym encrypt at the column level the below sql query will 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 send select name, pgp sym decrypt(ssn, 'encryption key') from sensitive data; view column level encrypted data to see the table with encrypted data, run select id, name, ssn from sensitive data; view column level encrypted data in hexadecimal format to see encrypted data in binary or hexadecimal, run select id, name, encode(ssn, 'hex') as encrypted ssn from sensitive data; find physical db files location to see where your physical database files are stored, firstly run the query select oid, datname from pg database where datname = 'testdb'; this will show you the oid for the 'testdb' files (e g , oid = 16387 ) now navigate to the directory where your postgresql is installed (e g , 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 now we'll apply futurex's 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 navigate to the path rules menu in your postgresql service select \[ add path rule ] enter active directory or individual group or user names add an advanced path rule to where the database files are stored for the 'testdb' you created example c \postgresql\data\base\16387\\ the rule above will encrypt all unstructured data in the folder the files in the directory c \postgresql\data\base\16387 (or similar directories) do 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 postgresql's storage engine summary of the symmetric key used for this operation 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; it is used temporarily during the encryption/decryption process encryption with a key stored in a text file you can create a database on postgresql by using the 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;