Re: Dumping an Extension's Script - Mailing list pgsql-hackers

On 05.12.2012 12:22, Dimitri Fontaine wrote:
> Heikki Linnakangas<hlinnakangas@vmware.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.

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.

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

- Heikki