7

On clients server I had a problem - I did not have permissions to create extension pgcrypto. So server admin did it for me, and now I can see in pg_available_extensions that pgcrypto is installed (1.1), however its functions are not available for me:

=> select gen_random_uuid();
ERROR:  function gen_random_uuid() does not exist
LINE 1: select gen_random_uuid();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Also I don't have permissions to drop extension (which might be a clue what's wrong?):

=> drop extension pgcrypto;
ERROR:  must be owner of extension pgcrypto

Any idea what's wrong and why I can't see pgcrypto functions? Something with permissions?

user606521
  • 267

1 Answers1

9

Several possible explanations:

  • PostgreSQL version is older than 9.4. This function gen_random_uuid() did not exist before http://www.postgresql.org/docs/9.4/static/release-9-4.html :

    Add UUID random number generator gen_random_uuid() to pgcrypto (Oskari Saarenmaa)

    Use select version(); to check the version.

  • or it's 9.4+, but the extension was created in a schema that is not in the search_path of your SQL session. Use the meta-command \dx in psql to list the installed extensions, and check the Schema column.

  • or the extension is in a schema included in search_path, but the db user lacks the permission to look into it. Grant it with: GRANT USAGE ON SCHEMA schema_name TO role_name.

Daniel Vérité
  • 1,610
  • Is there, perhaps, a 3rd possible explanation? I'm on pg9.6 and created extension pgcrypto in "extensions" schema which is in my search_path but I still get "No function matches" – Reinsbrain Nov 03 '17 at 16:08
  • @Reinsbrain: could be a permission problem. Make sure the user has been granted USAGE on the schema, for instance. – Daniel Vérité Nov 03 '17 at 17:05
  • @DanielVérité : thanks that was it - perhaps worthy of expanding your already good answer – Reinsbrain Nov 03 '17 at 18:47
  • The third option helped me-- I had to look in the "public" schema for the method by default, as I didn't know it was created for a schema. Thanks! – user4893106 Apr 29 '18 at 18:21
  • There's the fourth option - PEBKAC, you forgot to specify database name, as these extensions are per DB. – iElectric Sep 24 '18 at 18:34