0

I'm trying to use the PGP_SYM_ENCRYPT function to save encrypted data in a PostgreSQL database using Fastify.

The pgcrypto extension has been added using this command
CREATE EXTENSION IF NOT EXISTS pgcrypto;

enter image description here

I then checked if the extension was successfully added in my DB.
SELECT * FROM pg_extension WHERE extname = 'pgcrypto' AND extversion IS NOT NULL;

enter image description here

In my application.ts, I connect to the postgres db with this connection options:

 try {
    
      const connectionObject = {
        database: config.db_name,
        host: config.db_url,
        user: config.db_user,
        password: config.db_pass,
        port: 5432,
        pg: pg
      }
    
      await this.server.register(require('@fastify/postgres'), connectionObject)
      await this.server.pg.query('SELECT NOW()');
    
      console.log('DATABASE CONNECTED')
      
    } catch(error){
    
      console.error("CONNECTION ERROR", error);
    }

    await this.server.ready()

The database is connecting without any problem. Then, in my route.ts, I have a POST request that takes some data from a form, and I'm trying to insert this data with a column encrypted, just like that:

server.post(
  "/api/v1/api-name", 
  async function(request: any, reply: any) {
  
      try {

          ...
          // GET THE DATA
          // EXECUTING SOME CHECK ON DATA VALIDITY
          ...


          await server.pg.query(`
              INSERT INTO schema_name.table_name (data1, data2, data3, data3, data5, data6)
              VALUES ($1, $2, $3, $4, $5, PGP_SYM_ENCRYPT($6 ::text, $7 ::text))
          `, [data1, data2, data3, data4, data5, data6, config.secretKey]);

          return reply.code(201).send({
              statusCode: 202
          })

      } catch(error: any) {

          return reply.code(500).send({
              statusCode: 500,
              message: error.message
          })
      }
  }
);

But I receive this error when I execute the POST request function pgp_sym_encrypt(text, text) does not exist

Executing the query directly from dbeaver, the data is inserted and with the column encrypted

2
  • What is the exact command being executed directly on dbeaver? Commented Jul 13, 2023 at 12:17
  • @jjanes INSERT INTO schema_name.table_name (name, lastname, birthdate, birthplace, residence, fiscal_code) VALUES ('name', 'lastname', NOW(), 'birthplace', 'residence', PGP_SYM_ENCRYPT('XXXXXX00A00A000A', 'secret_key')) Commented Jul 13, 2023 at 12:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.