User cannot use extension "uuid-ossp"

Question

I am developing an application in which I decided to use UUIDs for the primary and foreign keys. For this purpose, I used the extension "uuid-ossp" which works fine in dev environment.

Now, I am installing the testing environment. The database setup is imposed by a script made by the customer. The structure is standard: admin user, application user, application namespace etc.

I can create the extension with the admin account:

$ psql mydb -U [admin_user]

mydb=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION

mydb=# select uuid_generate_v4();
        uuid_generate_v4
--------------------------------------
 23e45b57-a658-41a5-8661-0cc06568eff8

But when I connect with the database application user, I cannot generate a uuid :

$ psql mydb -U [app_user]

SELECT uuid_generate_v4();

mydb=> select uuid_generate_v4();
ERROR:  function uuid_generate_v4() does not exist

Both admin_user and app_user are on the same database. The app_user can "see" the extension but not use it:

bdd3001=> select * from pg_catalog.pg_extension;
  extname  | [...]
-----------+-
 plpgsql   | [...]
 uuid-ossp | [...]

Any ideas?

Answer 1

If you run the following in psql

\dx uuid-ossp

You will see the schema where the extension (and the function uuid_generate_v4) is installed.

Make sure that

• app_user has the schema in his search_path (you can e.g. use ALTER USER app_user SET current_schema = ... to change this for all future sessions).

• app_user has the permission to execute the function (this is normally allowed by default).

• app_user has the USAGE privilege on the extension schema.

Answer 2

You need the schema you installed the extension to in your search_path.

By default an extension is installed to the "current" schema at the time of installation - the current search_path setting of the installing role.

• How does the search_path influence identifier resolution and the "current schema"

So where did you end up installing it? See pg_extension.extnamespace:

SELECT e.extname
     , n.nspname      AS home_schema_of_extension
     , extrelocatable AS extension_can_be_relocated
FROM   pg_catalog.pg_extension e
JOIN   pg_catalog.pg_namespace n ON n.oid = e.extnamespace;

extname   | home_schema_of_extension | extension_can_be_relocated
----------+--------------------------+---------------------------
plpgsql   | pg_catalog               | f
intarray  | public                   | t
tablefunc | public                   | t
pg_trgm   | public                   | t
...

You can relocate an extension with ALTER EXTENSION:

ALTER EXTENSION uuid-ossp SET SCHEMA public;

Related with more explanation:

• Is it recommended to install extensions into pg_catalog schema?