Thread: Is "trust" really a good default?

Is it really such a good idea to have "trust" authentication enabled for
localhost (TCP/IP and Unix sockets) by default? Since this pretty much
means that anybody with shell access on the server (which depending on
the situation can be only dba people, or a whole lot of other people as
well) can do anything they want with the database, regardless of
permissions?

In some situations this is certainly safe (say a dedicated db server
which only trusted dba:s have access to). In others it's very definitly
not (say a shared hosting machine with hundreds of users). And even in
the first case, it provides a really simple way to get around any
auditing that is set up.

Wouldn't it be safer if you had to explicitly ask for this level if you
really know what you're doing, and default to using password auth (and
then probably have initdb require a superuser password to be specified)?


//Magnus

"Magnus Hagander" <mha@sollentuna.net> writes:
> Is it really such a good idea to have "trust" authentication enabled for
> localhost (TCP/IP and Unix sockets) by default?

No, but none of the others are better.  See previous discussions in the
archives.  I don't think the situation has changed any since the last
time we hashed this out.
        regards, tom lane

Tom Lane wrote:
> "Magnus Hagander" <mha@sollentuna.net> writes:
> > Is it really such a good idea to have "trust" authentication enabled for
> > localhost (TCP/IP and Unix sockets) by default?
> 
> No, but none of the others are better.  See previous discussions in the
> archives.  I don't think the situation has changed any since the last
> time we hashed this out.

If they supply a password to initdb, shouldn't we then require a
password in pg_hba.conf.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> If they supply a password to initdb, shouldn't we then require a
> password in pg_hba.conf.

We could, but I'm a bit disturbed about the issues of documenting two
fundamentally different out-of-the-box behaviors.  The ensuing confusion
might be worse than the existing problem.  (For instance, I can see
people expecting that if they assign the superuser a password later,
they should magically arrive at the same security state as if they'd
done it at initdb time.)

