Search code examples
postgresqlpostgresql-12pgcrypto

pgcrypto: unable to normal user

I need to enable pgcrypto on a postgresql 12 instance.

I enabled the extension and checked it was ok:

postgres=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
postgres=# SELECT digest('blah', 'sha256');
                               digest
--------------------------------------------------------------------
 \x8b7df143d91c716ecfa5fc1730022f6b421b05cedee8fd52b1fc65a96030ad52
(1 row)

I followed recommandations I read on SO:

postgres=# GRANT EXECUTE ON FUNCTION digest(bytea,text) TO normaluser;
GRANT
postgres=# GRANT EXECUTE ON FUNCTION digest(text,text) TO normaluser;
GRANT

Sadly, still no way to use that function with a "normaluser".

normaluser@planck:5432/cryptodb> SELECT digest('blah', 'sha256');
ERROR:  42883: function digest(unknown, unknown) does not exist
LINE 1: SELECT digest('blah', 'sha256');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
LOCATION:  ParseFuncOrColumn, parse_func.c:631
Time: 52,065 ms

Any hint is welcome, thank you :)

Solution

  • In the question, you can see I'm connected as superuser postgres when I create the extension and not into the database cryptodb used be user normaluser. So I connect to database cryptodb with postgres and now normaluser can use the pgcrypto function digest in cryptodb context.

    TL;DR; : Connect to the correct database before CREATE EXTENSION pgcrypto;