Thread: Disabling trust/ident authentication configure option

We have a customer using a patch to harden their PostgreSQL installation
(see attached) they would like to contribute. This patch adds the ability
to disable "trust" and "ident" authentication at compile time via configure
options, thus making it impossible to use these authentication methods for
sloppy configuration purposes. This is critical to their software
deployment, as stated in their use case description:

<snip>
PostgreSQL is deployed as part of a larger technical solution (e.g. a
Telecommunication system) and a field engineer has to install/upgrade this
solution. The engineer is a specialist in the Telco domain and has only
little knowledge of databases and especially PostgreSQL setup.

We now want to provide these kinds of users with pre-hardened packages that
make it very hard to accidentally expose their database. For this purpose
the patch allows to optionally disable the "trust" and "ident"
authentication methods. Especially the "trust" mechanism is very critical
as it might actually provide useful functionality for our user. Think of an
engineer who has to do a night shift upgrade with a customer breathing down
his neck to get the system back online. Circumventing all these
authentication configuration issues by just enabling "trust" is very easy
and looks well supported and documented. (the documentation states the
dangers but there are no *big red flags* or something). After finishing the
upgrade the engineer forgets to restore the secure configuration, and a
malicious attacker later uses this access method to gain access to the
database.
</snip>

Currently the patch adds new configure options --without-trust-auth and
--without-ident-auth and makes "peer" authentication default when these
options are set. My testing shows that regression tests (which are normally
using trust) are still working. This works as far as it goes to Linux and
friends, Windows currently is not adressed yet. Maybe its worth to consider
making "sspi" the default on this platform instead.

There was a discussion some time ago ([1]), but i think the reasoning
behind this patch is a little bit different than discussed there.

[1]
<http://www.postgresql.org/message-id/CAN2Y=uMt7CPkxZhAUfw7SzecKdWCWsUuLmh4XPhUxKqBtdUoyA@mail.gmail.com>

--
Thanks

    Bernd

On Thu, Apr 16, 2015 at 9:55 AM, Bernd Helmle <mailings@oopsware.de> wrote:
> PostgreSQL is deployed as part of a larger technical solution (e.g. a
> Telecommunication system) and a field engineer has to install/upgrade this
> solution. The engineer is a specialist in the Telco domain and has only
> little knowledge of databases and especially PostgreSQL setup.
>
> We now want to provide these kinds of users with pre-hardened packages that
> make it very hard to accidentally expose their database. For this purpose
> the patch allows to optionally disable the "trust" and "ident"
> authentication methods. Especially the "trust" mechanism is very critical
> as it might actually provide useful functionality for our user. Think of an
> engineer who has to do a night shift upgrade with a customer breathing down
> his neck to get the system back online. Circumventing all these
> authentication configuration issues by just enabling "trust" is very easy
> and looks well supported and documented.

But... the user could use password authentication with the password
set to "x" and that would be insecure, too, yet not prevented by any
of this.  I think it's pretty hard to prevent someone who has
filesystem-level access to the database server from configuring it
insecurely.

Of course, it's fine for people to make changes like this in their own
copies of PostgreSQL, but I'm not in favor of incorporating those
changes into core.  I don't think there's enough general utility to
this to justify that, and more to the point, I think different people
will want different things.  We haven't, for example, ever had a
request for this specific thing before.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--On 30. April 2015 08:00:23 -0400 Robert Haas <robertmhaas@gmail.com>
wrote:

> But... the user could use password authentication with the password
> set to "x" and that would be insecure, too, yet not prevented by any
> of this.  I think it's pretty hard to prevent someone who has
> filesystem-level access to the database server from configuring it
> insecurely.

Sure. But I think the point is to make their engineers to think about what
they're doing. Typing in a password gives you at least a hint, that you are
probably should use something safe.

I agree that you couldn't really make that bullet proof from just this
excluded functionality, but i could imagine that this makes sense in a more
system-wide context.

> 
> Of course, it's fine for people to make changes like this in their own
> copies of PostgreSQL, but I'm not in favor of incorporating those
> changes into core.  I don't think there's enough general utility to
> this to justify that, and more to the point, I think different people
> will want different things.  We haven't, for example, ever had a
> request for this specific thing before.

Well, i found at least one of such a proposal here:

<http://www.postgresql.org/message-id/CAN2Y=uMt7CPkxZhAUfw7SzecKdWCWsUuLmh4XPhUxKqBtdUoyA@mail.gmail.com>


-- 
Thanks
Bernd

On Tue, May 5, 2015 at 8:05 AM, Volker Aßmann <volker.assmann@gmail.com> wrote:
> Changing the password to something simple is immediately obvious as a
> security flaw for most people who may come across database configurations,
> but for the TRUST mode you actually need to know some background on why this
> is dangerous and when.

I frankly find that a bit difficult to swallow.  You think that
everyone knows that bad passwords are a problem, but some people might
not realize that an authentication method called "trust" might not be
secure?  I suppose that's possible, but I really think that if you
install an *authentication method* whose name means *just trust the
other guy to be telling the truth* without thinking about the
consequences of that, it's hard to have a lot of sympathy for whatever
happens afterwards.

Besides, your patch doesn't just disable "trust".  It also disables
"ident" authentication, which in some network configurations could
conceivably be more secure than password authentication.  When applied
to the local machine, "ident" actually means "peer", which has an
*excellent* chance of being more secure than password authentication.
For that matter, even "trust" might be better than "password".
Anybody who can sniff the network traffic can read the password right
off of the wire.  So either way, your PostgreSQL server is gonna get
hacked, but if you use password authentication, you might reveal a
password that is also used to protect access to something else that
used to be secure.

Personally, if I were going to start disabling authentication methods
at compile time, I'd start with password and md5.  If you are not
using SSL, and you use password or md5 authentication, you're
basically saying, well, I'm OK with somebody reading all of the data
that I'm sending and receiving over the wire, and I'm willing to take
the risk that my passwords are easily crackable or can be read
straight off the wire using wireshark, but to send your own queries
you will have to make at least some minimal effort.  If you need real
security, that is not nearly good enough.  If you don't need real
security, why bother making people hassle with a password that's not
providing any real protection?  There are some valid answers to that
question - e.g. if you are on a corporate WAN, you probably can't fire
somebody for blundering into an unprotected resource, but if somebody
goes to the trouble of cracking the password, even if it's weak, then
you can probably nail them.

For most users, though, I think password and md5 authentication serve
mostly to give people the illusion that they've secured the server.
The real security, if there is any, comes primarily from restricting
incoming connections via listen_addresses and/or operating system
mechanisms such as iptables and/or pg_hba.conf, and from requiring the
use of SSL.  Passwords are weak sauce.

A final point to consider is: what happens when you lock yourself out
of the server, like by forgetting that password?  Normally you can
recover by logging in as the postgres operating system user and then
connecting to the server using trust or peer authentication locally;
or you can make some temporary modification to pg_hba.conf to open up
trust access over the network in a carefully considered and temporary
way.  But your proposal would remove that safety hatch.  I guess you
could shut down the server and start up a single-user session to
change the password, but that means taking an outage.  Again, if you
like that trade-off, you can patch your own copy of the source code
however you like, but that doesn't sound like something I'd want to
recommend in general.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:

> I frankly find that a bit difficult to swallow.  You think that
> everyone knows that bad passwords are a problem, but some people might
> not realize that an authentication method called "trust" might not be
> secure?

Ultimately, what we offer to users is choice of a few options.  Should
we only offer options that we consider to be completely secure, and no
others?  If we were to follow that principle, we would completely
disable non-SSL builds, and all auth methods other than, I dunno, GSSAPI
and such.  But we don't do that, because we trust that users will use
whatever is most appropriate for them.  I see this patch is, in a way, a
mechanism to let system administrators choose at compile time what
options are available to DBAs at setup time.  This seems a reasonable
thing to me.

