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=#

Pear

grant access to the functions from the extension. If you create the extension in its own schema, you can simply grant access to everything in that schema – Patmore

How, please, I tried 'GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO ubuntu' and it had no effect – Pear

@a_horse_with_no_name Tried all my schemas and still doesnt work – Pear

Sorry, you say only if I am the postgres user and then you're grating to ubuntu user? – Gibraltar

@FabrizioMazzoni yes thats what im trying to do, ubuntu is the default user i use, but I had to su to postgres user for install to work but I still cant use it as ubuntu user – Pear

@FabrizioMazzoni Ive added \dx and \du output to make it clearer – Pear

Please do select public.unaccent('foo') as the ubuntu user and share what is the error you get. – Chauchaucer

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.

Teide

ah, thankyou. Yes that was the problem my database contains two schemas I needed to use so I had previously done ALTER USER ubuntu SET search_path = discogs,musicbrainz; changing to to ALTER USER ubuntu SET search_path = discogs,musicbrainz, public; fixed the issue – Pear

I have the exact same issue, can't use unaccent extension without non postgres user. But still can't get it works. – Feel

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.

Price

Answer 3

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

ALTER EXTENSION "unaccent" SET SCHEMA public;

Bernina