Thread: proper pg_hba config to require ssl from non-local/private ips

proper pg_hba config to require ssl from non-local/private ips

From

Matthew Lenz

Date:

19 October 2022, 15:49:49

This is what I've got currently but it's still allowing non-ssl connections from remote (non-local/private) hosts. Any thoughts?

local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 10.0.0.0/8 md5
host all all 172.16.0.0/12 md5
hostssl all all all md5 clientcert=verify-ca

Also when I require SSL on the client it allows SSL connections without a CA signed cert which I thought clientcert=verify-ca in this pg_hba should require.

Re: proper pg_hba config to require ssl from non-local/private ips

From

Laurenz Albe

Date:

19 October 2022, 18:00:32

On Wed, 2022-10-19 at 07:49 -0500, Matthew Lenz wrote:
> This is what I've got currently but it's still allowing non-ssl connections from remote (non-local/private) hosts.
Anythoughts?
 
> 
> local   all             all                                     trust
> host    all             all             127.0.0.1/32            trust
> host    all             all             ::1/128                 trust
> host    all             all             10.0.0.0/8              md5
> host    all             all             172.16.0.0/12           md5
> hostssl all             all             all                     md5 clientcert=verify-ca
> 
> Also when I require SSL on the client it allows SSL connections without a CA signed cert
> which I thought clientcert=verify-ca in this pg_hba should require.

Then your client IP address must match the CIDR 172.16.0.0/12, right?

That line matches both unencrypted and encrypted connections, that's why it is used
for SSL connectios as well.  To change that, use "hostnossl" in the penultimate line.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: proper pg_hba config to require ssl from non-local/private ips

From

Jeff Janes

Date:

19 October 2022, 18:47:14

On Wed, Oct 19, 2022 at 8:50 AM Matthew Lenz <mlenz@nocturnal.org> wrote:

This is what I've got currently but it's still allowing non-ssl connections from remote (non-local/private) hosts. Any thoughts?