I don't necessarily agree with the patch as proposed.  I would rather
have a comma-separated list of methods, as in:
   --disable-auth=ident,peer

which lets you choose what to disable without hardcoded choices.  Due to
the nature of autoconf, this might be too fiddly to implement, though,
and if so I think the method proposed by this patch seems a reasonable
compromise.  I've seen configure in other programs offer options such as
--disable-foo=list that lists acceptable values (or --disable-foo=help)

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 5/6/15 6:02 AM, Volker Aßmann wrote:
> Well "trust" actually does not sound that dangerous in case you only
> take a quick glance at the documentation - "trust PostgreSQL to do the
> right thing?"

Hah, we could rename it to "wideopen".

> Please note that the patch does nothing by default, it just adds the
> option to disable trust/ident but leaves them on in the standard
> configuration. I do not want to disable "trust" by default for everyone,
> but it would be great to have the option to do this without having to
> patch (and thus test and verify) the PostgreSQL sources for each new
> release.

Any new compile-time option creates a nonlinear maintenance burden.
We're going to need to test whether each option builds cleanly and
works, also in combination with other options, and on several platforms.The authentication code is already littered
withbuild-time
 
dependencies and platform-specific code.  So the "it doesn't bother
anyone" argument doesn't quite work.

Actually, in this particular case, you wouldn't even need a compile-time
option.  You could just make it a restart-only option.

> I think this is a sufficiently general requirement to warrant including
> an option to disable this, as most hardening guides I have seen for
> PostgreSQL unconditionally require to disable trust authentication and
> disabling it in the code removes the need to check this in the runtime
> configuration.

I think people would be interested in well-thought out, generalized
hardening facilities.  But that would likely include other things than
just disabling an authentication method or two.  And we can't be adding
a new compile-time option as we add each one.  We need a more general
approach.

> Single user sessions would work, but the "peer" authentication is also
> still available and should be the preferred method to reset passwords
> when trust is disabled, so this should not be an issue.

"peer" authentication is unfortunately not quite portable.

On 05/06/2015 10:47 AM, Alvaro Herrera wrote:

>
> I don't necessarily agree with the patch as proposed.  I would rather
> have a comma-separated list of methods, as in:
>
>      --disable-auth=ident,peer
>
> which lets you choose what to disable without hardcoded choices.  Due to
> the nature of autoconf, this might be too fiddly to implement, though,
> and if so I think the method proposed by this patch seems a reasonable
> compromise.  I've seen configure in other programs offer options such as
> --disable-foo=list that lists acceptable values (or --disable-foo=help)
>


I don't necessarily object to this idea, but I do think we need to 
ensure that we don't allow both trust and peer to be disabled (which 
means on Windows you would not be able to disable trust). Otherwise this 
becomes a footgun which would require the whole server to be stopped so 
you could connect in single user mode to correct certain mistakes, which 
are unfortunately all too common.

cheers

andrew

On Wed, May 6, 2015 at 3:57 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> I don't necessarily object to this idea, but I do think we need to ensure
> that we don't allow both trust and peer to be disabled (which means on
> Windows you would not be able to disable trust). Otherwise this becomes a
> footgun which would require the whole server to be stopped so you could
> connect in single user mode to correct certain mistakes, which are
> unfortunately all too common.

Of course that's precisely what the OP wanted to do, which goes to my
point that not everybody's going to want the same thing.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 05/06/2015 04:19 PM, Robert Haas wrote:
> On Wed, May 6, 2015 at 3:57 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> I don't necessarily object to this idea, but I do think we need to ensure
>> that we don't allow both trust and peer to be disabled (which means on
>> Windows you would not be able to disable trust). Otherwise this becomes a
>> footgun which would require the whole server to be stopped so you could
>> connect in single user mode to correct certain mistakes, which are
>> unfortunately all too common.
> Of course that's precisely what the OP wanted to do, which goes to my
> point that not everybody's going to want the same thing.
>

If that is indeed the proposal, then I vote no.

But he did say upthread:

> Single user sessions would work, but the "peer" authentication is also 
> still available and should be the preferred method to reset passwords 
> when trust is disabled, so this should not be an issue.

