Thread: Re: [PATCHES] Re: [PATCH] Re: Setuid functions
Bruce Momjian writes: > > Peter might be referring to this: > > > > http://fts.postgresql.org/db/mw/msg.html?mid=1022775 > > > > There was some discussion afterward, but I don't think a definite conclusion > > was reached. > > But I see Tom Lane saying he doesn't see a security issue: > > http://fts.postgresql.org/db/mw/msg.html?mid=1022758 > > I don't pretend to understand it. Just tell me what to do with the > patch. :-) The problem with setuid functions in general is that a database user can effectively re-grant privileges to which he has no grant privileges. E.g., user1=> create table table1 (id int, secret_content text); user1=> grant select on test to user2; /* made up the syntax */ user2=> create function testfunc (int) returns text as ' user2'> begin user2'> set authorization definer; user2'> return select secret_content from table1 where id = $1; user2'> end;' as 'plpgsql'; user3=> select * from table1 where id = 5; (fails) user3=> select testfunc(5); (succeeds) Tom has a point that as soon as user2 has the select privilege, he can make a private copy of table1 and send it to user3. But if you take this attitude you might as well get rid of the fine-grained privilege system, you'd just need 'select to public'. Also, there may be other security or at least auditing mechanisms to supervise the communication between user2 and user3. Or maybe user2 and user3 are just pseudo-users implementing some sort of "least privilege" paranoid design. At least we should discuss whether we'd eventually like to have grantable privileges, and if so, how this would fit in. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Good point. Would the issue be resolved by either: - Only allowing the database superuser to use this mechanism? - Allowing it only in trigger functions? (That way a user has to actually own one of the tables) Mark Peter Eisentraut wrote: > > Bruce Momjian writes: > > > > Peter might be referring to this: > > > > > > http://fts.postgresql.org/db/mw/msg.html?mid=1022775 > > > > > > There was some discussion afterward, but I don't think a definite conclusion > > > was reached. > > > > But I see Tom Lane saying he doesn't see a security issue: > > > > http://fts.postgresql.org/db/mw/msg.html?mid=1022758 > > > > I don't pretend to understand it. Just tell me what to do with the > > patch. :-) > > The problem with setuid functions in general is that a database user can > effectively re-grant privileges to which he has no grant privileges. > E.g., > > user1=> create table table1 (id int, secret_content text); > user1=> grant select on test to user2; > > /* made up the syntax */ > user2=> create function testfunc (int) returns text as ' > user2'> begin > user2'> set authorization definer; > user2'> return select secret_content from table1 where id = $1; > user2'> end;' as 'plpgsql'; > > user3=> select * from table1 where id = 5; > (fails) > user3=> select testfunc(5); > (succeeds) > > Tom has a point that as soon as user2 has the select privilege, he can > make a private copy of table1 and send it to user3. > > But if you take this attitude you might as well get rid of the > fine-grained privilege system, you'd just need 'select to public'. Also, > there may be other security or at least auditing mechanisms to supervise > the communication between user2 and user3. Or maybe user2 and user3 are > just pseudo-users implementing some sort of "least privilege" paranoid > design. > > At least we should discuss whether we'd eventually like to have grantable > privileges, and if so, how this would fit in. > > -- > Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Mark Volpe writes: > Good point. Would the issue be resolved by either: > > - Only allowing the database superuser to use this mechanism? If you mean "only allow a superuser do define functions using this mechanism", that could work. But it would probably make this feature a lot less attractive, because any setuid function would have to run with super powers. > - Allowing it only in trigger functions? (That way a user has to actually own > one of the tables) Your premise is no longer correct in 7.2devel. > > Mark > > Peter Eisentraut wrote: > > > > Bruce Momjian writes: > > > > > > Peter might be referring to this: > > > > > > > > http://fts.postgresql.org/db/mw/msg.html?mid=1022775 > > > > > > > > There was some discussion afterward, but I don't think a definite conclusion > > > > was reached. > > > > > > But I see Tom Lane saying he doesn't see a security issue: > > > > > > http://fts.postgresql.org/db/mw/msg.html?mid=1022758 > > > > > > I don't pretend to understand it. Just tell me what to do with the > > > patch. :-) > > > > The problem with setuid functions in general is that a database user can > > effectively re-grant privileges to which he has no grant privileges. > > E.g., > > > > user1=> create table table1 (id int, secret_content text); > > user1=> grant select on test to user2; > > > > /* made up the syntax */ > > user2=> create function testfunc (int) returns text as ' > > user2'> begin > > user2'> set authorization definer; > > user2'> return select secret_content from table1 where id = $1; > > user2'> end;' as 'plpgsql'; > > > > user3=> select * from table1 where id = 5; > > (fails) > > user3=> select testfunc(5); > > (succeeds) > > > > Tom has a point that as soon as user2 has the select privilege, he can > > make a private copy of table1 and send it to user3. > > > > But if you take this attitude you might as well get rid of the > > fine-grained privilege system, you'd just need 'select to public'. Also, > > there may be other security or at least auditing mechanisms to supervise > > the communication between user2 and user3. Or maybe user2 and user3 are > > just pseudo-users implementing some sort of "least privilege" paranoid > > design. > > > > At least we should discuss whether we'd eventually like to have grantable > > privileges, and if so, how this would fit in. > > > > -- > > Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter > > -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> Mark Volpe writes: > > > Good point. Would the issue be resolved by either: > > > > - Only allowing the database superuser to use this mechanism? > > If you mean "only allow a superuser do define functions using this > mechanism", that could work. But it would probably make this feature a > lot less attractive, because any setuid function would have to run with > super powers. Patch backed out until we resolve the security issues. It remains in the patch queue. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
OK, I finally got around to adding the superuser check to my patch. So I try to test it and... ERROR: Only users with Postgres superuser privilege are permitted to create a function in the 'plpgsql' language. D'oh! So, if this is the case, then the last patch should be fine after all. Mark Peter Eisentraut wrote: > > If you mean "only allow a superuser do define functions using this > mechanism", that could work. But it would probably make this feature a > lot less attractive, because any setuid function would have to run with > super powers. >
Mark Volpe <volpe.mark@epa.gov> writes:
> ERROR: Only users with Postgres superuser privilege are permitted to create a
> function in the 'plpgsql' language.
> D'oh! So, if this is the case, then the last patch should be fine after all.
No, evidently you broke something, or your plpgsql is installed wrong.
regards, tom lane
You are right; I have forgotten to create a "trusted" language. Mark Tom Lane wrote: > > Mark Volpe <volpe.mark@epa.gov> writes: > > ERROR: Only users with Postgres superuser privilege are permitted to create a > > function in the 'plpgsql' language. > > > D'oh! So, if this is the case, then the last patch should be fine after all. > > No, evidently you broke something, or your plpgsql is installed wrong. > > regards, tom lane