postgresqlsearch-pathpostgresql-extensions

User cannot use extension "uuid-ossp"

EDIT : there is no need for an extension anymore. You can now generate a uuid with the function gen_random_uuid() that is available by default since PostgreSQL 14.

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?

Solution

  • 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 first schema in the search_path setting during the session that installed it.

    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;

    Double quotes required for the name with dash (an unfortunate choice of name in Postgres).

    Related with more explanation: