Re: Procedural language definitions (was Re: 8.1 and syntax checking at create time)

On Wed, Aug 31, 2005 at 05:56:52PM -0400, Tom Lane wrote:
> I wrote:
> > We've had repeated problems with PL languages stemming from the fact
> > that pg_dump dumps them at a pretty low semantic level.  Aside from this
> > problem with adding a validator, we used to have issues with hardwired
> > paths to the shared libraries in the CREATE FUNCTION commands.  And in
> > 8.1, whether the functions are in "public" or "pg_catalog" is going to
> > vary across installations depending on whether the language was restored
> > from a dump or not.
> 
> > I wonder if we could change the dump representation to abstract out the
> > knowledge encapsulated in "createlang".  I don't suppose this would
> > work:
> >     \! createlang plpgsql <dbname>
> > but it'd be nice if the dump didn't know any more about the language
> > than its name, and didn't mention the implementation functions at all.
> 
> I thought some more about this and came up with a sketch of a solution.
> This would solve the problem of loading subtly-bogus language
> definitions from existing dump files, and it also offers a possibility
> of relaxing the rule that only superusers can create PLs.
> 
> The basic idea is to create a shared catalog that contains "procedural
> language templates".  This catalog would essentially replace the
> knowledge that's now hardwired in the createlang program.  It's shared
> because we need it to be already available in a new database; and
> anyway, the information in it need not vary across databases of an
> installation.  I'm envisioning a schema like
> 
> pg_pltemplate:
>  lanname    name        name of PL
>  lantrusted    boolean        trusted?
>  lanhandler    text        name of its call handler function
>  lanvalidator    text        name of its validator function, or NULL
>  lanlibrary    text        path of shared library, eg $libdir/plpgsql
>  lanacl        acl[]        see below
> 
> This could be filled in at initdb time with information about all the
> languages available in the standard distribution (whether or not they've
> actually been built) --- heck, we could include entries for all the PLs
> we know of, whether shipped in the core or not.
> 
> Then we would change CREATE LANGUAGE so that it first takes the given
> PL name and looks to see if there is an entry by that name in
> pg_pltemplate.  If so, it *ignores the given parameters* (if any) and
> uses what's in pg_pltemplate.  The logic would be identical to what
> createlang does now: look to see if the functions already exist in the
> current database, create them if not, then create the language entry.
> (If the specified shared library does not actually exist in the
> installation, we'd fail at the "create functions" step --- this is why
> it's OK to have entries for languages not built in the distribution.)
> 
> The bit about ignoring the given parameters is needed to be able to have
> the right things happen when loading an existing dump script from an
> older PG version with different support functions for the language.
> However, we would also simplify pg_dump to never dump the implementation
> functions of a language in future, and to emit CREATE LANGUAGE as just
>     CREATE LANGUAGE plpgsql;
> without decoration.  (createlang would reduce to that too.)
> 
> For languages that do not have a template in pg_pltemplate, CREATE
> LANGUAGE would operate the same as now.  This case supports languages
> that we don't know of.  It might also be worthwhile to create a command
> like
>     CREATE LANGUAGE TEMPLATE ...
> to simplify making new entries in pg_pltemplate.  (However, we could not
> ask pg_dump to dump templates, else we've merely moved the obsolete-dump
> problem over one space.  Not sure if anyone would see that as a fatal
> objection to the scheme.  I think it's a pretty minor point as long as
> we are liberal about including template entries in the standard distro,
> so that you'd seldom need to add one by hand.)
> 
> Finally, you noticed I stuck an ACL column in there.  I am imagining
> that the superuser could grant USAGE rights on a template to designated
> people (eg, admins of individual databases), who could then issue CREATE
> LANGUAGE using that template in their databases, without needing
> superuser rights.  You'd still have to be superuser to muck with the
> templates of course, but given a known-good template there's no reason
> why a non-superuser shouldn't be allowed to instantiate the language
> within his database.  (This might need a little more thought when it
> comes to untrusted PLs, but the idea seems sound.)
> 
> It's a shame that we didn't think about this before feature freeze,
> as the recent changes to create PL support functions in pg_catalog
> have made both pg_dump and createlang noticeably uglier than before.
> We could have dispensed with those hacks.  Oh well.
> 
> Comments?

This idea appears to me to be sound.  It may be worth adding the
feature during beta anyway to simplify the ugliness of pg_dump
with createlang problems.  The large number of weird configurations
"out there" could use the beta testing of this release.  I 
ran into this issue a lot with non-standard installations.   

--elein

> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
>