Postgresql – Do I need to execute “create extension pgcrypto” everytime

pgcryptopostgresqlpostgresql-extensionsread-only-database

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?

Best Answer

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.

Related Question