(Personally I think there's a very good case for completely ripping out 
RFC1413 ident auth. I've not seen it used in a great long while, and 
it's always been a security risk.)


cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:
> (Personally I think there's a very good case for completely ripping out 
> RFC1413 ident auth. I've not seen it used in a great long while, and 
> it's always been a security risk.)

FWIW, I agree with that --- or at least making it a not-built-by-default
option.

Probably the right time to make any such changes is at the same time
we add the proposed more-secure-than-MD5 password option.
        regards, tom lane

On 5/6/15 12:56 PM, Peter Eisentraut wrote:
>> I think this is a sufficiently general requirement to warrant including
>> >an option to disable this, as most hardening guides I have seen for
>> >PostgreSQL unconditionally require to disable trust authentication and
>> >disabling it in the code removes the need to check this in the runtime
>> >configuration.
> I think people would be interested in well-thought out, generalized
> hardening facilities.  But that would likely include other things than
> just disabling an authentication method or two.  And we can't be adding
> a new compile-time option as we add each one.  We need a more general
> approach.

Yeah. I think one of the big use cases here is that many environments 
are OK with at least ident (if not trust) but only from the local 
machine. So you'd probably want to handle that somehow.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 05/06/2015 02:13 PM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> (Personally I think there's a very good case for completely ripping out 
>> RFC1413 ident auth. I've not seen it used in a great long while, and 
>> it's always been a security risk.)
> 
> FWIW, I agree with that --- or at least making it a not-built-by-default
> option.

I have seen it in the last year, actually, but only once, which even for
my personal pool represents < 1% usage.  So ...

> Probably the right time to make any such changes is at the same time
> we add the proposed more-secure-than-MD5 password option.

+1 to kill off ident when we replace MD5, since users will need to be
beaten over the head about changes to auth methods anyway.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 05/07/2015 01:32 AM, Jim Nasby wrote:
> On 5/6/15 12:56 PM, Peter Eisentraut wrote:
>>> I think this is a sufficiently general requirement to warrant including
>>>> an option to disable this, as most hardening guides I have seen for
>>>> PostgreSQL unconditionally require to disable trust authentication and
>>>> disabling it in the code removes the need to check this in the runtime
>>>> configuration.
>> I think people would be interested in well-thought out, generalized
>> hardening facilities.  But that would likely include other things than
>> just disabling an authentication method or two.  And we can't be adding
>> a new compile-time option as we add each one.  We need a more general
>> approach.
>
> Yeah. I think one of the big use cases here is that many environments
> are OK with at least ident (if not trust) but only from the local
> machine. So you'd probably want to handle that somehow.

That's called 'peer', since 9.1.

- Heikki

<p dir="ltr"><br /> On May 7, 2015 12:41 AM, "Heikki Linnakangas" <<a
href="mailto:hlinnaka@iki.fi">hlinnaka@iki.fi</a>>wrote:<br /> ><br /> > On 05/07/2015 01:32 AM, Jim Nasby
wrote:<br/> >><br /> >> On 5/6/15 12:56 PM, Peter Eisentraut wrote:<br /> >>>><br />
>>>>I think this is a sufficiently general requirement to warrant including<br /> >>>>><br
/>>>>>> an option to disable this, as most hardening guides I have seen for<br /> >>>>>
PostgreSQLunconditionally require to disable trust authentication and<br /> >>>>> disabling it in the
coderemoves the need to check this in the runtime<br /> >>>>> configuration.<br /> >>><br />
>>>I think people would be interested in well-thought out, generalized<br /> >>> hardening
facilities. But that would likely include other things than<br /> >>> just disabling an authentication method
ortwo.  And we can't be adding<br /> >>> a new compile-time option as we add each one.  We need a more
general<br/> >>> approach.<br /> >><br /> >><br /> >> Yeah. I think one of the big use cases
hereis that many environments<br /> >> are OK with at least ident (if not trust) but only from the local<br />
>>machine. So you'd probably want to handle that somehow.<br /> ><br /> ><br /> > That's called 'peer',
since9.1.<br /> ><p dir="ltr">There is also ident over localhost tcp. Since it only passes through the local kernel,
itcan probably be considered quite secure (but requires an extra piece of software to be installed, but that's easy).
Andprovides a usable option for those that can't use peer. <p dir="ltr">/Magnus <br /> 

* Josh Berkus (josh@agliodbs.com) wrote:
> On 05/06/2015 02:13 PM, Tom Lane wrote:
> > Andrew Dunstan <andrew@dunslane.net> writes:
> >> (Personally I think there's a very good case for completely ripping out
> >> RFC1413 ident auth. I've not seen it used in a great long while, and
> >> it's always been a security risk.)
> >
> > FWIW, I agree with that --- or at least making it a not-built-by-default
> > option.
>
> I have seen it in the last year, actually, but only once, which even for
> my personal pool represents < 1% usage.  So ...
>
> > Probably the right time to make any such changes is at the same time
> > we add the proposed more-secure-than-MD5 password option.
>
> +1 to kill off ident when we replace MD5, since users will need to be
> beaten over the head about changes to auth methods anyway.

I realize it's not going to be popular, but I'd love to have 'trust'
only allowed if a command-line option is passed to the postmaster or
something along those lines.  It's really got no business being an
option for a network service like PG.  I'd suggest ripping it out
entirely but I'm sure that'd be even less popular and Andrew does make a
good point that our single-user-mode is still so terrible that we have
to support a multi-user-mode with zero auth, to deal with certain kinds
of breakage/corruption.  The fix for that is having a real single-user
mode that is usable, as has been discussed previously, but we don't seem
to be making much progress in that direction, unfortunately.
Thanks!
    Stephen

On Thu, May  7, 2015 at 11:53:21AM +0200, Volker Aßmann wrote:
> Yes in fact, it would be a way for organizations distributing their own
> PostgreSQL packages to enforce authentication restrictions within the package
> instead of having to educate all users about the options.
>  
> 
>     I don't necessarily agree with the patch as proposed.  I would rather
>     have a comma-separated list of methods, as in:
> 
>         --disable-auth=ident,peer

Why don't they just apply a source code patch before compiling? 
Configure seems like an odd place for this.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On Thu, May 7, 2015 at 11:02 AM, Stephen Frost <sfrost@snowman.net> wrote:
> I realize it's not going to be popular, but I'd love to have 'trust'
> only allowed if a command-line option is passed to the postmaster or
> something along those lines.  It's really got no business being an
> option for a network service like PG.

I disagree wholeheartedly.  There is such a thing as a trusted network.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Thu, May 7, 2015 at 11:02 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > I realize it's not going to be popular, but I'd love to have 'trust'
> > only allowed if a command-line option is passed to the postmaster or
> > something along those lines.  It's really got no business being an
> > option for a network service like PG.
>
> I disagree wholeheartedly.  There is such a thing as a trusted network.

Likely a good topic of conversation to be had in Ottawa. :)  I agree
that there are trusted networks, but the ones that I work with still
expect network services to require authentication and authorization.
Perhaps they're not really "trusted" then, from your perspective.  On
the other hand, I suppose if you use pg_hba to limit which accounts can
be logged into with 'trust' then you might be able to have, say, a
"read-only" user/database that anyone could see.  That's a pretty narrow
case though and I'd rather we figure out how to address it directly and
more specifically (no-password login roles?) than the broad
disable-all-authentication "trust" method.
Thanks!
    Stephen

--On 6. Mai 2015 16:28:43 -0400 Andrew Dunstan <andrew@dunslane.net> wrote:

>> Single user sessions would work, but the "peer" authentication is also 
>> still available and should be the preferred method to reset passwords 
>> when trust is disabled, so this should not be an issue.
> 
> (Personally I think there's a very good case for completely ripping out
> RFC1413 ident auth. I've not seen it used in a great long while, and it's
> always been a security risk.)

I have the same feeling. I haven't seen it in the last 6+ years used
anywhere and I personally think it's a relict...so +1.

-- 
Thanks
Bernd

On Thu, May 7, 2015 at 4:57 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Robert Haas (robertmhaas@gmail.com) wrote:
>> On Thu, May 7, 2015 at 11:02 AM, Stephen Frost <sfrost@snowman.net> wrote:
>> > I realize it's not going to be popular, but I'd love to have 'trust'
>> > only allowed if a command-line option is passed to the postmaster or
>> > something along those lines.  It's really got no business being an
>> > option for a network service like PG.
>>
>> I disagree wholeheartedly.  There is such a thing as a trusted network.
>
> Likely a good topic of conversation to be had in Ottawa. :)  I agree
> that there are trusted networks, but the ones that I work with still
> expect network services to require authentication and authorization.
> Perhaps they're not really "trusted" then, from your perspective.  On
> the other hand, I suppose if you use pg_hba to limit which accounts can
> be logged into with 'trust' then you might be able to have, say, a
> "read-only" user/database that anyone could see.  That's a pretty narrow
> case though and I'd rather we figure out how to address it directly and
> more specifically (no-password login roles?) than the broad
> disable-all-authentication "trust" method.

Let's suppose that you have an application server and a DB server
running on the same node.  That turns out to be too much load, so you
move the application server to a separate machine and connect the two
machines with a crossover cable, or a VLAN that has nothing else on
it.  To me, it's quite sane to want connections on that network to
proceed without authentication or authorization.  If you've got to
open up the database more than that then, yes, you need authentication
and authorization.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Mon, May 11, 2015 at 10:00 PM, Robert Haas <span
dir="ltr"><<ahref="mailto:robertmhaas@gmail.com" target="_blank">robertmhaas@gmail.com</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div
class="HOEnZb"><divclass="h5">On Thu, May 7, 2015 at 4:57 PM, Stephen Frost <<a
href="mailto:sfrost@snowman.net">sfrost@snowman.net</a>>wrote:<br /> > * Robert Haas (<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>)wrote:<br /> >> On Thu, May 7, 2015 at 11:02 AM,
StephenFrost <<a href="mailto:sfrost@snowman.net">sfrost@snowman.net</a>> wrote:<br /> >> > I realize
it'snot going to be popular, but I'd love to have 'trust'<br /> >> > only allowed if a command-line option is
passedto the postmaster or<br /> >> > something along those lines.  It's really got no business being an<br />
>>> option for a network service like PG.<br /> >><br /> >> I disagree wholeheartedly.  There is
sucha thing as a trusted network.<br /> ><br /> > Likely a good topic of conversation to be had in Ottawa. :)  I
agree<br/> > that there are trusted networks, but the ones that I work with still<br /> > expect network services
torequire authentication and authorization.<br /> > Perhaps they're not really "trusted" then, from your
perspective. On<br /> > the other hand, I suppose if you use pg_hba to limit which accounts can<br /> > be logged
intowith 'trust' then you might be able to have, say, a<br /> > "read-only" user/database that anyone could see. 
That'sa pretty narrow<br /> > case though and I'd rather we figure out how to address it directly and<br /> >
morespecifically (no-password login roles?) than the broad<br /> > disable-all-authentication "trust" method.<br
/><br/></div></div>Let's suppose that you have an application server and a DB server<br /> running on the same node. 
Thatturns out to be too much load, so you<br /> move the application server to a separate machine and connect the
two<br/> machines with a crossover cable, or a VLAN that has nothing else on<br /> it.  To me, it's quite sane to want
connectionson that network to<br /> proceed without authentication or authorization.  If you've got to<br /> open up
thedatabase more than that then, yes, you need authentication<br /> and authorization.<br /><div class="HOEnZb"><div
class="h5"><br/> --<br /> Robert Haas<br /> EnterpriseDB: <a href="http://www.enterprisedb.com"
target="_blank">http://www.enterprisedb.com</a><br/> The Enterprise PostgreSQL Company<br
/></div></div></blockquote></div><br/></div><div class="gmail_extra">Even in this case it still means that any breach
inany of the network services running on your application server would immediately own your database, or at least
everythingyour application can access. This applies even to totally unrelated services running with restricted
permissions.Using password or certificate based authentication at least gives you the additional security of local
filesystemaccess controls and is not much harder to setup. M2M authentication is always a difficult topic as the
"authenticationtokens" have to be secured but I would agree that a more specific / secure method than
"disable-all-authentication"would be preferable.<br /><br /></div><div class="gmail_extra">Best regards,<br /><br
/></div><divclass="gmail_extra">    Volker<br /></div><div class="gmail_extra"><br /></div></div> 

On Wed, May 13, 2015 at 8:01 AM, Volker Aßmann <volker.assmann@gmail.com> wrote:
> Even in this case it still means that any breach in any of the network
> services running on your application server would immediately own your
> database, or at least everything your application can access. This applies
> even to totally unrelated services running with restricted permissions.
> Using password or certificate based authentication at least gives you the
> additional security of local filesystem access controls and is not much
> harder to setup. M2M authentication is always a difficult topic as the
> "authentication tokens" have to be secured but I would agree that a more
> specific / secure method than "disable-all-authentication" would be
> preferable.

Sure, opinions on the best way to do any given thing are going to
vary, and nobody's trying to prevent you from configuring your
instances of PostgreSQL however you like.  The email to which I was
responding was suggesting limiting MY ability to set up MY instances
of PostgreSQL the way I like.  And I'm opposed to that.

All of this is fairly far afield from the original topic of this
thread, which was whether a configure option disabling trust + ident
authentication would be a good idea.  I said no.  Then we had a bunch
of counter-proposals:

Alvaro: Support a configure switch whose value is a comma-separated
list of authentication methods to disable.
Peter: Generalized hardening facility.
Andrew: Like what Alvaro said, but require at least one of trust +
peer to remain enabled so people can't hose themselves.
Andrew, v2: Rip out RFC1413 ident authentication completely.
Stephen: Require a command-line option to use trust auth.

There's clearly no consensus on any of these proposals, and most of
them don't address your original requirement anyway, though Alvaro's
would.   I guess the point is that nothing is going to get changed
here on one person's say-so if other people don't agree, so if you
want to get something done, you're going to need to pick something
that can achieve consensus and then implement that.  Also, anything
you want to get done is certainly going to be in 9.6 at the earliest,
because the time for 9.5 proposals has already come and gone.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> On Wed, May 13, 2015 at 2:18 PM, Robert Haas <robertmhaas@gmail.com
> <mailto:robertmhaas@gmail.com>> wrote:
>     All of this is fairly far afield from the original topic of this
>     thread, which was whether a configure option disabling trust + ident
>     authentication would be a good idea.  I said no.  Then we had a bunch
>     of counter-proposals:
> 
>     Alvaro: Support a configure switch whose value is a comma-separated
>     list of authentication methods to disable.

So, I'm going to throw in why a configure option to disable "trust,
peer" is an unworkable idea.

The goal here was stated to preventing authentication misconfiguration
by shortsighted admins who have superuser access and the ability to
change pg_hba.conf.  This is tantamount to giving someone a gun and
bullets, but expecting duct tape across the cartridge slot to prevent
them from loading or using the gun.

Let's say we offered a compile-time option, and then someone built a
package postgresql-9.6-secureauth.deb.  So, your lazy admin is having
trouble debugging an auth problem and wants to set "trust".  But they
can't.  So they search on Google and figure out how to download and
install postgresql-9.6-normalauth.deb.  Or, alternately, they set all
passwords to "password" or to "".  Or they put .pgpass files on all
machines.  Or they put the password in pgbouncer and set pgbouncer to
"trust".

You've added exactly one additional step in their way, and not a
particularly difficult one.  It simply doesn't solve the problem you're
trying to solve, which is unsurprising, because technology has never
been able to solve the problem of untrustworthy humans with positions of
responsibility.

Now, if you wanted to add an audit log every time someone changes an
auth method in pg_hba.conf?  I'd be all for that, I can see all kinds of
uses for that, and it might actually accomplish something effective.

If you disagree with me, well, it would be very easy to hack out the
auth methods you don't like and compile your own.  It *is* open source.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 5/17/15 10:58 PM, Josh Berkus wrote:
> The goal here was stated to preventing authentication misconfiguration
> by shortsighted admins who have superuser access and the ability to
> change pg_hba.conf.  This is tantamount to giving someone a gun and
> bullets, but expecting duct tape across the cartridge slot to prevent
> them from loading or using the gun.

The idea is to prevent *accidental* misconfiguration, not to try and 
permanently lock them out. IE: make them think before allowing them to 
just do something silly. Disabling auth methods at compile time seems a 
very reasonable way to accomplish that.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 05/18/2015 11:36 AM, Jim Nasby wrote:
> On 5/17/15 10:58 PM, Josh Berkus wrote:
>> The goal here was stated to preventing authentication misconfiguration
>> by shortsighted admins who have superuser access and the ability to
>> change pg_hba.conf.  This is tantamount to giving someone a gun and
>> bullets, but expecting duct tape across the cartridge slot to prevent
>> them from loading or using the gun.
>
> The idea is to prevent *accidental* misconfiguration, not to try and 
> permanently lock them out. IE: make them think before allowing them to 
> just do something silly. Disabling auth methods at compile time seems 
> a very reasonable way to accomplish that.


It's not more secure or more useful if it increases substantially the 
difficulty and disruption of recovering from misconfiguration, whether 
accidental or not. Disabling both trust and peer would do just that, 
without significantly impeding malicious users.

cheers

andrew

On 05/17/2015 08:58 PM, Josh Berkus wrote:
> You've added exactly one additional step in their way, and not a
> particularly difficult one.  It simply doesn't solve the problem you're
> trying to solve, which is unsurprising, because technology has never
> been able to solve the problem of untrustworthy humans with positions of
> responsibility.

BTW, here I'm addressing Volker and other supporters of the
"compile-time option", not Robert.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 05/18/2015 08:36 AM, Jim Nasby wrote:
> On 5/17/15 10:58 PM, Josh Berkus wrote:
>> The goal here was stated to preventing authentication misconfiguration
>> by shortsighted admins who have superuser access and the ability to
>> change pg_hba.conf.  This is tantamount to giving someone a gun and
>> bullets, but expecting duct tape across the cartridge slot to prevent
>> them from loading or using the gun.
> 
> The idea is to prevent *accidental* misconfiguration, not to try and
> permanently lock them out. IE: make them think before allowing them to
> just do something silly. Disabling auth methods at compile time seems a
> very reasonable way to accomplish that.

It doesn't seem like it to me.  You're talking about a mechanism which
would either require people to compile their own PostgreSQL binaries, or
have an additional set of packages available.  That's a huge amount of
overhead for something which could be just as easily accomplished by
putting the comment "#IF YOU ENABLE TRUST AUTH IN THIS FILE YOU WILL BE
FIRED" in pg_hba.conf.

Or, heck, simply putting pg_hba.conf in Puppet/Chef/Ansible/Salt and
restricting permissions to the CMS repo.  Which is something most modern
shops *already do*.

There are *real* things we could do to improve the overall security of
auth methods.  Mandatory logging of pg_hba.conf changes, for example. Or
having a way to monitor which servers have which auth methods for which
networks available in some scalable way (i.e. without reading
pg_hba.conf).  Those would also take a lot of work and thought lest they
expose additional attack surfaces.

One thing computer security history has demonstrated again and again is
that ad-hoc attempts to duct-tape over real security issues are
counter-productive, since they interfere with real work and the steps
people take to get around them create bigger security holes than the
original problem.  Let's please not do that in Postgres.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Mon, May 18, 2015 at 09:32:23PM +0200, Volker Aßmann wrote:
> That's what we are currently doing with the patch Bernd posted at the beginning
> of this thread. But we thought we might post the patch for consideration here
> as the use case might be sufficiently general that it may be of use to others.

It isn't general enough.

> But I like the more general approach proposed by Alvaro, so in case this patch
> would have a chance to not be immediately rejected, I would try to implement
> the more generic approach. I would also include a check to ensure at least one
> reasonably secure way for password recovery is available. For Unix systems
> "peer" authentication seems to be a good candidate.

Likely to be rejected.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

Bruce Momjian wrote:
> On Mon, May 18, 2015 at 09:32:23PM +0200, Volker Aßmann wrote:

> > But I like the more general approach proposed by Alvaro, so in case this patch
> > would have a chance to not be immediately rejected, I would try to implement
> > the more generic approach. I would also include a check to ensure at least one
> > reasonably secure way for password recovery is available. For Unix systems
> > "peer" authentication seems to be a good candidate.
> 
> Likely to be rejected.

Why?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, May 18, 2015 at 05:00:41PM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > On Mon, May 18, 2015 at 09:32:23PM +0200, Volker Aßmann wrote:
> 
> > > But I like the more general approach proposed by Alvaro, so in case this patch
> > > would have a chance to not be immediately rejected, I would try to implement
> > > the more generic approach. I would also include a check to ensure at least one
> > > reasonably secure way for password recovery is available. For Unix systems
> > > "peer" authentication seems to be a good candidate.
> > 
> > Likely to be rejected.
> 
> Why?

Because, as Josh stated, it is more of a bandaid rather than a fix ---
we can't protect administrators against themselves in this way without
causing a lot of confusion.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On May 18, 2015, at 3:32 PM, Volker Aßmann <volker.assmann@gmail.com> wrote:
> I know these measures won't protect against an experienced attacker who gains root access, but hope it slows them
downsufficiently so the admins may have a chance to detect the attack. 

It won't.

...Robert

On Wed, May 20, 2015 at 4:20 AM, Volker Aßmann <volker.assmann@gmail.com> wrote:
> On Tue, May 19, 2015 at 1:53 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On May 18, 2015, at 3:32 PM, Volker Aßmann <volker.assmann@gmail.com>
>> wrote:
>> > I know these measures won't protect against an experienced attacker who
>> > gains root access, but hope it slows them down sufficiently so the admins
>> > may have a chance to detect the attack.
>>
>> It won't.
>
> You don't seem to have much trust in your other authentication mechanisms
> and seem to know our environment quite well then...
>
> But anyway you don't seem to understand why "being able to remove a 'disable
> all security let anyone in' option" might be a reasonable idea, so there is
> no point in arguing, please just ignore the patch.

Please don't be discouraged here.  Contributing to the PostgreSQL
community can be frustrating when you don't get what you want, and
even though I have been a member of this community for about 7 years
now and am a major contributor and committer, I still very often do
not get what I want.

We make decisions here by consensus.  As far as this patch goes, the
question is simple: do we, as a group, agree that this patch will be a
net positive for PostgreSQL?  I think that it is fairly clear that the
answer is no.  There's a a fair degree support for the idea of adding
a configure option of some kind, but there are widely diverging
opinions about what it should do.  Unless and until a reasonable
degree of agreement can be reached, we can't proceed.

But please don't view that as a personal rejection.  I stand by what I
said: disallowing trust authentication in pg_hba.conf will not slow
down an attacker who wants to create a backdoor.  I believe that to be
true, and I can tell you why, but regardless of anything I say, you
can still believe it to be false.  I'm OK with that, and I hope you're
OK with me having a different belief.  It doesn't mean that I don't
want you to continue reading this mailing list or suggesting things;
in fact, I hope you will.  The fact that I (and others) don't like
this particular idea doesn't mean we won't like your next one, or the
one after that.

If this discussing has come across as bruising, I apologize for that.
One of the things that sometimes happens is that somebody submits a
patch and it goes for a long time without receiving any meaningful
feedback.  Then eventually, sometimes after a lot of work has been put
into it, it gets rejected.  That's not fun.  So another approach is
for people to respond right away when somebody posts a patch that they
think is a bad idea and say: hey, wait, let's not do this, I think
it's a bad idea.  But then you can have a situation (which I think may
have happened in this case) where a contributor feels that other
people are jumping all over them.  That's not fun, either.

