User cannot use extension "uuid-ossp"

Question

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

Now I am installing a testing environment. The database is configured using a script created by the client. The structure is standard: admin user, application user, application namespace, etc.

I can create an extension with an administrator 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 to the user of the database application, I can not create 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 in the same database. 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

, search_path.

"" - search_path .

• " "

, ? . 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
...

ALTER EXTENSION:

ALTER EXTENSION uuid-ossp SET SCHEMA public;

:

• pg_catalog?

Answer 2

psql

:

\dx uuid-ossp

, ( uuid_generate_v4).

,

• app_user search_path ( , , ALTER USER app_user SET current_schema = ..., ).

• app_user ( ).

• app_user USAGE .