Re: CREATE EXTENSION without superuser access

> On Apr 27, 2016, at 3:47 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 04/27/2016 03:30 PM, Steve Atkins wrote:
>>
>>>>
>>>> Is there any way to install an extension either from a SQL connection or from a user-defined directory instead of .../extensions?
>>>
>>> Have not tried it, but you might want to take a look at:
>>>
>>> http://www.postgresql.org/docs/9.5/interactive/extend-extensions.html
>>>
>>> A control file can set the following parameters:
>>>
>>> directory (string)
>>>
>>> The directory containing the extension's SQL script file(s). Unless an absolute path is given, the name is relative to the installation's SHAREDIR directory. The default behavior is equivalent to specifying directory = 'extension'.
>>
>> That's a useful feature, for sure, but I think the control file itself still needs to be in the .../extensions directory.
>
> Yeah, that would be a problem, I needed to read a couple of paragraphs up from the above:(
>
> Got to thinking, when you say root do you really mean root or the user the database cluster is initdb'ed as?

Neither - I don't have write access to the postgresql extensions directory.

The use case for me is distributing an enterprise app that talks to a database which the people who use the app don't have administrative access to. The admins who install and run the database don't install extensions (they're not responsive, they just don't trust third party extensions, ...). For most of my users the database would be running on a machine they have filesystem access to, so being able to point to SQL scripts in another directory would be enough, but in a few cases it's running on a separate system and they only have access via port 5432.

Any solution that didn't require filesystem access at all would probably be really convenient for people using managed PostgreSQL services too.

Cheers,
Steve