Thread: Let people set host(no)ssl settings from initdb
Folks,
I've found myself writing a lot of boilerplate pg_hba.conf entries
along the lines of
hostnossl all all 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 md5
so I thought I'd make it easier to do that from initdb.
What say?
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachment
David Fetter <david@fetter.org> writes:
> I've found myself writing a lot of boilerplate pg_hba.conf entries
> along the lines of
> hostnossl all all 0.0.0.0/0 reject
> hostssl all all 0.0.0.0/0 md5
> so I thought I'd make it easier to do that from initdb.
> What say?
I'm pretty suspicious of loading down initdb with random configuration
options, because I think most people nowadays use PG via vendor packages
that script their calls to initdb. So an option like this doesn't help
unless you can persuade all those vendors to pass the option through.
That problem exists even before you get to the question of whether
this specific option is useful or well-designed ... a question I'm
not opining about here, but it would certainly require thought.
regards, tom lane
On Thu, Dec 12, 2019 at 12:23:42AM -0500, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > I've found myself writing a lot of boilerplate pg_hba.conf entries > > along the lines of > > hostnossl all all 0.0.0.0/0 reject > > hostssl all all 0.0.0.0/0 md5 > > so I thought I'd make it easier to do that from initdb. > > What say? > > I'm pretty suspicious of loading down initdb with random configuration > options, because I think most people nowadays use PG via vendor packages > that script their calls to initdb. So an option like this doesn't help > unless you can persuade all those vendors to pass the option through. Would the official PGDG .deb and .rpm packages suffice? > That problem exists even before you get to the question of whether > this specific option is useful or well-designed ... a question I'm > not opining about here, but it would certainly require thought. I think it was a reasonable extension. We cover lines that start with local and host, but they can also start with hostssl and hostnossl. Meanwhile, please find attached a fix for an oversight around IPv6. Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachment
On 2019-12-12 07:24, David Fetter wrote: >> That problem exists even before you get to the question of whether >> this specific option is useful or well-designed ... a question I'm >> not opining about here, but it would certainly require thought. > I think it was a reasonable extension. We cover lines that start with > local and host, but they can also start with hostssl and hostnossl. I suspect the real purpose here is to easily reject non-SSL connections altogether. This is currently quite cumbersome and requires careful ongoing maintenance of pg_hba.conf. But I see two problems with the proposed approach: (1) initdb doesn't support setting up SSL, so the only thing you can achieve here is to reject all TCP/IP connections, until you have set up SSL. (2) The default pg_hba.conf only covers localhost connections. The value of enforcing SSL connections to localhost is probably quite low. You still need ongoing careful pg_hba.conf maintenance as you add more host entries. Maybe we just need something like libpq's sslmode on the server side. Probably not quite the same, perhaps just ssl = require. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Thu, Dec 12, 2019 at 10:47:52AM +0100, Peter Eisentraut wrote: > On 2019-12-12 07:24, David Fetter wrote: > > > That problem exists even before you get to the question of whether > > > this specific option is useful or well-designed ... a question I'm > > > not opining about here, but it would certainly require thought. > > I think it was a reasonable extension. We cover lines that start with > > local and host, but they can also start with hostssl and hostnossl. > > I suspect the real purpose here is to easily reject non-SSL connections > altogether. This is currently quite cumbersome and requires careful ongoing > maintenance of pg_hba.conf. Yes, and kinda. It's certainly possible to put lines high up in pg_hba.conf that read: hostnossl all all 0.0.0.0/0 reject hostnossl all all ::/0 reject and then the only ongoing maintenance is not to put lines above them that contradict it. > But I see two problems with the proposed approach: (1) initdb > doesn't support setting up SSL, so the only thing you can achieve > here is to reject all TCP/IP connections, until you have set up SSL. I don't believe any special setup is needed to require TLS for the connection, which is what this patch handles in a straightforward way. Setting up cert-based auth is the hassle you describe. > (2) The default pg_hba.conf only covers localhost connections. As of this patch, it can be asked to cover all connections. Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
From: David Fetter <david@fetter.org>
> > But I see two problems with the proposed approach: (1) initdb
> > doesn't support setting up SSL, so the only thing you can achieve
> > here is to reject all TCP/IP connections, until you have set up SSL.
>
> I don't believe any special setup is needed to require TLS for the
> connection, which is what this patch handles in a straightforward way.
I think this feature can be useful because it's common to reject remote non-TLS connections. Eliminating the need to
scriptfor pg_hba.conf is welcome. Setting GUC parameters just after initdb is relatively easy, because we can simply
addlines at the end of postgresql.conf. But pg_hba.conf is not because the first matching entry is effective.
In terms of rejecting non-secure remote connections, should hostgssenc/hostnogssenc also be handled similarly?
> > (2) The default pg_hba.conf only covers localhost connections.
>
> As of this patch, it can be asked to cover all connections.
+ <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term>
+ <listitem>
+ <para>
+ This option specifies the authentication method for users via
fg
+ TLS connections used in <filename>pg_hba.conf</filename>
+ (<literal>hostssl</literal> lines).
+ </para>
+ </listitem>
The relationship between --auth/--auth-local/--auth-host and --auth-hostssl/--auth-hostnossl is confusing. The former
isfor local connections, and the latter is for remote ones. Can we just add "remote" in the above documentation?
Plus, you're adding the first option to initdb that handles remote connections. As the following execution shows, it
doesn'twarn about using "trust" for remote connections.
$ initdb --auth=md5 --pwprompt --auth-hostssl=trust --auth-hostnossl=trust
...
syncing data to disk ... ok
Success. You can now start the database server using:
pg_ctl -D /tuna/pg2 -l logfile start
I think we should emit a warning message like the following existing one:
--------------------------------------------------
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
-
initdb: warning: enabling "trust" authentication
Regards
Takayuki Tsunakawa
On Wed, Jan 08, 2020 at 02:53:47AM +0000, tsunakawa.takay@fujitsu.com wrote: > From: David Fetter <david@fetter.org> > > > But I see two problems with the proposed approach: (1) initdb > > > doesn't support setting up SSL, so the only thing you can achieve > > > here is to reject all TCP/IP connections, until you have set up SSL. > > > > I don't believe any special setup is needed to require TLS for the > > connection, which is what this patch handles in a straightforward way. > > I think this feature can be useful because it's common to reject remote non-TLS connections. Eliminating the need to scriptfor pg_hba.conf is welcome. Setting GUC parameters just after initdb is relatively easy, because we can simply addlines at the end of postgresql.conf. But pg_hba.conf is not because the first matching entry is effective. > > In terms of rejecting non-secure remote connections, should hostgssenc/hostnogssenc also be handled similarly? Yes, and they are in the enclosed patch. > > > (2) The default pg_hba.conf only covers localhost connections. > > > > As of this patch, it can be asked to cover all connections. > > + <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term> > + <listitem> > + <para> > + This option specifies the authentication method for users via > fg > + TLS connections used in <filename>pg_hba.conf</filename> > + (<literal>hostssl</literal> lines). > + </para> > + </listitem> > > The relationship between --auth/--auth-local/--auth-host and --auth-hostssl/--auth-hostnossl is confusing. The formeris for local connections, and the latter is for remote ones. Can we just add "remote" in the above documentation? Done. > Plus, you're adding the first option to initdb that handles remote connections. As the following execution shows, it doesn'twarn about using "trust" for remote connections. > > > $ initdb --auth=md5 --pwprompt --auth-hostssl=trust --auth-hostnossl=trust > ... > syncing data to disk ... ok > > Success. You can now start the database server using: > > pg_ctl -D /tuna/pg2 -l logfile start > > > > I think we should emit a warning message like the following existing one: > > -------------------------------------------------- > initdb: warning: enabling "trust" authentication for local connections > You can change this by editing pg_hba.conf or using the option -A, or > --auth-local and --auth-host, the next time you run initdb. > - > initdb: warning: enabling "trust" authentication Done. Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Fri, Jan 17, 2020 at 08:47:49PM +0100, David Fetter wrote: > On Wed, Jan 08, 2020 at 02:53:47AM +0000, tsunakawa.takay@fujitsu.com wrote: > > From: David Fetter <david@fetter.org> > > > > But I see two problems with the proposed approach: (1) initdb > > > > doesn't support setting up SSL, so the only thing you can achieve > > > > here is to reject all TCP/IP connections, until you have set up SSL. > > > > > > I don't believe any special setup is needed to require TLS for the > > > connection, which is what this patch handles in a straightforward way. > > > > I think this feature can be useful because it's common to reject remote non-TLS connections. Eliminating the need toscript for pg_hba.conf is welcome. Setting GUC parameters just after initdb is relatively easy, because we can simplyadd lines at the end of postgresql.conf. But pg_hba.conf is not because the first matching entry is effective. > > > > In terms of rejecting non-secure remote connections, should hostgssenc/hostnogssenc also be handled similarly? > > Yes, and they are in the enclosed patch. > > > > > (2) The default pg_hba.conf only covers localhost connections. > > > > > > As of this patch, it can be asked to cover all connections. > > > > + <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term> > > + <listitem> > > + <para> > > + This option specifies the authentication method for users via > > fg > > + TLS connections used in <filename>pg_hba.conf</filename> > > + (<literal>hostssl</literal> lines). > > + </para> > > + </listitem> > > > > The relationship between --auth/--auth-local/--auth-host and --auth-hostssl/--auth-hostnossl is confusing. The formeris for local connections, and the latter is for remote ones. Can we just add "remote" in the above documentation? > > Done. > > > Plus, you're adding the first option to initdb that handles remote connections. As the following execution shows, itdoesn't warn about using "trust" for remote connections. > > > > > > $ initdb --auth=md5 --pwprompt --auth-hostssl=trust --auth-hostnossl=trust > > ... > > syncing data to disk ... ok > > > > Success. You can now start the database server using: > > > > pg_ctl -D /tuna/pg2 -l logfile start > > > > > > > > I think we should emit a warning message like the following existing one: > > > > -------------------------------------------------- > > initdb: warning: enabling "trust" authentication for local connections > > You can change this by editing pg_hba.conf or using the option -A, or > > --auth-local and --auth-host, the next time you run initdb. > > - > > initdb: warning: enabling "trust" authentication > > Done. > > Best, > David. This time, with the patch attached. Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachment
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed
Hi
I applied the patch "v3-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patch" and did some verification with
it.The intended feature works overall and I think it is quite useful to support default auth methods for ssl and gss
hosttypes. I have also found some minor things in the patch and would like to share as below:
> +"# CAUTION: Configuring the system for \"trust\" authentication\n" \
> +"# allows any user who can reach the databse on the route specified\n" \
> +"# to connect as any PostgreSQL user, including the database\n" \
> +"# superuser. If you do not trust all the users who could\n" \
> +"# reach the database on the route specified, use a more restrictive\n" \
> +"# authentication method.\n"
Found a typo: should be 'database' instead of 'databse'
> * a sort of poor man's grep -v
> */
> -#ifndef HAVE_UNIX_SOCKETS
> static char **
> filter_lines_with_token(char **lines, const char *token)
> {
> @@ -461,7 +466,6 @@ filter_lines_with_token(char **lines, const char *token)
>
> return result;
> }
> -#endif
I see that you have removed "#ifndef HAVE_UNIX_SOCKETS" around the filter_lines_with_token() function definition so it
wouldbe always available, which is used to remove the @tokens@ in case user does not specify a default auth method for
thenew hostssl, hostgss options. I think you should also remove the "#ifndef HAVE_UNIX_SOCKETS" around its declaration
aswell so both function definition and declaration would make sense.
#ifndef HAVE_UNIX_SOCKETS
static char **filter_lines_with_token(char **lines, const char *token);
#endif
Cary Huang
-------------
HighGo Software Inc. (Canada)
cary.huang@highgo.ca
www.highgo.ca
On Mon, Apr 06, 2020 at 10:12:16PM +0000, Cary Huang wrote:
> The following review has been posted through the commitfest application:
> make installcheck-world: tested, passed
> Implements feature: tested, passed
> Spec compliant: tested, passed
> Documentation: tested, passed
>
> Hi
> I applied the patch "v3-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patch" and did some verification with
it.The intended feature works overall and I think it is quite useful to support default auth methods for ssl and gss
hosttypes. I have also found some minor things in the patch and would like to share as below:
>
> > +"# CAUTION: Configuring the system for \"trust\" authentication\n" \
> > +"# allows any user who can reach the databse on the route specified\n" \
> > +"# to connect as any PostgreSQL user, including the database\n" \
> > +"# superuser. If you do not trust all the users who could\n" \
> > +"# reach the database on the route specified, use a more restrictive\n" \
> > +"# authentication method.\n"
>
> Found a typo: should be 'database' instead of 'databse'
Fixed.
> > * a sort of poor man's grep -v
> > */
> > -#ifndef HAVE_UNIX_SOCKETS
> > static char **
> > filter_lines_with_token(char **lines, const char *token)
> > {
> > @@ -461,7 +466,6 @@ filter_lines_with_token(char **lines, const char *token)
> >
> > return result;
> > }
> > -#endif
>
> I see that you have removed "#ifndef HAVE_UNIX_SOCKETS" around the
> filter_lines_with_token() function definition so it would be always
> available, which is used to remove the @tokens@ in case user does
> not specify a default auth method for the new hostssl, hostgss
> options. I think you should also remove the "#ifndef
> HAVE_UNIX_SOCKETS" around its declaration as well so both function
> definition and declaration would make sense.
Fixed.
Thanks very much for the review!
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachment
The CF Patch Tester consider this patch to be malformed and is unable to apply and test it. Can you please submit a rebased version? cheers ./daniel
David Fetter <david@fetter.org> writes:
> On Wed, Dec 30, 2020 at 08:24:06PM +0100, David Fetter wrote:
>> On Mon, Sep 07, 2020 at 11:57:58AM +0900, Michael Paquier wrote:
>>> I have looked at the patch of this thread, and I doubt that it is a
>>> good idea to put more burden into initdb for that. I agree that
>>> being able to reject easily non-SSL connections in pg_hba.conf is a
>>> bit of a hassle now, but putting more logic into initdb does not seem
>>> the right course to me. Perhaps we could consider an idea like
>>> Peter's to have a sslmode=require on the server side and ease the
>>> generation of HBA rules..
>> Please find attached the rebased patch.
>>
>> Peter's suggestion seems a little more subtle to me than requiring TLS
>> on the server side in that what people generally want to do is
>> disallow clear text connections entirely. In those scenarios, people
>> would also want to set (and be able to change at runtime) some kind of
>> cryptographic policy, as SSH and TLS do. While I see this as a worthy
>> goal, it's a much bigger lift than an optional argument or two to
>> initdb, and requires a lot more discussion than it's had to date.
FWIW, I still agree with what Michael says above. I do not think
that adding more options to initdb is a useful solution here.
In the first place, it's unlikely that we'll manage to cover many
people's exact requirements this way. In the second place, it's
very unclear where to stop adding options. In the third place,
I believe the vast majority of users don't invoke initdb "by hand"
anymore. The typical scenario is to go through a packager-provided
script, which almost certainly won't offer access to these additional
options. In the fourth place, many people won't know at initdb time
exactly what they should do, or they'll change their minds later.
The last two points suggest that what'd be more useful is some sort
of tool to modify an existing pg_hba.conf file. Or maybe even just
audit a file to see if it implements $desired-policy, such as
"no unencrypted network connections" or "no plaintext passwords".
(I kind of like the auditing-tool approach; it seems less scary
than something that actually rewrites the file.)
regards, tom lane
On Wed, 30 Dec 2020 at 15:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
In the third place,
I believe the vast majority of users don't invoke initdb "by hand"
anymore. The typical scenario is to go through a packager-provided
script, which almost certainly won't offer access to these additional
options.
I can't speak to other distributions, but on Ubuntu pg_createcluster allows a -- followed by initdb options. So at least on Ubuntu any additional options will indeed be available to everybody. I would hope that other distributions have the same capability.
I for one would like to be able to tell initdb (pg_createcluster) what to put in the first column of pb_hba.conf in the same way I can already use --auth{,-host,-local}= to set the auth-method column. Ideally, for simple situations (think testing scripts and the like, rather than long-term installations) the pg_hba.conf could be created by initdb and not changed after that.
On Wed, Dec 30, 2020 at 9:00 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Fetter <david@fetter.org> writes:
> On Wed, Dec 30, 2020 at 08:24:06PM +0100, David Fetter wrote:
>> On Mon, Sep 07, 2020 at 11:57:58AM +0900, Michael Paquier wrote:
>>> I have looked at the patch of this thread, and I doubt that it is a
>>> good idea to put more burden into initdb for that. I agree that
>>> being able to reject easily non-SSL connections in pg_hba.conf is a
>>> bit of a hassle now, but putting more logic into initdb does not seem
>>> the right course to me. Perhaps we could consider an idea like
>>> Peter's to have a sslmode=require on the server side and ease the
>>> generation of HBA rules..
>> Please find attached the rebased patch.
>>
>> Peter's suggestion seems a little more subtle to me than requiring TLS
>> on the server side in that what people generally want to do is
>> disallow clear text connections entirely. In those scenarios, people
>> would also want to set (and be able to change at runtime) some kind of
>> cryptographic policy, as SSH and TLS do. While I see this as a worthy
>> goal, it's a much bigger lift than an optional argument or two to
>> initdb, and requires a lot more discussion than it's had to date.
FWIW, I still agree with what Michael says above. I do not think
that adding more options to initdb is a useful solution here.
In the first place, it's unlikely that we'll manage to cover many
people's exact requirements this way. In the second place, it's
very unclear where to stop adding options. In the third place,
I believe the vast majority of users don't invoke initdb "by hand"
anymore. The typical scenario is to go through a packager-provided
script, which almost certainly won't offer access to these additional
options. In the fourth place, many people won't know at initdb time
exactly what they should do, or they'll change their minds later.
AFAIK bot the debian/ubuntu script mentioned by Isaac downthread, and the RedHat/Fedora ones do allow you to specify inidb options. That would cover the majority I'd say...
That said, I agree with not adding it as an option to initdb. You'll quickly get to the point where you specify the whole pg_hba file on the commandline to initdb -- and most people today who actually care that much about it would have their pg_hba.conf file under some sort of configuration management anyway, whether it's ansible, chef, puppet or something else.
The last two points suggest that what'd be more useful is some sort
of tool to modify an existing pg_hba.conf file. Or maybe even just
I don't think we need, or indeed want, a tool to *modify* pg_hba.conf. For people who want that, there are already plenty options out there in the configuration management space, let's not invent our own.
audit a file to see if it implements $desired-policy, such as
"no unencrypted network connections" or "no plaintext passwords".
(I kind of like the auditing-tool approach; it seems less scary
than something that actually rewrites the file.)
Audiring, however, is a lot more interesting.
For people who actually care about most of this, it's not that important what the initial one is, if it can trivially be changed to become insecure. And unfortunately due to the complexity of pg_hba, that can easily happen. Keeping it under configuration management helps with that, but doesn't entirely solve the problem.
Another possible approach could be to add global gucs for "allow_unencrypted_connections" and maybe "available_authentication_methods". That would override pg_hba. At least in doing so, there would be *one* spot where one could fairly strictly lock things down. (Similar to Peters suggestion upthread)