I'm currently using knex to connect my node.js sever to a postgres database and I have started using pgcrypto to encrypt some of my data. I am a bit late to the game with encrypting my data, so I have several queries I'll need to update, and am looking for the most efficient way to not only swap over my queries, but to actually query the database. When I try to implement the PGP_SYM_DECRYPT directly inside the knex.select() query, I get an error saying the user can't be found. However, if I use the knex.raw() query, I can get it to work. Is there any way to use the PGG_SYM_DECRYPT inside the .select() query, or perhaps a way to pass the secret key alongside of the query so it will automatically decrypt any encrypted columns?
Example WORKING code:
const user = await knex("n_user AS u")
.where({
"u.uuid": uuid,
"su.site_id": site.id
})
.first()
.join("site_has_user AS su", { "su.user_id": "u.id" })
.select(
"u.id",
"u.uuid",
"u.mobile_number",
"u.email",
"u.first_name",
"u.last_name",
"u.department",
// "u.note", the note is the encrypted data
"u.disabled",
"su.role"
)
.select(
knex.raw(
`PGP_SYM_DECRYPT(u.note::bytea, '${process.env.SECRET_KEY}') as note`
)
);
Example DESIRED code (or some other variant):
const user = await knex("n_user AS u")
.where({
"u.uuid": uuid,
"su.site_id": site.id
})
.first()
.join("site_has_user AS su", { "su.user_id": "u.id" })
.select(
"u.id",
"u.uuid",
"u.mobile_number",
"u.email",
"u.first_name",
"u.last_name",
"u.department",
`PGP_SYM_DECRYPT(u.note::bytea, '${process.env.SECRET_KEY}') as note`,
"u.disabled",
"su.role"
);
Any thoughts?
You can add raw snippet inside select like this:
.select(
"u.id",
"u.uuid",
"u.mobile_number",
"u.email",
"u.first_name",
"u.last_name",
"u.department",
knex.raw("PGP_SYM_DECRYPT(??::bytea, ?) as note", ['u.note', process.env.SECRET_KEY]),
"u.disabled",
"su.role"
);
In raw syntax ?? is identifier replacement and ? is value binding so that secret key is passed to driver safely as binding without trying to interpolate it directly to SQL string.