Re: State of the art re: group default privileges - Mailing list pgsql-general

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.