Hello,

For 8.0 are we going to allow group modifications to the data 
directories for PostgreSQL? It is kind of silly that it must be 700.
I think we should allow at least 770. This allows you to have 
administrators with postgresql.conf editing rights without giving
them the ability to su to postgresql.

Sincerely,

Joshua D. Drake

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Tue, 12 Oct 2004 11:51:47 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Required permissions for data directory
"Joshua D. Drake" <jd@commandprompt.com> writes:
> For 8.0 are we going to allow group modifications to the data 
> directories for PostgreSQL? It is kind of silly that it must be 700.

Not in the least.  There are many systems where users by default
are all in a "users" group, and so 770 isn't much safer than 777.

> I think we should allow at least 770. This allows you to have 
> administrators with postgresql.conf editing rights without giving
> them the ability to su to postgresql.

Being able to edit postgresql.conf gives one the ability to become
postgres (hint: you can cause the backend to load a shlib of your
choosing, or even more trivially, adjust pg_hba.conf to let you in
as superuser), so the above distinction is unenforceable.

In short: no way.

			regards, tom lane

Tue, 12 Oct 2004 15:09:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Required permissions for data directory 


Tom Lane wrote:

>
>Being able to edit postgresql.conf gives one the ability to become
>postgres (hint: you can cause the backend to load a shlib of your
>choosing, or even more trivially, adjust pg_hba.conf to let you in
>as superuser), so the above distinction is unenforceable.
>
>
>  
>

And can't we now even point to a completely different location for the 
actual data, as well as the rest of the config? I'd hate to think of 
someone changing that out from under me.

cheers

andrew

Tue, 12 Oct 2004 15:18:20 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: Required permissions for data directory
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> 
>>For 8.0 are we going to allow group modifications to the data 
>>directories for PostgreSQL? It is kind of silly that it must be 700.
> 
> 
> Not in the least.  There are many systems where users by default
> are all in a "users" group, and so 770 isn't much safer than 777.

Which is not the responsibility of us. It is the responsibility of the 
administrator of that system.

> Being able to edit postgresql.conf gives one the ability to become
> postgres (hint: you can cause the backend to load a shlib of your
> choosing, or even more trivially, adjust pg_hba.conf to let you in
> as superuser), so the above distinction is unenforceable.

Again, the responsibility of the administrator for the system.

It seems really odd that we build this dynamic, extensible database 
system -- yet cripple (at least in this way) the ability for the 
administrators of that system to have a dynamic, and extensible 
administration policy.

If we are going to take the route of... "Oh gosh, the user is probably
and idiot.", then we should also get rid of the ability to delete from ;)

Sincerely,

Joshua D. Drake



> 
> In short: no way.
> 
> 			regards, tom lane


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Tue, 12 Oct 2004 12:22:59 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: Required permissions for data directory
>>  
>>
> 
> And can't we now even point to a completely different location for the 
> actual data, as well as the rest of the config? I'd hate to think of 
> someone changing that out from under me.

We can do that in 8.0 can't we? Well then I guess it will be moot in 
about 6 months :)

> 
> cheers
> 
> andrew


-- 
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Tue, 12 Oct 2004 12:24:36 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: Required permissions for data directory
Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> Being able to edit postgresql.conf gives one the ability to become
>> postgres (hint: you can cause the backend to load a shlib of your
>> choosing, or even more trivially, adjust pg_hba.conf to let you in
>> as superuser), so the above distinction is unenforceable.

> And can't we now even point to a completely different location for the 
> actual data, as well as the rest of the config? I'd hate to think of 
> someone changing that out from under me.

Well, that's an interesting point.  As of CVS tip it is possible to keep
the config files somewhere else than the data directory, and there is no
permissions enforcement at all on the config files or their containing
directory when you do that.  I'm not sure this is a good idea, but it
does mean that Joshua can do what he wants to (and be just as insecure
as he wants to).

Should we try to enforce any permissions restrictions on the config
files when they are stored elsewhere?  If so, what?  One obvious point
is that the files and parent dir could quite legitimately be root-owned,
so we cannot simply require 700-or-less permission as we did before.

			regards, tom lane

