Обсуждение: (Default) Group permissions
We use Postgres for shared hosting; i.e. what most people use MySQL for. The biggest headache for us so far has been that we're unable to get group permissions set up effectively so that different groups of customers, admins, apaches, etc. can access/modify the data they need, without manual intervention from someone with root and a relatively deep knowledge of the permissions system. I posted about this a while ago, and I promised to step back and come up with an example of the type of situation I'm talking about. It's not perfect, but if I don't post it now, it'll never get finished: https://github.com/mjorlitzky/postgres-groups This is a "test suite" for the sort of behavior I'd like to achieve. I've tried to explain what I want in the README, and I've implemented it in the filesystem with only 4 commands. The closest I was able to come in Postgres is a mess (see pg-test/02-create-permissions.sh), and doesn't work when we add a new user in pg-test/04-add-new-user-and-retest.sh. First, if I'm just overlooking something, I'd really like to know how I can make this work in 02-create-permissions.sh! If not, consider this a feature request that would make Postgres much more popular on shared hosts. I'll answer any questions on- or off-list; like I said, it ain't perfect, but it attained a permanent spot somewhere in the middle of my todo list so I'm posting it as-is. **THIS WILL ADD SYSTEM USERS AND MESS WITH YOUR LOCAL POSTGRES INSTALL** It will clean up after itself and should be safe, but please read the scripts before you run them as root.
On 6/30/2013 12:46 PM, Michael Orlitzky wrote:
> We use Postgres for shared hosting; i.e. what most people use MySQL for.
> The biggest headache for us so far has been that we're unable to get
> group permissions set up effectively so that different groups of
> customers, admins, apaches, etc. can access/modify the data they need,
> without manual intervention from someone with root and a relatively deep
> knowledge of the permissions system.
90% of shared hosting is one user one database, right?
CREATE USER username PASSWORD 'somepassword';
CREATE DATABASE username OWNER username;
done.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
John R Pierce wrote > On 6/30/2013 12:46 PM, Michael Orlitzky wrote: >> We use Postgres for shared hosting; i.e. what most people use MySQL for. >> The biggest headache for us so far has been that we're unable to get >> group permissions set up effectively so that different groups of >> customers, admins, apaches, etc. can access/modify the data they need, >> without manual intervention from someone with root and a relatively deep >> knowledge of the permissions system. > > 90% of shared hosting is one user one database, right? > > CREATE USER username PASSWORD 'somepassword'; > CREATE DATABASE username OWNER username; > > done. So PostgreSQL is only useful, for shared hosting, when the only permissible access is via vendor-supplied resources (APIs, administrators, etc...)? With recent improvements in row-level security it seems that some desire exists to make shared hosting (on a single instance) more useful; but this is all for naught if you cannot get the permissions right. I haven't looked into the OP's materials but my experience tells me that either (or both) the permissions system is lacking some features or the resources to get people to properly use the features is insufficient. I'm hoping that this is all a case of humor just being difficult to communicate in text since even if the OP is just mis-understood the complaint is legitimate. I'd look deeper by I'm likely in the same boat so but I currently belong with the 90%. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Default-Group-permissions-tp5761938p5761952.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Sun, Jun 30, 2013 at 05:45:47PM -0700, David Johnston wrote: > So PostgreSQL is only useful, for shared hosting, when the only permissible > access is via vendor-supplied resources (APIs, administrators, etc...)? No, of course not, especially in light of recent improvements. But any finely-grained permissions system is a pain to use. (E.g.: WindowsNT from the very beginning had the excellent granularity that users of VMS were used to, but it was hard to manage, just like it was in VMS. In Windows-land, this meant endless compromises because of people doing things as Administrator; that wasn't a condemnation of the ACL system, which really was very good. But as a practical security system, it turned out to be weak because of usability.) If you want "easy", then just give different databases per user. If you want complicated, you need an administrator; yes, that needs to be in some sense under the control of the host. We have roughly 40 years of experience with these things, and the evidence is that "comprehensive but easy" is either badly insecure or very hard to operate well. Which trade do you want to make? Best, A -- Andrew Sullivan ajs@crankycanuck.ca
On 06/30/2013 07:06 PM, John R Pierce wrote:
> On 6/30/2013 12:46 PM, Michael Orlitzky wrote:
>> We use Postgres for shared hosting; i.e. what most people use MySQL for.
>> The biggest headache for us so far has been that we're unable to get
>> group permissions set up effectively so that different groups of
>> customers, admins, apaches, etc. can access/modify the data they need,
>> without manual intervention from someone with root and a relatively deep
>> knowledge of the permissions system.
>
> 90% of shared hosting is one user one database, right?
>
> CREATE USER username PASSWORD 'somepassword';
> CREATE DATABASE username OWNER username;
>
> done.
>
For you to guess the right answer, you're going to have to at least read
the requirements that I listed in the README =)
I've already considered this, and it's not sufficient even when the
customer doesn't need access to the DB. I need access to it, and so do
my coworkers, and anything that winds up in the DB should be
read/writable by all of us.
The point of the test case was that you can delete everything I have in
step #2, try this, and it will fail:
$ sudo ./01-create-actors.sh
$ sudo psql -U postgres -d customer_project <<< \
"ALTER DATABASE customer_project OWNER TO alice"
ALTER DATABASE
$ sudo psql -U postgres -d dba_project <<< \
"ALTER DATABASE dba_project OWNER TO dba1"
ALTER DATABASE
$ sudo ./03-run-tests.sh
ERROR: bob can't modify dba1's table.
$ sudo ./04-add-new-user-and-retest.sh
ERROR: dba2 can't modify alice's table.
$ sudo ./05-destroy-actors.sh
If you get an ERROR, it means one of the requirements (explained in the
README) wasn't met.
On 06/30/2013 08:45 PM, David Johnston wrote: > > So PostgreSQL is only useful, for shared hosting, when the only permissible > access is via vendor-supplied resources (APIs, administrators, etc...)? > I'm not sure I understand, but I don't think that's what I'm saying. I want my customers and coworkers to be self-sufficient. As it stands, every time a new developer or database is created, they have to ask me to log in as the superuser and perform dark magic to set the permissions correctly. This gets worse as the number of users/databases grows. I'd like there to be some way to do it automatically, like we can with the website directory on the filesystem using the four commands I mention in the README. This works forever, for any number of users or subdirectories, without my intervention. > With recent improvements in row-level security it seems that some desire > exists to make shared hosting (on a single instance) more useful; but this > is all for naught if you cannot get the permissions right. We only need database-level permissions, but this sounds right. > I haven't looked into the OP's materials but my experience tells me that > either (or both) the permissions system is lacking some features or the > resources to get people to properly use the features is insufficient. Precisely. I'm very open to the possibility that I'm doing something wrong. That would be the easiest to fix.
On 06/30/2013 09:12 PM, Andrew Sullivan wrote: > > If you want "easy", then just give different databases per user. If > you want complicated, you need an administrator; yes, that needs to be > in some sense under the control of the host. We have roughly 40 years > of experience with these things, and the evidence is that > "comprehensive but easy" is either badly insecure or very hard to > operate well. Which trade do you want to make? > This is a false, er, trichotomy? The requirements I listed aren't very hard to meet. Here's how you do it for a directory on the filesystem (why do I get the feeling nobody is going to check out the repo): # Admins can do anything. setfacl -m group:admins:rwx *-project setfacl -d -m group:admins:rwx *-project # The customer's developers can access their own projects. setfacl -m group:customer-devs:rwx customer-project setfacl -d -m group:customer-devs:rwx customer-project # The anonymous user can only read things. setfacl -m user:anonymous:rx customer-project setfacl -d -m user:anonymous:rx customer-project This will work for eternity, and is perfectly secure. "Easy" is relative, but it's easy for me, and I only have to do it once, so who cares. I have find/xargs scripts that do the hard part for me.
On Sun, Jun 30, 2013 at 09:31:18PM -0400, Michael Orlitzky wrote:
> (why do I get the feeling nobody is going to check out the repo):
Probably because you're asking random strangers on the Internet to
help you solve their problems, and many of such strangers have other
things to do than go somewhere else to learn about your problems.
> # Admins can do anything.
You've been able to create this situation with the superuser flag for
as long as I can remember (I started with Postgres in the 6.5.x era,
but I won't claim my memory goes back that far).
> # The customer's developers can access their own projects.
Surely this is the "create a database per user" issue. Give each dev
user a ROLE that is the same as the owner of the database. This has
been available for many releases.
> # The anonymous user can only read things.
Create a role that can read anything (in a database? In all
databases? You don't say) and GRANT that automatically to these anon
users. This has been possible for ages.
> This will work for eternity, and is perfectly secure.
It is not even remotely "perfectly" secure. It has truck-sized holes.
Every dev can screw over every other's area. There is no
write-can't-read case. Admins are all or nothing. This is
"perfectly" secure only to someone who thinks the traditional UNIX
permissions system is a good model. Anyone who has spent much more
time with the disasters of ACL abuse knows that a general purpose
system cannot be both easy and fully secure ("hard and fully secure"
also turns out to be either false or impossible). But if the
description you give is accurate, then Postgres has been able to do
this for ages, and I've used exactly the Postgres facilities to
implement something like what you're describing. Any 9-era manual has
all you need for this, in my experience. I'm not sure what to tell
you except that, perhaps, you need a Postgres consultant.
A
--
Andrew Sullivan
ajs@crankycanuck.ca
Michael Orlitzky wrote: > We use Postgres for shared hosting; i.e. what most people use MySQL for. > The biggest headache for us so far has been that we're unable to get > group permissions set up effectively so that different groups of > customers, admins, apaches, etc. can access/modify the data they need, > without manual intervention from someone with root and a relatively deep > knowledge of the permissions system. > > I posted about this a while ago, and I promised to step back and come up > with an example of the type of situation I'm talking about. It's not > perfect, but if I don't post it now, it'll never get finished: > > https://github.com/mjorlitzky/postgres-groups I took a look, but it takes more time than I'm willing to spend to actually get to your problem. Could you outline briefly what the problem is? Yours, Laurenz Albe
On 06/30/2013 09:56 PM, Andrew Sullivan wrote: > On Sun, Jun 30, 2013 at 09:31:18PM -0400, Michael Orlitzky wrote: >> (why do I get the feeling nobody is going to check out the repo): > > Probably because you're asking random strangers on the Internet to > help you solve their problems, and many of such strangers have other > things to do than go somewhere else to learn about your problems. > It's a link to a README file. You certainly don't have to clone the repo and run the scripts. >> # Admins can do anything. > > You've been able to create this situation with the superuser flag for > as long as I can remember (I started with Postgres in the 6.5.x era, > but I won't claim my memory goes back that far). > I'm not giving root to people who don't need it. They need to be able to read/write any database. >> # The customer's developers can access their own projects. > > Surely this is the "create a database per user" issue. Give each dev > user a ROLE that is the same as the owner of the database. This has > been available for many releases. > >> # The anonymous user can only read things. > > Create a role that can read anything (in a database? In all > databases? You don't say) and GRANT that automatically to these anon > users. This has been possible for ages. > In one database. The example.com user should be able to read the example.com database. If you can come up with a way to grant permissions automatically, I'd like to hear it. You can do it for a user but not for a group, which is the whole problem I'm trying to describe. >> This will work for eternity, and is perfectly secure. > > It is not even remotely "perfectly" secure. It has truck-sized holes. I defined a set of requirements, and these permissions exactly meet them without granting anyone access that they don't need. That's what I want. I'm not going to argue over the meaning of "secure."
On 07/01/2013 03:36 AM, Albe Laurenz wrote:
>
> I took a look, but it takes more time than I'm willing to spend
> to actually get to your problem.
>
> Could you outline briefly what the problem is?
>
(I'm going to copy from the README a bit, but I'll try to pare it down)
I want to be able to create a database, set up the (default) group
permissions, and have them work, even when a new user is added to one of
the groups. Right now I don't know of a way to get default group
permissions.
The example I came up with requires two groups, and five users (two in
each group):
The two groups:
* admins
These guys can modify anything on the server, but the objects they
create should not necessarily be shared to others.
* customer-devs
They should be able to access anything in their own databases.
And the users:
* dba1 (admins, customer-devs)
The first system administrator. He can do whatever he wants on the
server, but if he creates an object in one of the customer's
databases, it should be visible to and writable by customer-devs.
* dba2 (admins, customer-devs)
Same as dba1.
* anonymous
The website user that will be used to read (only) data from the
customer's databases.
* alice (customer-devs)
An employee of our customer. Everything she creates in one of the
customer's databases should be writable by bob and vice-versa.
* bob (customer-devs)
Another customer employee. Same as alice.
If I could set up a database with (default) permissions that worked this
way, I'd be happy.
Right now, I can get it working temporarily with a huge mess of scripts,
but if another customer-dev gets added, I have to hop in as the
superuser and run O(n) commands again, where n is either the number of
databases or number of users (depending on which solution you choose).
On Mon, Jul 01, 2013 at 09:34:24AM -0400, Michael Orlitzky wrote: > >> # Admins can do anything. > > > > You've been able to create this situation with the superuser flag for > > as long as I can remember (I started with Postgres in the 6.5.x era, > > but I won't claim my memory goes back that far). > > > > I'm not giving root to people who don't need it. They need to be able to > read/write any database. So not "can do anything", but can read and write any database. Looks to me to be something like CREATE ROLE adminuser NOSUPERUSER NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT LOGIN NOREPLICATION ADMIN; Whenever a database is created, you need to GRANT ALL PRIVILEGES ON database TO adminuser. > In one database. The example.com user should be able to read the > example.com database. If you can come up with a way to grant permissions > automatically, I'd like to hear it. You can do it for a user but not for > a group, which is the whole problem I'm trying to describe. There's no distinction between users and groups. They're all ROLEs. According to the manual, that's been true since PostgreSQL 8.1. You need to create your users with certain ROLEs, is all. For instance, 'CREATE ROLE newuser LOGIN IN ROLE examplereader;' Then you GRANT the various privileges you want on the database. It's true that the role system feels a little odd if you're used to the user handling in some other systems (like UNIX). But it isn't impossible to do what you appear to want. Best, A -- Andrew Sullivan ajs@crankycanuck.ca
On 07/01/2013 10:21 AM, Andrew Sullivan wrote: > > So not "can do anything", but can read and write any database. Looks > to me to be something like > > CREATE ROLE adminuser NOSUPERUSER NOCREATEDB NOCREATEROLE > NOCREATEUSER INHERIT LOGIN NOREPLICATION ADMIN; > > Whenever a database is created, you need to GRANT ALL PRIVILEGES ON > database TO adminuser. > This is basically what we have on our real system, and is fine so far... >> In one database. The example.com user should be able to read the >> example.com database. If you can come up with a way to grant permissions >> automatically, I'd like to hear it. You can do it for a user but not for >> a group, which is the whole problem I'm trying to describe. > > There's no distinction between users and groups. They're all ROLEs. > According to the manual, that's been true since PostgreSQL 8.1. > This is technically true, and is why the default permissions don't work for groups. See below. > You need to create your users with certain ROLEs, is all. For instance, > 'CREATE ROLE newuser LOGIN IN ROLE examplereader;' Then you GRANT the > various privileges you want on the database. > Of course, and this works for any objects that exist when I do it. But if someone creates a new table in the database, I want the other users to be able to access it automatically, without me having to log in and GRANT something. The initial GRANT doesn't do that. There's something that sounds like it will work, ALTER DEFAULT PRIVILEGES, but it only works for the one role that creates the new object. So if the 'developers' role has default privileges set, and alice is a developer, she can create a table and the default privileges will *not* apply. They would only apply if 'developers' logged in and created the table. There was another post about this a while ago: http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7OjQ_KtadrzDGd45jU7Gke3dUZ0Sz92g@mail.gmail.com that went unresolved. The solution that I'm using right now is to ALTER DEFAULT PRIVILEGES for alice and bob individually, but this is basically a pen-and-paper method of access control, and I'm very wary of making a mistake. Furthermore, it scales terribly if you have lots of users and databases. I'd like to be able to manage their permissions as a group, rather than having to keep track of the group members mentally.
Michael Orlitzky wrote: > I want to be able to create a database, set up the (default) group > permissions, and have them work, even when a new user is added to one of > the groups. Right now I don't know of a way to get default group > permissions. There is none, as far as I can say. You have two options: - You have the default privileges on the roles, and every user has to run SET ROLE to the role before he or she creates objects. - Whenever you create a new user, you set default privileges for the user. I guess that the first solution is not useful until there are event triggers for database logins. > The example I came up with requires two groups, and five users (two in > each group): [...] > If I could set up a database with (default) permissions that worked this > way, I'd be happy. > > Right now, I can get it working temporarily with a huge mess of scripts, > but if another customer-dev gets added, I have to hop in as the > superuser and run O(n) commands again, where n is either the number of > databases or number of users (depending on which solution you choose). I guess I can think of no better way to do it. I'd try to automatize the process as much as possible, e.g. by writing functions that create users and automatically set all the necessary default privileges. Yours, Laurenz Albe
On 07/02/2013 03:35 AM, Albe Laurenz wrote: > Michael Orlitzky wrote: >> I want to be able to create a database, set up the (default) group >> permissions, and have them work, even when a new user is added to one of >> the groups. Right now I don't know of a way to get default group >> permissions. > > There is none, as far as I can say. > > You have two options: > - You have the default privileges on the roles, and every user > has to run SET ROLE to the role before he or she creates objects. > - Whenever you create a new user, you set default privileges > for the user. > > I guess that the first solution is not useful until there are > event triggers for database logins. > Thanks for taking a look. The first isn't manageable because it requires users to do things. The second is what I've decided on for now, but the way that I know which default privileges to set is to look up his group membership "by hand" and set his default privileges on the databases where the group should have them. I'd love it if that step could be automated.