Thread: Re: [GENERAL] What user to defaults execute as?

On Wed, Oct 30, 2002 at 08:27:37 -0600,
  Bruno Wolff III <bruno@wolff.to> wrote:
> Do default expressions execute with access of the user doing the insert
> or the owner of the table?
> What I was thinking was that the owner of a table may want to allow people
> to do inserts into a table and update a sequence through a default expression,
> but not let people change the sequence directly.
> A quick look under create table (in the 7.3b3 docs) didn't turn up any
> information and I don't remember running accross anything covering this
> previously.

OK, I went and tested this and I found that default expressions (at least
nextval) are executed with the permissions of the user doing the insert
and not the owner of the table.

I would like to suggest this be changed for two reasons:

It may not be obvious to the end user that inserting a row into a table
may result in other things being done with their access. I.e. you can
use a default expression as a trojan and by getting someone to do an insert
into a table you created can be used to do bad things.

I think that the table owner may want to let be insert rows into a table
with a serial column, but not want to also let people reset the sequence
number to arbitrary values.

This would be similar to rules working with the access of the rule owner.
While I am not sure about triggers, it certainly is possible to get
a similar effect be having the referenced function run with the security
of the definer.

On Wed, Oct 30, 2002 at 14:03:21 -0600,
> 
> While I am not sure about triggers, it certainly is possible to get
> a similar effect be having the referenced function run with the security
> of the definer.

I read some more on triggers and found that according to the documentation,
they appear to run as the user doing the insert, update or delete and
are specifically noted to be dangerous. And while using the execute as
definer can allow a trigger writer to provide limited access to the invoker,
it doesn't protect the invoker from the trigger writer. It seems unlikely
that triggers should be doing things to objects that the trigger owner
doesn't have rights to. And this might be another place where using the
access of the owner would be better than using that of the invoker.

Constraints also run as the user modifying a table instead of the table
owner.
Again I don't see a good reason to want to execute constraints as the
user modifying a table. But I do think there can be reasons to want to
execute them as the table owner.

To summarize, my suggestion for change is:

Execute default expressions and constraints as the owner of the table.
Execute triggers as the owner of the trigger.

On Thu, 2002-10-31 at 09:54, Bruno Wolff III wrote:
> Constraints also run as the user modifying a table instead of the table
> owner.
> Again I don't see a good reason to want to execute constraints as the
> user modifying a table. But I do think there can be reasons to want to
> execute them as the table owner.
> 
> To summarize, my suggestion for change is:
> 
> Execute default expressions and constraints as the owner of the table.
> Execute triggers as the owner of the trigger.

Can't necessarily run them as the table owner, as it may give
information to other users with the ability to ALTER that table.

However, I can see a good argument to allowing running the constraints
as the user who created the constraint. This means would require 
tracking of constraint ownership.

--  Rod Taylor

On Thu, Oct 31, 2002 at 10:17:26 -0500, Rod Taylor <rbt@rbt.ca> wrote:
> Can't necessarily run them as the table owner, as it may give
> information to other users with the ability to ALTER that table.

You have to be the table owner to alter a table. So it should be OK
to have the default expressions and check constraints run as the owner.

On Thu, 2002-10-31 at 10:33, Bruno Wolff III wrote:
> On Thu, Oct 31, 2002 at 10:17:26 -0500,
>   Rod Taylor <rbt@rbt.ca> wrote:
> > Can't necessarily run them as the table owner, as it may give
> > information to other users with the ability to ALTER that table.
> 
> You have to be the table owner to alter a table. So it should be OK
> to have the default expressions and check constraints run as the owner.

Yes, default expressions and check constraints could possibly.  However,
both revoke complex expressions (no sub-selects, etc) so there is little
point.

Functions can already suid if you are using them in check constraints
for complex lookups.


An ASSERTION may be appropriate for suid, as would REFERENCES -- but
only when explicitly asked for, and those should run as the constraint
owner NOT as the table owner.

--  Rod Taylor

On Thu, Oct 31, 2002 at 11:15:31 -0500, Rod Taylor <rbt@rbt.ca> wrote:
> 
> Yes, default expressions and check constraints could possibly.  However,
> both revoke complex expressions (no sub-selects, etc) so there is little
> point.

I disagree. They can call functions which can do unexpected things.
In particular calling nextval in default expressions is common. I think
it is also reasonable that the owner of the table and sequence may
not want people resetting the value of a sequence, while still wanting
them to be able to use nextval when inserting records.

> Functions can already suid if you are using them in check constraints
> for complex lookups.

Yes, and this is a good idea that can be used now. However I think it
would also be a good idea, if users couldn't get burned by running
unexpected functions when modifying tables owned by others. In reality
it will be rare when you would have mutually untrusted people having
this kind of interaction.

> An ASSERTION may be appropriate for suid, as would REFERENCES -- but
> only when explicitly asked for, and those should run as the constraint
> owner NOT as the table owner.

References is already handled using the REFERENCES privilege.

I am a bit confused by the constraint ownership. As far as I can tell
constraints can only be created by the table owner using create table
or alter table. I think that constraints are actually implemented with
triggers. I beleive that triggers do have owners. I also think that triggers
should be run with the access of the trigger owner.

I don't know how hard this would be to do. 7.3 does have setuid type effects
for running rules and optionally usuable for functions. So my uninformed
guess would be that it isn't too hard.

