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

问题描述

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

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.

根据CREATE EXTENSION 文档:

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

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.

hstore 在做什么需要超级用户权限?它是否会影响我将其添加到的数据库之外的部分集群?

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

进一步混淆:

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

Heroku Postgres 用户被授予对其数据库的所有非超级用户权限.这些包括SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE, 和 USAGE.

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.

但是，该用户可以创建扩展 hstore:

要创建任何受支持的扩展，请使用 heroku pg:psql 打开一个会话并运行适当的命令:

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

(就上下文而言，我正在尝试设置 Dokku 部署，因此与 Heroku 的比较尤为重要.)

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

推荐答案

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

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.

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

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屋！