has_privs_of_role vs. is_member_of_role, redux - Mailing list pgsql-hackers

Hi,

We've had some previous discussions about when to use
has_privs_of_role and when to use is_member_of_role, and
has_privs_of_role has mostly won the fight. That means that, if role
"robert" is set to NOINHERIT and you "GRANT stuff TO robert", for the
most part "robert" will not actually be able to do things that "stuff"
could do. Now, robert will be able TO "SET ROLE stuff" and then do all
and only those things that "stuff" can do, but he won't be able to do
those things as "robert". For example:

rhaas=# set role robert;
SET
rhaas=> select * from stuff_table;
ERROR:  permission denied for table stuff_table

So far, so good. But it's clearly not the case that "GRANT stuff TO
robert" has conferred no privileges at all on robert. At the very
least, it's enabled him to "SET ROLE stuff", but what else? I decided
to go through the code and make a list of the things that robert can
now do that he couldn't do before. Here it is:

1. robert can create new objects of various types owned by stuff:

rhaas=> create schema stuff_by_robert authorization stuff;
CREATE SCHEMA
rhaas=> create schema unrelated_by_robert authorization unrelated;
ERROR:  must be member of role "unrelated"

2. robert can change the owner of objects he owns to instead be owned by stuff:

rhaas=> alter table robert_table owner to unrelated;
ERROR:  must be member of role "unrelated"
rhaas=> alter table robert_table owner to stuff;
ALTER TABLE

3. robert can change the default privileges for stuff:

rhaas=> alter default privileges for role unrelated grant select on
tables to public;
ERROR:  must be member of role "unrelated"
rhaas=> alter default privileges for role stuff grant select on tables
to public;
ALTER DEFAULT PRIVILEGES

4. robert can execute "SET ROLE stuff".

That's it. There are two other behaviors that change -- the return
value of pg_has_role('robert', 'stuff', 'MEMBER') and pg_hba.conf
matching to groups -- but those aren't things that robert gains the
ability to do. The above is an exhaustive list of the things robert
gains the ability to do.

I argue that #3 is a clear bug. robert can't select from stuff's
tables or change privileges on stuff's objects, so why can he change
stuff's default privileges? is_member_of_role() has a note that it is
not to be used for privilege checking, and this seems like it's pretty
clearly a privilege check.

On the flip side, #4 is pretty clearly correct. Presumably, allowing
that to happen was the whole point of executing "GRANT stuff TO
robert" in the first place.

The other two are less clear, in my opinion. We don't want users to
end up owning objects that they didn't intend to own; in particular,
if any user could make a security-definer function and then gift it to
the superuser, it would be a disaster. So, arguably, the ability to
make some other role the owner of an object represents a privilege
that your role holds with respect to their role. Under that theory,
the is_member_of_role() checks that are performed in cases #1 and #2
are privilege checks, and we ought to be using has_privis_of_role()
instead, so that a non-inherited role grant doesn't confer those
privileges. But I don't find this very clear cut, because except when
the object you're gifting is a Trojan horse, giving stuff away helps
the recipient, not the donor.

Also, from a practical point of view, changing the owner of an object
is different from other things that robert might want to do. If robert
wants to create a table as user stuff or read some data from tables
user stuff can access or change privileges on objects that role stuff
owns, he can just execute "SET ROLE stuff" and then do any of that
stuff. But he can't give away his own objects by assuming stuff's
privileges. Either he can do it as himself, or he can't do it at all.
It wouldn't be crazy IMHO to decide that a non-inherited grant isn't
sufficient to donate objects to the granted role, and thus an
inherited grant is required in such cases. However, the current system
doesn't seem insane either, and in fact might be convenient in some
situations.

In short, my proposal is to change the ALTER DEFAULT PRIVILEGES code
so that you have to have the privileges of the target role, not jut
membership in the target role, and leave everything else unchanged.

Thoughts?

-- 
Robert Haas
EDB: http://www.enterprisedb.com