initdb recommendations
Given some of the recent hubbub and analysis of CVE entries, one part of
the documentation[1]https://www.postgresql.org/docs/current/creating-cluster.html that could be further clarified is what initdb does
by default, i.e. creates a cluster where users can connect with trust
authentication. While this may be great for people who are hacking or
running PostgreSQL in a trusted local environment, this may not make
sense for many (most?) other systems.
The attached patch clarifies this fact and adds a "warning" box just
below the initdb examples that provides recommendations to create a more
secure environment. It also removes the section that discusses this
below the part that discusses securing the directory, as really this
explanation should go right after the "initdb" call.
(There could be an additional discussion about whether or not we want to
change the default behavior for initdb, but I would suggest that a safe
starting point would be to ensure we call this out)
Credits to Magnus for pointing this out, and Tom + Andrew D. for review
before posting to list.
Jonathan
[1]: https://www.postgresql.org/docs/current/creating-cluster.html
Attachments:
0001-Add-a-warning-about-the-client-authentication-defaul.patchtext/plain; charset=UTF-8; name=0001-Add-a-warning-about-the-client-authentication-defaul.patch; x-mac-creator=0; x-mac-type=0Download+24-23
On 2019-04-05 18:11, Jonathan S. Katz wrote:
(There could be an additional discussion about whether or not we want to
change the default behavior for initdb, but I would suggest that a safe
starting point would be to ensure we call this out)
I think we should just change the defaults. There is a risk of warning
fatigue. initdb does warn about this, so anyone who cared could have
gotten the information.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 4/5/19 4:58 PM, Peter Eisentraut wrote:
On 2019-04-05 18:11, Jonathan S. Katz wrote:
(There could be an additional discussion about whether or not we want to
change the default behavior for initdb, but I would suggest that a safe
starting point would be to ensure we call this out)I think we should just change the defaults. There is a risk of warning
fatigue. initdb does warn about this, so anyone who cared could have
gotten the information.
It might actually be a combination of both updating the defaults and
modifying the documentation.
If we introduce better defaults, we'll need an explanation of what the
defaults are and why they are as such.
If we don't, we certainly need to warn the user what's happening. The
way it's currently written, it's very easy to miss.
I also don't see how it's warning fatigue when it's both a) a feature
that could put your system into a vulnerable state if you're not careful
and b) the only warning on that page.
Jonathan
On Fri, Apr 5, 2019 at 10:58 PM Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:
On 2019-04-05 18:11, Jonathan S. Katz wrote:
(There could be an additional discussion about whether or not we want to
change the default behavior for initdb, but I would suggest that a safe
starting point would be to ensure we call this out)I think we should just change the defaults. There is a risk of warning
fatigue. initdb does warn about this, so anyone who cared could have
gotten the information.
I've been suggesting that for years, so definite strong +1 for doing that.
If it's something that annoys backend developers who initdb very often, I
suggest we add an environment variable to override it. But I'm not sure
that's really necessary -- creating a shell alias or similar is easy to do,
and most have probably already done so for other reasons.
That said, I think it would make sense to *also* have a warning. And in
particular, we should strongly consider backpatching a warning.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
On Sat, Apr 06, 2019 at 11:35:44AM +0200, Magnus Hagander wrote:
On Fri, Apr 5, 2019 at 10:58 PM Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
On 2019-04-05 18:11, Jonathan S. Katz wrote:
(There could be an additional discussion about whether or not we want to
change the default behavior for initdb, but I would suggest that a safe
starting point would be to ensure we call this out)I think we should just change the defaults. There is a risk of warning
fatigue. initdb does warn about this, so anyone who cared could have
gotten the information.I've been suggesting that for years, so definite strong +1 for doing that.
+1
If it's something that annoys backend developers who initdb very often, I
suggest we add an environment variable to override it. But I'm not sure
that's really necessary -- creating a shell alias or similar is easy to do,
and most have probably already done so for other reasons.
I, for one, do most initdb runs via a script and wouldn't use such an
environment variable.
On 2019-04-05 18:11, Jonathan S. Katz wrote:
+ <para> + We recommend using the <option>-W</option>, <option>--pwprompt</option>, + or <option>--pwfile</option> flags to assign a password to the database + superuser, and to override the <filename>pg_hba.conf</filename> default + generation using <option>-auth-local peer</option> for local connections, + and <option>-auth-host scram-sha-256</option> for remote connections. See + <xref linkend="client-authentication"/> for more information on client + authentication methods. + </para>
As discussed on hackers, we are not ready to support scram-sha-256 out
of the box. So this advice, or any similar advice elsewhere, would need
to recommend "md5" as the setting --- which would probably be embarrassing.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 4/8/19 8:25 AM, Peter Eisentraut wrote:
On 2019-04-05 18:11, Jonathan S. Katz wrote:
+ <para> + We recommend using the <option>-W</option>, <option>--pwprompt</option>, + or <option>--pwfile</option> flags to assign a password to the database + superuser, and to override the <filename>pg_hba.conf</filename> default + generation using <option>-auth-local peer</option> for local connections, + and <option>-auth-host scram-sha-256</option> for remote connections. See + <xref linkend="client-authentication"/> for more information on client + authentication methods. + </para>As discussed on hackers, we are not ready to support scram-sha-256 out
of the box. So this advice, or any similar advice elsewhere, would need
to recommend "md5" as the setting --- which would probably be embarrassing.
Well, it's less embarrassing than trust, and we currently state:
"Also, specify -A md5 or -A password so that the default trust
authentication mode is not used"[1]https://www.postgresql.org/docs/current/creating-cluster.html
We could also modify it to say :
"and <option>-auth-host scram-sha-256</option> for remote connections if
your client supports it, otherwise <option>-auth-host md5</option>"
Jonathan
[1]: https://www.postgresql.org/docs/current/creating-cluster.html
On Mon, Apr 8, 2019 at 2:41 PM Jonathan S. Katz <jkatz@postgresql.org>
wrote:
On 4/8/19 8:25 AM, Peter Eisentraut wrote:
On 2019-04-05 18:11, Jonathan S. Katz wrote:
+ <para> + We recommend using the <option>-W</option>,<option>--pwprompt</option>,
+ or <option>--pwfile</option> flags to assign a password to the
database
+ superuser, and to override the <filename>pg_hba.conf</filename>
default
+ generation using <option>-auth-local peer</option> for local
connections,
+ and <option>-auth-host scram-sha-256</option> for remote
connections. See
+ <xref linkend="client-authentication"/> for more information on
client
+ authentication methods.
+ </para>As discussed on hackers, we are not ready to support scram-sha-256 out
of the box. So this advice, or any similar advice elsewhere, would need
to recommend "md5" as the setting --- which would probably beembarrassing.
Well, it's less embarrassing than trust, and we currently state:
Yes. Much less.
"Also, specify -A md5 or -A password so that the default trust
authentication mode is not used"[1]
We could also modify it to say :
"and <option>-auth-host scram-sha-256</option> for remote connections if
your client supports it, otherwise <option>-auth-host md5</option>"
That would be the best from a correctness, but if of course also makes
things sound more complicated. I'm not sure where the right balance is
there.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
On 4/8/19 8:44 AM, Magnus Hagander wrote:
On Mon, Apr 8, 2019 at 2:41 PM Jonathan S. Katz <jkatz@postgresql.org
<mailto:jkatz@postgresql.org>> wrote:On 4/8/19 8:25 AM, Peter Eisentraut wrote:
On 2019-04-05 18:11, Jonathan S. Katz wrote:
+ <para> + We recommend using the <option>-W</option>,<option>--pwprompt</option>,
+ or <option>--pwfile</option> flags to assign a password to
the database
+ superuser, and to override the
<filename>pg_hba.conf</filename> default
+ generation using <option>-auth-local peer</option> for
local connections,
+ and <option>-auth-host scram-sha-256</option> for remote
connections. See
+ <xref linkend="client-authentication"/> for more
information on client
+ authentication methods.
+ </para>As discussed on hackers, we are not ready to support scram-sha-256 out
of the box. So this advice, or any similar advice elsewhere,would need
to recommend "md5" as the setting --- which would probably be
embarrassing.
Well, it's less embarrassing than trust, and we currently state:
Yes. Much less.
"Also, specify -A md5 or -A password so that the default trust
authentication mode is not used"[1]We could also modify it to say :
"and <option>-auth-host scram-sha-256</option> for remote connections if
your client supports it, otherwise <option>-auth-host md5</option>"That would be the best from a correctness, but if of course also makes
things sound more complicated. I'm not sure where the right balance is
there.
We could link here[1]https://wiki.postgresql.org/wiki/List_of_drivers from the docs on the line for "client supports it"
Jonathan
On 2019-04-06 20:08, Noah Misch wrote:
I think we should just change the defaults. There is a risk of warning
fatigue. initdb does warn about this, so anyone who cared could have
gotten the information.I've been suggesting that for years, so definite strong +1 for doing that.
+1
To recap, the idea here was to change the default authentication methods
that initdb sets up, in place of "trust".
I think the ideal scenario would be to use "peer" for local and some
appropriate password method (being discussed elsewhere) for host.
Looking through the buildfarm, I gather that the only platforms that
don't support peer are Windows, AIX, and HP-UX. I think we can probably
figure out some fallback or alternative default for the latter two
platforms without anyone noticing. But what should the defaults be on
Windows? It doesn't have local sockets, so the lack of peer wouldn't
matter. But is it OK to default to a password method, or would that
upset people particularly?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Thu, May 23, 2019, 18:54 Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:
On 2019-04-06 20:08, Noah Misch wrote:
I think we should just change the defaults. There is a risk of warning
fatigue. initdb does warn about this, so anyone who cared could have
gotten the information.I've been suggesting that for years, so definite strong +1 for doing
that.
+1
To recap, the idea here was to change the default authentication methods
that initdb sets up, in place of "trust".I think the ideal scenario would be to use "peer" for local and some
appropriate password method (being discussed elsewhere) for host.Looking through the buildfarm, I gather that the only platforms that
don't support peer are Windows, AIX, and HP-UX. I think we can probably
figure out some fallback or alternative default for the latter two
platforms without anyone noticing. But what should the defaults be on
Windows? It doesn't have local sockets, so the lack of peer wouldn't
matter. But is it OK to default to a password method, or would that
upset people particularly?
I'm sure password would be fine there. It's what "everybody else" does
(well sqlserver also cord integrated security, but people are used to it).
/Magnus
On 5/23/19 12:54 PM, Peter Eisentraut wrote:
On 2019-04-06 20:08, Noah Misch wrote:
I think we should just change the defaults. There is a risk of warning
fatigue. initdb does warn about this, so anyone who cared could have
gotten the information.I've been suggesting that for years, so definite strong +1 for doing that.
+1
To recap, the idea here was to change the default authentication methods
that initdb sets up, in place of "trust".I think the ideal scenario would be to use "peer" for local and some
appropriate password method (being discussed elsewhere) for host.
+1.
Looking through the buildfarm, I gather that the only platforms that
don't support peer are Windows, AIX, and HP-UX. I think we can probably
figure out some fallback or alternative default for the latter two
platforms without anyone noticing. But what should the defaults be on
Windows? It doesn't have local sockets, so the lack of peer wouldn't
matter. But is it OK to default to a password method, or would that
upset people particularly?
+1 for password method. Definitely better than trust :)
Jonathan
On 5/23/19 6:47 PM, Jonathan S. Katz wrote:
On 5/23/19 12:54 PM, Peter Eisentraut wrote:
On 2019-04-06 20:08, Noah Misch wrote:
I think we should just change the defaults. There is a risk of warning
fatigue. initdb does warn about this, so anyone who cared could have
gotten the information.I've been suggesting that for years, so definite strong +1 for doing that.
+1
To recap, the idea here was to change the default authentication methods
that initdb sets up, in place of "trust".I think the ideal scenario would be to use "peer" for local and some
appropriate password method (being discussed elsewhere) for host.+1.
Looking through the buildfarm, I gather that the only platforms that
don't support peer are Windows, AIX, and HP-UX. I think we can probably
figure out some fallback or alternative default for the latter two
platforms without anyone noticing. But what should the defaults be on
Windows? It doesn't have local sockets, so the lack of peer wouldn't
matter. But is it OK to default to a password method, or would that
upset people particularly?+1 for password method. Definitely better than trust :)
Attached is v2 of the patch.
For now I have left in the password based method to be scram-sha-256 as
I am optimistic about the support across client drivers[1] (and FWIW I
have an implementation for crystal-pg ~60% done).
However, this probably means we would need to set the default password
encryption guc to "scram-sha-256" which we're not ready to do yet, so it
may be moot to leave it in.
So, thinking out loud about that, we should probably use "md5" and once
we decide to make the encryption method "scram-sha-256" by default, then
we update the recommendation?
Thanks,
Jonathan
Attachments:
0001-Add-a-warning-about-the-client-authentication-v2.patchtext/plain; charset=UTF-8; name=0001-Add-a-warning-about-the-client-authentication-v2.patch; x-mac-creator=0; x-mac-type=0Download+25-22
"Jonathan S. Katz" <jkatz@postgresql.org> writes:
For now I have left in the password based method to be scram-sha-256 as
I am optimistic about the support across client drivers[1] (and FWIW I
have an implementation for crystal-pg ~60% done).
However, this probably means we would need to set the default password
encryption guc to "scram-sha-256" which we're not ready to do yet, so it
may be moot to leave it in.
So, thinking out loud about that, we should probably use "md5" and once
we decide to make the encryption method "scram-sha-256" by default, then
we update the recommendation?
Meh. If we're going to break things, let's break them. Set it to
scram by default and let people who need to cope with old clients
change the default. I'm tired of explaining that MD5 isn't actually
insecure in our usage ...
regards, tom lane
Greetings,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
"Jonathan S. Katz" <jkatz@postgresql.org> writes:
For now I have left in the password based method to be scram-sha-256 as
I am optimistic about the support across client drivers[1] (and FWIW I
have an implementation for crystal-pg ~60% done).However, this probably means we would need to set the default password
encryption guc to "scram-sha-256" which we're not ready to do yet, so it
may be moot to leave it in.So, thinking out loud about that, we should probably use "md5" and once
we decide to make the encryption method "scram-sha-256" by default, then
we update the recommendation?Meh. If we're going to break things, let's break them. Set it to
scram by default and let people who need to cope with old clients
change the default. I'm tired of explaining that MD5 isn't actually
insecure in our usage ...
+many.
Thanks,
Stephen
On 5/23/19 10:30 PM, Stephen Frost wrote:
Greetings,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
"Jonathan S. Katz" <jkatz@postgresql.org> writes:
For now I have left in the password based method to be scram-sha-256 as
I am optimistic about the support across client drivers[1] (and FWIW I
have an implementation for crystal-pg ~60% done).However, this probably means we would need to set the default password
encryption guc to "scram-sha-256" which we're not ready to do yet, so it
may be moot to leave it in.So, thinking out loud about that, we should probably use "md5" and once
we decide to make the encryption method "scram-sha-256" by default, then
we update the recommendation?Meh. If we're going to break things, let's break them. Set it to
scram by default and let people who need to cope with old clients
change the default. I'm tired of explaining that MD5 isn't actually
insecure in our usage ...+many.
many++
Are we doing this for pg12? In any case, I would think we better loudly
point out this change somewhere.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
On Fri, 24 May 2019 at 07:48, Joe Conway <mail@joeconway.com> wrote:
On 5/23/19 10:30 PM, Stephen Frost wrote:
Greetings,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
"Jonathan S. Katz" <jkatz@postgresql.org> writes:
For now I have left in the password based method to be scram-sha-256
as
I am optimistic about the support across client drivers[1] (and FWIW I
have an implementation for crystal-pg ~60% done).However, this probably means we would need to set the default password
encryption guc to "scram-sha-256" which we're not ready to do yet, soit
may be moot to leave it in.
So, thinking out loud about that, we should probably use "md5" and
once
we decide to make the encryption method "scram-sha-256" by default,
then
we update the recommendation?
Meh. If we're going to break things, let's break them. Set it to
scram by default and let people who need to cope with old clients
change the default. I'm tired of explaining that MD5 isn't actually
insecure in our usage ...+many.
many++
Are we doing this for pg12? In any case, I would think we better loudly
point out this change somewhere.
+many as well given the presumption that we are going to break existing
behaviour
Dave
On 2019-05-24 13:48, Joe Conway wrote:
Are we doing this for pg12?
no
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Greetings,
* Joe Conway (mail@joeconway.com) wrote:
On 5/23/19 10:30 PM, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
"Jonathan S. Katz" <jkatz@postgresql.org> writes:
For now I have left in the password based method to be scram-sha-256 as
I am optimistic about the support across client drivers[1] (and FWIW I
have an implementation for crystal-pg ~60% done).However, this probably means we would need to set the default password
encryption guc to "scram-sha-256" which we're not ready to do yet, so it
may be moot to leave it in.So, thinking out loud about that, we should probably use "md5" and once
we decide to make the encryption method "scram-sha-256" by default, then
we update the recommendation?Meh. If we're going to break things, let's break them. Set it to
scram by default and let people who need to cope with old clients
change the default. I'm tired of explaining that MD5 isn't actually
insecure in our usage ...+many.
many++
Are we doing this for pg12? In any case, I would think we better loudly
point out this change somewhere.
Sure, we should point it out, but I don't know that it needs to be
screamed from the rooftops considering the packagers have already been
largely ignoring our defaults here anyway...
Thanks,
Stephen
On 5/24/19 8:13 AM, Stephen Frost wrote:
Greetings,
* Joe Conway (mail@joeconway.com) wrote:
On 5/23/19 10:30 PM, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
"Jonathan S. Katz" <jkatz@postgresql.org> writes:
For now I have left in the password based method to be scram-sha-256 as
I am optimistic about the support across client drivers[1] (and FWIW I
have an implementation for crystal-pg ~60% done).However, this probably means we would need to set the default password
encryption guc to "scram-sha-256" which we're not ready to do yet, so it
may be moot to leave it in.So, thinking out loud about that, we should probably use "md5" and once
we decide to make the encryption method "scram-sha-256" by default, then
we update the recommendation?Meh. If we're going to break things, let's break them. Set it to
scram by default and let people who need to cope with old clients
change the default. I'm tired of explaining that MD5 isn't actually
insecure in our usage ...+many.
many++
Are we doing this for pg12? In any case, I would think we better loudly
point out this change somewhere.Sure, we should point it out, but I don't know that it needs to be
screamed from the rooftops considering the packagers have already been
largely ignoring our defaults here anyway...
Yeah, I thought about that, but anyone not using those packages will be
in for a big surprise. Don't get me wrong, I wholeheartedly endorse the
change, but I predict many related questions on the lists, and anything
we can do to mitigate that should be done.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
