Re: State of the art re: group default privileges

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@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,

  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.