node.jspostgresqlencryptionsequelize.jspgcrypto

How to use postgres pgcrypto with Nodejs and Sequelize?


  1. How to use sequelize with pgcrypto plugin in postgres.
  2. How to encrypt and decrypt the values of a column using sequelize
  3. How to use PGP_SYM_ENCRYPT and PGP_SYM_DECRYPT using nodejs and sequelize

Solution

  • I will walk through from starting what steps you need to follow :)

    Steps to follow to get pgcrypto plugin into the Schema you are using

    1. Login to Postgres and goto the Schema used. Or If you have pgadmin running... goto Schema... right-click... click Query Tool.

    2. Run this query there to check available plugins in your Postgres:

      select * from pg_available_extensions
      

      Scroll and check there if pgcrypto is available. If yes, move on to (3), else download pgcrypto plugin first.

    3. Run another query, this will show what plugins are there with the Schema you have selected in (1):

      select * from pg_extension
      

      Check if pgcrypto is there. If yes, skip to (5); if no, continue to (4)

    4. Run this query to bring pgcrypto plugin from extensions to current Schema supported extension:

      create extension pgcrypto
      

      You can re-verify by running the query from (3) to check if pgcrypto got pulled successfully to current Schema supported pluings.


    Now we are ready to use pgcrypto in our Nodejs application

    1. For the query which you want to encrypt, make use of sequelize to encrypt it. Use below code to modify the text value of that column to encrypted value:

      query: sequelize.fn("PGP_SYM_ENCRYPT", "data_to_encrypt", "secret_key")
      
    2. When you save the data to the DB using create, data will be encrypted using PGP_SYM_ENCRYPT which is a method provided by the pgcrypto plugin.

    3. To query or decrypt the values now, you can run this query in Postgres:

      select PGP_SYM_DECRYPT(colum_name::bytea, 'secret_key') FROM table where PGP_SYM_DECRYPT(column_name::bytea, 'secret_key' LIKE '%search_string%';
      
    4. To decrypt the value in your Node application, use:

      sequelize.findAll({
        attributes: [
          [
            sequelize.fn(
              'PGP_SYM_DECRYPT',
              sequelize.cast(sequelize.col('column_name'), 'bytea'), 
              'secret_key'
            ), 
            "column_name"
          ]
        ]
      }).then(data => console.log(data))
      

    NOTE: To automate the 1st part (getting extension into Schema), you can use a sequelize raw query, so that you don't have to do it manually each time required.

    CREATE EXTENSION IF NOT EXISTS pgcrypto;