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?
PostgreSQL Permissions – Enabling Extensions Without Super-User
permissionspostgresqlpostgresql-extensions
Related Question
- Postgresql – Why isn’t postgres prompting me about a new user
- PostgreSQL – How to Initialize PostGIS Database in Single-User Mode
- PostgreSQL – How to Install pgcrypto Extension on CentOS 7
- PostgreSQL – Roles Without Linux Accounts
- PostgreSQL Ubuntu – How to Package a PostgreSQL Extension as a Binary .deb File
- PostgreSQL Permissions – Drop Schema Public Without Losing Extensions
Best Answer
From the docs on Extensions,
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 getFrom the docs on pg_language
... of course you can make
c
trusted withUPDATE pg_language set lanpltrusted = true where lanname = 'c';
as a superuser. ThenCREATE 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.