Re: Permissions with multiple groups...

nielsene(at)MIT(dot)EDU wrote:
> Version 7.1.3 (release tarball) on development machine -- Error
> 7.2.1 on production machine (7.2.1-2 Debian) -- Works
>
> I can't easily upgrade the development machine in the near future, but this
> at least gives more ammunition. If you know of any good workaround besides
> just GRANT ALL on <each_table> TO test_cib_admin, (which works) I'ld appreciate
> hearing about it.
> ...

I haven't seen this written up, and can't find it described in HISTORY
or other documentation, so here is my more-than-you-want-to-know explanation
of what changed, and a possibly easier workaround.

PostgreSQL <= 7.1.3 checked rights like this:
If rights are granted to your user name, you get those rights and only
those rights.
Else, if the right you are trying to use is granted to any group you
belong to, access is allowed.
Else, if the right you are trying to use is granted to PUBLIC,
access is allowed.
Else, access is denied.

PostgreSQL >= 7.2 checks rights like this:
If the right you are trying to use is granted to PUBLIC, access is allowed.
Else, if the right you are trying to use is granted to your user name,
access is allowed.
Else, if the right you are trying to use is granted to any group you
belong to, access is allowed.
Else, access is denied.

The point is that before 7.2, a user-specific GRANT could reduce your
rights. Starting with 7.2, you effectively get the union of all rights
assigned to PUBLIC, your user name, and all groups you belong to, so a
user-specific GRANT cannot reduce your rights at 7.2.

This explains the behavior you are seeing. User test_cib_admin is granted
DELETE and not SELECT rights, but belongs to group test_cib_all which does
have SELECT rights. Under 7.1 you are denied SELECT; under 7.2 you are
allowed.

To fix it under 7.1, you can grant SELECT rights to the user, as you said
in your message. Or, possibly better, you can make a new group just for
this user test_cib_admin, and grant the special rights you want them to
have (DELETE) to the group instead of the user. Make sure there is no
user-specific access control list entry for the user. I haven't tried
it, but "I see no reason why this shouldn't work" (my favorite tech
support quote).