Re: [PATCHES] Merge pg_shadow && pg_group -- UNTESTED

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > Ok.  Can I get some help defining what the New Truth will look like
> > then?  I understand users and groups pretty well but I'm not 100% sure
> > about roles.
>
> So I'm envisioning something like
[...]
> It might be better to call this by some name other than "pg_role",
[...]
> It might be better to lose the rolmembers/roladmin columns and instead
> represent membership in a separate table, roughly
[...]
> One thing that needs to be thought about before going too far is exactly
> how ACL rights testing will work, particularly in the face of roles
> being members of other roles.  That is the one performance-critical
> operation that uses these data structures, so we ought to design around
> making it fast.

Alright, here's a patch against head which adds in the tables pg_authid
and pg_auth_members as described in your previous mail.  I've gotten a
bit farther than this in terms of implementation but here's something
for people to comment on, if they'd like to.

I've been thinking about the performance issues some and have to admit
that I havn't really come to much of a solution.  It seems to me that
there's two ways to come at the issue:

a) start from the user:
   Search for useroid in pg_auth_members.member
   For each returned role, search for that role in member column
   Repeat until all roles the useroid is in have been found
   [Note: This could possibly be done and stored per-user on connection,
   but it would mean we'd have to have a mechanism to update it when
   necessary, possibly instigated by the user, or just force them to
   reconnect ala unix group membership]
   Look through ACL list to see if the useroid has permission or if any
   of the roles found do.

b) start from the ACL list:
   Search for each roleoid in pg_auth_members.role
   For each returned member, search for that member in role column
   Upon member == useroid match is found check for permission, if
   granted then stop, otherwise continue processing
   Has the advantage that the search stops once it's been determined
   that permission is there and doesn't require updating.

If we do the user-part-of-which-roles search upon connection I expect
'a' would be quite fast, obviously it has it's drawbacks though.  If we
feel that's not acceptable then I'm thinking 'b' would probably be
faster given that the ACL list is probably generally small and we can
short-circuit.  I'm afraid 'b' might still be too slow though, comments?
thoughts?  better ideas?

    Thanks,

        Stephen