How do I make Postgres extension available to non superuser

Question

I installed a Postgres extension (unaccent) with

sudo su posgres
psql create extension unaccent

and now I can use unacccent in sql, but only if I am the Postgres user.

How do I make Postgres extension available to all/another user

(Im on Ubuntu using Postgres 9.3.5 installed using apt-install)

jthinksearch=# \dx;
                         List of installed extensions
   Name   | Version |   Schema   |                 Description
----------+---------+------------+---------------------------------------------
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
 unaccent | 1.0     | public     | text search dictionary that removes accents
(2 rows)

jthinksearch=#


jthinksearch=> \du;
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 ubuntu    |                                                | {}

postgres@ip-172-31-39-147:/home/ubuntu/code/jthinksearch/reports/src/main/sql$ exit ubuntu@ip-172-31-39-147:~/code/jthinksearch/reports/src/main/sql$ psql jthinksearch psql (9.3.5) Type "help" for help.

I gave user superuser role but that didnt help, then as suggested put the schema name in , that had an effect on the error message but still didnt work

jthinksearch=# \du;
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 ubuntu    | Superuser                                      | {}

jthinksearch=# select unaccent(name) from musicbrainz.artist where id=195660;
ERROR:  function unaccent(character varying) does not exist
LINE 1: select unaccent(name) from musicbrainz.artist where id=19566...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
jthinksearch=# ^C
jthinksearch=# select public.unaccent(name) from musicbrainz.artist where id=195660;
ERROR:  text search dictionary "unaccent" does not exist
jthinksearch=#

Answer 1

Based on this error message:

ERROR: text search dictionary "unaccent" does not exist

and the previous one where unaccent without the schema prefix is not found, it means that the public schema, where the unaccent function resides, is not in your search_path.

It happens that unaccent fails in this case because it's a dictionary function and basically it needs to find its stuff through the search_path.

This is explained in more details in Does PostgreSQL support “accent insensitive” collations?

Once the public schema is added to the search_path of the users who need to call it (this is normally the default), this should work and they don't need to be superuser.

Or if this solution is not acceptable, you may also use an intermediate stub function that embeds the schema and adds immutability, as suggested in the answer linked above.