postgresql 为什么只有超级用户才能 CREATE EXTENSION hstore，而 Heroku 不能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20723100/
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
Why can only a superuser CREATE EXTENSION hstore, but not on Heroku?
提问by Peeja
When I attempt to enable hstore on my database:
当我尝试在我的数据库上启用 hstore 时：
=> CREATE EXTENSION IF NOT EXISTS hstore;
ERROR: permission denied to create extension "hstore"
HINT: Must be superuser to create this extension.
My user is not a superuser, but isthe owner of the database.
我的用户是不是超级用户，但是是数据库的所有者。
According to the CREATE EXTENSION docs:
根据创建扩展文档：
Loading an extension requires the same privileges that would be required to create its component objects. For most extensions this means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension's script.
加载扩展需要的权限与创建其组件对象所需的权限相同。对于大多数扩展，这意味着需要超级用户或数据库所有者权限。运行 CREATE EXTENSION 的用户将成为扩展的所有者以进行以后的权限检查，以及由扩展的脚本创建的任何对象的所有者。
What is hstore doing that requires superuser privileges? Is it affecting parts of the cluster outside the database I'm adding it to?
hstore 做什么需要超级用户权限？它是否会影响我将其添加到的数据库之外的部分集群？
Further confundity:
进一步的混淆：
The DB user Heroku Postgres provides is not a superuser:
Heroku Postgres 提供的 DB 用户不是超级用户：
Heroku Postgres users are granted all non-superuser permissions on their database. These include
SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,CREATE,CONNECT,TEMPORARY,EXECUTE, andUSAGE.
Heroku Postgres 用户被授予对其数据库的所有非超级用户权限。这些措施包括
SELECT，INSERT，UPDATE，DELETE，TRUNCATE，REFERENCES，TRIGGER，CREATE，CONNECT，TEMPORARY，EXECUTE，和USAGE。
However, that user is able to CREATE EXTENSION hstore:
To create any supported extension, open a session with heroku pg:psql and run the appropriate command:
$ heroku pg:psql Pager usage is off. psql (9.2.4) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. ad27m1eao6kqb1=> CREATE EXTENSION hstore; CREATE EXTENSION ad27m1eao6kqb1=>
要创建任何受支持的扩展，请使用 heroku pg:psql 打开一个会话并运行适当的命令：
$ heroku pg:psql Pager usage is off. psql (9.2.4) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. ad27m1eao6kqb1=> CREATE EXTENSION hstore; CREATE EXTENSION ad27m1eao6kqb1=>
(For context, I'm attempting to set up a Dokkudeployment, so the comparison to Heroku is especially important.)
（就上下文而言，我正在尝试设置Dokku部署，因此与 Heroku 的比较尤为重要。）
回答by Peter Eisentraut
The hstore extension creates functions that call code from an external dynamic object, which requires superuser privilege. That's why creating the hstore extension requires superuser privilege.
hstore 扩展创建了从外部动态对象调用代码的函数，这需要超级用户权限。这就是创建 hstore 扩展需要超级用户权限的原因。
As for Heroku, it is my understanding that they are running with a special extension whitelisting module, which allows users to create certain extensions even though they are not superusers. I believe it is based on this code: https://github.com/dimitri/pgextwlist. You can try to install that code yourself if you want the same functionality in your databases.
至于 Heroku，我的理解是他们运行一个特殊的扩展白名单模块，允许用户创建某些扩展，即使他们不是超级用户。我相信它基于此代码：https: //github.com/dimitri/pgextwlist。如果您希望在您的数据库中使用相同的功能，您可以尝试自己安装该代码。
回答by Arun
ALTER USER postgres WITH SUPERUSER; this solve your create extension issue.
使用超级用户更改用户 postgres；这解决了您的创建扩展问题。
