Thread: pg_hba.conf

pg_hba.conf

From
Dick Davies
Date:
Just needed clarification on how pg_hba.conf operates.
Does a specific host take precedence over a more general network setting?

The local socket is only accessible to a certain group, but I don't want
the overhead of SSL for loopback connections. If I connect to the server
from the local machine, the connections show up as (eg) 10.2.3.4, the NIC
ip.

I was hoping the more specific 'host' entry would take entry over the universal
'hostssl' entry, but it does'nt seem to...

I have this:

root@eris:postgresql80-server$ cat /opt/pgsql/data/pg_hba.conf
# TYPE     DATABASE    USER        IP-ADDRESS      METHOD
local      all         all                         trust
host    all         all         10.2.3.4/32   md5
hostssl    all         all      0.0.0.0/0   md5

Is there a way to say 'all IP traffic should be encrypted except one IP' that
I'm missing?

I know I could just add the local process into the dba group, but the app doesn't
reconnect if the socket goes away on a db restart, so that's not ideal...


--
'That question was less stupid; though you asked it in a profoundly stupid way.'
        -- Prof. Farnsworth
Rasputin :: Jack of All Trades - Master of Nuns

Re: pg_hba.conf

From
KÖPFERL Robert
Date:
According to the excelent doc, the _first_ matching entry will be used.

C:\> -----Original Message-----
C:\> From: Dick Davies [mailto:rasputnik@hellooperator.net]
C:\> Sent: Dienstag, 22. Februar 2005 12:57
C:\> To: PostgreSQL Admin
C:\> Subject: [ADMIN] pg_hba.conf
C:\>
C:\>
C:\>
C:\> Just needed clarification on how pg_hba.conf operates.
C:\> Does a specific host take precedence over a more general
C:\> network setting?
C:\>
C:\> The local socket is only accessible to a certain group,
C:\> but I don't want
C:\> the overhead of SSL for loopback connections. If I connect
C:\> to the server
C:\> from the local machine, the connections show up as (eg)
C:\> 10.2.3.4, the NIC
C:\> ip.
C:\>
C:\> I was hoping the more specific 'host' entry would take
C:\> entry over the universal
C:\> 'hostssl' entry, but it does'nt seem to...
C:\>
C:\> I have this:
C:\>
C:\> root@eris:postgresql80-server$ cat /opt/pgsql/data/pg_hba.conf
C:\> # TYPE     DATABASE    USER        IP-ADDRESS      METHOD
C:\> local      all         all                         trust
C:\> host    all         all         10.2.3.4/32   md5
C:\> hostssl    all         all      0.0.0.0/0   md5
C:\>
C:\> Is there a way to say 'all IP traffic should be encrypted
C:\> except one IP' that
C:\> I'm missing?
C:\>
C:\> I know I could just add the local process into the dba
C:\> group, but the app doesn't
C:\> reconnect if the socket goes away on a db restart, so
C:\> that's not ideal...
C:\>
C:\>
C:\> --
C:\> 'That question was less stupid; though you asked it in a
C:\> profoundly stupid way.'
C:\>         -- Prof. Farnsworth
C:\> Rasputin :: Jack of All Trades - Master of Nuns
C:\>
C:\> ---------------------------(end of
C:\> broadcast)---------------------------
C:\> TIP 7: don't forget to increase your free space map settings
C:\>

Re: pg_hba.conf

From
Dick Davies
Date:
* K?PFERL Robert <robert.koepferl@sonorys.at> [0228 12:28]:
> According to the excelent doc, the _first_ matching entry will be used.

If that were true, the below would work, surely?

> C:\> I have this:
> C:\>
> C:\> root@eris:postgresql80-server$ cat /opt/pgsql/data/pg_hba.conf
> C:\> # TYPE     DATABASE    USER        IP-ADDRESS      METHOD
> C:\> local      all         all                         trust
> C:\> host    all         all         10.2.3.4/32   md5
> C:\> hostssl    all         all      0.0.0.0/0   md5

--
'Interesting. No, wait, the other thing - Tedious.'
        -- Bender
Rasputin :: Jack of All Trades - Master of Nuns

Re: pg_hba.conf

From
"Donald Fraser"
Date:
If postgres has ssl enabled then it will by default negotiate to use ssl,
regardless of the host or hostssl settings in pg_hba. Your client software
needs to refuse ssl connections then it will fall back to a non-ssl
connection so long as there exists a host setting in pg_hba. The hostssl
setting in pg_hba means that it must use ssl to connect, where as the host
setting in pg_hba can mean either or, depending on your client.

What client software are you using?

Regards
Donald Fraser

----- Original Message -----
From: "Dick Davies" <rasputnik@hellooperator.net>
To: "PostgreSQL Admin" <pgsql-admin@postgresql.org>
Sent: Tuesday, February 22, 2005 1:26 PM
Subject: Re: [ADMIN] pg_hba.conf


