How do I use pgcrypto with knex inside a .select statement? (Postgres database)

Question

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?

score 2 · Accepted Answer · answered Jan 06 '21 at 12:34

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.

How do I use pgcrypto with knex inside a .select statement? (Postgres database)

1 Answers1