AWS RDS Postgres Crypto functions doesn't work even with the pgcrypto extension enabled

Question

I have the fresh AWS RDS Postgres (v 11) instance. I've installed the pgcrypto extension and it doesn't allow to do that again:

CREATE EXTENSION pgcrypto;
Error in query (7): ERROR: extension "pgcrypto" already exists

But I can't use the extension functions:

select gen_salt('bf');
Error in query (7): ERROR: function gen_salt(unknown) does not exist
LINE 1: select gen_salt('bf')
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

What I'm doing wrong?

https://dba.stackexchange.com/questions/135093/in-rds-digest-function-is-undefined-after-creating-pgcrypto-extension — Antti Haapala -- Слава Україні, May 26 '19 at 07:10
You could find it in the catalogs I guess. If you didn't install it yourself then you should add an answer here after you find the fix — Antti Haapala -- Слава Україні, May 26 '19 at 07:11

score 4 · Accepted Answer · answered May 26 '19 at 07:20

The issue was that the extension probably was added when a schema was active. Thank you @ Antti Haapala for the link to the same question: https://dba.stackexchange.com/questions/135093/in-rds-digest-function-is-undefined-after-creating-pgcrypto-extension .

I've made the following when no schema selected:

DROP EXTENSION pgcrypto;
Query executed OK, 0 rows affected. (0.031 s)

CREATE EXTENSION pgcrypto;
Query executed OK, 0 rows affected. (0.046 s)

SELECT gen_salt('bf');
gen_salt
$2a$06$kyj11fcRtpwxrqgCfZEIaO

And everything works fine now.

AWS RDS Postgres Crypto functions doesn't work even with the pgcrypto extension enabled

1 Answers1

Linked