postgresql 如何使非超级用户可以使用 Postgres 扩展
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30800685/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I make Postgres extension available to non superuser
提问by Paul Taylor
I installed a Postgres extension (unaccent) with
我安装了一个 Postgres 扩展（非重音）
sudo su posgres
psql create extension unaccent
and now I can use unacccent in sql, but only if I am the Postgres user.
现在我可以在 sql 中使用 unaccent，但前提是我是 Postgres 用户。
How do I make Postgres extension available to all/another user
如何使所有/其他用户都可以使用 Postgres 扩展
(Im on Ubuntu using Postgres 9.3.5 installed using apt-install)
（我在使用 apt-install 安装的 Postgres 9.3.5 的 Ubuntu 上）
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.
postgres@ip-172-31-39-147:/home/ubuntu/code/jthinksearch/reports/src/main/sql$ 退出 ubuntu@ip-172-31-39-147:~/code/jthinksearch/reports/ src/main/sql$ psql jthinksearch psql (9.3.5) 输入“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=#
采纳答案by Daniel Vérité
Based on this error message:
基于此错误消息：
ERROR: text search dictionary "unaccent" does not exist
错误：文本搜索词典“unaccent”不存在
and the previous one where unaccentwithout the schema prefix is not found, it means that the publicschema, where the unaccent function resides, is not in your search_path.
而前一个unaccent没有架构前缀的地方没有找到，这意味着publicunaccent 函数所在的架构不在你的search_path.
It happens that unaccentfails in this case because it's a dictionary function and basically it needs to find its stuff through the search_path.
碰巧unaccent在这种情况下失败了，因为它是一个字典函数，基本上它需要通过search_path.
This is explained in more details in Does PostgreSQL support “accent insensitive” collations?
这在PostgreSQL 是否支持“重音不敏感”排序规则中有更详细的解释？
Once the publicschema is added to the search_pathof the users who need to call it (this is normally the default), this should work and they don't need to be superuser.
一旦将public模式添加到search_path需要调用它的用户（这通常是默认设置），这应该可以工作，他们不需要是超级用户。
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.
或者，如果此解决方案不可接受，您也可以使用嵌入模式并添加不变性的中间存根函数，如上面链接的答案中所建议的。
回答by drchuck
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 hstoreand uuid-osspextensions and use them without becoming the superuser. I am using PostgreSQL 11.
这是我的解决方案。我有一个超级用户 (postgres) 和一个非超级用户 (pg4e_user_8087f) 和一个数据库 (pg4e)，我想在不成为超级用户的情况下安装hstore和uuid-ossp扩展并使用它们。我正在使用 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 connectedto 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.
有一次，我已经想通了几乎所有内容，但没有意识到超级用户必须连接到相关数据库才能创建并允许扩展。一旦我发现这是为特定数据库完成的，它很快就到位。
