Re: [PATCHES] Merge pg_shadow && pg_group -- UNTESTED
От | Bort, Paul |
---|---|
Тема | Re: [PATCHES] Merge pg_shadow && pg_group -- UNTESTED |
Дата | |
Msg-id | 735D404BD9E7EB44B9CDFC27FC88809B0582D8AE@mail2.tmwsystems.com обсуждение исходный текст |
Список | pgsql-hackers |
<p><font size="2">> a) start from the user:</font><br /><font size="2">> Search for useroid in pg_auth_members.member</font><br/><font size="2">> For each returned role, search for that role in member column</font><br/><font size="2">> Repeat until all roles the useroid is in have been found</font><br /><font size="2">> [Note: This could possibly be done and stored per-user on </font><br /><font size="2">> connection,</font><br/><font size="2">> but it would mean we'd have to have a mechanism to update it when</font><br/><font size="2">> necessary, possibly instigated by the user, or just force them to</font><br /><fontsize="2">> reconnect ala unix group membership]</font><br /><font size="2">> Look through ACL list tosee if the useroid has permission </font><br /><font size="2">> or if any</font><br /><font size="2">> of theroles found do.</font><br /><font size="2">> </font><br /><font size="2">> b) start from the ACL list:</font><br/><font size="2">> Search for each roleoid in pg_auth_members.role</font><br /><font size="2">> For each returned member, search for that member in role column</font><br /><font size="2">> Upon member== useroid match is found check for permission, if</font><br /><font size="2">> granted then stop, otherwisecontinue processing</font><br /><font size="2">> Has the advantage that the search stops once it's been determined</font><br/><font size="2">> that permission is there and doesn't require updating.</font><br /><font size="2">></font><p><font size="2">If I may humbly suggest another option: </font><p><font size="2">c) Use tables forusers, roles, and user x role as you already have</font><br /> <font size="2">(Or was a user's roles in an array?I forget)</font><br /> <font size="2">Add a fourth table (access?) with the PK (user, priv, role). </font><br/> <font size="2">Whenever a privilege is granted or revoked, for a user or a role, </font><br /> <font size="2">insert or delete the appropriate rows in the access table.</font><br /> <font size="2">Thispre-loads all of the cost of maintaining the ACL and should</font><br /> <font size="2">reduce theeffort of checking a particular privilege to an index</font><br /> <font size="2">seek.</font><p><font size="2">Withthis method, a user can be granted a privilege by more than one role,</font><br /><font size="2">and if theyare removed from one of those roles, the other still grants</font><br /><font size="2">the privilege. The access tablecan also store the privileges that each</font><br /><font size="2">role has by storing the role ID in the user ID column.</font><p><fontsize="2">I know that it makes for a potentially huge table, but it makes the model</font><br /><fontsize="2">straightforward and reliable. </font><p><font size="2">Examples:</font><p><font size="2">Grant role 'foo'privilege 'bar':</font><br /><font size="2">INSERT INTO access (user, priv, role ) VALUES ( 'foo', 'bar', 'foo' );</font><p><fontsize="2">Grant user 'baz' role 'foo':</font><br /><font size="2">INSERT INTO access ( user, priv, role )</font><br/> <font size="2">SELECT 'baz', priv, role FROM access WHERE user = 'foo';</font><p><font size="2">Removeuser 'baz' from role 'foo':</font><br /><font size="2">DELETE FROM access WHERE user = 'baz' AND role = 'foo';</font><p><fontsize="2">Remove privilege 'bar' from role 'foo':</font><br /><font size="2">DELETE FROM access WHEREpriv = 'bar' AND role = 'foo';</font><br /><font size="2">-- Note that this automatically cleaned up all of the users,too.</font><p><font size="2">Grant privilege 'bar' to user 'baz' without a role involved:</font><br /><font size="2">INSERTINTO access ( user, priv, role ) VALUES ( 'baz', 'bar', 'baz' );</font><p><font size="2">Grant user 'postgres'privilege 'su' in a hard-to-revoke way:</font><br /><font size="2">INSERT INTO access ( user, priv, role ) VALUES( 'postgres', 'su', '' );</font><p><font size="2">Check to see if user 'baz' has privilege 'bar':</font><br /><fontsize="2">SELECT user, priv, role FROM access WHERE user = 'baz' AND priv = 'bar';</font><br /><font size="2">-- Thiseven tells you the role(s) that grant the privilege.</font><p><font size="2">Inheritance from role to role can even behandled by repeating the inserts</font><br /><font size="2">or deletes with appropriate roles. (This would even allow arole to inherit</font><br /><font size="2">a privilege from multiple parent roles, and work correctly if it is revoked</font><br/><font size="2">by one.)</font>
В списке pgsql-hackers по дате отправления: