Обсуждение: Procedural language definitions (was Re: 8.1 and syntax checking at create time)

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 PLlantrusted    boolean        trusted?lanhandler    text        name of
itscall handler functionlanvalidator    text        name of its validator function, or NULLlanlibrary    text
pathof shared library, eg $libdir/plpgsqllanacl        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 justCREATE 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
likeCREATE 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?
        regards, tom lane

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
> 

elein@varlena.com (elein) writes:
> On Wed, Aug 31, 2005 at 05:56:52PM -0400, Tom Lane wrote:
>> 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 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.

> 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.   

I was thinking the same thing, but it's a big change to put in during
beta.

We could trim back the size of the patch a good deal by not implementing
the ACL part just yet (ie, you'd still have to be superuser to create a
PL).  However, we'd still need to force an initdb to add the new system
catalog, and I hate to do that to our long-suffering beta testers.

An even more trimmed-back version would not create a new system catalog
now, but would use a constant table of "known PLs" that's hardwired into
the CREATE LANGUAGE code.  We could do that in a really localized
fashion, so it seems small enough for a post-beta change.

On the other hand: if we put that into beta2, and then get a related
bug report, we wouldn't be really sure if the reporter had a correct
PL definition or an incorrect one that he'd carried forward from beta1.
Forcing an initdb would let us be sure from the version what we were
dealing with.

Comments anyone?
        regards, tom lane

elein wrote:

>On Wed, Aug 31, 2005 at 05:56:52PM -0400, Tom Lane wrote:
>  
>
>>[ interesting scheme for language handlers ]
>>
>>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.   
>
>  
>

I agree with Tom that it should not be done at this stage of beta. But 
maybe we should look again at the much lower impact suggestion I made 
when we moved the handlers and validators to pg_catalog, which was to 
have pg_dump also do that move rather than leave existing handlers in 
public. I suspect that might ease the pain a few people are feeling. If 
so it would be a reasonable stopgap until we get the whole thing right 
in the next cycle.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:
> I agree with Tom that it should not be done at this stage of beta. But 
> maybe we should look again at the much lower impact suggestion I made 
> when we moved the handlers and validators to pg_catalog, which was to 
> have pg_dump also do that move rather than leave existing handlers in 
> public.

How are you retroactively going to make existing pg_dumps do that?
I think trying to handle this in pg_dump would introduce still more
inconsistency across installations, because on top of the variables
we have already, it'd matter which pg_dump version you used.

I feel the best idea for a non-initdb-forcing solution is to hardwire
the template knowledge into CREATE LANGUAGE for 8.1 (with of course the
intention of doing my full original proposal for 8.2).  With that in
place, the only messiness from loading old dumps is that you would have
handler function definitions in public --- but they wouldn't be used
(the actual languages would rely on handlers in pg_catalog) and could be
dropped easily.

One reason for doing this now rather than later is that if we wait,
in 8.2 we will be having to contend with 8.1 dumps that want to load
handler function definitions into pg_catalog.  That'll be OK as long as
said definitions are correct --- but if we change any of the PL function
properties between now and 8.2, we'll have a self-inflicted problem to
deal with.  (In the PL template approach as I proposed it, any existing
function of the right name is presumed to be the right thing.)  I think
it would be a really good idea if we could get that out of pg_dump again
before 8.1 goes final.
        regards, tom lane

Tom Lane wrote:

>
>I feel the best idea for a non-initdb-forcing solution is to hardwire
>the template knowledge into CREATE LANGUAGE for 8.1 (with of course the
>intention of doing my full original proposal for 8.2).  With that in
>place, the only messiness from loading old dumps is that you would have
>handler function definitions in public --- but they wouldn't be used
>(the actual languages would rely on handlers in pg_catalog) and could be
>dropped easily.
>
>
>  
>

Ok, that sounds good. Maybe have pg_dump issue a warning about the 
useless handler funcs left lying around?

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> I feel the best idea for a non-initdb-forcing solution is to hardwire
>> the template knowledge into CREATE LANGUAGE for 8.1 (with of course the
>> intention of doing my full original proposal for 8.2).  With that in
>> place, the only messiness from loading old dumps is that you would have
>> handler function definitions in public --- but they wouldn't be used
>> (the actual languages would rely on handlers in pg_catalog) and could be
>> dropped easily.

> Ok, that sounds good. Maybe have pg_dump issue a warning about the 
> useless handler funcs left lying around?

Again, you're imagining that we can retroactively fix existing pg_dumps.
A pg_dump that's aware of this change will simply not dump handlers at
all --- so it doesn't need to issue any warning.
        regards, tom lane