Re: pg_hba.conf

Ran into a mystery that I can't seem to figure out....

I want to authenticate using SSL for all external IP addresses that I have in my
subnet. I also want to be able to authenticate via non-SSL for localhost (not
unix socket).

I thought something like this would work:

host all all 127.0.0.1/32 md5
hostssl all all 192.168.0.1/24 md5

But I have a localhost client that can't log in because it keeps trying to
authenticate via SSL.

What am I doing wrong? It seems simple enough.

Tom Allison wrote:
> Ran into a mystery that I can't seem to figure out....
>
>
> I want to authenticate using SSL for all external IP addresses that I
> have in my subnet. I also want to be able to authenticate via non-SSL
> for localhost (not unix socket).
>
> I thought something like this would work:
>
> host all all 127.0.0.1/32 md5
> hostssl all all 192.168.0.1/24 md5
>
> But I have a localhost client that can't log in because it keeps
> trying to authenticate via SSL.
>
> What am I doing wrong? It seems simple enough.
What command are you typing?

#nonssl
postgres$ psql -h localhost postgres
#ssl
postgres$ psql -h 192.168.1.1 postgres

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

Tom Allison <tom(at)tacocat(dot)net> writes:
> host all all 127.0.0.1/32 md5
> hostssl all all 192.168.0.1/24 md5
^^^^^^^^^^^^^^

That needs to be 192.168.0.0/24 ... as is, it won't match anything.

> But I have a localhost client that can't log in because it keeps trying to
> authenticate via SSL.

That seems unrelated --- your first line should match any local-loopback
connection, regardless of SSL or not.

regards, tom lane

Tom Lane wrote:
> Tom Allison <tom(at)tacocat(dot)net> writes:
>> host all all 127.0.0.1/32 md5
>> hostssl all all 192.168.0.1/24 md5
> ^^^^^^^^^^^^^^
>
> That needs to be 192.168.0.0/24 ... as is, it won't match anything.
>
>> But I have a localhost client that can't log in because it keeps trying to
>> authenticate via SSL.
>

Sorry, I mixed it up.

Copying from the pg_hba.conf:

# Database administrative login by UNIX sockets
local all postgres ident sameuser

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host dbmail all 127.0.0.1/32 md5
host all all 192.168.1.0/24 md5
host all all 192.168.0.0/24 md5
# IPv6 local connections:
host all all ::1/128 md5

I would like to be able to set change the lines maching 192.168...
to

hostssl all all 192.168....

and set ssl=true in postgres.conf

But when I do, the localhost connections try to do ssl first and then fail.

Setting
hostnossl dbmail all 127.0.0.1/32 md5

didn't seem to help but I might have missed something at the time.

Russell Smith wrote:
> Tom Allison wrote:
>> Ran into a mystery that I can't seem to figure out....
>>
>>
>> I want to authenticate using SSL for all external IP addresses that I
>> have in my subnet. I also want to be able to authenticate via non-SSL
>> for localhost (not unix socket).
>>
>> I thought something like this would work:
>>
>> host all all 127.0.0.1/32 md5
>> hostssl all all 192.168.0.1/24 md5
>>
>> But I have a localhost client that can't log in because it keeps
>> trying to authenticate via SSL.
>>
>> What am I doing wrong? It seems simple enough.
> What command are you typing?
>
> #nonssl
> postgres$ psql -h localhost postgres
> #ssl
> postgres$ psql -h 192.168.1.1 postgres
>

psql -h localhost

My "other" client is actually postfix and that's also specified as 'localhost'.

I suppose you are going to tell me that there is a difference here?
I've always assumed you had to use network IP ranges, not DNS like names (albeit
localhost is a special case).

Tom Allison wrote:
> Russell Smith wrote:
>> Tom Allison wrote:
>>> Ran into a mystery that I can't seem to figure out....
>>>
>>>
>>> I want to authenticate using SSL for all external IP addresses that
>>> I have in my subnet. I also want to be able to authenticate via
>>> non-SSL for localhost (not unix socket).
>>>
>>> I thought something like this would work:
>>>
>>> host all all 127.0.0.1/32 md5
>>> hostssl all all 192.168.0.1/24 md5
>>>
>>> But I have a localhost client that can't log in because it keeps
>>> trying to authenticate via SSL.
>>>
>>> What am I doing wrong? It seems simple enough.
>> What command are you typing?
>>
>> #nonssl
>> postgres$ psql -h localhost postgres
>> #ssl
>> postgres$ psql -h 192.168.1.1 postgres
>>
>
> psql -h localhost
>
> My "other" client is actually postfix and that's also specified as
> 'localhost'.
>
> I suppose you are going to tell me that there is a difference here?
> I've always assumed you had to use network IP ranges, not DNS like
> names (albeit localhost is a special case).
All good, it makes no difference.

try
hostnossl all all 127.0.0.1/32 md5

that should force non ssl for localhost connections, as long as there
are no entries before this one for localhost.

Hope that helps.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>

On Mon, 20 Nov 2006, Russell Smith wrote:

> Tom Allison wrote:
>> Russell Smith wrote:
>>> Tom Allison wrote:
>>>> Ran into a mystery that I can't seem to figure out....
>>>>
>>>>
>>>> I want to authenticate using SSL for all external IP addresses that I
>>>> have in my subnet. I also want to be able to authenticate via non-SSL
>>>> for localhost (not unix socket).
>>>>
>>>> I thought something like this would work:
>>>>
>>>> host all all 127.0.0.1/32 md5
>>>> hostssl all all 192.168.0.1/24 md5
>>>>
>>>> But I have a localhost client that can't log in because it keeps trying
>>>> to authenticate via SSL.
>>>>
>>>> What am I doing wrong? It seems simple enough.
>>> What command are you typing?
>>>
>>> #nonssl
>>> postgres$ psql -h localhost postgres
>>> #ssl
>>> postgres$ psql -h 192.168.1.1 postgres
>>>
>>
>> psql -h localhost
>>
>> My "other" client is actually postfix and that's also specified as
>> 'localhost'.
>>
>> I suppose you are going to tell me that there is a difference here?
>> I've always assumed you had to use network IP ranges, not DNS like names
>> (albeit localhost is a special case).
> All good, it makes no difference.
>
> try
> hostnossl all all 127.0.0.1/32 md5
>
> that should force non ssl for localhost connections, as long as there are no
> entries before this one for localhost.
>
> Hope that helps.

That is not necessarily true. Some OSes are now defaulting "localhost" to
::1, e.g. the IPv6 variant. Be certain that if you are in one of those
situations that you include the IPv6 address in you configuration, or take
whatever measures are necessary to insure consistency.

- Marc