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.

Answer 2

Here is my solution. I have a superuser (postgres) and a non-superuser (pg4e_user_8087f) and a database (pg4e) and I want to install hstore and uuid-ossp extensions and use them without becoming the superuser. I am using PostgreSQL 11.

Superuser window:

\c pg4e
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
ALTER EXTENSION "uuid-ossp" SET SCHEMA public;
CREATE EXTENSION IF NOT EXISTS "hstore";
ALTER EXTENSION "hstore" SET SCHEMA public;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO pg4e_user_8087f; 

Non-superuser window after the above commands are finished:

pg4e=> \dx
                            List of installed extensions
   Name    | Version |   Schema   |                   Description                    
-----------+---------+------------+--------------------------------------------------
 hstore    | 1.5     | public     | data type for storing sets of (key, value) pairs
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 uuid-ossp | 1.1     | public     | generate universally unique identifiers (UUIDs)

pg4e=> select uuid_generate_v1();
           uuid_generate_v1           
--------------------------------------
 2114df5a-16bb-11ea-8000-468ce7a721ef
(1 row)

pg4e=> SELECT 'a=>1,b=>2'::hstore;
       hstore       
--------------------
 "a"=>"1", "b"=>"2"
(1 row)

At one point, I had figured almost all of it out but did not realize that the superuser had to be connected to the database in question to create and then permit the extension. Once I figured out that this was done for a particular database, it fell into place quickly.

Answer 3

Had the same problem, this one solved it for me:

ALTER EXTENSION "unaccent" SET SCHEMA public;