Most databases contain sensitive information, so they should have multiple layers of security to protect that information. You can use Encryption to secure sensitive information. This article will teach you How to Secure Your Postgres Database by Encrypting Sensitive Data. If you intend to buy a cheap VPS server, check out the packages on our website.
Table of Contents
Tutorial Secure Your Postgres Database
Encryption of information in the database is also called encryption at rest. You can perform encryption at rest in three different layers which are:
1–> Encrypt the entire server disk or portions of the filesystem used to store data files.
2–> Encrypting the database cluster or individual tablespaces – also called Transparent Data Encryption (TDE).
3–> Encrypting the columns containing the sensitive information – also called column-level encryption
In this tutorial, we have covered the use of symmetric key encryption and public key (asymmetric) encryption and it is limited to encrypting data in specific columns.
pgcrypto extension
Encrypting data columns with the pgcrypto extension works in all recent versions of PostgreSQL. The commands in the public and private key generation section are based on the GNU PG (GNU Privacy Guard) tool, gpg. The most common Linux distro’s default installation includes gpg. Key generation commands in this article are compatible with all recent Linux releases.
Encryption in PostgreSQL is based on the OpenPGP standard. The pgcrypto extension has the necessary functions to encrypt and decrypt data according to the OpenPGP standard. You do not need to download or install additional software, as pgcrypto is a built-in plugin. To create this extension, just enter the following command:
CREATE EXTENSION pgcrypto ;For example, suppose you are encrypting users’ clinical records. To do this, create a test database with four columns-An ID field, a name, and two text fields automatically generated for both symmetrically and asymmetrically encoded clinical note data:
CREATE TABLE patients (id SERIAL, name VARCHAR(50), notes_symmetric TEXT UNIQUE, notes_asymmetric TEXT UNIQUE) ;Use Symmetric Keys
Symmetric key encryption, also known as single-key cryptography or private-key cryptography, is a method of data encryption. It uses the same key to encrypt and decrypt data. Note that you should restrict access to the key because anyone who has access to the key can decrypt the data:
--pseudocode cipher = encryption_function (plaintext, key) plaintext = decryption_function (cipher, key)Note: Each public key (asymmetric key cryptography) has a corresponding private key (symmetric key cryptography). In this article, we have used the term “private key” only in the context of asymmetric keys and as the counterpart of the corresponding public key, and we have only called it “symmetric key cryptography”. But we have used the terms “asymmetric key cryptography” and “public key cryptography” interchangeably.
Encrypting Symmetric Key
Encrypt data using Symmetric Key Encryption by running the encryption function pgp_sym_encrypt():
--pseudocode pgp_sym_encrypt('sensitive data to encrypt', 'symmetric_key')Symmetric_Key is a string value chosen by the administrator. The encryption function is called when inserting data into a table:
INSERT INTO patients (name, notes_symmetric) VALUES ( 'Jane Doe 1', pgp_sym_encrypt( 'A 66-year-old female presents symptoms of pain in their lower left molars for the last 2 weeks. No prior history of dental problems. Occasional smoker.', 'this_is_a_dummy_secret_key' ) ) ;Now insert another row:
INSERT INTO patients (name, notes_symmetric) VALUES ( 'John Doe 1', pgp_sym_encrypt( 'A 66-year-old male presents symptoms of pain in their lower left abdomen for the last 2 weeks. No prior history of kidney problems. Occasional drinker.', 'this_is_a_dummy_secret_key' ) ) ;Check the table with the encrypted data:
SELECT * FROM patients ;The data in the notes_symmetric column should appear as ciphertext.
Decrypting Symmetric Key
Now you can decrypt using the pgp_sym_decrypt() function on the encrypted data:
--pseudocode pgp_sym_decrypt('encrypted sensitive data', 'symmetric_key')You can also use the key used to encrypt data to decrypt it. Now decrypt the columns containing encrypted data as shown below:
SELECT name, pgp_sym_decrypt( notes_symmetric::BYTEA, 'this_is_a_dummy_secret_key' ) FROM patients ;Base the WHERE condition on the decrypted column value to run a query conditional on the actual (unencrypted) value of the encrypted column. For instance, find patients whose notes mention the word smoke as shown below:
SELECT name, pgp_sym_decrypt( notes_symmetric::BYTEA, 'this_is_a_dummy_secret_key' ) FROM patients WHERE pgp_sym_decrypt( notes_symmetric::BYTEA, 'this_is_a_dummy_secret_key' ) ILIKE '%smoke%' ;BYTEA Datatype
In the above examples, the encrypted data is typecast as Bytea, which you can is used for binary strings. Encryption produces a cipher text of binary data. Similarly, decryption works on strings of binary data. In our article, the columns contain encrypted data from Datatype TEXT. So, before decryption, the encrypted data (in text format) is typecast in binary.
The table can be designed such that the encrypted data columns are of type BYTEA. The decryption functions can work on the BYTEA column type directly. Remember that it might display differently depending on the interface if the column type is BYTEA. This tutorial uses TEXT columns for encrypted data.
Use Asymmetric Keys
Asymmetric key cryptography, also known as public key cryptography, involves a pair of keys – a public key and a private key. The public key encrypts the data. It is decrypted using only the corresponding private key. The public key is shared with a wider audience because it is easy to use. Access to the private key is restricted to only those who need to decrypt and read the data:
--pseudocode cipher = encryption_function (plaintext, public_key) plaintext = decryption_function (cipher, private_key)Key Generation
You need a key pair (public and private keys) to use asymmetric key encryption. Note that PGP keys cannot be generated in PostgreSQL and can only be generated on the operating system. Generate a key pair as shown below:
gpg --gen-keyEnter your name and email address when requesting. After generating a key pair, you get the option to set up a passphrase. Then press Enter without entering a passphrase.
Since the passphrase is necessary for decryption, remember to note it down if you enter it.
You can check all generated keys using the command below:
gpg --list-keysThe above command should list all generated key pairs as shown below:
pub rsa3072 2023-01-23 [SC] [expires: 2025-01-22] 7C43A1A93FB91FB86F8487B609C6A0F98287BED1 uid [ultimate] foobar <[email protected]> sub rsa3072 2023-01-23 [E] [expires: 2025-01-22]The key ID of the above example is the string 7C43A1A93FB91FB86F8487B609C6A0F98287BED1. The following examples refer to the key ID with the KEY_ID placeholder. Note that the keys are stored in the ~/.gnupg directory. After you generate a new key pair, you need to export the public and private keys to text files.
Run the command below to export the public key:
gpg --export KEY_ID > ~/.my_public_key.txtView the public key file as shown below:
less ~/.my_public_key.txtAs you know, the key is in binary, which makes it difficult to handle, especially in the text-based interfaces that are commonly used. In order to be able to copy and paste the key, it should be expressed as ASCII. For this, it is enough to armor the key from binary into ASCII as shown below:
gpg --export --armor KEY_ID > ~/.my_public_key.txtCheck again the public key file:
less ~/.my_public_key.txtThe public key should be readable:
-----BEGIN PGP PUBLIC KEY BLOCK----- random-looking-block-of-text -----END PGP PUBLIC KEY BLOCK-----Now export the armored private key as you see follows:
gpg --export-secret-keys --armor KEY_ID > ~/.my_private_key.txtIf you have already set a passphrase, you will be prompted for it when exporting the private key and you should enter it.
View the armored private key as shown below:
less ~/.my_private_key.txtIt should look like follows:
-----BEGIN PGP PRIVATE KEY BLOCK----- random-looking-block-of-text -----END PGP PRIVATE KEY BLOCK-----Encrypting Asymmetric Key
Encrypt data using Asymmetric Key Encryption by running the encryption function pgp_pub_encrypt():
--pseudocode pgp_pub_encrypt('sensitive data to encrypt', 'public_key')When inserting data into the table, the encryption function is calling. The encryption function requires the key in binary form. Therefore, the key is converting from ASCII text format to binary. You can do this using the dearmor() function as follows:
--pseudocode pgp_pub_encrypt('sensitive data to encrypt', dearmor('public_key')) ;You can insert dummy rows with patient data into the table as shown below:
INSERT INTO patients (name, notes_asymmetric) VALUES ( 'Jane Doe 2', pgp_pub_encrypt( 'A 66-year-old female presents symptoms of pain in their lower left molars for the last 2 weeks. No prior history of dental problems. Occasional smoker.', dearmor('public_key') ) ) ;At this point, in the code above, copy the armored public_key value from the exported text file.
Insert another row of dummy data as you did before:
INSERT INTO patients (name, notes_asymmetric) VALUES ( 'John Doe 2', pgp_pub_encrypt( 'A 66-year-old male presents symptoms of pain in their lower left abdomen for the last 2 weeks. No prior history of kidney problems. Occasional drinker.', dearmor('public_key') ) ) ;As you can see, in the examples in the previous section, patient data encrypted using symmetric keys was inserted into the notes_symmetric column. But in the examples in this section, patient data is encrypting using asymmetric keys and inserted into the notes_asymmetric column.
Decrypting Asymmetric Key
You need the private key to decrypt the data that was encrypted using the public key. To call the pgp_pub_decrypt() function on encrypted data, use the following command:
--pseudocode: using a passphrase pgp_pub_decrypt('encrypted sensitive data', 'private_key', 'passphrase') --pseudocode: without a passphrase pgp_pub_decrypt('encrypted sensitive data', 'private_key')The ASCII text value of the private key is also storing in binary before you use it. Decrypt the columns containing the encrypted data as shown below:
--using a passphrase SELECT name, pgp_pub_decrypt( notes_asymmetric::BYTEA, dearmor('private_key'), 'passphrase' ) FROM patients ;Now copy the value of private_key from the text file from the above example. Then, you need to write the passphrase you set earlier within single quotes. If you haven’t set a passphrase, just omit that argument in the function call:
--without a passphrase SELECT name, pgp_pub_decrypt( notes_asymmetric::BYTEA, dearmor('private_key') ) FROM patients ;To run a query conditional on the actual value of an encrypted column, base the condition on the value of the decrypted column.
That’s it!
Conclusion
This article taught you how to secure your Postgres Database by encrypting sensitive data. I hope this tutorial was useful for you and it helps you to secure your Postgres database. If you encounter a problem in the process or have any questions, you can contact us in the Comments section.