We'd also have some issues with the distributions that override the
default local auth method to be IDENT --- how would this interact
with that choice?  (Note that the RPMs have completely failed to
document this change, which doesn't make me any happier.)

The bottom line to my mind is that if there were a one-size-fits-all
authentication solution, we'd not have so many to choose from.  I don't
think we are doing DBAs any service by pretending that they might not
need to think about their choice of auth method.  I could make a good
case that the initial entry ought to be REJECT, so that you get nothing
at all until you've adjusted pg_hba.conf ...
        regards, tom lane

tom lane wrote:
> The bottom line to my mind is that if there were a one-size-fits-all
> authentication solution, we'd not have so many to choose from.  I
don't
> think we are doing DBAs any service by pretending that they might not
> need to think about their choice of auth method.  I could make a good
> case that the initial entry ought to be REJECT, so that you get
nothing
> at all until you've adjusted pg_hba.conf ...

The approach towards security from the default installation seems a
little inconsistent, and inconsistency could lead to complacency and
danger.  The server is default configured to not allow root execution,
which is a very good feature, but might lead one to believe that the
default installation is secure.  The win32 people just received a server
drubbing regarding the admin account issue.

However, trust based auth from localhost is very insecure out of the box
because anybody with shell access can root your database...they can even
bring their own psql along for the ride.

IMO, forcing su password at initdb time (allowing blank password with a
very stern warning) and bumping localhost to auth is the right way to
go.  As far as RPM's, etc. I don't think RPM considerations should be
driving security concerns.

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> IMO, forcing su password at initdb time (allowing blank password with a
> very stern warning) and bumping localhost to auth is the right way to
> go.

This isn't happening for a number of reasons, the most obvious being
that we cannot require initdb to be run interactively.  (That stern
warning will not impress /dev/null.)
        regards, tom lane

It has probably be said before, but new users need
to be able to get up and running on their *development*
system quickly.  Throwing new users for a loop with
the password configuration issues would be a problem.

Most people would put up a test server first anyway
in order to check things out and configure as necessary.

--elein

On Mon, Jul 12, 2004 at 05:27:23PM -0400, Tom Lane wrote:
> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> > IMO, forcing su password at initdb time (allowing blank password with a
> > very stern warning) and bumping localhost to auth is the right way to
> > go.
> 
> This isn't happening for a number of reasons, the most obvious being
> that we cannot require initdb to be run interactively.  (That stern
> warning will not impress /dev/null.)
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

> No, but none of the others are better.  See previous discussions in the
> archives.  I don't think the situation has changed any since the last
> time we hashed this out.

I'll chime in from the phpPgAdmin point of view.  The thing with 
phpPgAdmin is that it breaks the 'localhost' access is safe rule that 
the existing trust stuff assumes.  This is because the most common setup 
is Apache and PostgreSQL on the same machine.

The situation got SO BAD with being able to just Google for 'phpPgAdmin 
Login' and then just log straight in as 'pgsql' and no password that 
since version 3.2 or so we have had "extra login security".  That means 
that by default in phpPgAdmin we disallow any login as the 'pgsql', 
'postgres', 'root', or 'administrator' users, and you cannot log into 
any account without a password.

This has fixed the problem greatly, however we get heaps of people who 
cannot understand why they cannot log in.  Those are the people we save 
from themselves.

I think that pg_hba.conf should have md5 on all by default, and the -W 
initdb parameter should be required.

Chris

>>No, but none of the others are better.  See previous discussions in the
>>archives.  I don't think the situation has changed any since the last
>>time we hashed this out.
> 
> If they supply a password to initdb, shouldn't we then require a
> password in pg_hba.conf.

This is further to my previous suggestion that we output the encoding 
that is being defaulted to.

NEW USERS DO NOT KNOW THAT -W EXISTS!

Even the majority of experienced users don't!

It's a real pain in the butt that stuff like -E and -W aren't required 
parameters to initdb.

Chris

> >>No, but none of the others are better.  See previous discussions in
> >>the archives.  I don't think the situation has changed any
> since the
> >>last time we hashed this out.
> >
> > If they supply a password to initdb, shouldn't we then require a
> > password in pg_hba.conf.
>
> This is further to my previous suggestion that we output the
> encoding that is being defaulted to.
>
> NEW USERS DO NOT KNOW THAT -W EXISTS!
>
> Even the majority of experienced users don't!

Exactly...

How about requiring them to put in *either* -W (or --pwfile of course)
*or* a switch that *explicitly* enables trust. And if they don't put in
either of these parameters, refuse to initdb. (are other params
required?) That will at least require a concious decision to enable the
unsafe stuff. And packagers/distributions can add that trust switch if
it's necessary by their packaging system (which arguably is not very
flexible if it does, but I assume this is the reason why the default
wasn't changed - can't find the old discussions in the archives)

This will require initdb to edit pg_hba.conf on the fly and not just
copy it in, but that shuoldn't be too hard.

//Magnus

> > IMO, forcing su password at initdb time (allowing blank
> password with
> > a very stern warning) and bumping localhost to auth is the
> right way
> > to go.
>
> This isn't happening for a number of reasons, the most
> obvious being that we cannot require initdb to be run
> interactively.  (That stern warning will not impress /dev/null.)

This is the very reason --pwfile was added. It's not just a win32 fix,
it's a "any packager that needs to run without interactivity" fix. Yes,
you can stick a blank password in there, but again, this is a choice and
not a default in that case.

//Magnus

> It has probably be said before, but new users need to be able
> to get up and running on their *development* system quickly.
> Throwing new users for a loop with the password configuration
> issues would be a problem.

This is exactly the argument that was thrown out when people wanted to
be able to run their development backends as an admin account on
Windows.. These users are thrown into setting up new accounts etc, which
is a lot more work than just setting a superuser password in the db.

> Most people would put up a test server first anyway in order
> to check things out and configure as necessary.

See above.

The only difference is that one lets yuo root the machine, the other one
lets you root the database. Sure, the machine is worse, but not *that*
much worse.


//Magnus

Magnus Hagander wrote:
> This is the very reason --pwfile was added. It's not just a win32
> fix, it's a "any packager that needs to run without interactivity"
> fix. Yes, you can stick a blank password in there, but again, this is
> a choice and not a default in that case.

