Re: Getting rid of pg_pltemplate

On Aug 23, 2011, at 8:31 AM, Tom Lane wrote:

> One of my goals for the extensions feature has been that we should be able
> to get rid of the pg_pltemplate system catalog, moving all the information
> therein into languages' extension definition files.  This would allow
> third-party procedural languages to be installed as easily as built-in
> ones.  We failed to get this done in 9.1, mostly because we couldn't work
> out what to do about tmpldbacreate (the feature to allow non-superuser
> database owners to create "safe" languages).  Here's a proposal for that.

Awesome.

> We'll add a new boolean parameter to extension control files, called say
> "dba_create" (ideas for better names welcome).

as_superuser? security_superuser? install_as_superuser? Note that we already have a "superuser" flag, so we'll want to
carefullydocument the relationship. 

> Presumably, a dba_create extension could also be dropped by a
> non-superuser DBA.  We could either inspect the extension control file
> again when deciding whether to allow DROP EXTENSION, or copy the flag into
> a new column in pg_extension so that the installed extension doesn't rely
> on having the control file still around.  Probably the latter is a better
> idea.

I would think so, yes.

> The above mechanism could be applied to any sort of extension, not just
> procedural language ones, and would be useful for ones involving
> C-language functions (which is most).  But it's not my purpose at the
> moment to open a debate about whether any of our existing contrib modules
> ought to get marked as dba_create.  For the moment I'm just considering
> the procedural languages.

That can be discussed after there's a feature to discuss. :-)

> (In essence, if a database administrator allows a dba_create extension
> to be installed in his extensions directory, he's certifying that he
> trusts that extension enough to allow it to be installed by
> non-superusers.  This is not just a matter of whether the extension
> itself is safe, but whether the installation script could conceivably be
> subverted by running it in a malicious SQL environment.  I'd just as
> soon start out with assuming that only for the PL extensions, which need
> do nothing except a couple of CREATE FUNCTION commands and then CREATE
> LANGUAGE.)

I think this makes sense. But as you note, it does open things up, so we probably ought to have a way to alert the DBA
thatthe extension she just downloaded and installed from PGXN has this flag set. This needs to be an informed decision. 

> Having done that, we'd mark all the standard "trusted" PLs as dba_create,
> expand the existing definition scripts for the PL extensions so that they
> fully specify the languages and their support functions (transferring all
> that knowledge from the current contents of pg_pltemplate), and then
> remove pg_pltemplate.

What about untrusted languages?

> Now, the reason we invented pg_pltemplate in the first place was to
> solve problems with updating procedural language definitions from one
> release to the next.  Essentially, if we do this, we're making a bet
> that the extensions feature provides a more complete, better-thought-out
> update mechanism than pg_pltemplate itself.  I think that's probably
> right, especially when thinking about it from the standpoint of a
> non-core PL; but it's worth pointing out that we are taking some risk of
> having to do more work than before.  For example, if we wanted to add
> another type of support function to PLs in the future, this approach
> would mean having to add an ALTER LANGUAGE command for the PLs' update
> scripts to use to add that function to an existing PL.  Otherwise we
> could not support binary-upgrade scenarios.

Who came up with this upgrade script design, anyway? ;-P

Best,

David