I don't know the answer to this problem.  I'm not the world's greatest
diplomat, and tone is even harder to read over email than it is in
person.  But I can tell you that I'm not mad at you personally, and I
didn't spend time replying to this email thread just to get rid of
you.  If it came across that way, I'm sorry.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 05/20/2015 01:20 AM, Volker Aßmann wrote:

So, in the interests of trying to get you to understand why your
proposal met with a negative response, and to improve future proposals:

> You don't seem to have much trust in your other authentication
> mechanisms and seem to know our environment quite well then...

PostgreSQL is a general-purpose database with millions of users.  As
such, features which are only useful in a specialized environment
receive far more scrutiny and pushback than feature proposals which are
obviously generally applicable.

As such, proposals are more likely to be successful if the proposer can
show how they apply to a general use case, or adapt them so that they
are useful to a large number of our users.  This means that "this works
in our environment which has conditions X, Y, and Z" is not an effective
argument, unless you can follow it up with "... and here's the reason
why [large class of users] also has conditions X, Y and Z."

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus wrote:

> As such, proposals are more likely to be successful if the proposer can
> show how they apply to a general use case, or adapt them so that they
> are useful to a large number of our users.  This means that "this works
> in our environment which has conditions X, Y, and Z" is not an effective
> argument, unless you can follow it up with "... and here's the reason
> why [large class of users] also has conditions X, Y and Z."