No, that's not what it was added for.  It was added for catering to 
packaging mechanisms that have other interfaces for interactivity.  But 
just hardcoding a default password into a package gains no security at 
all.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

"Magnus Hagander" <mha@sollentuna.net> writes:
>> This isn't happening for a number of reasons, the most 
>> obvious being that we cannot require initdb to be run 
>> interactively.  (That stern warning will not impress /dev/null.)

> This is the very reason --pwfile was added.

No, that does not address the objection in the least.  That simply
allows a level of indirection.  There still has to be user interaction
going on somewhere to supply the password.
        regards, tom lane

> >> This isn't happening for a number of reasons, the most
> obvious being
> >> that we cannot require initdb to be run interactively.
> (That stern
> >> warning will not impress /dev/null.)
>
> > This is the very reason --pwfile was added.
>
> No, that does not address the objection in the least.  That
> simply allows a level of indirection.  There still has to be
> user interaction going on somewhere to supply the password.

Right. I realised that after posting.

I still think it would be a good move to add a switch you have to
explicitly put in there to make it use "trust" auth by default. This can
spit out some warnings, which will of course be ignored by RPM and such
packagers. But for those installs the package creator made the
decisions, and we will still get most of the
install-from-source-but-don't-know-about-the-switches people.

It would, of course, be better if the RPMs could do this as well. Don'tk
now how they work, though, but I assume this is the part that has been
discussed before by people who do.

