Thread: Getting rid of pg_pltemplate

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.

We'll add a new boolean parameter to extension control files, called say
"dba_create" (ideas for better names welcome).  If it's missing or set
to false, there's no change in behavior.  When it's true, then

(a) you must be superuser or owner of the current database to create the
extension;

(b) the commands within the extension's script will be run as though by a
superuser, even if you aren't one.

My initial thought about how to actually implement (b) was to hot-wire
superuser() so that it would return true while executing such a script.
However, the end result of that would be that the extension's contained
objects would be owned by the non-superuser DBA, and I'm not sure that's a
good idea.  I seem to recall some discussions about how the SQL owner of,
say, a C-language function could use ALTER FUNCTION on it in ways that
could open security holes.  So it might be a better idea to execute the
script as though we'd temporarily done SET ROLE to the bootstrap
superuser, so that the objects end up owned by that user.  (Of course,
we'd only need to do that when the calling user isn't already a superuser,
else he might as well own the objects.)

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.

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.

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

Once we have the above mechanism, we'd redefine CREATE LANGUAGE thusly:

1. CREATE LANGUAGE with no parameters becomes just a deprecated synonym
for CREATE EXTENSION, ie, we turn it into a request to create the
extension of the same name.  This is mostly to allow loading of old dump
files.

2. CREATE LANGUAGE with parameters is expected to be used in extension
definition files.  It becomes a superuser-only command (with the
dba_create override of course), and must specify all properties of the
language.  The existing weird rules about overriding the specified
parameters with data from pg_pltemplate go away.

3. Likewise, we get rid of the weird rules in pg_dump about when to
provide parameters for CREATE LANGUAGE.  If we're dumping a language
definition at all, dump it with full parameters.

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.

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.

Comments?
        regards, tom lane

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

"David E. Wheeler" <david@kineticode.com> writes:
> On Aug 23, 2011, at 8:31 AM, Tom Lane wrote:
>> 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?

Untrusted languages would still need to be installed by superusers.
At least, that's the policy enforced by the current contents of
pg_pltemplate, and I wasn't planning to question it.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:
> We'll add a new boolean parameter to extension control files, called say
> "dba_create" (ideas for better names welcome).  If it's missing or set
> to false, there's no change in behavior.  When it's true, then
>
> (a) you must be superuser or owner of the current database to create the
> extension;
>
> (b) the commands within the extension's script will be run as though by a
> superuser, even if you aren't one.

That's called sudo on linux.  I propose that we stick to such a name.

Do we want a more general SUDO facility in PostgreSQL?  It would be, I
guess, about the same thing as SET ROLE postgres; and you would have to
be granted the rights to “upgrade” to being that role.  I can see how
relaxing the role membership would help in the case of extensions
though, with the behavior you're describing.

Do you see other places where giving sudoer to a database owner even if
he's not in the postgres (or another superuser) role would be useful?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> We'll add a new boolean parameter to extension control files, called say
>> "dba_create" (ideas for better names welcome).  If it's missing or set
>> to false, there's no change in behavior.  When it's true, then
>> 
>> (a) you must be superuser or owner of the current database to create the
>> extension;
>> 
>> (b) the commands within the extension's script will be run as though by a
>> superuser, even if you aren't one.

> That's called sudo on linux.  I propose that we stick to such a name.

I'm not impressed with that name: it will mean nothing to Windows users,
nor for that matter to many non-sysadmin types on Unix.

> Do we want a more general SUDO facility in PostgreSQL?  It would be, I
> guess, about the same thing as SET ROLE postgres;

Yeah, I think SET ROLE already covers that territory.

The point of the current proposal is to grant a very limited subset of
superuser privileges --- specifically, the right to install specific
extensions --- to database owners.  Maybe it'd make sense to eliminate
the tie to database ownership and instead consider that you're allowed
to do this if you're a member of some predefined role, which then would
typically be GRANTed to database owners or other semi-trustworthy people.
But we don't currently have any predefined group roles like that, so
it'd be a rather large departure from past practice.
        regards, tom lane

On Tue, Aug 23, 2011 at 3:09 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> We'll add a new boolean parameter to extension control files, called say
>> "dba_create" (ideas for better names welcome).  If it's missing or set
>> to false, there's no change in behavior.  When it's true, then
>>
>> (a) you must be superuser or owner of the current database to create the
>> extension;
>>
>> (b) the commands within the extension's script will be run as though by a
>> superuser, even if you aren't one.
>
> That's called sudo on linux.  I propose that we stick to such a name.

Actually, this is somewhat more like UNIX setuid (2).

When I first started using SECURITY DEFINER functions, I thought of it
as being "like sudo."  But it's really "like setuid".
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Tom Lane <tgl@sss.pgh.pa.us> writes:
>> That's called sudo on linux.  I propose that we stick to such a name.
>
> I'm not impressed with that name: it will mean nothing to Windows users,
> nor for that matter to many non-sysadmin types on Unix.

Fair enough.

>> Do we want a more general SUDO facility in PostgreSQL?  It would be, I
>> guess, about the same thing as SET ROLE postgres;
>
> Yeah, I think SET ROLE already covers that territory.

Yes, except for the need of CREATE EXTENSION where you would want to
delegate to power to database owner without granting them superuser at
will.  So I agree with your statement that we need something more than
what we already have here, even if under the hood it will probably end
up using existing mechanisms.

> The point of the current proposal is to grant a very limited subset of
> superuser privileges --- specifically, the right to install specific
> extensions --- to database owners.  Maybe it'd make sense to eliminate
> the tie to database ownership and instead consider that you're allowed
> to do this if you're a member of some predefined role, which then would
> typically be GRANTed to database owners or other semi-trustworthy people.
> But we don't currently have any predefined group roles like that, so
> it'd be a rather large departure from past practice.

I think we have something like that, in fact, with the replication
privilege.  We could have an "extension" privilege that defaults to
being granted to database owners, and that would behave like SET ROLE
superuser; when issuing extension related features.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Christopher Browne <cbbrowne@gmail.com> writes:
> Actually, this is somewhat more like UNIX setuid (2).
>
> When I first started using SECURITY DEFINER functions, I thought of it
> as being "like sudo."  But it's really "like setuid".

I see SECURITY DEFINER functions definitely as setuid for PostgreSQL,
but I was thinking about this CREATE EXTENSION thing more like sudo.  In
the former case, you manage the rights on the object (script, function),
in the latter case you manage the rights on the command issued.

Well I guess it's a very thin line here.  But maybe the parameter could
be called security_definer, knowing that the control files are a
superuser privilege thing (so the definer needs to be a superuser
granted the postgres system user).

Maybe run_script_with_superuser is more explicit for the situation though.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support