How do I make Postgres extension available to non superuser

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.

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.