I think we open up more security problems by having the inserter doing
things as the owner of the table.

---------------------------------------------------------------------------

Bruno Wolff III wrote:
> On Wed, Oct 30, 2002 at 14:03:21 -0600,
> > 
> > While I am not sure about triggers, it certainly is possible to get
> > a similar effect be having the referenced function run with the security
> > of the definer.
> 
> I read some more on triggers and found that according to the documentation,
> they appear to run as the user doing the insert, update or delete and
> are specifically noted to be dangerous. And while using the execute as
> definer can allow a trigger writer to provide limited access to the invoker,
> it doesn't protect the invoker from the trigger writer. It seems unlikely
> that triggers should be doing things to objects that the trigger owner
> doesn't have rights to. And this might be another place where using the
> access of the owner would be better than using that of the invoker.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Fri, Nov 01, 2002 at 21:35:40 -0500, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> 
> I think we open up more security problems by having the inserter doing
> things as the owner of the table.

With triggers it is a bit hard to decide. Since people other than the
table owner can create them, but then they effectively belong to the
table owner. I think that makes having them execute as the table
owner reasonable. The table owner is taking his chances by letting
other people create triggers on his table.

For constraints and default expressions I don't see any problems for
having them execute as the table owner. This provides a small advantage
in providing limited update ability for sequences, that would otherwise
require creating a function to achieve.

As long as people realize that when they insert, update or delete from
a table owned by someone else they need to trust that person it probably
isn't a big deal. The descriptions of triggers hint at this but from
a different perspective (that of a table owner letting people create
triggers on his table) and people might not make the connection (assuming
they even read about triggers).

The problem is that the more complex you make things, the easier it is
to make a mistake.  That's why I like our simpler model unless there is
a glaring problem with it.

---------------------------------------------------------------------------

Bruno Wolff III wrote:
> On Fri, Nov 01, 2002 at 21:35:40 -0500,
>   Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> > 
> > I think we open up more security problems by having the inserter doing
> > things as the owner of the table.
> 
> With triggers it is a bit hard to decide. Since people other than the
> table owner can create them, but then they effectively belong to the
> table owner. I think that makes having them execute as the table
> owner reasonable. The table owner is taking his chances by letting
> other people create triggers on his table.
> 
> For constraints and default expressions I don't see any problems for
> having them execute as the table owner. This provides a small advantage
> in providing limited update ability for sequences, that would otherwise
> require creating a function to achieve.
> 
> As long as people realize that when they insert, update or delete from
> a table owned by someone else they need to trust that person it probably
> isn't a big deal. The descriptions of triggers hint at this but from
> a different perspective (that of a table owner letting people create
> triggers on his table) and people might not make the connection (assuming
> they even read about triggers).
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> The problem is that the more complex you make things, the easier it is
> to make a mistake.  That's why I like our simpler model unless there is
> a glaring problem with it.

I think Bruno's got a good point.  The implementation would be kind of
painful, so I've been trying to think of a reason to object to it, but
so far I don't see one ;-(

The example of a serial column (DEFAULT nextval('foo_seq')) seems
compelling.  You do not really want to grant general-purpose UPDATE
rights on foo_seq to everyone you might allow to INSERT into your
table.

And I have not yet been able to think of a concrete case where the
existing behavior (execute as calling user) is better.
        regards, tom lane

I said:
> And I have not yet been able to think of a concrete case where the
> existing behavior (execute as calling user) is better.

Okay, I've thought of one: consider the situation where you want to
label each row in a table with the ID of the user who inserted it.
Right now, you can do...,who    name    default current_user,...
or for greater security use a trigger to set the column value.
This will stop working if defaults and triggers run as the table
owner.  (You could maybe use session_user instead, but it's not
clear that that's the right thing if the user is calling setuid
functions that do things on his behalf.)

However this is only one example; I still think Bruno's got a good
argument.
        regards, tom lane

On Sat, Nov 02, 2002 at 01:01:11 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> The example of a serial column (DEFAULT nextval('foo_seq')) seems
> compelling.  You do not really want to grant general-purpose UPDATE
> rights on foo_seq to everyone you might allow to INSERT into your
> table.

If it is difficult to add setuid to defaults and constraints, a table owner
can handle nextval without too much trouble. He can to create a setuid
function that runs nextval.

I had thought since rules and functions could run as another user, that
it might be easy to do something similar for defaults, constraints and
triggers. While I think that running these as the table owner is more
logical and safer, I don't think the mutual trust situation will come
up that much in practice so as to make this a high priority item, even
if you aggree that it is a problem.

Tom Lane writes:

> Okay, I've thought of one: consider the situation where you want to
> label each row in a table with the ID of the user who inserted it.
> Right now, you can do
>     ...,
>     who    name    default current_user,
>     ...
> or for greater security use a trigger to set the column value.
> This will stop working if defaults and triggers run as the table
> owner.

According to the SQL standard, privileges on constraints should
effectively be checked at the time the constraint is created.  For
example, when you create a foreign key constraint you may need certain
REFERENCES privileges, and equally creating check constraints should
require REFERENCES privilege on tables involved in subqueries.

While the SQL standard doesn't say anything on how this should apply to
column defaults (since there you can't call user-defined functions or
subqueries in default clauses), it would make sense to carry this over.

-- 
Peter Eisentraut   peter_e@gmx.net