The proposal here is to have a configure argument that disables
arbitrary auth mechanisms.  How is that specific to a particular
environment?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:
> Josh Berkus wrote:
>
> > As such, proposals are more likely to be successful if the proposer can
> > show how they apply to a general use case, or adapt them so that they
> > are useful to a large number of our users.  This means that "this works
> > in our environment which has conditions X, Y, and Z" is not an effective
> > argument, unless you can follow it up with "... and here's the reason
> > why [large class of users] also has conditions X, Y and Z."
>
> The proposal here is to have a configure argument that disables
> arbitrary auth mechanisms.  How is that specific to a particular
> environment?

For my 2c, I'm still a fan of the general idea..  I still view 'trust'
as appropriate only for development environments and I don't believe it
really solves any use-cases that having password-less accounts doesn't.

Removing it as an option simply removes a potential configuration
mistake.  It's not going to protect you from a malicious administrator
who has root-level access any more than a CHECK constraint would stop a
malicious DBA.
Thanks!
    Stephen

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Josh Berkus wrote:
>> As such, proposals are more likely to be successful if the proposer can
>> show how they apply to a general use case, or adapt them so that they
>> are useful to a large number of our users.  This means that "this works
>> in our environment which has conditions X, Y, and Z" is not an effective
>> argument, unless you can follow it up with "... and here's the reason
>> why [large class of users] also has conditions X, Y and Z."

> The proposal here is to have a configure argument that disables
> arbitrary auth mechanisms.  How is that specific to a particular
> environment?

I think Josh's question is whether the feature is actually useful to
a large class of users.

One reason why it would not be, if it's a build-time decision,
is that it's quite unlikely that any popular packagers would build
that way.  So this would only be applicable to custom-built binaries,
which is a pretty small class of users to begin with.
        regards, tom lane

On 05/20/2015 11:10 AM, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> Josh Berkus wrote:
>>> As such, proposals are more likely to be successful if the proposer can
>>> show how they apply to a general use case, or adapt them so that they
>>> are useful to a large number of our users.  This means that "this works
>>> in our environment which has conditions X, Y, and Z" is not an effective
>>> argument, unless you can follow it up with "... and here's the reason
>>> why [large class of users] also has conditions X, Y and Z."
> 
>> The proposal here is to have a configure argument that disables
>> arbitrary auth mechanisms.  How is that specific to a particular
>> environment?
> 
> I think Josh's question is whether the feature is actually useful to
> a large class of users.
> 
> One reason why it would not be, if it's a build-time decision,
> is that it's quite unlikely that any popular packagers would build
> that way.  So this would only be applicable to custom-built binaries,
> which is a pretty small class of users to begin with.

