Thread: State of the art re: group default privileges

State of the art re: group default privileges

From
Michael Orlitzky
Date:
I'm running into this exact situation:

http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7OjQ_KtadrzDGd45jU7Gke3dUZ0Sz92g@mail.gmail.com

We really need to be able to have a group of developers who can create
things and modify each others' stuff[1]. Is it still more or less
impossible?

The workaround that comes to mind is a script to enumerate all
"developers" and then set the defaults one at a time. This breaks
however when we add a new developer -- he can't access any of the
existing stuff.



[1] WITHOUT making them set their own permissions. For the sake of the
discussion, let's say they can't be trusted to get it right, or more
likely, don't feel like it.


Re: State of the art re: group default privileges

From
Alvaro Herrera
Date:
Michael Orlitzky wrote:
> I'm running into this exact situation:
>
> http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7OjQ_KtadrzDGd45jU7Gke3dUZ0Sz92g@mail.gmail.com
>
> We really need to be able to have a group of developers who can create
> things and modify each others' stuff[1]. Is it still more or less
> impossible?
>
> The workaround that comes to mind is a script to enumerate all
> "developers" and then set the defaults one at a time. This breaks
> however when we add a new developer -- he can't access any of the
> existing stuff.

I don't understand.  Why doesn't alice do a "set role dev_user" before
creating the table?  Then, the table owner is dev_user, not alice, and
default privileges for dev_user apply.  In fact you needn't run ALTER
DEFAULT PRIVILEGES at all, because dev_user will be owner of the
objects, and both alice and bob have that role.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


Re: State of the art re: group default privileges

From
Michael Orlitzky
Date:
On 03/20/2013 04:12 PM, Alvaro Herrera wrote:
> Michael Orlitzky wrote:
>> I'm running into this exact situation:
>>
>> http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7OjQ_KtadrzDGd45jU7Gke3dUZ0Sz92g@mail.gmail.com
>>
>> We really need to be able to have a group of developers who can create
>> things and modify each others' stuff[1]. Is it still more or less
>> impossible?
>>
>> The workaround that comes to mind is a script to enumerate all
>> "developers" and then set the defaults one at a time. This breaks
>> however when we add a new developer -- he can't access any of the
>> existing stuff.
>
> I don't understand.  Why doesn't alice do a "set role dev_user" before
> creating the table?  Then, the table owner is dev_user, not alice, and
> default privileges for dev_user apply.  In fact you needn't run ALTER
> DEFAULT PRIVILEGES at all, because dev_user will be owner of the
> objects, and both alice and bob have that role.
>

