[PostgreSQL] pgcrypto - encryption and decryption

 PostgreSQL   pgcrypto  

▌Introduction

---

The pgcrypto module provides cryptographic functions for PostgreSQL.

This article shows how to support API versioning on ASP.NET Core Web API.

We will learn how to use HASH, symmetric/asymmetric encryption/decryption.

▌Environment

---

▋PostgreSQL 11.6

▌Implement

---

▋Install pgcrypto

// Install

create extension pgcrypto;

// Confirm

select * from pg_available_extensions where "name"='pgcrypto';

Before the walk-thru, lets create a table for testing encryption and decryption with pgcrypto.

CREATE TABLE public."Users"

(

    "Name" text,

    "Phone" bytea, --Encrpted phone number

    "Password" text --Hashed pwd

);

ALTER TABLE public."Users" OWNER to postgres;

INSERT INTO public."Users"("Name") VALUES('JB');

▋HASH

The data type for Hashed data is TEXT. pgcrypto’s crypto() supports Hash algorithms: bf, md5, xdes, des.

(From PostgreSQL document)

▋Hash password

UPDATE public."Users"

SET "Password" = crypt('!@$%qwer4567RTYU', gen_salt('md5'))

WHERE "Name"='JB'

▋Check if password matched

SELECT ("Password" = crypt('!@$%qwer4567RTYU', "Password")) AS IsMatch FROM public."Users";

▋PGP: Symmetric

The data type for encrypted data is BYTEA. The Symmetric encryption/decryption uses a same Secret.

▋Encrypt

UPDATE public."Users"

SET "Phone" = pgp_sym_encrypt('0912345678', 'my-secret')

▋Decrypt

SELECT pgp_sym_decrypt("Phone",'my-secret') FROM public."Users"

▋PGP: Asymmetric Public key/Private(secret) key

The data type for encrypted data is BYTEA. Asymmetric encryption/decryption uses a public key to encrypt, and uses a private(secret) key to decrypt.

We will generate a key pair by GPG for encryption n decryption.

▋GPG: Generate key pair

$ gpg --gen-key

Enter name and email address as following,

Set the passphrase to protect the key or just click Enter to bypass it.

We can check the key information with below commands:

$ gpg --list-keys

$ gpg --list-secret-keys

$ gpg --list-public-keys

For example,

The key id: 6C3ECF8AD70E41E7E6362C880E473D67D1C6E811, will be used when exporting the key.

▋GPG: Export the key pair

Since we will directly use the public/private key in SQL, we need to export them under PostgreSQL’s /data directory.

We can use the following SQL to get the right path:

SELECT setting FROM pg_settings where name='data_directory';

The export command:

$ gpg -a --export <key_id> > /var/lib/postgresql/data/public.key

$ gpg -a --export-secret-key <key_id> > /var/lib/postgresql/data/private.key

Now lets export the key pair to /var/lib/postgresql/data/keys,

$ cd /var/lib/postgresql/data

$ mkdir keys

$ gpg -a --export 6C3ECF8AD70E41E7E6362C880E473D67D1C6E811 > /var/lib/postgresql/data/keys/public.key

$ gpg -a --export-secret-key 6C3ECF8AD70E41E7E6362C880E473D67D1C6E811 > /var/lib/postgresql/data/keys/private.key

▋GPG: (Optional) Set key expiry

The key is good to use even is expired. The expiry is meant to tell users that do not use an expired key.

$ gpg --edit-key 6C3ECF8AD70E41E7E6362C880E473D67D1C6E811

$ gpp> expire

$ Key is valid for? <Set the expiry with the following options>

Value	Description
0	key does not expire
<n>	key expires in n days
<n>w	key expires in n weeks
<n>m	key expires in n months
<n>y =	key expires in n years
<yyyy>-<MM>-<dd>	e.q. 2025-12-31

▋Encrypt

UPDATE public."Users"

SET "Phone" = pgp_pub_encrypt('0912345678', dearmor(pg_read_file('keys/public.key')))

▋Decrypt

SELECT pgp_pub_decrypt("Phone",dearmor(pg_read_file('keys/private.key'))) AS "Phone" FROM public."Users"

▋DB functions

I created some DB functions for reference.

/* HASH */

--Hash                           

CREATE OR REPLACE FUNCTION my_hash(t text) RETURNS text  AS $$

BEGIN

   RETURN crypt(t, gen_salt('md5'));

END;

$$ LANGUAGE plpgsql;

--Usage sample

SELECT my_hash('this_is_not_my_pwd');

                            

--Is Match

CREATE OR REPLACE FUNCTION my_hash_match(t text, hashed text) RETURNS boolean  AS $$

BEGIN

   RETURN (hashed = crypt(t, hashed)) AS IsMatch;

END;

$$ LANGUAGE plpgsql;

--Usage sample

SELECT my_hash_match('this_is_not_my_pwd', '$1$pSFXakbG$XgX9GvfcQCiIRjOYek7O4/')

                            

/* PGP: Symmetric */

--Encrypt

CREATE OR REPLACE FUNCTION my_sym_encrypt(t text, secret text) RETURNS bytea AS $$

BEGIN

   RETURN pgp_sym_encrypt(t, secret);

END;

$$ LANGUAGE plpgsql;

                          

--Decrypt

CREATE OR REPLACE FUNCTION my_sym_decrypt(t bytea, secret text) RETURNS text AS $$

BEGIN

   RETURN pgp_sym_decrypt(t, secret);

END;

$$ LANGUAGE plpgsql;

--Usage sample

SELECT my_sym_decrypt(my_sym_encrypt('something_not_important', 'my-secret'), 'my-secret');

                          

/* PGP: Asymmetric */

--Encrypt

CREATE OR REPLACE FUNCTION my_pub_encrypt(t text) RETURNS bytea AS $$

BEGIN

   RETURN pgp_pub_encrypt(t, dearmor(pg_read_file('keys/public.key')));

END;

$$ LANGUAGE plpgsql;

                          

--Decrypt

CREATE OR REPLACE FUNCTION my_pvt_decrypt(t bytea) RETURNS text AS $$

BEGIN

   RETURN pgp_pub_decrypt(t,dearmor(pg_read_file('keys/private.key')));

END;

$$ LANGUAGE plpgsql;

--Usage sample            

SELECT my_pvt_decrypt(my_pub_encrypt('something_not_important'))

▌Reference

---

▋PostgreSQL Document - F.25. pgcrypto

▋Transparent Data Encryption in PostgreSQL and Integration with Key Management Service