Re: Dumping an Extension's Script

On 2012-12-05 19:13:10 +0200, Heikki Linnakangas wrote:
> On 05.12.2012 12:22, Dimitri Fontaine wrote:
> >Heikki Linnakangas<hlinnakangas(at)vmware(dot)com> writes:
> >>No doubt about that. I'm sure extensions written in pure SQL or PL/pgSQL are
> >>very common. But what does that have to do with this patch?
> >
> >This patch is all about enabling users to create extension without
> >having to ship them as root on the file system of the database(s)
> >server(s) first.
> >...
> >When you can code your extension using dynamic code such as SQL or
> >PL/pgSQL, PL/pythonu or PL/perl, there's absolutely no good reason to
> >have to do the "ship on the server's file system first" that I can see.
> >
> >Technically creating an extension "inline" (sending its definition in
> >the CREATE EXTENSION query itself) solves the problem of having to
> >access the server's file system as root.
>
> Ok, I'm with you this far.
>
> >Then, next pg_dump will include "CREATE EXTENSION foo;" as usual and at
> >pg_restore time that access files on the file systems. But maybe you
> >still are not granted access to the server's file system as root on the
> >pg_restore target, right? So now you need to be able to include the
> >extension's script into the dump.
>
> Now you lost me. I can see the need to install an extension without access
> to the filesystem - but it does not follow that you need to be able to dump
> an extension script. In general, I think you're confusing three things:
>
> 1. The way an extension is deployed. It could be by copying the files to the
> file system, by sending them over libpq, or shipped in .rpms by the OS, or
> something else.
>
> 2. The way an extension's files are laid out before it's deployed.
> Typically, you want to keep an extension's source code (whether it's C or
> SQL or plpython) in a version control system.
>
> 3. Being able to deploy extensions to the server without superuser or root
> access
>
> I think it would make this discussion a lot clearer if we keep those
> concerns separate. It's useful to have a mechanism to deploy an extension
> over libpq. It's not clear to me if you're envisioning to change 2. I don't
> think we should; having a .sql file and a .control file seems perfectly fine
> to me.
>
> I'd suggest that we just need a way to upload an extension to the server via
> libpq. Something like "UPLOAD EXTENSION foo", which goes into COPY mode and
> you can stream over a zip file containing the .sql and .control file that
> make up the extension. The server would unzip the file into the right
> directory.

Not sure what is better here. Dimitri's way seems to be easier to manage
for people who maintain their database in update scripts and such and
your's seems to be a bit simpler from the backend perspective.

> Now, point 3 is yet another issue. If you need to copy the extension files
> to /usr/share/, you need root (or similar) access on the filesystem. We
> could allow extensions to be located somewhere in the data directory
> instead. Like $PGDATA/extensions. But again, that would be an independent
> change from 1 and 2.

I think installing them into some global space is not a sensible
interim-step. Having a UPLOAD EXTENSION in one database affect all other
databases or even clusters (because you e.g. updated the version) would
be really confusing.

Which leads to:

> And I still don't understand why pg_dump needs to know about any of this...

Extensions should be fully per-database and we want pg_dump backups to
be restorable into another database/clusters/servers. So having a mode
for pg_dump that actually makes dumps that are usable for recovering
after a disaster seems sensible to me. Otherwise you need to redeploy
from the VCS or whatever, which isn't really what you want when
restoring a database backup.

Comparing the situation to the one where you have extensions provided by
the packaging system or by /contrib or whatever doesn't seem to be all
that valid to me.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services