Precisely.

My second point is that it's not useful for the reason Volker says it
is; that is, it simply doesn't protect against "lazy admin mistakes",
*and* there are already other mechanisms in the world of IT which do a
better job of this (primarily, centralized configuration management).

I am aware of a large group of users who are concerned with lock-down
security and do custom builds: the makers of security appliances, many
or most of whom use PostgreSQL as a database.  However, those vendors
also lock down access to pg_hba.conf and postgresql.conf, so a
compile-time option is of, at best, marginal use to them.  Stretching my
imagination over the different types of PostgreSQL users I know, I can't
actually think of any who, as a group, would make use of this feature.

So the first thing to establish is "other than Volker himself, who are
we helping here?"

The second major issue I have is that it's an anti-security feature.
That is, it ignores the several ways in which someone with superuser
access can bypass the lack of an auth mechanism, while giving anyone who
installs the option a false sense of safety.  Ineffective security
measures lead to worse security holes than being aware that you're at risk.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

* Josh Berkus (josh@agliodbs.com) wrote:
> On 05/20/2015 11:10 AM, Tom Lane wrote:
> > Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> >> The proposal here is to have a configure argument that disables
> >> arbitrary auth mechanisms.  How is that specific to a particular
> >> environment?
> >
> > I think Josh's question is whether the feature is actually useful to
> > a large class of users.
> >
> > One reason why it would not be, if it's a build-time decision,
> > is that it's quite unlikely that any popular packagers would build
> > that way.  So this would only be applicable to custom-built binaries,
> > which is a pretty small class of users to begin with.
>
> Precisely.

I do not agree with the 'quite unlikely' characterization.  We would
need to make a case to them as to why there are better options than
having 'trust' be supported, but that only makes sense once there *are*.
We know there are reasons we need it today and there's not much point
having this discussion until those have been addressed.

> So the first thing to establish is "other than Volker himself, who are
> we helping here?"

I don't agree with this either.  Providing a "bypass all authentication"
configuration option really isn't a good thing.  Why don't packagers use
our default pg_hba.conf?  Because it only makes sense in a development
type of environment.  I'd argue the same is true for 'trust'.

> The second major issue I have is that it's an anti-security feature.
> That is, it ignores the several ways in which someone with superuser
> access can bypass the lack of an auth mechanism, while giving anyone who
> installs the option a false sense of safety.  Ineffective security
> measures lead to worse security holes than being aware that you're at risk.

I don't believe this is correct.  Your definition of an anti-security
feature is fine, but I don't agree with the assumption that people will
feel secure because 'trust' isn't compiled in.

Most users are unlikely to notice it's gone and the argument you're
making here is that the users who will be upset that it's no longer
available are all knowledgable enough to realize when it's valid to
use.  I seriously doubt that's the case and throwing a big warning in
front of them saying "trust is no longer built-in because it's a big
glaring security hole, please use another mechanism" would hopefully
get them to understand why it was an issue, why it shouldn't be used,
and what options are available and what their tradeoffs are.

I still don't believe there's much point in the discussion until there
are viable alternatives for the use-cases where we really need to be
able to just get into PG and get data out quickly in the face of
corruption, but I wanted to share my disagreement regarding the
assumption that packagers would never consider disabling 'trust'.
Thanks!
    Stephen

Tom,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > I don't agree with this either.  Providing a "bypass all authentication"
> > configuration option really isn't a good thing.  Why don't packagers use
> > our default pg_hba.conf?  Because it only makes sense in a development
> > type of environment.  I'd argue the same is true for 'trust'.
>
> Sure.  And the problem is that development environments are a perfectly
> common and respectable use-case.

Apologies for the confusion- the 'development type of environment' I was
referring to above is the environment where our default from-source
pg_hba.conf is installed: when doing *PostgreSQL* development.  I don't
see the use-case for using 'trust' when doing application development
with PG as a database.  I've certainly not used it before and when I've
found it in places that I've gone, after explaining how it actually
works, everyone I've worked with has either changed it or made plans to
do so.

> If we could get to a point where there is another way that is superior
> to "trust" even for single-user development environments, then maybe
> it would be useful to try to persuade packagers to disable "trust".

I agree with this, as mentioned up-thread.  Having a way to support
single-user development / running of PG would really need to exist
before we could encourage getting rid of 'trust'.

> But I don't even see a proposal for such a thing, let alone a track record
> showing that nobody needs "trust".  And you really have got to get to the
> point of being able to argue that *nobody* needs trust, not that some
> use-cases don't need it, before you will impress most packagers.

I don't see a real proposal for it either; I was simply trying to
outline a path forward, one which I would agree with, rather than
simply saying "no, never."
Thanks!
    Stephen

Stephen Frost <sfrost@snowman.net> writes:
> I don't agree with this either.  Providing a "bypass all authentication"
> configuration option really isn't a good thing.  Why don't packagers use
> our default pg_hba.conf?  Because it only makes sense in a development
> type of environment.  I'd argue the same is true for 'trust'.

Sure.  And the problem is that development environments are a perfectly
common and respectable use-case.  I cannot see Red Hat, for example,
shipping a Postgres that's built (not merely configured by user-changeable
config files, but hard-wired) to be unfriendly to developers.

If we could get to a point where there is another way that is superior
to "trust" even for single-user development environments, then maybe
it would be useful to try to persuade packagers to disable "trust".
But I don't even see a proposal for such a thing, let alone a track record
showing that nobody needs "trust".  And you really have got to get to the
point of being able to argue that *nobody* needs trust, not that some
use-cases don't need it, before you will impress most packagers.
        regards, tom lane

On 2015-05-20 15:42:23 -0400, Stephen Frost wrote:
> > So the first thing to establish is "other than Volker himself, who are
> > we helping here?"
> 
> I don't agree with this either.  Providing a "bypass all authentication"
> configuration option really isn't a good thing.  Why don't packagers use
> our default pg_hba.conf?  Because it only makes sense in a development
> type of environment.  I'd argue the same is true for 'trust'.

Uh. So if the shit hit the fan because you mismanaged a password
rollover, kereberos is down, or something like that, and you can't
access postgres anymore you want to recompile? And no peer isn't an
answer isn't an answer, it's not available on windows. Your only way out
is going to be single user mode. But wait, that's a security hole too.


I find the arguments presented in this thread for a configure option
entirely unconvincing. If you'd argued for a saner default
authentication setup: I'd be on board with that. But this seems just a
pointless exercise in making things more complicated.

Greetings,

Andres Freund

Andres Freund wrote:
> On 2015-05-20 15:42:23 -0400, Stephen Frost wrote:
> > > So the first thing to establish is "other than Volker himself, who are
> > > we helping here?"
> > 
> > I don't agree with this either.  Providing a "bypass all authentication"
> > configuration option really isn't a good thing.  Why don't packagers use
> > our default pg_hba.conf?  Because it only makes sense in a development
> > type of environment.  I'd argue the same is true for 'trust'.
> 
> Uh. So if the shit hit the fan because you mismanaged a password
> rollover, kereberos is down, or something like that, and you can't
> access postgres anymore you want to recompile?

Yeah, it's pretty messy.  I thought about providing the feature without
involving configure: say have a file listing allowed auth methods; in
Volker's case it's easy to ship packages were trust/peer are removed in
the file.  If you're in deep trouble just enable trust there and fix it.
But this has the exact problem we started with: the lazy admin will just
enable it "momentarily" to get the thing running and forget to turn it
off later.

