为什么只有超级用户CREATE EXTENSION hstore，而不是Heroku？ [英] Why can only a superuser CREATE EXTENSION hstore, but not on Heroku?

问题描述

当我尝试在我的数据库上启用hstore时：

  =>如果不存在，创建扩展hstore; 
错误：权限被拒绝创建扩展名hstore
提示：必须是超级用户才能创建此扩展名。 
  

我的用户不是超级用户，但是是数据库的所有者。

根据 CREATE EXTENSION文档：

加载扩展需要创建其组件对象所需的相同特权。对于大多数扩展，这意味着需要超级用户或数据库所有者权限。运行CREATE EXTENSION的用户将成为扩展的所有者，以便进行后续特权检查，以及由扩展脚本创建的任何对象的所有者。

什么是hstore，需要超级用户权限？是否影响到数据库之外的部分集群？

---

更多信息：

数据库用户Heroku Postgres提供的不是超级用户：

Heroku Postgres用户在数据库中被授予所有非超级用户权限。这些包括 SELECT ， INSERT ， UPDATE ， DELETE ， TRUNCATE ， REFERENCES ， TRIGGER ， CREATE ， CONNECT ， TEMPORARY ， EXECUTE 和 USAGE 。

但是，该用户可以通过 CREATE EXTENSION hstore a>：
$ b

要创建任何受支持的扩展，请使用heroku pg：psql打开会话并运行相应的命令：

  $ heroku pg：psql 
传呼机使用已关闭。 
 psql（9.2.4）
 SSL连接（密码：DHE-RSA-AES256-SHA，bits：256）
键入help寻求帮助。 
 
 ad27m1eao6kqb1 => CREATE EXTENSION hstore; 
 CREATE EXTENSION 
 ad27m1eao6kqb1 => 
  

（对于上下文，我试图设置一个<因此与Heroku的比较尤为重要。）

解决方案

hstore扩展创建了从外部动态对象调用代码的函数，这需要超级用户权限。这就是为什么创建hstore扩展需要超级用户权限的原因。

至于Heroku，我的理解是他们正在运行一个特殊的扩展白名单模块，它允许用户创建特定即使他们不是超级用户也是如此。我相信它基于以下代码： https://github.com/dimitri/pgextwlist 。如果您想在数据库中使用相同的功能，则可以尝试自行安装该代码。

When I attempt to enable hstore on my database:

=> 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 is the 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.

What is hstore doing that requires superuser privileges? Is it affecting parts of the cluster outside the database I'm adding it to?

---

Further confundity:

The DB user Heroku Postgres provides is not a superuser:

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, and 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=>

(For context, I'm attempting to set up a Dokku deployment, so the comparison to Heroku is especially important.)

解决方案

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.

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.

这篇关于为什么只有超级用户CREATE EXTENSION hstore，而不是Heroku？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！