On Apr 2, 6:48 pm, t...@xxxxxxxxxxxxx (Tom Lane) wrote: > Kev <kevinjamesfi...@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
