Re: State of the art re: group default privileges

On 03/20/2013 08:05 PM, Adrian Klaver wrote:
>>
>> 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.

I must have misspoken; things being owned by dev_user is not a problem.

It's that, when we have 100 databases and I add a new developer, his
permissions don't really kick in automatically. I have to go back and
run a command on each database to which he should have access.

Since I'm going to script it, it doesn't really matter /which/ commands
I need to run. So it could be SET ROLE, or ALTER DEFAULT PRIVILEGES, or
whatever else. But I shouldn't need to do any of it -- adding the user
to the developers group should make him a developer (in all databases
where that is meaningful), and that should be the end of it.

Imagine if, after adding yourself to the unix 'postgres' group, you had
to go around and run a command on every file belonging to the 'postgres'
group. And otherwise, you wouldn't be able to access those files. That
would be weird, right? No one would want to do it, right? I don't want
to do it in the database either =)

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

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.

I do appreciate the suggestions though, so don't interpret my pessimism
as lack of appreciation.