Postgresql enabling extensions without super-user

Question

I have a PostgreSQL 9.5 server on which I have scripts that create roles and databases for users automatically. Within these databases it would be helpful to enable specific extensions (e.g. pgcrypto), but as I understand it one must be a superuser to run CREATE EXTENSION. Is there a way to enable such extensions without manually logging in with a superuser account?

• Kassandry: Have you tried adding them to template1 and then creating each user database from template1 like CREATE DATABASE foo OWNER=userfoo TEMPLATE=template1?
• beldaz: @Kassandry no hadn't thought of that, but is a good thought. Ideally I'd like the owners to be able to add the extension if they wish, but this is still an acceptable possibility.
• jackbravo: can you add extensions after database creation using this method?

Answer 1

From the docs on Extensions,

superuser (boolean) If this parameter is true (which is the default), only superusers can create the extension or update it to a new version. If it is set to false, just the privileges required to execute the commands in the installation or update script are required.

The value isn't set in pgcrypto.control, so it's defaulting to true which requires a SuperUser.

This means you can not CREATE EXTENSION as the mere owner of the database, despite what the docs on CREATE EXTENSION lead you to believe.

I tried hard setting it to false, and no joy. C is an untrusted language and you'll get

ERROR: permission denied for language c

From the docs on pg_language

Only superusers can create functions in untrusted languages.

... of course you can make c trusted with UPDATE pg_language set lanpltrusted = true where lanname = 'c'; as a superuser. Then CREATE EXTENSION pgcrypto will work fine as a non-superuser. But, that sounds like a bad idea if you have to worry about your users uploading source to your extension directory and then installing it in the database. That is to say, I wouldn't go that far. I'd find another way to skin this cat.

• beldaz: Thanks Evan, that's as thorough an answer as I could ask for. I'll probably opt for @Kassandry's cat-skinning proposal to get around this. I did also think about wrapping the CREATE EXTENSION in a stored procedure, but couldn't find a route to making this work in the same database without dblink authentication yuckiness.
• Claudi: What's the point, then, of not having any option in pg_dump to prevent it from dumping statements regarding extensions? I currently have to use external text processing tools to remove those statements from the SQL dumped by pg_dump.
• ribamar: @Evan Carroll: is it possible to set the superuser to false via psql cli? I have an instance on amazon aws rds and don't have access to pgcrypto.control .
• Evan Carroll: @ribamar no because that would mean that anyone connected to the database could perform literal arbitrary code execution as the db postmaster. that would be a horrible idea.
• ribamar: not anybody, the superuser. I understand that this way you differentiate the operating system super from the dbms super user, although if I was making such a decision I would go for enpowering the tool, and if really needed to create yet another more powerful user, i'd implement it inside the tool.