Re: State of the art re: group default privileges

On 03/20/2013 04:11 PM, Michael Orlitzky wrote:
> On 03/20/2013 06:40 PM, Adrian Klaver wrote:
>> On 03/20/2013 03:26 PM, Michael Orlitzky wrote:
>>> On 03/20/2013 05:18 PM, Rob Sargent wrote:
>>
>>>
>>> At the moment, everyone's just experimenting. Even with the proper
>>> tooling, my blog app shouldn't have to handle the database permissions
>>> table-by-table. I should be able to set up sensible defaults.
>>>
>> CREATE ROLE adrian LOGIN;
>> CREATE ROLE ranger LOGIN;
>> CREATE ROLE dev_user ROLE;
>> GRANT dev_user TO adrian;
>> GRANT dev_user TO ranger;
>>
>> ALTER ROLE adrian IN DATABASE test set role=dev_user;
>>
>> aklaver(at)panda:~> psql -d test -U adrian
>> ...
>
>
> Thanks, this is extremely close, but doesn't quite nail it: at the end,
> what happens if you create a table as ranger? By default, adrian doesn't
> have access to it. You could of course do,
>
> ALTER ROLE ranger IN DATABASE test set role=dev_user;
>
> Now everything in the database will be owned by dev_user. But what
> happens if we have 100 databases (this is realistic for us), and add a
> new developer a year down the road? I have to not only add him to
> dev_user, but look through each database, figure out which ones we've
> used this trick on, and do,

Not sure why everything being owned by dev_user is a problem, you said
the developers don't care about permissions or want to deal with them so
why does it matter what role their objects get created as? As long as
developer roles inherit dev_user they get common access to the objects.

Leave out the IN DATABASE and it will work for all databases in cluster.

>
> ALTER ROLE the_new_guy IN DATABASE foo set role=dev_user;
>
> And I can already achieve this result with a pile of scripts. It just
> feels half-assed. When I add someone to a group, they should inherit the
> permissions of the group. More convenient, way safer.
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com