Thread: State of the art re: group default privileges
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.
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
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.
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)
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.
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
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.
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
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.
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
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.)
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
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.