Suppose I have some group roles, say "student" and "employee", to
which I want to grant another group role, "user". I then want to give
privileges to an updateable view "my_preferences" to "user" so that
they'll be given to any login role that's a member of "student" or
"employee".
Seems sensible, right? That way some login role could even be both
"student" and "employee" and I need not add "user" to that because
it's taken care of automatically, nor do I have to worry about whether
to take away "user" if the login role later ceases to be "student" but
remains "employee", and again later when the login role ceases to be
"employee".
For some reason, which I couldn't see spelled out very well in the
docs for GRANT ROLE and SET ROLE, indirect membership in the group
"user" doesn't give one its privileges unless you SET ROLE "user"
first, even if all roles involved have INHERIT set. The difference is
seen in pg_has_role('user','member') vs pg_has_role('user','usage').
I don't understand the rationale for this limitation. It seems to
make inheritance much less useful, because then (very frequently used)
SELECT, UPDATE, etc. statements have the extra (programming and
execution) overhead of at least one SET ROLE statement, and worse,
probably have to SELECT pg_has_role() first or be ready to do some
error handling. All because the membership is indirect. Could
someone explain the reasoning to me?
Thanks,
Kev