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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> 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:
> ...
> b) start from the ACL list:
> ...

The current ACL-checking code does (b), relying on a function in_group()
that tests whether the target userid is a member of a given group.
I would suggest preserving this basic structure if only to avoid breaking
things unintentionally. However, you could think about caching the set
of groups that a user belongs to, thus combining the best features of
both (a) and (b). It's always bothered me that in_group() seemed like a
fairly expensive operation.

> [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]

No, we'd drive it off syscache invalidation. Any change in pg_auth_members
would cause us to just discard the whole membership cache. This sort of
mechanism is already in use in a couple places (schema search list
maintenance is one example IIRC --- look at namespace.c).

regards, tom lane