Re: indirect membership in group roles

On Apr 2, 6:48 pm, t(dot)(dot)(dot)(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
> Kev <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)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