Tue, 12 Oct 2004 15:26:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Required permissions for data directory 
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Tom Lane wrote:
>> Being able to edit postgresql.conf gives one the ability to become
>> postgres (hint: you can cause the backend to load a shlib of your
>> choosing, or even more trivially, adjust pg_hba.conf to let you in
>> as superuser), so the above distinction is unenforceable.

> Again, the responsibility of the administrator for the system.

How so?  The point is that there is *no such thing* as giving someone
config edit permissions without thereby implicitly trusting them with
the keys to the city.  If you trust them that much, you may as well let
them su to postgres.  There is no point in using group membership as a
substitute.

			regards, tom lane

Tue, 12 Oct 2004 15:31:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Required permissions for data directory 
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> 
>>Tom Lane wrote:
>>
>>>Being able to edit postgresql.conf gives one the ability to become
>>>postgres (hint: you can cause the backend to load a shlib of your
>>>choosing, or even more trivially, adjust pg_hba.conf to let you in
>>>as superuser), so the above distinction is unenforceable.
> 
> 
>>Again, the responsibility of the administrator for the system.
> 
> 
> How so?  The point is that there is *no such thing* as giving someone
> config edit permissions without thereby implicitly trusting them with
> the keys to the city.

Well that isn't entirely true. Yes, you are obviously correct in that if 
I give a user the ability to edit the pg_hba.conf file -- that user has 
the ability to become a superuser for PostgreSQL and completely screw my 
database.

That is what lawyers are for.

However, it is also true that by having the ability to give say a tier2 
the ability to edit the postgresql.conf withough the ability to log in 
as postgres or root, then that user can not stop/start the database, or 
have root access. They can however, allow another IP, user, network access.

I can also put other items in place that detect a change in those files 
fairly easily. Which means if there isn't a work order stating change 
this file, then a tier3 is notified.

   If you trust them that much, you may as well let
> them su to postgres.  There is no point in using group membership as a
> substitute.

I disagree. I trust my tier1 to make a change to the conf file and let 
me know the changes are done and ready for review. I do not trust my 
tier1 to arbitrarily turn on logging, blow the config, restart 
postgresql and have it not start up because of the error....

Allowing that tier1 to su to postgres gives them that capability.


Sincerely,

Joshua D. Drake



> 
> 			regards, tom lane


-- 
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Tue, 12 Oct 2004 12:53:35 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: Required permissions for data directory

"Joshua D. Drake" <jd@commandprompt.com> writes:

> However, it is also true that by having the ability to give say a tier2 the
> ability to edit the postgresql.conf withough the ability to log in as postgres
> or root, then that user can not stop/start the database, or have root access.
> They can however, allow another IP, user, network access.

What about in my case where it's not that I don't have permission to log in as
postgres it's just that I consider it a pain. It means I can't open files in
my own editor and other tools easily, I have to start up a separate login and
use separate tools.

Or sites where as a matter of policy DBAs and system administrators are
supposed to use their own accounts, not because they couldn't break into the
systems if they chose to, but because it's just better policy. 

In general the more often people have to authenticate as root (or postgres in
this case) the more easily it can be compromised, and the less useful audit
records are. ("hm, was this root login a compromise or was it just the 69th
normal root login that day?")

I can understand checking for "anyone" privilege on the basis that that would
never make sense. But checking for "group" access always struck me as dumb. It
assumes the sysadmin is shooting himself in the foot just because he might be.

-- 
greg


12 Oct 2004 17:18:20 -0400
Greg Stark <gsstark@mit.edu>
Re: Required permissions for data directory
Greg Stark wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> 
> 
>>However, it is also true that by having the ability to give say a tier2 the
>>ability to edit the postgresql.conf withough the ability to log in as postgres
>>or root, then that user can not stop/start the database, or have root access.
>>They can however, allow another IP, user, network access.
> 
> 
> What about in my case where it's not that I don't have permission to log in as
> postgres it's just that I consider it a pain. It means I can't open files in
> my own editor and other tools easily, I have to start up a separate login and
> use separate tools.

This sounds much as if accessing the file via SQL would be appropriate, 
i.e. the generic file functions that I've been discussing a while ago. 
They exist in a module, and will be used by the postgresql.conf 
implemented in  pgAdmin3 within the next few weeks (as soon we're out of 
feature freeze).
Regards,
Andreas

Tue, 12 Oct 2004 21:59:31 +0000
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Required permissions for data directory