Обсуждение: "setuid" functions, a solution to the RI privilege problem
Email was fried, so one more time... -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ ---------- Forwarded message ---------- Date: Wed, 6 Sep 2000 16:19:08 +0200 (CEST) From: Peter Eisentraut <peter_e@gmx.net> To: PostgreSQL Development <pgsql-hackers@postgresql.org> Subject: "setuid" functions, a solution to the RI privilege problem With the code cleanup that is just coming through it is now easily possible to change the current user id during a session/connection. Hence we can now attack the issue of "setuid" functions, which would also provide a means to circumvent the well-known RI privilege problem. I haven't looked closely, but I envision it working like this: Add a boolean attribute to pg_proc, maybe "setuid", but I'd rather avoid that name. If it is false then everything happens as usual. If it is true then the function manager saves the uid before the function call, sets the current uid to the uid of the function creator, and restores it afterwards. It might end up touching only a few dozen lines in fmgr.c. As for syntax, we can easily do with the "CREATE FUNCTION WITH" mechanism, until we implement the standard syntax. What this means in particular for the RI triggers is that they would then always execute with the permission of the bootstrap user (usually "postgres"), which would give them a free ticket. OTOH, that would commit us that the "postgres" user always has to be a superuser, which should be okay I should think. For those interested in the standards, I append here a relevant section. Note that it actually requires SQL language functions to be "setuid" by default, but I think we can safely ignore that little artifact. [4.23] When the <routine body> of an SQL-invoked routine is executed and the new SQL-session context for theSQL-session is created, the SQL-session user identifier in the new SQL-session context is set to the currentuser identifier in the SQL-session context that was active when the SQL-session caused the execution of the<routine body>. The authorization stack of this new SQL-session context is initially set to empty and anew pair of identifiers is immediately appended to the authorization stack such that: - The user identifieris the newly initialized SQL-session user identifier. - The role name is the current role nameof the SQL-session context that was active when the SQL-session caused the execution of the <routinebody>. The identifiers in this new entry of the authorization stack are then modified depending onwhether the SQL-invoked routine is an SQL routine or an external routine. If the SQL-invoked routine is anSQL routine, then, if the routine authorization identifier is a user identifier, the user identifier is set to the routine authorization identifier and the role name is set to null; otherwise, the role name is set to theroutine authorization and the user identifier is set to null. If the SQL-invoked routine is an externalroutine, then the identifiers are determined according to the external security characteristic of theSQL-invoked routine: - If the external security characteristic is DEFINER, then: o If the routine authorizationidentifier is a user identifier, then the user identifier is set to the routine authorization identifier and the role name is set to the null value. o Otherwise, the role name is set to the routineauthorization identifier and the user identifier is set to the null value. - If the external securitycharacteristic is INVOKER, then the identifiers remain unchanged. - If the external security characteristicis IMPLEMENTATION DEFINED, then the identifiers are set to implementation-defined values. [11.49] <external security clause> ::= EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER | EXTERNAL SECURITY IMPLEMENTATION DEFINED -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Fri, Sep 08, 2000 at 07:14:54PM +0200, Peter Eisentraut wrote: > Date: Wed, 6 Sep 2000 16:19:08 +0200 (CEST) > From: Peter Eisentraut <peter_e@gmx.net> > To: PostgreSQL Development <pgsql-hackers@postgresql.org> > Subject: "setuid" functions, a solution to the RI privilege problem > > With the code cleanup that is just coming through it is now easily > possible to change the current user id during a session/connection. Hence > we can now attack the issue of "setuid" functions, which would also > provide a means to circumvent the well-known RI privilege problem. > This sounds good. > I haven't looked closely, but I envision it working like this: Add a > boolean attribute to pg_proc, maybe "setuid", but I'd rather avoid that > name. If it is false then everything happens as usual. If it is true then > the function manager saves the uid before the function call, sets the > current uid to the uid of the function creator, and restores it > afterwards. It might end up touching only a few dozen lines in fmgr.c. > Good for functions. Rather than a boolean, how about something to store the three standard defined behaviors DEFINER,INVOKER,IMPLEMENTATION DEFINED: "proauth" int, with #DEFINES, perhaps? Or, we could store the userid that this procedure will run as, with null signifying invoker. (BTW, that's the first time I've seen 'IMPLEMENTATION DEFINED' in a standard leaking out into the defined grammar!) I have some concerns about views, see below. > > For those interested in the standards, I append here a relevant section. > Note that it actually requires SQL language functions to be "setuid" by > default, but I think we can safely ignore that little artifact. > Well, currently, views access the tables in their FROM clause with the priviliges of the creating user, which means 'setuid' by default. As I recently found out, subselects in a view definition do _not_ run as the creating user, however. I wonder if your approach might also be useful for views? I realize this is off topic for your suggestion for functions. And I have a sneaking suspicion that the only fix for VIEWs requires the planner rewrite Tom's been working on. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Ross J. Reedstrom writes: > This sounds good. The sad part of this story is that, while setuid functions work well for me in my tree, they cannot be used for the RI problem after all. The problem is that the lookup table for builtin function is already generated at compile time (Gen_fmgrtab.sh), whereas we don't know the user id of their owner until initdb at the earliest. Hence setuid functions don't work for builtins, currently. (With 7.2 I plan to get rid of pg_shadow.usesysid and identify users via pg_shadow.oid and the superuser oid will be hard-coded into include/catalog/pg_shadow.h, so at that point they will work.) An alternative answer would be to tie the user id not to the owner of the function but to the owner of the trigger, as an additional feature. > Good for functions. Rather than a boolean, how about something to store > the three standard defined behaviors DEFINER,INVOKER,IMPLEMENTATION > DEFINED: "proauth" int, with #DEFINES, perhaps? Or, we could store > the userid that this procedure will run as, with null signifying > invoker. Well, the standards defines these three behaviours, in terms of its "authorization stack": * INVOKER -- nothing changes when the function is called * DEFINER -- push function owner's identifier on top of stack * IMPLEMENTATION DEFINED -- put whatever you want on the stack Since IMPLEMENTATION DEFINED is the default it has to be what we have now, which in turn is INVOKER. So ISTM that we do not have 3 options really. > (BTW, that's the first time I've seen 'IMPLEMENTATION DEFINED' > in a standard leaking out into the defined grammar!) I have a sneaking suspicion that this was done because certain vendors with large interests in the SQL specification process had completely incompatible behaviours by default that would not fit in with the SQL model at all, so they could only be described by "do what you want". ;-) > I have some concerns about views, see below. No clue about views... :-\ -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/