PostgreSQL: Can Access Crypto Function on an Admin User, But Not on a Normal User

Question

I have a PostgreSQL database with the pgcrypto extension installed:

postgres=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
NOTICE:  extension "pgcrypto" already exists, skipping
CREATE EXTENSION

I can use that extension, while logged-in to an admin user, just fine:

postgres=# select public.gen_salt('bf', 8);
       gen_salt            
-------------------------------
 $2_SOME_SALT_Qu
(1 row)

Also, the extension is available to the public schema:

postgres=# \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description          
----------+---------+------------+------------------------------
 pgcrypto | 1.3     | public     | cryptographic functions

In spite of all of the above, when I try to use the crypto functions from any ordinary database user, it fails:

user=> select gen_salt('bf', 8);
ERROR:  function gen_salt(unknown, integer) does not exist
LINE 1: select gen_salt('bf', 8);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The same thing happens if I try to invoke public.gen_salt instead.

Can anyone explain how I can fix my database to allow all users to access the crypto funcitions?

Answer 1

The usual prompt for psql includes the database name, not the user name.

So it looks like you logged on to a different database, not just as a different user.

You need to create the extension in each database you want to use it in.