Secure sensitive data with pgcrypto extension in Azure PostgreSQL Flexible Server

[Guest Gennadyk]

The pgcrypto module is a cryptographic extension that provides a number of hashing and cryptographic functions using MD5, SHA, HMAC, AES, BLOWFISH, PGP, and CRYPT, as well as DES and 3DES.

 

When encrypting data, one must take into an account the more difficult you make it to read data, it also takes more performance overhead to query and decrypt this data. With that in mind, it's important to pick your encryption strategies based on the sensitivity of your data.

 

 

There are two basic kinds of encryption, one-way and two-way.

 

• In one-way encryption you don't ever care about decrypting the data into readable form, but you just want to verify the user knows what the underlying secret text is. This is normally used for passwords.
  
• In two-way encryption, you want the ability to encrypt data as well as allow authorized users to decrypt it into a meaningful form. Data such as credit cards and SSNs would fall in this category.
  

Setup pgcrypto on Azure Database for PostgreSQL - Flexible Server

 

 

As any extension pgcrypto has to be installed on Postgres Flex Server. Before we can do that, we will need to allow list pgcrypto using azure.extensions server parameter. It can be done via Azure Portal as shown in the image below:

 

 

 

 

Pic 1. Allow-list pgcrypto extension using Server parameters blade in Azure Portal.

 

 

 

Next step is to install pgcrypto extension via CREATE EXTENSION command.

 

 

 

 

Pic 2. Installing pgcrypto extension using create extension statement in pgAdmin editor.

 

 

 

Using pgcrypto extension to secure password stored in Azure PostgreSQL Flexible Server.

 

 

Normally when people want one way encryption and just want a basic simple level of encryption, they use the md5 function which is built into PostgreSQL by default. The md5 function is equivalent to using the PASSWORD function in MySQL. If you want anything beyond that, you'll need to use pgcrypto. It adds 34 functions to your list of options when it comes to column level encryption.

 

 

 

For one-way encryption, the crypt function packaged in pgcrypto provides an added level of security above the md5 way. Let's now create an example table with password credentials store in it and insert values encrypted value with crypt function:

 

 

 

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

email TEXT NOT NULL UNIQUE,

password TEXT NOT NULL

);

 

 

 

Now creating a new employee, we can use the pgcrypto's crypt function to encrypt the password, before we store it in the table"

 

 

 

INSERT INTO employees (email, password) VALUES (

'employee@corporatemail.com',

crypt('mypassword', gen_salt('bf'))

);

 

 

 

The crypt function accepts two arguments:

 

• The password to encrypt
  
• The salt to use when encrypting
  

 

In our example above I used blowfish (bf) algorithm, but other algorithms are popular and can be used including md5, Extended DES (xdes), etc.

 

To authenticate a user, we use same function again, but this time we pass these arguments:

 

• The submitted password
  
• The encrypted password we already have in the database
  

 

If the password matches, crypt function will return the same value as the one we already have in the database.

 

 

 

SELECT id

FROM employees

WHERE email = 'employee@corporatemail.com' AND password = crypt('mypassword', password).

 

 

 

This returns matching id, as you can see on image below:

 

 

Pic 3. Verifying encrypted password via query in PgAdmin.

 

For more information about Azure Database for PostgreSQL and its support for extensions see - Azure Database for PostgreSQL - Flexible Server | Microsoft Docs.

 

For more details about pgcrypto see - Encrypting data with pgcrypto - Postgres OnLine Journal, PostgreSQL: Documentation: 15: F.28. pgcrypto, PostgreSQL: Best way for Password Encryption using pgcrypto's Cryptographic functions (dbrnd.com)

 

We’re always eager to get your feedback, so please reach out via email to Ask Azure DB for PostgreSQL.

 

Continue reading...