Re: State of the art re: group default privileges

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

The thing is roles are global to a cluster, they will be meaningful to
all databases in the cluster.

>
> 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.

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.

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