Did you reload the server configurations after changing the file? What is the address of that non-local host, as seen by the server? (you can check the first with `select * from pg_hba_file_rules`, and second with `select client_addr from pg_stat_activity where pid=pg_backend_pid();`

local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 10.0.0.0/8 md5
host all all 172.16.0.0/12 md5
hostssl all all all md5 clientcert=verify-ca

Also when I require SSL on the client it allows SSL connections without a CA signed cert which I thought clientcert=verify-ca in this pg_hba should require.

No, clientcert=verify-ca forces the server to check the client's certificate. Forcing the client to check the server's certificate must be done on the client end. (And of course if you are not connecting via that line of the pg_hba, then that setting doesn't do anything.)

Cheers,

Jeff

Re: proper pg_hba config to require ssl from non-local/private ips

From

Matthew Lenz

Date:

19 October 2022, 19:26:20

They are external internet routable ips. They will not match any of the host lines.

On Wed, Oct 19, 2022 at 10:00 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Wed, 2022-10-19 at 07:49 -0500, Matthew Lenz wrote:
> This is what I've got currently but it's still allowing non-ssl connections from remote (non-local/private) hosts. Any thoughts?
>
> local all all trust
> host all all 127.0.0.1/32 trust
> host all all ::1/128 trust
> host all all 10.0.0.0/8 md5
> host all all 172.16.0.0/12 md5
> hostssl all all all md5 clientcert=verify-ca
>
> Also when I require SSL on the client it allows SSL connections without a CA signed cert
> which I thought clientcert=verify-ca in this pg_hba should require.

Then your client IP address must match the CIDR 172.16.0.0/12, right?

That line matches both unencrypted and encrypted connections, that's why it is used
for SSL connectios as well. To change that, use "hostnossl" in the penultimate line.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Re: proper pg_hba config to require ssl from non-local/private ips

From

Frank Gard

Date:

19 October 2022, 19:26:56

Hi Jeff,

Am 19.10.22 um 17:47 schrieb Jeff Janes:

On Wed, Oct 19, 2022 at 8:50 AM Matthew Lenz <mlenz@nocturnal.org> wrote:
This is what I've got currently but it's still allowing non-ssl connections from remote (non-local/private) hosts. Any thoughts?

Did you reload the server configurations after changing the file? What is the address of that non-local host, as seen by the server? (you can check the first with `select * from pg_hba_file_rules`,

unfortunately that's not true, at least up to Pg v14 (I don't know if they've changed this IMHO "unexpected" behaviour in the meantime). The pg_hba_file_rules seems to be just an SQL frontend to the hba-file's content and does not(!) reflect the currently active configuration. So you can see your changes before the are activated, e.g. by calling pg_reload_conf().

[…]

Cheers,
Frank.

Re: proper pg_hba config to require ssl from non-local/private ips

From

Matthew Lenz

Date:

19 October 2022, 19:29:26

On Wed, Oct 19, 2022 at 10:47 AM Jeff Janes <jeff.janes@gmail.com> wrote:

On Wed, Oct 19, 2022 at 8:50 AM Matthew Lenz <mlenz@nocturnal.org> wrote:
This is what I've got currently but it's still allowing non-ssl connections from remote (non-local/private) hosts. Any thoughts?

Did you reload the server configurations after changing the file? What is the address of that non-local host, as seen by the server? (you can check the first with `select * from pg_hba_file_rules`, and second with `select client_addr from pg_stat_activity where pid=pg_backend_pid();`

They are seen as external internet routable ips as expected (which should only match on the hostssl line).

local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 10.0.0.0/8 md5
host all all 172.16.0.0/12 md5
hostssl all all all md5 clientcert=verify-ca

Also when I require SSL on the client it allows SSL connections without a CA signed cert which I thought clientcert=verify-ca in this pg_hba should require.

No, clientcert=verify-ca forces the server to check the client's certificate. Forcing the client to check the server's certificate must be done on the client end. (And of course if you are not connecting via that line of the pg_hba, then that setting doesn't do anything.)

I didn't say the client was meant to enforce it. I meant the server should be enforcing it (it's not).

Cheers,

Jeff

Re: proper pg_hba config to require ssl from non-local/private ips

From

Jeff Janes

Date:

19 October 2022, 19:36:55

On Wed, Oct 19, 2022 at 12:26 PM Frank Gard <frank.von.postgresql.org@familie-gard.de> wrote:

Hi Jeff,
Am 19.10.22 um 17:47 schrieb Jeff Janes:
On Wed, Oct 19, 2022 at 8:50 AM Matthew Lenz <mlenz@nocturnal.org> wrote:
This is what I've got currently but it's still allowing non-ssl connections from remote (non-local/private) hosts. Any thoughts?

Did you reload the server configurations after changing the file? What is the address of that non-local host, as seen by the server? (you can check the first with `select * from pg_hba_file_rules`,
unfortunately that's not true, at least up to Pg v14 (I don't know if they've changed this IMHO "unexpected" behaviour in the meantime). The pg_hba_file_rules seems to be just an SQL frontend to the hba-file's content and does not(!) reflect the currently active configuration. So you can see your changes before the are activated, e.g. by calling pg_reload_conf().

Yes, thanks for the correction. I'd mistaken using it for checking that the file you changed was the correct one for use by the connected server (people often edit the wrong pg_hba.conf file), for checking that it had actually been put into use via a reload, which as you note it doesn't do.

Cheers,

Jeff

Re: proper pg_hba config to require ssl from non-local/private ips

From

Scott Ribe

Date:

19 October 2022, 19:41:41

> On Oct 19, 2022, at 10:29 AM, Matthew Lenz <mlenz@nocturnal.org> wrote:
>
> I didn't say the client was meant to enforce it.  I meant the server should be enforcing it (it's not).

Doesn't really make sense for the server to determine client verification of server certificate.

1) Server controls what certificate is provided, thus has control over what CA is used.

2) What would it mean for server to turn OFF client verification? Server is allowed to say "here's my cert, doesn't
matterthat it's using a bogus CA, you take it regardless of your local settings"???

Re: proper pg_hba config to require ssl from non-local/private ips

From

Jeff Janes

Date:

19 October 2022, 19:51:45

On Wed, Oct 19, 2022 at 12:29 PM Matthew Lenz <mlenz@nocturnal.org> wrote:

On Wed, Oct 19, 2022 at 10:47 AM Jeff Janes <jeff.janes@gmail.com> wrote:

No, clientcert=verify-ca forces the server to check the client's certificate. Forcing the client to check the server's certificate must be done on the client end. (And of course if you are not connecting via that line of the pg_hba, then that setting doesn't do anything.)

I didn't say the client was meant to enforce it. I meant the server should be enforcing it (it's not).

Well, if it isn't enforcing ssl in the first place, it certainly can't be enforcing clientcert. Worry about making sure your current version of pg_hba is actually in use first, then the clientcert issue should take care of itself. You still can't start debugging the one (in the unlikely event it still needs debugging) until after you fix the other.

Cheers,

Jeff

Re: proper pg_hba config to require ssl from non-local/private ips

From

jagjit singh

Date:

19 October 2022, 21:50:29

Do you have any documents to implement SSL over Postgres? i am looking for this and this thread is very helpful for me

On Wed, Oct 19, 2022 at 10:47 AM Jeff Janes <jeff.janes@gmail.com> wrote:

On Wed, Oct 19, 2022 at 8:50 AM Matthew Lenz <mlenz@nocturnal.org> wrote:
This is what I've got currently but it's still allowing non-ssl connections from remote (non-local/private) hosts. Any thoughts?

Did you reload the server configurations after changing the file? What is the address of that non-local host, as seen by the server? (you can check the first with `select * from pg_hba_file_rules`, and second with `select client_addr from pg_stat_activity where pid=pg_backend_pid();`

local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 10.0.0.0/8 md5
host all all 172.16.0.0/12 md5
hostssl all all all md5 clientcert=verify-ca

Also when I require SSL on the client it allows SSL connections without a CA signed cert which I thought clientcert=verify-ca in this pg_hba should require.

No, clientcert=verify-ca forces the server to check the client's certificate. Forcing the client to check the server's certificate must be done on the client end. (And of course if you are not connecting via that line of the pg_hba, then that setting doesn't do anything.)

Cheers,

Jeff