Re: Quick Extensions Question - Mailing list pgsql-hackers

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> In the simplest form we could implement this by just removing the
>> superuser() check in CREATE EXTENSION.  But then people who tried
>> to load a superuser-only extension would get a permissions failure
>> on some random command within the extension, which might be thought
>> less than user-friendly.  Also it might be good to have a more
>> explicit marking of superuser-only extensions.  So I'm thinking it
>> might be best to invent a control file property along the lines of
>> 
>> superuser = false        -- default is true
>> 
>> which would presently do nothing except control whether to make a
>> superuser() permissions check before running the script.  (In future
>> it might do more, but only after a lot of careful thought.)

> Well it's easy to read that the other way round.  Is superuser = true
> means that I need to be a superuser or does it mean that the script will
> get run as superuser no matter what?  Not a huge problem, but still.
> What about using the PL terminology here, and calling the property
> 'trusted' (default false, so you have to be a superuser to load them)?

Hmm.  I see your point, but "trusted" seems like it could just as easily
be misunderstood.  Anybody have any other opinions about the color of
that bikeshed?

> In summary, +1 for this proposal.

I will be working on this today.

> Also, do we want pg_upgrade to automatically do the create extension
> from unpackaged for us when upgrading a PL?  I think it makes sense.

That seems like a bit of a wart, but if it can be done in not too ugly a
way it's fine with me.  I'll leave hacking pg_upgrade to somebody else
though.

I've thought of two other issues that need some discussion before we
can get very far with this:

1. What should pg_dump do with the preinstalled extension plpgsql?
We could put in a hardwired hack to not dump it, on the assumption that
it will be preinstalled in the destination database, but that seems a
bit ugly.  When we decided to preinstall the language, we made pg_dump
emit CREATE OR REPLACE LANGUAGE so that the dump script would not fail
if the language was preinstalled.  We don't have an equivalent command
for extensions, though.  We can either invent one, or put a kluge into
pg_dump.  Although I'm on record as generally disliking CREATE IF NOT
EXISTS, I think that having pg_dump emit "CREATE EXTENSION IF NOT EXISTS
foo" might be the best solution here.  The reason why is that unlike the
case with other sorts of objects, you typically want the latest version
of an extension installed, not the one that was present in the source
database; so the dump script shouldn't be trying to force a particular
version to be installed, which is the semantics I'd expect of CREATE OR
REPLACE EXTENSION.

2. What shall we do with createlang?  Presumably it should start
emitting CREATE EXTENSION not CREATE LANGUAGE, at which point it's
really a general purpose extension installer not a PL installer.
To what extent should we reflect that repurposing in the documentation?
I think changing the name would be going too far: it would break
existing scripts for little return.  But it might seem a little weird
to read "createlang -- install an extension" in the table of contents.
Thoughts?
        regards, tom lane