Steps to follow to get pgcrypto
plugin into the Schema you are using
Login to Postgres and goto the Schema used. Or If you have pgadmin
running... goto Schema... right-click... click Query Tool
.
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.
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)
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
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")
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.
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%';
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;