Re: [HACKERS] [GENERAL] What user to defaults execute as?
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 namedefault 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] What user to defaults execute as?
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 namedefault 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] What user to defaults execute as?
On Sat, Nov 02, 2002 at 01:01:11 -0500,
Tom Lane [EMAIL PROTECTED] 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.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] What user to defaults execute as?
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 [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] What user to defaults execute as?
On Fri, Nov 01, 2002 at 21:35:40 -0500, Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] What user to defaults execute as?
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] What user to defaults execute as?
Bruce Momjian [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [GENERAL] What user to defaults execute as?
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 [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] What user to defaults execute as?
On Wed, Oct 30, 2002 at 08:27:37 -0600, Bruno Wolff III [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
