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

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

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?

Solution

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.