Using pgcrypto in Managed Service for PostgreSQL

The pgcrypto extension provides cryptographic functions that allow database administrators to store certain columns of data in encrypted form.

Installing the pgcrypto extension in a PostgreSQL clusterInstalling the pgcrypto extension in a PostgreSQL cluster

Add pgcrypto to the database.

For more information about the pgcrypto extension, see the official documentation.

Use casesUse cases

One-way encryptionOne-way encryption

1. Create a table:

   CREATE TABLE tbl_one_way_crypt(username varchar(100) PRIMARY KEY, cryptpwd text);
   

2. Insert the hash text data in the cryptpwd column:

   INSERT INTO tbl_one_way_crypt(username, cryptpwd)
       VALUES ('bob', crypt('Password123', gen_salt('md5'))),
              ('alice', crypt('Password123', gen_salt('md5')));
   

3. Make sure that the data in the cryptpwd column is encrypted:

   SELECT * FROM tbl_one_way_crypt;
   

4. See if Password123 is stored in the encrypted data:

   SELECT username FROM tbl_one_way_crypt
   WHERE cryptpwd = crypt('Password123', cryptpwd);
   

Symmetric encryptionSymmetric encryption

1. Create a table:

   CREATE TABLE tbl_sym_crypt (username varchar(100) PRIMARY KEY, crypttext text);
   

2. Insert the encrypted text data in the crypttext column:

   INSERT INTO tbl_sym_crypt (username, crypttext)
       VALUES ('bob', pgp_sym_encrypt('Text to encrypt','!qazSymKeyXsw2')),
              ('alice', pgp_sym_encrypt('Secret Data','!qazSymKeyXsw2'));
   

3. Make sure that the data in the crypttext column is encrypted:

   SELECT * FROM tbl_sym_crypt;
   

4. Retrieve the decrypted data by explicitly specifying bytea as the data type:

   SELECT username, pgp_sym_decrypt(crypttext::bytea, '!qazSymKeyXsw2')
   FROM tbl_sym_crypt;
   

Public-key encryptionPublic-key encryption

1. Use the GnuPG utility to generate a pair of keys:

   1. Create a pair of keys:

      gpg --gen-key
      

      Specify the key USER-ID (Real name and Email address) and the Passphrase.

   2. Export the keys to files:

      gpg -a --export <key_Real_name> > public.key && \
      gpg -a --export-secret-keys <key_Real_name> > private.key
      

2. Create a table:

   CREATE TABLE tbl_asym_crypt(ssn_id SERIAL PRIMARY KEY,
       username varchar(100), ssn bytea);
   

3. Insert the encrypted text data in the ssn column using the public key:

   INSERT INTO tbl_asym_crypt (username, ssn)
   SELECT tmp.username, pgp_pub_encrypt(tmp.ssn, keys.pubkey) AS tbl
   FROM (
       VALUES ('Alice', '123-45-6788'), ('Bob', '123-45-6799'))
       AS tmp(username, ssn)
   CROSS JOIN (SELECT dearmor('<contents_of_public.key_file>') AS pubkey) AS keys;
   

4. Make sure that the data in the ssn column is encrypted:

   SELECT * FROM tbl_asym_crypt;
   

5. Decrypt the data in the ssn column using the private key:

   SELECT username, pgp_pub_decrypt(ssn, keys.privkey, '<key_Passphrase>')
       AS decrypted_ssn FROM tbl_asym_crypt
   CROSS JOIN (SELECT dearmor('<contents_of_private.key_file>') AS privkey) AS keys;