Re: State of the art re: group default privileges

On 03/21/2013 11:34 AM, Adrian Klaver wrote:
> On 03/21/2013 07:52 AM, Michael Orlitzky wrote:
>> On 03/21/2013 10:39 AM, Adrian Klaver wrote:
>>>>
>>>> This won't fly unfortunately. It's a shared host, and the "developers"
>>>> are a mixed bag of our employees, consultants, and the customer's employees.
>>>
>>> Do not follow. The set role= is put on a login role. It will only work
>>> on those databases the user role is allowed to log into.
>>
>> If one of our employees creates a table for one of our other projects,
>> in one of our other databases, we don't want it being owned by a group
>> of people who don't work for us.
>>
>> Or if we're working on a project for customer2, we don't want everything
>> to be owned by the developers group if "developers" contains customer1's
>> employees.
>
> I understand the above, I am just not sure how that differs from your
> file system analogy. Say:
>
> group
> dev
> users
> aklaver
> morlitzky
>
> Both users are made members of dev and granted access on each others
> files through dev. Any one else added to dev would have the same access,
> it would seem to be the same situation. Then you are led to the question
> below on different dev groups.
>

When I add 'user3' to the 'dev' group above, he automatically gets
access to everything that we have created. Likewise, if he creates
something in a setgid 'dev' directory, then you and I could access it.
This works instantly and without intervention even if we have 100
directories owned by 'dev' with a setgid bit.

In postgres, it seems that I have to go back and either run SET ROLE or
ALTER DEFAULT PRIVILEGES on each database. Otherwise, anything that
'user3' creates will be owned by 'user3', and you and I cannot touch it.

>
> What it comes down to is the privileges system is what it is and while
> change is possible, probably not on a time scale that meets your
> immediate needs. Over the course of this conversation there have been
> quite a few scenerios presented, it might be helpful to create an
> outline of your usage cases and see what the collective wisdom comes up
> with.

Alright, I'll step back and try to draft a minimal example that covers
everything I want to do.