I was thinking that another use for this would be cases where an auth
method is found to be insecure and you want to disable it completely
(say Kerberos is cracked irreparably or whatever).  But the real
solution to that problem is to remove it from pg_hba.conf.  In the end,
it seems there is no actual hole being solved here that cannot be
better attacked by other means.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, May 20, 2015 at 02:10:30PM -0400, Tom Lane wrote:
> One reason why it would not be, if it's a build-time decision,
> is that it's quite unlikely that any popular packagers would build
> that way.  So this would only be applicable to custom-built binaries,
> which is a pretty small class of users to begin with.

There might be appliance vendors who ship PostgreSQL along with their
product.  Then, they decide they want to use the pristine tarballs for
reproducibility and accountability.  If done right, they could publish
their set of configure options and a build-id or whatever, and 3rd 
parties could verify the binaries they ship have not been tampered
with[1].  Granted, they could also just publish the patch for those 3rd
parties to apply as well, but that sounds slightly inelegant.

The other set of users I could think of are those who, for whatever
reason, tend to always compile PostgreSQL from source for their
company/organization.  Maybe they have internal rules that requires a
custom installation prefix for all their servers or whatever. Due to
procedural requirements, or just the unwillingness to carry deltas, they
absolutely want to use the pristine tarballs as well but would be very
happy to get rid of some of the authentication methods.

That said, I agree that both examples are rather contrived and this is 
more of an advocatus diaboli kind of reply.


Michael

[1] seems like PostgreSQL is in the set of packages which successfully 
build reproducibly according to the Debian reproducible builds effort, 
yay

Michael Banck wrote:
> On Wed, May 20, 2015 at 02:10:30PM -0400, Tom Lane wrote:
> > One reason why it would not be, if it's a build-time decision,
> > is that it's quite unlikely that any popular packagers would build
> > that way.  So this would only be applicable to custom-built binaries,
> > which is a pretty small class of users to begin with.
> 
> There might be appliance vendors who ship PostgreSQL along with their
> product.  Then, they decide they want to use the pristine tarballs for
> reproducibility and accountability.  If done right, they could publish
> their set of configure options and a build-id or whatever, and 3rd 
> parties could verify the binaries they ship have not been tampered
> with[1].  Granted, they could also just publish the patch for those 3rd
> parties to apply as well, but that sounds slightly inelegant.

I don't think you can mix "elegance" and "appliance vendor" in the same
sentence with a straight face, so while I agree that in theory this
might be true, in reality this functionality would seldom be used for
this.

> The other set of users I could think of are those who, for whatever
> reason, tend to always compile PostgreSQL from source for their
> company/organization.  Maybe they have internal rules that requires a
> custom installation prefix for all their servers or whatever. Due to
> procedural requirements, or just the unwillingness to carry deltas, they
> absolutely want to use the pristine tarballs as well but would be very
> happy to get rid of some of the authentication methods.

Right.  That's the set of users that Josh B says is only comprised of
Volker (the OP).

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Michael Banck wrote:
>> The other set of users I could think of are those who, for whatever
>> reason, tend to always compile PostgreSQL from source for their
>> company/organization.  Maybe they have internal rules that requires a
>> custom installation prefix for all their servers or whatever. Due to
>> procedural requirements, or just the unwillingness to carry deltas, they
>> absolutely want to use the pristine tarballs as well but would be very
>> happy to get rid of some of the authentication methods.

> Right.  That's the set of users that Josh B says is only comprised of
> Volker (the OP).

That might be a bit harsh, but here's the thing: assuming you're willing
to build from source, what is the reason for wanting $small_market_feature
to be built into Postgres rather than being something you carry a patch
for?  ISTM the core reason is that you're expecting the community to carry
the load of testing and maintaining the feature.  And the fact of the
matter is that we're not terribly good at testing non-mainstream build
options.  (There is depressingly little variety in the configure options
used in the buildfarm, for example.)  So I wouldn't be a bit surprised
if something like this broke every time somebody touched the auth code,
and we would not notice.  It would only be reliable if it were something
the community tended to use regularly ... which gets us back to the point
that what needs to happen first is a credible replacement for "trust"
mode.

I think Andres' point about "trust" being an essential disaster recovery
mode is something to consider, as well.  That puts pretty strict limits
on what would be a credible replacement.
        regards, tom lane

On Wed, May 20, 2015 at 07:03:26PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > Michael Banck wrote:
> >> The other set of users I could think of are those who, for whatever
> >> reason, tend to always compile PostgreSQL from source for their
> >> company/organization.  Maybe they have internal rules that requires a
> >> custom installation prefix for all their servers or whatever. Due to
> >> procedural requirements, or just the unwillingness to carry deltas, they
> >> absolutely want to use the pristine tarballs as well but would be very
> >> happy to get rid of some of the authentication methods.
> 
> > Right.  That's the set of users that Josh B says is only comprised of
> > Volker (the OP).
> 
> That might be a bit harsh, but here's the thing: assuming you're willing
> to build from source, what is the reason for wanting $small_market_feature
> to be built into Postgres rather than being something you carry a patch
> for?  ISTM the core reason is that you're expecting the community to carry
> the load of testing and maintaining the feature.  And the fact of the
> matter is that we're not terribly good at testing non-mainstream build
> options.  (There is depressingly little variety in the configure options
> used in the buildfarm, for example.)  So I wouldn't be a bit surprised
> if something like this broke every time somebody touched the auth code,
> and we would not notice.  It would only be reliable if it were something
> the community tended to use regularly ... which gets us back to the point
> that what needs to happen first is a credible replacement for "trust"
> mode.

Fair enough.

> I think Andres' point about "trust" being an essential disaster recovery
> mode is something to consider, as well.  That puts pretty strict limits
> on what would be a credible replacement.

Then let's rename it from `trust' to `disaster'... ;)


Michael

Andres,

* Andres Freund (andres@anarazel.de) wrote:
> On 2015-05-20 15:42:23 -0400, Stephen Frost wrote:
> > > So the first thing to establish is "other than Volker himself, who are
> > > we helping here?"
> >
> > I don't agree with this either.  Providing a "bypass all authentication"
> > configuration option really isn't a good thing.  Why don't packagers use
> > our default pg_hba.conf?  Because it only makes sense in a development
> > type of environment.  I'd argue the same is true for 'trust'.
>
> Uh. So if the shit hit the fan because you mismanaged a password
> rollover, kereberos is down, or something like that, and you can't
> access postgres anymore you want to recompile? And no peer isn't an
> answer isn't an answer, it's not available on windows. Your only way out
> is going to be single user mode. But wait, that's a security hole too.

Apologies for not being clearer.  I agree that we need an alternative
for addressing this use-case before we can consider getting rid of
'trust' or not having it built into the binaries which are distributed.

In other words, I agree with you that we can't simply get rid of 'trust'
without having another solution.  I *do* believe that a real single-user
mode that is only available to the owner of the cluster would go a long
way towards this goal.  If 'trust' was only able to be used by the owner
of the database, I'd have much less of an issue with it.

> I find the arguments presented in this thread for a configure option
> entirely unconvincing. If you'd argued for a saner default
> authentication setup: I'd be on board with that. But this seems just a
> pointless exercise in making things more complicated.

Thankfully, the packagers have already addressed the insecure default
that the source build provides for pg_hba.conf and so we don't need to
worry about it (except perhaps for new distributions or new packagers,
but I hope they'll usually look at the existing packages and not just
distribute what we provide as the default pg_hba.conf).
Thanks!
    Stephen

* Michael Banck (mbanck@gmx.net) wrote:
> On Wed, May 20, 2015 at 07:03:26PM -0400, Tom Lane wrote:
> > I think Andres' point about "trust" being an essential disaster recovery
> > mode is something to consider, as well.  That puts pretty strict limits
> > on what would be a credible replacement.
>
> Then let's rename it from `trust' to `disaster'... ;)

While certainly an amusing idea, I'm afraid that it's simply not worth
the complaints we'd get, as we wouldn't have materially improved
things. :/  Not only that, but they might mistakenly believe that it's
then only available when doing recovery or something along those lines.
Thanks!
    Stephen

