Re: [PATCHES] Merge pg_shadow && pg_group -- UNTESTED - Mailing list 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>