End Point

News

Welcome to End Point's blog

Ongoing observations by End Point people.

pgcrypto pg_cipher_exists errors on upgrade from PostgreSQL 8.1

While migrating a client from a 8.1 Postgres database to a 8.4 Postgres database, I came across a very annoying pgcrypto problem. (pgcrypto is a very powerful and useful contrib module that contains many functions for encryption and hashing.) Specifically, the following functions were removed from pgcrypto as of version 8.2 of Postgres:

  • pg_cipher_exists
  • pg_digest_exists
  • pg_hmac_exists

While the functions listed above were deprecated, and marked as such for a while, their complete removal from 8.2 presents problems when upgrading via a simple pg_dump. Specifically, even though the client was not using those functions, they were still there as part of the dump. Here's what the error message looked like:

$ pg_dump mydb --create | psql -X -p 5433 -f - >pg.stdout 2>pg.stderr
...
psql::2654: ERROR:  could not find function "pg_cipher_exists"
  in file "/var/lib/postgresql/8.4/lib/pgcrypto.so"
psql::2657: ERROR:  function public.cipher_exists(text) does not exist

While it doesn't stop the rest of the dump from importing, I like to remove any errors I can. In this case, it really was a SMOP. Inside the Postgres 8.4 source tree, in the contrib/pgcrypto directory, I added the following declarations to pgcrypto.h:

Datum       pg_cipher_exists(PG_FUNCTION_ARGS);
Datum       pg_digest_exists(PG_FUNCTION_ARGS);
Datum       pg_hmac_exists(PG_FUNCTION_ARGS);

Then I added three simple functions to the bottom of the pgcrypto.c file that simply throw an error if they are invoked, letting the user know that the functions are deprecated. This is a much friendlier way than simply removing the functions, IMHO.

/* SQL function: pg_cipher_exists(text) returns boolean */
PG_FUNCTION_INFO_V1(pg_cipher_exists);

Datum
pg_cipher_exists(PG_FUNCTION_ARGS)
{
    ereport(ERROR,
            (errcode(ERRCODE_EXTERNAL_ROUTINE_INVOCATION_EXCEPTION),
             errmsg("pg_cipher_exists is a deprecated function")));
    PG_RETURN_TEXT_P("0");
}

/* SQL function: pg_cipher_exists(text) returns boolean */
PG_FUNCTION_INFO_V1(pg_digest_exists);

Datum
pg_digest_exists(PG_FUNCTION_ARGS)
{

    ereport(ERROR,
            (errcode(ERRCODE_EXTERNAL_ROUTINE_INVOCATION_EXCEPTION),
             errmsg("pg_digest_exists is a deprecated function")));
    PG_RETURN_TEXT_P("0");
}
/* SQL function: pg_hmac_exists(text) returns boolean */
PG_FUNCTION_INFO_V1(pg_hmac_exists);

Datum
pg_hmac_exists(PG_FUNCTION_ARGS)
{

    ereport(ERROR,
            (errcode(ERRCODE_EXTERNAL_ROUTINE_INVOCATION_EXCEPTION),
             errmsg("pg_hmac_exists is a deprecated function")));
    PG_RETURN_TEXT_P("0");
}

After running make install from the pgcrypto directory, the dump proceeded without any further pgcrypto errors. From this point forward, if the anyone attempts to use one of the functions, it will be quite obvious that the function is deprecated, rather than leaving the user wondering if they typed the function name incorrectly or wondering if pgcrypto is perhaps not installed.

Why not just add some dummy SQL functions to the pgcrypto.sql file instead of hacking the C code? Because pg_dump by default will create the database as a copy of template0. While there are other ways around the problem (such as putting the SQL functions into template1 and forcing the load to use that instead of template0, or by creating the database, adding the SQL functions, and then loading the data), this was the simplest approach.

Photo of Enigma machine by Marcin Wichary

Learn more about End Point's Postgres Support, Development, and Consulting.

5 comments:

Anonymous said...

Can't you just dump in custom format, generate the dump list, remove the lines you don't want and import that?

Jon Jensen said...

Certainly you could do that. That's what I would have done myself.

But I think Greg makes a good case that it's nice to have the old functions defined but throwing a meaningful error, rather than just not working and making it appear that pgcrypto isn't installed, or isn't installed correctly.

In other words, I think Greg's solution here is about helping users of the system after the import, not just fixing the import itself.

Greg Sabino Mullane said...

Anon: In addition to Jon's reasoning (which is quite correct: I hate the fact that the functions simply disappeared), I prefer my way because I was running the dump often and on different databases, so that would have been a lot of extra steps to do each time compared with pg_dump --clean mydb | psql

Robert Treat said...

This is why I have been advocating putting contrib modules in their own schemas for years. In most cases, it's much simpler to exclude the contrib modules on the dump, and install the new version of contrib module before upgrading.

Greg Sabino Mullane said...

Robert: Yes, that would be nice, but I'm not holding my breath on that one. For the final iteration, I will probably export the schema only, run uninstall plus install for each contrib module, and then install the data. What's annoying is some contrib modules do not have uninstall SQL in older versions!