> * K?PFERL Robert <robert.koepferl@sonorys.at> [0228 12:28]:
> > According to the excelent doc, the _first_ matching entry will be used.
>
> If that were true, the below would work, surely?
>
> > C:\> I have this:
> > C:\>
> > C:\> root@eris:postgresql80-server$ cat /opt/pgsql/data/pg_hba.conf
> > C:\> # TYPE     DATABASE    USER        IP-ADDRESS      METHOD
> > C:\> local      all         all                         trust
> > C:\> host    all         all         10.2.3.4/32   md5
> > C:\> hostssl    all         all      0.0.0.0/0   md5
>
> --
> 'Interesting. No, wait, the other thing - Tedious.'
> -- Bender
> Rasputin :: Jack of All Trades - Master of Nuns
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>


Re: pg_hba.conf

From
Dick Davies
Date:
* Donald Fraser <postgres@kiwi-fraser.net> [0257 13:57]:
> If postgres has ssl enabled then it will by default negotiate to use ssl,
> regardless of the host or hostssl settings in pg_hba. Your client software
> needs to refuse ssl connections then it will fall back to a non-ssl
> connection so long as there exists a host setting in pg_hba. The hostssl
> setting in pg_hba means that it must use ssl to connect, where as the host
> setting in pg_hba can mean either or, depending on your client.
>
> What client software are you using?

psql and ignorance :)- though it'll be ruby-postgres for the webapp.

Thanks for the explanation.


--
'This must be Thursday. I never could get the hang of Thursdays.'
        -- Arthur Dent
Rasputin :: Jack of All Trades - Master of Nuns

Re: pg_hba.conf

From
Bruno Wolff III
Date:
On Tue, Feb 22, 2005 at 11:56:41 +0000,
  Dick Davies <rasputnik@hellooperator.net> wrote:
>
> Just needed clarification on how pg_hba.conf operates.
> Does a specific host take precedence over a more general network setting?

No. The first line that matches the triple connection type, database name
and user name is the one that is used. Typically you can get what you
want by using the narrower rule first.

> The local socket is only accessible to a certain group, but I don't want
> the overhead of SSL for loopback connections. If I connect to the server
> from the local machine, the connections show up as (eg) 10.2.3.4, the NIC
> ip.
>
> I was hoping the more specific 'host' entry would take entry over the universal
> 'hostssl' entry, but it does'nt seem to...

The host entry is the one that applies. But the host entry will allow either
ssl or nonssl, so it doesn't do what you want without cooperation from the
connecting client. You can use hostnossl to match without allowing ssl.
You will also want to use a hostssl line with 'reject' authentication
to keep the later rule from matching. I am not sure if all of the normal
clients will fallback after trying ssl to not using ssl. That should be
pretty easy to test though.

>
> I have this:
>
> root@eris:postgresql80-server$ cat /opt/pgsql/data/pg_hba.conf
> # TYPE     DATABASE    USER        IP-ADDRESS      METHOD
> local      all         all                         trust
> host    all         all         10.2.3.4/32   md5
> hostssl    all         all      0.0.0.0/0   md5
>
> Is there a way to say 'all IP traffic should be encrypted except one IP' that
> I'm missing?
>
> I know I could just add the local process into the dba group, but the app doesn't
> reconnect if the socket goes away on a db restart, so that's not ideal...
>
>
> --
> 'That question was less stupid; though you asked it in a profoundly stupid way.'
>         -- Prof. Farnsworth
> Rasputin :: Jack of All Trades - Master of Nuns
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

Re: pg_hba.conf

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
> The host entry is the one that applies. But the host entry will allow either
> ssl or nonssl, so it doesn't do what you want without cooperation from the
> connecting client. You can use hostnossl to match without allowing ssl.
> You will also want to use a hostssl line with 'reject' authentication
> to keep the later rule from matching. I am not sure if all of the normal
> clients will fallback after trying ssl to not using ssl. That should be
> pretty easy to test though.

Perhaps easier would be to set "PGSSLMODE=allow" (or even "disable") in
the client environment.  This will work for libpq-based clients; there
may be something equivalent if you are using other software.

The important point here is that it's the client's choice whether to try
an SSL connection first or not, and libpq defaults to trying SSL first.
So unless you set up pg_hba.conf to actively reject SSL-based
connections, that's what you're going to get.

Also: why aren't you just using a Unix socket?  We never do SSL over
Unix sockets.

            regards, tom lane

Re: pg_hba.conf

From
Dick Davies
Date:
* Tom Lane <tgl@sss.pgh.pa.us> [0218 15:18]:

> Perhaps easier would be to set "PGSSLMODE=allow" (or even "disable") in
> the client environment.  This will work for libpq-based clients; there
> may be something equivalent if you are using other software.

Thanks Tom, I'll give that a go.

> Also: why aren't you just using a Unix socket?  We never do SSL over
> Unix sockets.

As I said, it's set to 'trust' and restricted to a local group.

Also, the deletion/rebuilding of the socket causes the application to
lose the db connection, hopefully it will be more forgiving of a server
bounce over IP.

--
'Oh, wait you're serious. Let me laugh even harder.'
        -- Bender
Rasputin :: Jack of All Trades - Master of Nuns