It comes down to a separation of concerns. These developers shouldn't
(and really, don't) know/care what the privileges should be. They don't
know that they're even in a group. Why should they?

As with filesystem permissions, the admin should be able to set this all
up (correctly) and forget about it.



Re: State of the art re: group default privileges

From
Rob Sargent
Date:
On 03/20/2013 02:24 PM, Michael Orlitzky wrote:
> On 03/20/2013 04:12 PM, Alvaro Herrera wrote:
>> Michael Orlitzky wrote:
>>> I'm running into this exact situation:
>>>
>>> http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7OjQ_KtadrzDGd45jU7Gke3dUZ0Sz92g@mail.gmail.com
>>>
>>> We really need to be able to have a group of developers who can create
>>> things and modify each others' stuff[1]. Is it still more or less
>>> impossible?
>>>
>>> The workaround that comes to mind is a script to enumerate all
>>> "developers" and then set the defaults one at a time. This breaks
>>> however when we add a new developer -- he can't access any of the
>>> existing stuff.
>>
>> I don't understand.  Why doesn't alice do a "set role dev_user" before
>> creating the table?  Then, the table owner is dev_user, not alice, and
>> default privileges for dev_user apply.  In fact you needn't run ALTER
>> DEFAULT PRIVILEGES at all, because dev_user will be owner of the
>> objects, and both alice and bob have that role.
>>
>
> It comes down to a separation of concerns. These developers shouldn't
> (and really, don't) know/care what the privileges should be. They don't
> know that they're even in a group. Why should they?
>
> As with filesystem permissions, the admin should be able to set this all
> up (correctly) and forget about it.
>
>
>
What's your process?  First I've heard of a group of dev's ignorant of
permission _and_ trusted to change things in a db which affect others.

If they are in a group, can that not define the role and go from there
with std permission layouts?

Are these mostly DDL changes?  Might want to look at migrations tools
(MyBatis, flyway and others)



Re: State of the art re: group default privileges

From
Michael Orlitzky
Date:
On 03/20/2013 05:18 PM, Rob Sargent wrote:
> What's your process?  First I've heard of a group of dev's ignorant of
> permission _and_ trusted to change things in a db which affect others.

It's a playground for a group of people. They want to be able to create
stuff, and then modify that stuff. No one has to see it. There are no
consequences to their being completely oblivious to the permissions and
ownership. It's not really an outlandish use case.

(rant ahead)

Windows servers, you can create a share for a bunch of, say, attorneys,
and let them throw WordPerfect documents in it. Any attorney can modify
any document. None of them need to be system administrators or
understand the implications of the permissions on newly-created files,
because the admin (you) has made sure that the share is e.g. not visible
to "Everyone." This is done in every small business on Earth.

In Unix, we have the setgid bit. If you create an "attorneys" group, and
setgid the "documents" directory, then the same thing is achieved.
Newly-created documents are owned by the attorneys group and so any
attorney can modify it (if you set the umask properly). The permissions
are up to the system administrator, nobody else has to care.

MSSQL and Oracle both let you do the same thing with groups or roles or
whatever they're called. If you want your permissions to be as
restrictive as possible (i.e. correct), but not a huge pain in the ass,
then you need to be able to grant those restrictive-as-possible
permissions automatically.

Nobody would put up with you if you made them manually set the
permissions on every new file that they created. More importantly, if
you did, most of the permissions would be screwed up, because nobody but
you (our hypothetical admin) cares. They just want it to work. And chmod
777 makes it work, unless it already worked by default (my goal).

My use case is the same. I have a bunch of people who want it to just
work, and I'm the one who knows how the ownership and permissions should
be set. I should be able to make it work for them. It's like, the
canonical use case for user/group permission systems.


> If they are in a group, can that not define the role and go from there
> with std permission layouts?

Not sure what you mean? They're all in the same role, but there's no way
to make sure that everyone in that role can access the objects that
other members create.



> Are these mostly DDL changes?  Might want to look at migrations tools
> (MyBatis, flyway and others)

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.



Re: State of the art re: group default privileges

From
Adrian Klaver
Date:
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
Password for user adrian:
psql (9.0.12)
Type "help" for help.

test=> SELECT current_user;
  current_user
--------------
  dev_user
(1 row)

test=> SELECT session_user;
  session_user
--------------
  adrian
(1 row)

test=> CREATE TABLE adrian_tbl(id int);
CREATE TABLE
test=> \c - ranger
Password for user ranger:
You are now connected to database "test" as user "ranger".
test=> INSERT INTO adrian_tbl VALUES (1);
INSERT 0 1



>
>


--
Adrian Klaver
adrian.klaver@gmail.com


Re: State of the art re: group default privileges

From
Michael Orlitzky
Date:
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.



Re: State of the art re: group default privileges

From
Adrian Klaver
Date:
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@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.klaver@gmail.com


Re: State of the art re: group default privileges

From
Michael Orlitzky
Date:
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.



Re: State of the art re: group default privileges

From
Adrian Klaver
Date:
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.klaver@gmail.com


Re: State of the art re: group default privileges

From
Michael Orlitzky
Date:
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.

(Not to mention: how would this work if we wanted to have two separate
developers groups? I.e. if we had devs1 and devs2, with only some people
in common.)



Re: State of the art re: group default privileges

From
Adrian Klaver
Date:
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.

Now it is entirely possible I am missing something obvious:)

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.

>
> (Not to mention: how would this work if we wanted to have two separate
> developers groups? I.e. if we had devs1 and devs2, with only some people
> in common.)
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com


Re: State of the art re: group default privileges

From
Michael Orlitzky
Date:
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.