Databases
StackOverflow2013
Postsdbo
POWhy can only a superuser CREATE EXTENSION hstore, but not on Heroku?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy can only a superuser CREATE EXTENSION hstore, but not on Heroku?
    text
    copied!<p>When I attempt to enable hstore on my database:</p> <pre><code>=&gt; CREATE EXTENSION IF NOT EXISTS hstore; ERROR: permission denied to create extension "hstore" HINT: Must be superuser to create this extension. </code></pre> <p>My user is not a superuser, but <em>is</em> the owner of the database.</p> <p>According to <a href="http://www.postgresql.org/docs/current/static/sql-createextension.html">the CREATE EXTENSION docs</a>:</p> <blockquote> <p>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.</p> </blockquote> <p>What is hstore doing that requires superuser privileges? Is it affecting parts of the cluster outside the database I'm adding it to?</p> <hr> <p>Further confundity:</p> <p><a href="https://devcenter.heroku.com/articles/heroku-postgresql#connection-permissions">The DB user Heroku Postgres provides is not a superuser</a>:</p> <blockquote> <p>Heroku Postgres users are granted all non-superuser permissions on their database. These include <code>SELECT</code>, <code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code>, <code>TRUNCATE</code>, <code>REFERENCES</code>, <code>TRIGGER</code>, <code>CREATE</code>, <code>CONNECT</code>, <code>TEMPORARY</code>, <code>EXECUTE</code>, and <code>USAGE</code>.</p> </blockquote> <p>However, <a href="https://devcenter.heroku.com/articles/heroku-postgres-extensions-postgis-full-text-search">that user is able to CREATE EXTENSION hstore</a>:</p> <blockquote> <p>To create any supported extension, open a session with heroku pg:psql and run the appropriate command:</p> <pre><code>$ 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=&gt; CREATE EXTENSION hstore; CREATE EXTENSION ad27m1eao6kqb1=&gt; </code></pre> </blockquote> <p>(For context, I'm attempting to set up a <a href="https://github.com/progrium/dokku">Dokku</a> deployment, so the comparison to Heroku is especially important.)</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload