0

I'm using pgcrypto module for PostgresSQL, and in the document it was indicated that this line:

  CREATE EXTENSION pgcrypto;

should be executed before creating the table which stores encrypted data.

My question is, if the table has already been created without the above mentioned line being executed, can I modify the table later by running this line to give it the encryption ability?

My goal is to use pgp_sym_encrypt() for INSERT operations, pgp_sym_decrypt() for SELECT operations and both functions for UPDATE operations. For now I can get prepare statement to work, but exec would fail, I guess because the CREATE EXTENSION line hasn't been run.

5
  • I guess it depends on how exactly you use this extension to "give [the table] the encryption ability"; can you add details?
    – mustaccio
    Aug 16, 2022 at 19:27
  • Sorry for not providing the details, I'm aiming at using pgp_sym_encrypt() for INSERT operations and pgp_sym_decrypt() for SELECT operations. I'll update my question. Aug 16, 2022 at 22:01
  • Don't use pgcrypto, encrypt and decrypt inside the application. You will have to rewrite the table. Aug 17, 2022 at 6:55
  • pgcrypto doesn't offer transparent data encryption. It just provides some tools you could use to implement it yourself (which I guess would then not qualify as transparent). You need to create the extension before you can use it, but it doesn't matter when the table was created.
    – jjanes
    Aug 17, 2022 at 13:37
  • @jjanes Thanks for the answer! Aug 17, 2022 at 15:02

0

Your Answer

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

Browse other questions tagged or ask your own question.