Thread: CREATE EXTENSION without superuser access
I have an app that would benefit from being able to use pg_partman rather than doing it's own ad-hoc partition management. Unfortunately, some of the places where the app needs to run don't have root access to the database server filesystem, soI can't install the extension in the postgresql extensions directory. I could get all the pg_partman functionality by modifying the extension SQL script to remove the extension functionalityand installing the functions in it manually, but then I can't take advantage of the extension features for backups,config tables, upgrades and so on - and it's probably not going to interact well if someone does a "create extensionpg_partman" in the database. Is there any way to install an extension either from a SQL connection or from a user-defined directory instead of .../extensions? (And if not, is there a TODO here?) Cheers, Steve
On 04/27/2016 01:22 PM, Steve Atkins wrote: > I have an app that would benefit from being able to use pg_partman rather than doing it's own ad-hoc partition management. > > Unfortunately, some of the places where the app needs to run don't have root access to the database server filesystem,so I can't install the extension in the postgresql extensions directory. > > I could get all the pg_partman functionality by modifying the extension SQL script to remove the extension functionalityand installing the functions in it manually, but then I can't take advantage of the extension features for backups,config tables, upgrades and so on - and it's probably not going to interact well if someone does a "create extensionpg_partman" in the database. > > 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'. > > (And if not, is there a TODO here?) > > Cheers, > Steve > > > -- Adrian Klaver adrian.klaver@aklaver.com
> On Apr 27, 2016, at 2:47 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 04/27/2016 01:22 PM, Steve Atkins wrote: >> I have an app that would benefit from being able to use pg_partman rather than doing it's own ad-hoc partition management. >> >> Unfortunately, some of the places where the app needs to run don't have root access to the database server filesystem,so I can't install the extension in the postgresql extensions directory. >> >> I could get all the pg_partman functionality by modifying the extension SQL script to remove the extension functionalityand installing the functions in it manually, but then I can't take advantage of the extension features for backups,config tables, upgrades and so on - and it's probably not going to interact well if someone does a "create extensionpg_partman" in the database. >> >> 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 relativeto 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. Cheers, Steve
On 04/27/2016 01:22 PM, Steve Atkins wrote:I have an app that would benefit from being able to use pg_partman rather than doing it's own ad-hoc partition management.
Unfortunately, some of the places where the app needs to run don't have root access to the database server filesystem, so I can't install the extension in the postgresql extensions directory.
I could get all the pg_partman functionality by modifying the extension SQL script to remove the extension functionality and installing the functions in it manually, but then I can't take advantage of the extension features for backups, config tables, upgrades and so on - and it's probably not going to interact well if someone does a "create extension pg_partman" in the database.
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'.
Un-researched thoughts here...
T
his still doesn't avoid the problem of somehow getting to access to SHAREDIR.
I suspect that any extension that is not SQL-only is going to be problematic - period. What I'd like, though, is a way to hook into the extension mechanism for SQL-only extensions. More specifically, having some blessed way to install a PGXN SQL-only module into a server using only psql/libpq. Especially being able to do so by pointing to a local tarball of said PGXN extension. I haven't looked into this at all, though. Ultimately the result could be used when working RDS and similar hosting setups.
David J.
On 04/27/2016 03:30 PM, Steve Atkins wrote: > >> On Apr 27, 2016, at 2:47 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> >> On 04/27/2016 01:22 PM, Steve Atkins wrote: >>> I have an app that would benefit from being able to use pg_partman rather than doing it's own ad-hoc partition management. >>> >>> Unfortunately, some of the places where the app needs to run don't have root access to the database server filesystem,so I can't install the extension in the postgresql extensions directory. >>> >>> I could get all the pg_partman functionality by modifying the extension SQL script to remove the extension functionalityand installing the functions in it manually, but then I can't take advantage of the extension features for backups,config tables, upgrades and so on - and it's probably not going to interact well if someone does a "create extensionpg_partman" in the database. >>> >>> 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 relativeto 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? > > Cheers, > Steve > > > -- Adrian Klaver adrian.klaver@aklaver.com
> On Apr 27, 2016, at 3:47 PM, Adrian Klaver <adrian.klaver@aklaver.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 relativeto 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 haveadministrative 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 machinethey have filesystem access to, so being able to point to SQL scripts in another directory would be enough, but ina 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 PostgreSQLservices too. Cheers, Steve
On 04/27/2016 04:06 PM, Steve Atkins wrote: > >> On Apr 27, 2016, at 3:47 PM, Adrian Klaver <adrian.klaver@aklaver.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 relativeto 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 haveadministrative 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 machinethey have filesystem access to, so being able to point to SQL scripts in another directory would be enough, but ina 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 managedPostgreSQL services too. I see and suspected as much, I just do not like to assume. Yes, that is a problem: https://github.com/keithf4/pg_partman "I've received many requests for being able to install this extension on Amazon RDS. RDS does not support third-party extension management outside of the ones it has approved and provides itself. " > > Cheers, > Steve > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 04/27/2016 10:22 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? > > (And if not, is there a TODO here?) There is actually a TODON'T here. Search the pgsql-hackers archives for some very, very long reading about "extension templates". This was (I think) the last thread about it: www.postgresql.org/message-id/flat/m2bo5hfiqb.fsf@2ndQuadrant.fr -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
> On Apr 28, 2016, at 6:44 AM, Vik Fearing <vik@2ndquadrant.fr> wrote: > > On 04/27/2016 10:22 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? >> >> (And if not, is there a TODO here?) > > There is actually a TODON'T here. Search the pgsql-hackers archives for > some very, very long reading about "extension templates". > > This was (I think) the last thread about it: > www.postgresql.org/message-id/flat/m2bo5hfiqb.fsf@2ndQuadrant.fr That's a very long thread. OK, I'll give up on using the extension infrastructure. Time to either fork pg_partman or write a little perl script that converts extensions to not-extensions, I guess. Cheers, Steve