Обсуждение: indirect membership in group roles

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

Kev <kevinjamesfield@gmail.com> writes:
> 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.

Really?  Works for me:

regression=# create group student inherit;
CREATE ROLE
regression=# create group employee inherit;
CREATE ROLE
regression=# create group "user";
CREATE ROLE
regression=# grant "user" to student;
GRANT ROLE
regression=# grant "user" to employee;
GRANT ROLE
regression=# create user joe inherit;
CREATE ROLE
regression=# grant student to joe;
GRANT ROLE
regression=# create table mytable (f1 int);
CREATE TABLE
regression=# grant select on mytable to "user";
GRANT
regression=# \c - joe
psql (8.4devel)
You are now connected to database "regression" as user "joe".
regression=> select * from mytable;
 f1
----
(0 rows)

I suspect you forgot to attach the "inherit" property to the
intermediate-level group.

            regards, tom lane

On Apr 2, 6:48 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Kev <kevinjamesfi...@gmail.com> writes:
> > For some reason, which I couldn't see spelled out very well in the
> > docs for GRANT ROLE and SET ROLE,indirectmembership in the group
> > "user" doesn't give one its privileges unless you SET ROLE "user"
> > first, even if all roles involved have INHERIT set.
>
> Really?  Works for me:
>
> regression=# create group student inherit;
> CREATE ROLE
> regression=# create group employee inherit;
> CREATE ROLE
> regression=# create group "user";
> CREATE ROLE
> regression=# grant "user" to student;
> GRANT ROLE
> regression=# grant "user" to employee;
> GRANT ROLE
> regression=# create user joe inherit;
> CREATE ROLE
> regression=# grant student to joe;
> GRANT ROLE
> regression=# create table mytable (f1 int);
> CREATE TABLE
> regression=# grant select on mytable to "user";
> GRANT
> regression=# \c - joe
> psql (8.4devel)
> You are now connected to database "regression" as user "joe".
> regression=> select * from mytable;
>  f1
> ----
> (0 rows)
>
> I suspect you forgot to attach the "inherit" property to the
> intermediate-level group.
>
>                         regards, tom lane

That's interesting...

This is what I'm showing in pgAdmin3:

CREATE ROLE employee
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
ALTER ROLE employee SET search_path=public;
GRANT "user" TO employee;

CREATE ROLE "user"
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

...you know, I wonder if it's only within the context of how I'm
connecting, which is to connect as a superuser and then SET SESSION
AUTHORIZATION to the selected user.  Sorry, I should've mentioned
that.

Although, now it seems to be working.  That makes my head hurt,
because I have logs full of this:

"DBD::Pg::db selectrow_array failed: ERROR:  permission denied for
relation my_table"

...and I remember going through and testing and reading up on it until
I figured out the SET ROLE thing.  Gosh.  Well, sorry to waste your
time, I have no idea how all this was possible.  I guess I'll log my
testing a lot more verbosely next time.  Thanks for humouring me.

Kev