1

I have used crypt() in one of my functions to hash user password. But what I observed the next day I started my pc that the same thing failed as cryptographic functions became unavailable upon restart. It showed message like functions gen_salt() / crypt() is unknown or not defined. I executed create extension pgcrypto; once and it started working.

How to handle this in prod environment? Is there a way to make this module permanently available in db even if db is restarted?

Or do I have to instruct server manager to run this command in case they required to restart db server?

Or can I write this statement in functions itself so that it will create the extension every time before using cryptographic functions?

Moreover, we are using a read-replica of db, where it did not allowed to execute this "create extension" statement as there is read permission only. So how to make this cryptographic functions available in such read-only environment?

  • Welcome to the site, could you update your crypt() function. whatever you have written for better understanding. – Md Haidar Ali Khan Feb 6 '18 at 11:36
  • Pardon, I didnt get it. If you're asking for short description, then it could be : How to retain cryptographic functions available on db restart event ? And how to execute the "create extension pgcrypto" in readonly permission. – Arjun_TECH Feb 6 '18 at 12:09
2

pgcrypto is a regular Postgres extension. You have to install it once per database (not per DB cluster like you may have been assuming) to use it. Installation is permanent, surviving DB restarts.

If you want every new database in the same DB cluster to have it pre-installed, you can install it to the default template database template1 by connecting to it and installing it normally. Or install it in any other database and use that as template with:

CREATE DATABASE new_db TEMPLATE my_template;

See:

One other thing that can go wrong: the search_path. See:

But if that was your problem you'd get warnings when trying to install the same extension a second time.

The only other exotic explanation I can think of: your DB might have been restored to a previous state. But you would certainly know of that, wouldn't you?

Or did you just connect to a different database by mistake? The "read replica" you mentioned?

how to make this cryptographic functions available in such read-only environment?

If it's not replicated by your replication solution, you have to install it manually once.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.