On 2015-05-20 19:46:12 -0400, Stephen Frost wrote:
> In other words, I agree with you that we can't simply get rid of 'trust'
> without having another solution.  I *do* believe that a real single-user
> mode that is only available to the owner of the cluster would go a long
> way towards this goal.

I think that's a restriction that doesn't make much sense. What if you
want to dump the data as fast as possible to get things up in another
machine/datacenter/whatever after a fault? Uh wait, parallel dump won't
work with single user mode.


This isn't strengthening security. This is making something far too
complicated (pg_hba.conf) into something even more complicated, because
suddenly even the most basic things only work in some environments. If
you want to improve security significantly, make it easier to configure
authentication/authorization. That's one of the hardest parts of
postgres.

* Andres Freund (andres@anarazel.de) wrote:
> On 2015-05-20 19:46:12 -0400, Stephen Frost wrote:
> > In other words, I agree with you that we can't simply get rid of 'trust'
> > without having another solution.  I *do* believe that a real single-user
> > mode that is only available to the owner of the cluster would go a long
> > way towards this goal.
>
> I think that's a restriction that doesn't make much sense. What if you
> want to dump the data as fast as possible to get things up in another
> machine/datacenter/whatever after a fault? Uh wait, parallel dump won't
> work with single user mode.

We're talking about vaporware here at the moment, so I'll just throw out
that, perhaps, you could have multiple PG instances in single-user which
are all running at the same time in a read-only fashion. :)

Actually, having a tool like that would be *really* handy for a lot of
uses.  In some ways, I believe our lack of such tooling is specifically
because we simply don't have as many issues in this area as other
databases do.  Where is a tool to extract out all the records (with
their system columns) from a file based on a provided table definition?
With that, you could certainly parallelize pulling all of the data out
into flat files.

> This isn't strengthening security. This is making something far too
> complicated (pg_hba.conf) into something even more complicated, because
> suddenly even the most basic things only work in some environments. If
> you want to improve security significantly, make it easier to configure
> authentication/authorization. That's one of the hardest parts of
> postgres.

pg_hba.conf isn't being made any more complicated by removal of an
option.  I agree that we could certainly improve on it.
Thanks!
    Stephen

On 5/20/15 7:19 PM, Stephen Frost wrote:
> * Andres Freund (andres@anarazel.de) wrote:
>> >On 2015-05-20 19:46:12 -0400, Stephen Frost wrote:
>>> > >In other words, I agree with you that we can't simply get rid of 'trust'
>>> > >without having another solution.  I*do*  believe that a real single-user
>>> > >mode that is only available to the owner of the cluster would go a long
>>> > >way towards this goal.
>> >
>> >I think that's a restriction that doesn't make much sense. What if you
>> >want to dump the data as fast as possible to get things up in another
>> >machine/datacenter/whatever after a fault? Uh wait, parallel dump won't
>> >work with single user mode.
> We're talking about vaporware here at the moment, so I'll just throw out
> that, perhaps, you could have multiple PG instances in single-user which
> are all running at the same time in a read-only fashion.:)
>
> Actually, having a tool like that would be*really*  handy for a lot of
> uses.  In some ways, I believe our lack of such tooling is specifically
> because we simply don't have as many issues in this area as other
> databases do.  Where is a tool to extract out all the records (with
> their system columns) from a file based on a provided table definition?
> With that, you could certainly parallelize pulling all of the data out
> into flat files.

Now that we have shared memory queues, perhaps it wouldn't be that hard 
to use them as an alternative communication method. OS handles auth for 
you (and I'd hope this would work in windows too...)
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On Wed, May 20, 2015 at 7:09 PM, Michael Banck <mbanck@gmx.net> wrote:
>> I think Andres' point about "trust" being an essential disaster recovery
>> mode is something to consider, as well.  That puts pretty strict limits
>> on what would be a credible replacement.
>
> Then let's rename it from `trust' to `disaster'... ;)

I still don't buy it.  Say you have a server that connects on its own
VLAN every night to run a backup.  What's wrong with trust?  Would you
really be better putting it on a less-secure network and using a
password that will just have to be stored in a config file someplace?

Answer: No, you wouldn't.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Volker,

> I likely just viewed this too much through a "security" lens - you see a
> possible attack scenario, a way to turn it off, and only minor
> downsides, so just go for it - but this is not how you can work in a
> huge open source project. I guess as a developer you would have to take
> many other issues (like maintainability, user confusion because of the
> change, edge use cases) into account. And as it seems to cause too much
> trouble for "official inclusion" I am fine with patching it during our
> package build.

The security issue you're trying to address is real.  My argument is
that you're addressing it in the wrong way.

So from my perspective anything which requires going off standard
PostgreSQL packages, and encourages users to go off standard PostgreSQL
packages, is a actually a fairly high cost even if the code is
non-invasive.  I would be more open to a GUC which limited the auth
mechansisms available (requiring restart to change), for example, than a
compile flag.

> And yes once someone has write access to your pg_hba.conf you are very
> likely doomed. This would just prevent an attack happening through a
> careless "trust" entry left there, which is just a very quick win for an
> attacker, and may be a bit less likely through this patch.
> 
> 
> To answer to Tom: I see a restricted audience for this patch, but also
> no impact for anyone not wanting to use it. The group of users I see
> would be as follows:
> 
> * People who package PostgreSQL as part of their product and want to
> provide their customers with a restricted "more secure" functionality
> set only to reduce training and support effort. (my use case)

Doesn't it may more sense to deny them direct access to pg_hba.conf
entirely?  I don't understand the use-case here, I guess.

> * People with large Postgres deployments who build their own packages
> and want to enforce a certain security policy (e.g. services are not
> allowed to offer authentication-less access over the network)

It's hard for me to see this argument as persuasive because anyone with
a large deployment is going to be using configuration management, and
CMS already can be configured to control pg_hba.conf, far more
effectively than merely denying specific auth methods.

>    - specifically a good security plan would be to only allow a "safe
> subset" of methods and ensure that these are well documented and perhaps
> audited automatically
>    - this would also allow ensuring there is only one documented /
> audited way to reset passwords (modulo single user mode, that is an
> additional problem which won't be easily fixable)

This latter (forcing users to only use GSSAPI auth, for example) would
be something which would be nice to support, athough again CMS.

> * Distributions which want to provide a more secure package and want to
> ensure each available method can be configured securely and documented
> clearly for their specific setup.
> 
> It does not apply to (or would have a negative effect for) the following
> groups:
> 
> * PostgreSQL users on Windows (disabling trust should not work or should
> show a very prominent warning)
> * Users of default builds (they simply won't be affected)
> * People with a specific use case requiring "trust", "ident" or for the
> more generic patch other specific auth methods. They will be affected if
> they happen to be using a build with this method turned off.
> * People who are used to resetting passwords using "trust" and are
> surprised this suddenly does not work on some specific system

The above is a good list of reasons why we can't realistically remove
Trust from the codebase entirely.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus <josh@agliodbs.com> writes:
> So from my perspective anything which requires going off standard
> PostgreSQL packages, and encourages users to go off standard PostgreSQL
> packages, is a actually a fairly high cost even if the code is
> non-invasive.

Agreed.

> I would be more open to a GUC which limited the auth
> mechansisms available (requiring restart to change), for example, than a
> compile flag.

But how would that fix Volker's scenario?  GUCs are even easier to change
than pg_hba.conf --- in fact, now that we have ALTER SYSTEM, you couldn't
even use configuration management of postgresql.conf to prevent somebody
from altering the value of such a GUC.

I think the real bottom line is this: our code is not designed to prevent
DBAs from doing things that are contrary to local policy, and I for one
am not terribly excited about trying to make it do so.  The list of things
that might be contrary to local policy is just too long, and the number
of ways a DBA might get around any particular restriction is too great.
        regards, tom lane