Encrypt data using pgcrypto

The pgcrypto PostgreSQL extension provides cryptographic functions such as:

• General hashing
• Password hashing
• PGP encryption
• Raw encryption
• Random-data

For more information about these functions and the options available, see the pgcrypto documentation.

Use the pgcrypto extension to encrypt inserted data

The pgcrypto extension allows you to encrypt, decrypt, hash, and create digital signatures within your database. Tiger Data understands how precious your data is and safeguards sensitive information.

1. Install the pgcrypto extension

   CREATE EXTENSION IF NOT EXISTS pgcrypto;

   Confirm the extension is installed using the \dx command:

       List of installed extensions
           Name         | Version |   Schema   |                                      Description
   ---------------------+---------+------------+---------------------------------------------------------------------------------------
    pg_stat_statements  | 1.10    | public     | track planning and execution statistics of all SQL statements executed
    pgcrypto            | 1.3     | public     | cryptographic functions
    plpgsql             | 1.0     | pg_catalog | PL/pgSQL procedural language
    timescaledb         | 2.24.0  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
    timescaledb_toolkit | 1.22.0  | public     | Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities

2. Create a table named user_passwords

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

3. Insert values and replace <Password_Key> with a password key of your choice

      INSERT INTO tbl_sym_crypt (username, crypttext)
       VALUES ('user1', pgp_sym_encrypt('user1_password','<Password_Key>')),
          ('user2', pgp_sym_encrypt('user2_password','<Password_Key>'));

4. Confirm the password is encrypted

   SELECT * FROM user_passwords;

   The encrypted passwords are listed:

          username |                                                                              crypttext
   ----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    user1      | \xc30d040703025caa37f9d1c731d169d240018529d6f0002b2948905a87e4787efaa0046e58fd3f04ee95594bea1803807063321f62c9651cbf0422b04508093df9644a76684b504b317cf633552fcf164f
    user2   | \xc30d0407030279bbcf760b81d3de73d23c01c04142632fc8527c0c1b17cc954c77f16df46022acddc565fd18f0f0f761ddb2f31b21c4ebe47a48039d685287d64506029e027cf29b5493b574df
   (2 rows)

5. View the decrypted passwords

   Replace <Password_Key> with the password key that you created:

    SELECT username, pgp_sym_decrypt(crypttext::bytea, '<Password_Key>')
    FROM user_passwords;

   The decrypted passwords are listed:

    username | pgp_sym_decrypt
    ----------+-----------------
     user1   | user1_password
     user2   | user2_password
    (2 rows)