Or we could initdb with requiring password, and just refuse logins with
blank passwords. That way you get a system that can be installed, but
you cannot actually connect to it until you have set a password. We'd
need to supply a tool that could change the password without connecting
(since you can't connect with no password, catch-22) but that should be
fairly straightforward with a standalone backend, right?



For reference, does anybody know how other databases do it? Like MySQL
or Oracle (which both run on RedHat, which should mean RPMs, right?) I
know MSSQL refuses to install with blank passwords these days (didn't
use to be that way, no, which is why there are still a lot of
installations out there with blank sa passwords).

//Magnus

On Tue, 2004-07-13 at 11:03, Magnus Hagander wrote:
> > >> This isn't happening for a number of reasons, the most 
> > obvious being 
> > >> that we cannot require initdb to be run interactively.  
> > (That stern 
> > >> warning will not impress /dev/null.)
> > 
> > > This is the very reason --pwfile was added.
> > 
> > No, that does not address the objection in the least.  That 
> > simply allows a level of indirection.  There still has to be 
> > user interaction going on somewhere to supply the password.
> 
> Right. I realised that after posting.
> 
> I still think it would be a good move to add a switch you have to
> explicitly put in there to make it use "trust" auth by default. This can
> spit out some warnings, which will of course be ignored by RPM and such
> packagers. But for those installs the package creator made the
> decisions, and we will still get most of the
> install-from-source-but-don't-know-about-the-switches people.
> 
> It would, of course, be better if the RPMs could do this as well. Don'tk
> now how they work, though, but I assume this is the part that has been
> discussed before by people who do.
> 
> Or we could initdb with requiring password, and just refuse logins with
> blank passwords. That way you get a system that can be installed, but
> you cannot actually connect to it until you have set a password. We'd
> need to supply a tool that could change the password without connecting
> (since you can't connect with no password, catch-22) but that should be
> fairly straightforward with a standalone backend, right? 
> 
> 
> 
> For reference, does anybody know how other databases do it? Like MySQL
> or Oracle (which both run on RedHat, which should mean RPMs, right?) I
> know MSSQL refuses to install with blank passwords these days (didn't
> use to be that way, no, which is why there are still a lot of
> installations out there with blank sa passwords).
> 

I am sure Chris would back me up on saying that the inability to
authenticate a database connection is the #1 support problem on the
phppgadmin mailing lists.... and you want to make this harder for
people??  

I think we are obliged to provide security mechanisms that people can
use, and to make sure our software is a not a conduit of exploits for
the systems we're installed on, but forcing people to deploy the
software in a fasion that we deem acceptable for production use goes
above and beyond what we should be doing. 


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla@users.sourceforge.net> writes:
> I am sure Chris would back me up on saying that the inability to
> authenticate a database connection is the #1 support problem on the
> phppgadmin mailing lists.... and you want to make this harder for
> people??  

The other thing that bothers me about this proposal is that password
auth is certainly the least convenient-to-use auth method we have,
and it encourages insecure practices like coding passwords right into
access scripts.  So I'm not pleased with the idea of making it the
default.  For local-access-only installations, either IDENT or
socket-file-permissions-based access control is likely to be a much more
usable choice, but I don't think we can usefully make either of those
the default either.  So it still comes down to the DBA having to make a
conscious choice.

If what you want to do is raise the visibility of the need to make that
choice, we could do something like this:
initdb --trust    installs pg_hba.conf with TRUST local auth, same as nowinitdb with -W or --pwfile    installs
pg_hba.confwith MD5 local authinitdb with no relevant switch    installs pg_hba.conf with REJECT local auth
 

thus forcing the DBA to make some choice before he can do anything.

We could also add initdb --ident to install with IDENT local auth,
which would be a cleaner solution for the distros that are currently
enforcing that policy via a patch to pg_hba.conf.sample.

I suspect however that we'd wind up reverting the whole thing before
we get out of beta, because one thing I guarantee you is there will
be lots of complaints.

The only part of this discussion that I'd really be prepared to buy into
is the part about *if* you use -W or --pwfile, then set up pg_hba.conf
with MD5 as the default auth (because that's probably what the user
wants anyway).  But otherwise I think we should leave initdb's behavior
alone.  I do not agree with trying to force people to use passwords.
        regards, tom lane

At this stage, I would be happy adding --ident to enable only ident, and
-W/--pwfile to enable only MD5, and allow initdb to default to full
local access (with a warning printed that package builders would at
least see).

---------------------------------------------------------------------------

Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > I am sure Chris would back me up on saying that the inability to
> > authenticate a database connection is the #1 support problem on the
> > phppgadmin mailing lists.... and you want to make this harder for
> > people??  
> 
> The other thing that bothers me about this proposal is that password
> auth is certainly the least convenient-to-use auth method we have,
> and it encourages insecure practices like coding passwords right into
> access scripts.  So I'm not pleased with the idea of making it the
> default.  For local-access-only installations, either IDENT or
> socket-file-permissions-based access control is likely to be a much more
> usable choice, but I don't think we can usefully make either of those
> the default either.  So it still comes down to the DBA having to make a
> conscious choice.
> 
> If what you want to do is raise the visibility of the need to make that
> choice, we could do something like this:
> 
>     initdb --trust
>         installs pg_hba.conf with TRUST local auth, same as now
>     initdb with -W or --pwfile
>         installs pg_hba.conf with MD5 local auth
>     initdb with no relevant switch
>         installs pg_hba.conf with REJECT local auth
> 
> thus forcing the DBA to make some choice before he can do anything.
> 
> We could also add initdb --ident to install with IDENT local auth,
> which would be a cleaner solution for the distros that are currently
> enforcing that policy via a patch to pg_hba.conf.sample.
> 
> I suspect however that we'd wind up reverting the whole thing before
> we get out of beta, because one thing I guarantee you is there will
> be lots of complaints.
> 
> The only part of this discussion that I'd really be prepared to buy into
> is the part about *if* you use -W or --pwfile, then set up pg_hba.conf
> with MD5 as the default auth (because that's probably what the user
> wants anyway).  But otherwise I think we should leave initdb's behavior
> alone.  I do not agree with trying to force people to use passwords.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

>The only part of this discussion that I'd really be prepared
>to buy into
>is the part about *if* you use -W or --pwfile, then set up pg_hba.conf
>with MD5 as the default auth (because that's probably what the user
>wants anyway).  But otherwise I think we should leave initdb's behavior
>alone.  I do not agree with trying to force people to use passwords.


Ok. Here is a patch that does this. I still think there should be a
warning when trust is set, but I'm clearly not convincing enough about
this.

Might still be worth adding "--ident" as a parameter anyway, but in that
case only to help the distros that need it. Or not, because they already
have a way to deal with it.


//Magnus

Magnus Hagander wrote:
> >The only part of this discussion that I'd really be prepared
> >to buy into
> >is the part about *if* you use -W or --pwfile, then set up pg_hba.conf
> >with MD5 as the default auth (because that's probably what the user
> >wants anyway).  But otherwise I think we should leave initdb's behavior
> >alone.  I do not agree with trying to force people to use passwords.
>
>
> Ok. Here is a patch that does this. I still think there should be a
> warning when trust is set, but I'm clearly not convincing enough about
> this.

I think there should be a warning.  The warning will not be 100%
effective, but I see no reason _not_ to give a warning.  This is an
ease-of-user issues which are usuaully not 100% but can be very helpful.

> Might still be worth adding "--ident" as a parameter anyway, but in that
> case only to help the distros that need it. Or not, because they already
> have a way to deal with it.

I think --ident would be very helpful, and we know with OS's support
ident too.  Actually looking at the code, we need some way to define
this so initdb would know if ident was a reasonable value for this OS:

            errmsg("Ident authentication is not supported on local connections on this platform")));

