如何使Postgres扩展对非超级用户可用

Question

我安装了一个 Postgres 扩展 (unaccent)。

sudo su posgres
psql create extension unaccent

现在我可以在SQL中使用unaccent，但前提是我必须作为Postgres用户进行操作。

如何将Postgres扩展程序对所有用户/另一个用户开放？

（我正在使用Ubuntu，使用apt-install安装了Postgres 9.3.5）

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.

我给了用户超级用户角色，但这没有帮助，然后按建议在中放置模式名称，这对错误消息产生了影响，但仍未起作用。

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

- Paul Taylor

---

授予访问扩展功能的权限。如果您在其自己的模式中创建扩展程序，则可以简单地授予对该模式中所有内容的访问权限。 - user330315

请问，我尝试了“GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO ubuntu”，但没有任何效果。 - Paul Taylor

@FabrizioMazzoni 是的，这就是我想做的，Ubuntu 是我使用的默认用户，但我必须切换到 postgres 用户才能使安装工作正常，但我仍然无法在 ubuntu 用户下使用它。 - Paul Taylor

@FabrizioMazzoni 我已经添加了\dx和\du输出，以使其更清晰明了。 - Paul Taylor

请以ubuntu用户身份执行select public.unaccent('foo')命令，并分享您遇到的错误。 - Daniel Vérité

Answer 1

根据这个错误信息：

ERROR: 文本搜索词典“unaccent”不存在

以及之前的一个错误，在没有模式前缀的情况下找不到unaccent，这意味着包含unaccent函数的public模式不在您的search_path中。

由于unaccent是一个字典函数，它需要通过search_path找到其内容，所以它在此情况下失败。

详细信息请参见Does PostgreSQL support “accent insensitive” collations?

一旦将public模式添加到需要调用它的用户的search_path中（这通常是默认设置），这就可以工作，他们不需要成为超级用户。

或者，如果此解决方案不可接受，您还可以使用嵌入模式并添加不变性的中间存根函数，如上面链接的答案中所建议的那样。

- Daniel Vérité

---

啊，谢谢。是的，那就是问题所在了。我的数据库包含了两个模式，我需要使用它们，因此之前我执行了 ALTER USER ubuntu SET search_path = discogs,musicbrainz; 的操作。将其更改为 ALTER USER ubuntu SET search_path = discogs,musicbrainz, public; 就解决了这个问题。 - Paul Taylor

我有完全相同的问题，没有非postgres用户就无法使用unaccent扩展。但仍然无法使其正常工作。 - Mas Bagol

Answer 2

这是我的解决方案。我有一个超级用户（postgres）和一个非超级用户（pg4e_user_8087f），以及一个数据库（pg4e）。我想安装hstore和uuid-ossp扩展并在不成为超级用户的情况下使用它们。我正在使用PostgreSQL 11。

超级用户窗口：

\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; 

以上命令完成后，非超级用户窗口如下：

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)

曾经有一段时间，我几乎完全理解了它的所有内容，但是没有意识到超级用户必须连接到要创建并允许扩展的数据库。一旦我发现这是针对特定数据库执行的操作，就很快摆脱了困境。

- drchuck