15

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?

Arnaud Denoyelle
  • 29,980
  • 16
  • 92
  • 148

2 Answers2

15

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.

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Added 'public' to the search path. It worked. Thanks :) – Arnaud Denoyelle Sep 28 '16 at 15:30
  • 1
    I had this problem, I kept checking the search path of my db_user and it looked ok, I checked where the extension was installed using my admin_user and it said it was in the public schema. Turns out the default database for the admin_user is different than the one I had my db_user set up for. I used `psql -d db_user_database` and added the extension there... that did it – crackpotHouseplant Oct 16 '16 at 03:19
  • I also removed something that looked like `${user}` (don't remember) from my users path. Not a clue what problem that will cause down the line... – crackpotHouseplant Oct 16 '16 at 03:21
4

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    Your answer suggested me another solution which also work : `CREATE EXTENSION "uuid-ossp" WITH SCHEMA my_app_user_schema`. – Arnaud Denoyelle Sep 28 '16 at 15:37
  • @ArnaudDenoyelle: Installing in the app_user's schema might mean that other users don't have that schema in their `search_path`. Typically *not* what you want. Extensions typically go into the `public` schema or some dedicated schema you set in the general `search_path`. – Erwin Brandstetter Sep 28 '16 at 15:39