Right now it is burried down inside a bunch of define tests.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

"Magnus Hagander" <mha@sollentuna.net> writes:
>> The only part of this discussion that I'd really be prepared=20
>> to buy into
>> is the part about *if* you use -W or --pwfile, then set up pg_hba.conf
>> with MD5 as the default auth (because that's probably what the user
>> wants anyway).

> Ok. Here is a patch that does this.

... and rather severely mangles the comments, too; not to mention the
more basic problem that the comments will now be wrong.

            regards, tom lane

>>> The only part of this discussion that I'd really be prepared=20
>>> to buy into
>>> is the part about *if* you use -W or --pwfile, then set up
>pg_hba.conf
>>> with MD5 as the default auth (because that's probably what the user
>>> wants anyway).
>
>> Ok. Here is a patch that does this.
>
>... and rather severely mangles the comments, too;

Um, no, it doesn't. At least not on my installation.


> not to mention the
>more basic problem that the comments will now be wrong.

That, however, it is correct :-( Sloppy.

How about a text along the line of:
CAUTION: Configuring the system for "trust" authentication allows any
local user to connect using any PostgreSQL user name, including the
superuser, over either Unix domain sockets or TCP/IP. If you are on
a multiple-user machine, this is probably not good. Change it to use
something other than "trust" authentication.



Or something along that line? Since it would no longer actually be
default. Or do we want something like "On some installations, the
default is..."?


//Magnus

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Magnus Hagander wrote:
>> Might still be worth adding "--ident" as a parameter anyway, but in that
>> case only to help the distros that need it. Or not, because they already
>> have a way to deal with it.

> I think --ident would be very helpful, and we know with OS's support
> ident too.

If we're going to be doing sed-like substitutions on pg_hba.conf.sample,
then we really really wanna discourage distros from hacking the sample
file directly, because that could break the sed results.  So I think
it's important to provide the switch.

I was toying with the notion of a different editing mechanism though,
so that initdb could emit a pg_hba.conf containing comments that are
actually pertinent to the selected behavior.  One simple way would be to
prefix each line with a keyword to select when to emit it:
    ALWAYS this text is always emitted
    NEVER  this text is never emitted (a meta-comment)
    TRUST  this text is emitted if we're selecting TRUST mode
    IDENT  this text is emitted if we're selecting IDENT mode
    etc.

            regards, tom lane

Magnus Hagander wrote:
> >>> The only part of this discussion that I'd really be prepared=20
> >>> to buy into
> >>> is the part about *if* you use -W or --pwfile, then set up
> >pg_hba.conf
> >>> with MD5 as the default auth (because that's probably what the user
> >>> wants anyway).
> >
> >> Ok. Here is a patch that does this.
> >
> >... and rather severely mangles the comments, too;
>
> Um, no, it doesn't. At least not on my installation.
>
>
> > not to mention the
> >more basic problem that the comments will now be wrong.
>
> That, however, it is correct :-( Sloppy.
>
> How about a text along the line of:
> CAUTION: Configuring the system for "trust" authentication allows any
> local user to connect using any PostgreSQL user name, including the
> superuser, over either Unix domain sockets or TCP/IP. If you are on
> a multiple-user machine, this is probably not good. Change it to use
> something other than "trust" authentication.

New wording:

CAUTION: Configuring the system for local "trust" authentication allows
any local user to connect as any PostgreSQL user, including the database
superuser. If you do not trust all your local users, use another
authenication method.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Magnus Hagander wrote:
> > not to mention the
> >more basic problem that the comments will now be wrong.
>
> That, however, it is correct :-( Sloppy.
>
> How about a text along the line of:
> CAUTION: Configuring the system for "trust" authentication allows any
> local user to connect using any PostgreSQL user name, including the
> superuser, over either Unix domain sockets or TCP/IP. If you are on
> a multiple-user machine, this is probably not good. Change it to use
> something other than "trust" authentication.
>
>
>
> Or something along that line? Since it would no longer actually be
> default. Or do we want something like "On some installations, the
> default is..."?

Woh, I didn't think we agreed that the default would change from
'trust', only that we would now emit a warning and allow other
authentication methods to be specified at initdb time.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Monday 12 July 2004 17:10, Merlin Moncure wrote:
> IMO, forcing su password at initdb time (allowing blank password with a
> very stern warning) and bumping localhost to auth is the right way to
> go.  As far as RPM's, etc. I don't think RPM considerations should be
> driving security concerns.

FWIW, the RPMs default to ident authentication, and trust is off.  This is 
however done as a patch to the sample pg_hba.conf.  A command line switch to 
initdb to mung up an ident default would be fine with me, though.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu

On Tue, 2004-07-13 at 22:27, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I think --ident would be very helpful, and we know with OS's support
> > ident too.
>
> If we're going to be doing sed-like substitutions on pg_hba.conf.sample,
> then we really really wanna discourage distros from hacking the sample
> file directly, because that could break the sed results.  So I think
> it's important to provide the switch.

Speaking for Debian, I should like to explain how pg_hba.conf is managed
(at least at present and probably in the next stable release).

The basic assumption is that a system-installed package is of universal
applicability, so there is only one (official) database cluster.  The
configuration files in that cluster are actually symlinks to
/etc/postgresql/*.  The Debian packaged version of initdb is hacked to
write those symlinks rather than copy the sample files.  (An extra
command option --debian-conffile does this, and is used by the
installation script.)

(A local user running initdb in his own space would get the upstream
behaviour, but this is not the normal case for package installations.)

The reasons for the changes are found in Debian policy:

1. All configuration files [conffiles] must be in /etc .
[motivation: administrators should be able to find configuration files
quickly, without having to research each package separately.]

2. No conffile may be changed by a package upgrade without the
administrator's consent.  A package (such as postgresql) cannot simply
overwrite a conffile such as pg_hba.conf with a new version.  Its new
version is written in parallel (/etc/postgresql/pg_hba.conf.dpkg-new)
and only overwrites the old one if the administrator consents.
[motivation: system administrators should not be surprised by having
their systems redefined without their consent.]


The default pg_hba.conf installed by a new package installation is
configured thus:

 local   all  postgres                              ident sameuser
 local   all  all                                   ident sameuser
 host    all  all      127.0.0.1  255.255.255.255   ident sameuser
 host    all  all      ::1        ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff  ident sameuser
 host    all  all      ::ffff:127.0.0.1/128         ident sameuser
 host    all  all      0.0.0.0    0.0.0.0           reject

that is, to accept local connections authenticated by ident and reject
the rest.  The adminstrator is advised not to change the first line, so
as to allow cron jobs to run.
[motivation: to install the package with a sufficient level of security
that it will not open the machine to remote exploits and to ensure that
local users cannot spoof their identity to the database or change other
people's data without permission.  We trust the local ident server,
since it is installed by the same administrator that is installing
postgresql.]


The point of this explanation is that as Debian maintainer I would have
to disable any procedures that attempt to edit these conffiles, or at
least ensure that their operation is under package control and produce
only the effects that I desire.  When initdb is rerun during major
upgrades, it must then leave the previous configuration unchanged.
Ensuring this is part of ensuring a smooth upgrade path, which is a
major part of the package maintainer's job.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "Let your character be free from the love of money,
      being content with what you have; for He Himself has
      said, "I will never desert you, nor will I ever
      forsake you."
                              Hebrews 13:5

On Tue, 2004-07-13 at 17:44, Bruce Momjian wrote:
> Magnus Hagander wrote:
> > > not to mention the
> > >more basic problem that the comments will now be wrong.
> >
> > That, however, it is correct :-( Sloppy.
> >
> > How about a text along the line of:
> > CAUTION: Configuring the system for "trust" authentication allows any
> > local user to connect using any PostgreSQL user name, including the
> > superuser, over either Unix domain sockets or TCP/IP. If you are on
> > a multiple-user machine, this is probably not good. Change it to use
> > something other than "trust" authentication.
> >
> >
> >
> > Or something along that line? Since it would no longer actually be
> > default. Or do we want something like "On some installations, the
> > default is..."?
>
> Woh, I didn't think we agreed that the default would change from
> 'trust', only that we would now emit a warning and allow other
> authentication methods to be specified at initdb time.
>

I sure hope not (and that was my understanding as well)

Incidentally that warning is a little misleading since it isn't just
trust authentication that allows the wide open connections, but the
combo of all users / all dbs / trust that does it.  For example on one
of my development machine I have a guest user who only has read access
to a specific database from a limited subnet, but with trust
authentication since random people inside the company will sometimes
want to take a look at what I am cooking up. For my needs I use the
superuser account who can access all databases but must come through
ident on a unix socket.  Different strokes for different folks eh?


Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
> > Woh, I didn't think we agreed that the default would change from
> > 'trust', only that we would now emit a warning and allow other
> > authentication methods to be specified at initdb time.
> >
>
> I sure hope not (and that was my understanding as well)
>
> Incidentally that warning is a little misleading since it isn't just
> trust authentication that allows the wide open connections, but the
> combo of all users / all dbs / trust that does it.  For example on one
> of my development machine I have a guest user who only has read access
> to a specific database from a limited subnet, but with trust
> authentication since random people inside the company will sometimes
> want to take a look at what I am cooking up. For my needs I use the
> superuser account who can access all databases but must come through
> ident on a unix socket.  Different strokes for different folks eh?

Sure, but the point is that the 'trust' line added by initdb is
wide-open.  Folks who do that fine-grained control will not get confused
by the warning, hopefully.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Oliver Elphick <olly@lfix.co.uk> writes:
> ...
> The point of this explanation is that as Debian maintainer I would have
> to disable any procedures that attempt to edit these conffiles, or at
> least ensure that their operation is under package control and produce
> only the effects that I desire.

Uh, is this relevant at all?  There has been no suggestion that initdb
should try any harder or less hard than it does now to write
$PGDATA/pg_hba.conf.  All that's been discussed is what it should write
there.  If you are going to hack on it to enforce your opinion of what
it should do, then you'll be making the same hack either way.

            regards, tom lane

> Magnus Hagander wrote:
> > > not to mention the
> > >more basic problem that the comments will now be wrong.
> >
> > That, however, it is correct :-( Sloppy.
> >
> > How about a text along the line of:
> > CAUTION: Configuring the system for "trust" authentication
> allows any
> > local user to connect using any PostgreSQL user name, including the
> > superuser, over either Unix domain sockets or TCP/IP. If
> you are on a
> > multiple-user machine, this is probably not good. Change it to use
> > something other than "trust" authentication.
> >
> >
> >
> > Or something along that line? Since it would no longer actually be
> > default. Or do we want something like "On some installations, the
> > default is..."?
>
> Woh, I didn't think we agreed that the default would change
> from 'trust', only that we would now emit a warning and allow
> other authentication methods to be specified at initdb time.

Certainly, I'm not saying it shuold change (I've given that up by now).
But the difference would be that if you used -W with initdb, it would
change the default *for that installation*. Initdb-with-no-parameters
would stay the same to keep people who don't know about the switches
happier.

//Magnus

On Wed, 2004-07-14 at 05:08, Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> > ...
> > The point of this explanation is that as Debian maintainer I would have
> > to disable any procedures that attempt to edit these conffiles, or at
> > least ensure that their operation is under package control and produce
> > only the effects that I desire.
>
> Uh, is this relevant at all?  There has been no suggestion that initdb
> should try any harder or less hard than it does now to write
> $PGDATA/pg_hba.conf.  All that's been discussed is what it should write
> there.  If you are going to hack on it to enforce your opinion of what
> it should do, then you'll be making the same hack either way.

It's just that if people are going to do things to initdb to accommodate
the distributions, they need to understand the constraints.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "God is faithful, by whom ye were called unto the
      fellowship of his Son Jesus Christ our Lord."
                                   I Corinthians 1:9

Magnus Hagander wrote:
> Certainly, I'm not saying it shuold change (I've given that up by
> now). But the difference would be that if you used -W with initdb, it
> would change the default *for that installation*.
> Initdb-with-no-parameters would stay the same to keep people who
> don't know about the switches happier.

The fallacy with this line of thought is that it assumes that one 
authentication scheme applies to all ways of connecting.  Often, the 
postgres account is not password protected for local access, for the 
benefit of autovacuum and that sort of maintenance.  Still I might like 
passwords for remote access.  The bottom line is, the user has to 
figure this out himself.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:
> Magnus Hagander wrote:
>> Certainly, I'm not saying it shuold change (I've given that up by
>> now). But the difference would be that if you used -W with initdb, it
>> would change the default *for that installation*.

> The fallacy with this line of thought is that it assumes that one 
> authentication scheme applies to all ways of connecting.

I think Magnus misspoke by saying that the "default" auth method would
change; there is no default really, and should not be.  What he was
proposing was that the initial contents of pg_hba.conf should specify
password rather than trust authentication for local access.  This does
not imply anything about non-local access rules, since there are none
in the initial pg_hba.conf file.

I don't really see a problem with doing it that way.  People who want to
use -W are presumably worried about the security of their local system,
otherwise they would just fire up the postmaster and set a password
later.  So it seems reasonable to assume that they want password auth
on local connections and go ahead and set up the initial state of
pg_hba.conf to do that.  (If that isn't what they wanted, they can just
edit pg_hba.conf; they're not any worse off than before.)

There are of course some questions about how to document this
effectively, so that it doesn't create more confusion than it avoids.
But in principle it sounds like reasonable behavior to me.
        regards, tom lane

Tom Lane wrote:
> I don't really see a problem with doing it that way.  People who want
> to use -W are presumably worried about the security of their local
> system, otherwise they would just fire up the postmaster and set a
> password later.

No, that is exactly what I don't agree with.  People might want to 
assign a password just so that the user has one, with the intention of 
configuring non-local password-protected access right after initdb 
finishes.  It's a convenience that you set the password when the user 
is logically created.

> There are of course some questions about how to document this
> effectively, so that it doesn't create more confusion than it avoids.

Yes, that is another thing I'm afraid of.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> There are of course some questions about how to document this
>> effectively, so that it doesn't create more confusion than it avoids.

> Yes, that is another thing I'm afraid of.

Yeah, if you look up-thread you'll find me expressing the same concern
several days ago.  But this doesn't seem to me to be a reason to reject
the idea outright.  Let's ask for a proposed patch that covers all the
needed documentation changes, and see if it seems clear enough or not.

I still think that the presence of -W/--pwfile in initdb's arguments
is a pretty clear cue that TRUST auth isn't what the DBA intends to
use.  Yes, there will be exceptions, but there will be far more
cases where the TRUST default is wrong even though no -W was given.
So I think we might as well try to do the "right thing" here.  I see
no good argument against it except for the potential-confusion one;
let's see if the proponents can refute that argument by providing
clear documentation.
        regards, tom lane