Re: odbc access and pg_hba.conf

Hi,
I installed the odbc driver (psqlodbc v08_03_0400) on a computer. I
configured the odbc connection and the connection test was succesfull.
That surprised me since this computer is not yet in my pg_hba.conf

Is the odbc driver supposed to "bypass" the pg_hba.conf?

I don't know much about the admin side of postgres. I don't know which
information could be useful to you.
Please ask for any information that would help

Here is the content of my pg_hba.conf
host all dba xxx.xxx.xxx.xxx 255.255.255.0 md5
host all dba yyy.yyy.yyy.yyy 255.255.254.0 md5

Thanks for your help,
steve

----- "Steve Toutant" <Steve(dot)Toutant(at)inspq(dot)qc(dot)ca> wrote:

> Hi,
> I installed the odbc driver (psqlodbc v08_03_0400) on a computer. I
> configured the odbc connection and the connection test was succesfull.
> That surprised me since this computer is not yet in my pg_hba.conf
>
> Is the odbc driver supposed to "bypass" the pg_hba.conf?
>
> I don't know much about the admin side of postgres. I don't know which
> information could be useful to you.
> Please ask for any information that would help
>
> Here is the content of my pg_hba.conf
> host all dba xxx.xxx.xxx.xxx 255.255.255.0 md5
> host all dba yyy.yyy.yyy.yyy 255.255.254.0 md5

Is this your entire pg_hba.conf? Remember the first match wins, so if there is a more permissive line above these it will take precendence.

>
>
>
>
> Thanks for your help,
> steve

Adrian Klaver
aklaver(at)comcast(dot)net

On 19/11/2009 18:25, Steve(dot)Toutant(at)inspq(dot)qc(dot)ca wrote:

> I installed the odbc driver (psqlodbc v08_03_0400) on a computer. I
> configured the odbc connection and the connection test was succesfull.
> That surprised me since this computer is not yet in my pg_hba.conf
>
> Is the odbc driver supposed to "bypass" the pg_hba.conf?

No, not at all - ODBC is client-side, while the pg_hba.conf file is
server-side.

> I don't know much about the admin side of postgres. I don't know which
> information could be useful to you.
> Please ask for any information that would help
>
> Here is the content of my pg_hba.conf
> host all dba xxx.xxx.xxx.xxx 255.255.255.0 md5
> host all dba yyy.yyy.yyy.yyy 255.255.254.0 md5

Are you sure that (a) the client machine's IP address doesn't match
these rules, and (b) there isn't another rule elsewhere in the file that
grants it access? Check also that the user as which you're connecting is
what you expect.

I know these are obvious things, but without further details it's hard
to tell what the problem is.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

the first line is host all all 127.0.0.1/32 md5

So the full hba.conf is

host all all 127.0.0.1/32 md5
host all dba xxx.xxx.xxx.xxx 255.255.255.0 md5
host all dba yyy.yyy.yyy.yyy 255.255.254.0 md5

xxx.xxx.xxx.xxx is the ip address of the server running postgres

Adrian Klaver <aklaver(at)comcast(dot)net>@postgresql.org
Envoyé par : pgsql-odbc-owner(at)postgresql(dot)org
19/11/2009 01:48 PM

A
Steve Toutant <Steve(dot)Toutant(at)inspq(dot)qc(dot)ca>
cc
pgsql-odbc(at)postgresql(dot)org
Objet
Re: [ODBC] odbc access and pg_hba.conf

----- "Steve Toutant" <Steve(dot)Toutant(at)inspq(dot)qc(dot)ca> wrote:

> Hi,
> I installed the odbc driver (psqlodbc v08_03_0400) on a computer. I
> configured the odbc connection and the connection test was succesfull.
> That surprised me since this computer is not yet in my pg_hba.conf
>
> Is the odbc driver supposed to "bypass" the pg_hba.conf?
>
> I don't know much about the admin side of postgres. I don't know which
> information could be useful to you.
> Please ask for any information that would help
>
> Here is the content of my pg_hba.conf
> host all dba xxx.xxx.xxx.xxx 255.255.255.0 md5
> host all dba yyy.yyy.yyy.yyy 255.255.254.0 md5

Is this your entire pg_hba.conf? Remember the first match wins, so if
there is a more permissive line above these it will take precendence.

>
>
>
>
> Thanks for your help,
> steve

Adrian Klaver
aklaver(at)comcast(dot)net

On Thu, Nov 19, 2009 at 10:25 AM, <Steve(dot)Toutant(at)inspq(dot)qc(dot)ca> wrote:

> Here is the content of my pg_hba.conf
> host        all        dba        xxx.xxx.xxx.xxx 255.255.255.0        md5
> host        all        dba        yyy.yyy.yyy.yyy 255.255.254.0        md5

There is one point that may or may not be the cause of your problem.
The pg_hba.conf file can be altered any way you want. But the changes
don't take affect until the PostgreSQL server reload the config files.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

I didn't modify the hba.conf yet.
The user has access without his IP address in it.

thanks!

Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
19/11/2009 01:54 PM

A
Steve(dot)Toutant(at)inspq(dot)qc(dot)ca
cc
pgsql-odbc(at)postgresql(dot)org
Objet
Re: [ODBC] odbc access and pg_hba.conf

On Thu, Nov 19, 2009 at 10:25 AM, <Steve(dot)Toutant(at)inspq(dot)qc(dot)ca> wrote:

> Here is the content of my pg_hba.conf
> host all dba xxx.xxx.xxx.xxx 255.255.255.0
md5
> host all dba yyy.yyy.yyy.yyy 255.255.254.0
md5

There is one point that may or may not be the cause of your problem.
The pg_hba.conf file can be altered any way you want. But the changes
don't take affect until the PostgreSQL server reload the config files.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

----- "Steve Toutant" <Steve(dot)Toutant(at)inspq(dot)qc(dot)ca> wrote:

> the first line is host all all 127.0.0.1/32 md5
>
> So the full hba.conf is
>
> host all all 127.0.0.1/32 md5
> host all dba xxx.xxx.xxx.xxx 255.255.255.0 md5
> host all dba yyy.yyy.yyy.yyy 255.255.254.0 md5
>
>
>
> xxx.xxx.xxx.xxx is the ip address of the server running postgres
>
>
>
>

> >
> > Thanks for your help,
> > steve
>
>

In addition to what Richard said, what is the network that the client is running on?

Adrian Klaver
aklaver(at)comcast(dot)net

On 19/11/2009 18:51, Steve(dot)Toutant(at)inspq(dot)qc(dot)ca wrote:
> the first line is host all all 127.0.0.1/32 md5
>
> So the full hba.conf is
>
> host all all 127.0.0.1/32 md5
> host all dba xxx.xxx.xxx.xxx 255.255.255.0 md5
> host all dba yyy.yyy.yyy.yyy 255.255.254.0 md5
>
>
> xxx.xxx.xxx.xxx is the ip address of the server running postgres

And what's yyy.yyy.yyy.yyy?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

ok, done with thesse xxx, yyy I'm on a private network so here is the real
content of hba.conf
host all all 127.0.0.1/32 md5
host all dbaInspq 10.2.192.247 255.255.255.0 md5
host all dbaInspq 10.2.195.37 255.255.254.0 md5

10.2.192.247 is the server running postgres/postgis
10.2.195.37 is my computer

Also, please find attached the screenshot of the odbc config of the
client.

In this ODBC config of the client, if I change the User Name to test
instead of dbaInspq, I get this error
FATAL: no pg_hba.conf entry for host "10.2.195.53", user "test", database
"geo", SSL off

10.2.195.53 is the ipaddress of the client, and it is not in pg_hba.conf

The client has winXP and he's using the ODBC driver to access postgres
with SAS.
Another information, the database geo is a postgis database.

thanks for helping me!
Steve

Adrian Klaver <aklaver(at)comcast(dot)net>
19/11/2009 02:00 PM

A
Steve Toutant <Steve(dot)Toutant(at)inspq(dot)qc(dot)ca>
cc
pgsql-odbc(at)postgresql(dot)org, pgsql-odbc-owner(at)postgresql(dot)org
Objet
Re: [ODBC] odbc access and pg_hba.conf

----- "Steve Toutant" <Steve(dot)Toutant(at)inspq(dot)qc(dot)ca> wrote:

> the first line is host all all 127.0.0.1/32 md5
>
> So the full hba.conf is
>
> host all all 127.0.0.1/32 md5
> host all dba xxx.xxx.xxx.xxx 255.255.255.0 md5
> host all dba yyy.yyy.yyy.yyy 255.255.254.0 md5
>
>
>
> xxx.xxx.xxx.xxx is the ip address of the server running postgres
>
>
>
>

> >
> > Thanks for your help,
> > steve
>
>

In addition to what Richard said, what is the network that the client is
running on?

Adrian Klaver
aklaver(at)comcast(dot)net

I I remove this line the client doesn't have access.
host all dbaInspq 10.2.195.37 255.255.254.0 md5

But I don't understand why. Maybe because of 255.255.254.0

I probably misunderstood the option I should put in this hba.conf

what do you think?
thanks
steve

Steve Toutant, M. Sc.
Analyste en géomatique
Secteur environnement
Direction de la santé environnementale et de la toxicologie
Institut national de santé publique du Québec
945, avenue Wolfe
Québec, Qc G1V 5B3
Tél.: (418) 650-5115 #5281
Fax.: (418) 654-3144
steve(dot)toutant(at)inspq(dot)qc(dot)ca
http://www.inspq.qc.ca

Adrian Klaver <aklaver(at)comcast(dot)net>
19/11/2009 02:00 PM

A
Steve Toutant <Steve(dot)Toutant(at)inspq(dot)qc(dot)ca>
cc
pgsql-odbc(at)postgresql(dot)org, pgsql-odbc-owner(at)postgresql(dot)org
Objet
Re: [ODBC] odbc access and pg_hba.conf

----- "Steve Toutant" <Steve(dot)Toutant(at)inspq(dot)qc(dot)ca> wrote:

> the first line is host all all 127.0.0.1/32 md5
>
> So the full hba.conf is
>
> host all all 127.0.0.1/32 md5
> host all dba xxx.xxx.xxx.xxx 255.255.255.0 md5
> host all dba yyy.yyy.yyy.yyy 255.255.254.0 md5
>
>
>
> xxx.xxx.xxx.xxx is the ip address of the server running postgres
>
>
>
>

> >
> > Thanks for your help,
> > steve
>
>

In addition to what Richard said, what is the network that the client is
running on?

Adrian Klaver
aklaver(at)comcast(dot)net

On 19/11/2009 19:26, Steve(dot)Toutant(at)inspq(dot)qc(dot)ca wrote:
> ok, done with thesse xxx, yyy I'm on a private network so here is the real
> content of hba.conf

That's more like it! :-) The more detail you can give, the easier it is
for people to help you.

> host all all 127.0.0.1/32 md5
> host all dbaInspq 10.2.192.247 255.255.255.0 md5
> host all dbaInspq 10.2.195.37 255.255.254.0 md5
>
> 10.2.192.247 is the server running postgres/postgis
> 10.2.195.37 is my computer

I'm no whizz at working out IP addresses and masks, but if all these
machines are on the same network then they should be sharing the same
network mark. You've got two different ones there - is that correct?

Only a wild guess, but this *might* be the root of your trouble.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

On 19/11/2009 19:37, Raymond O'Donnell wrote:

> network mark. You've got two different ones there - is that correct?

Whoops - s/mark/mask/

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

Setting this line
host all dbaInspq 10.2.195.37/32 md5
Only allow this computer to access the DB and Block my other client
If I add this line
host all dbaInspq 10.2.195.53/32 md5

It works
So the problem was using a netmask, that specify all host with the IP
10.2.195.*

So, ODBC works! My config didn't.

Thanks for your help,
steve

Raymond O'Donnell <rod(at)iol(dot)ie>
19/11/2009 02:37 PM
Veuillez répondre à
rod(at)iol(dot)ie

A
Steve(dot)Toutant(at)inspq(dot)qc(dot)ca
cc
Adrian Klaver <aklaver(at)comcast(dot)net>, pgsql-odbc(at)postgresql(dot)org,
pgsql-odbc-owner(at)postgresql(dot)org
Objet
Re: [ODBC] odbc access and pg_hba.conf

On 19/11/2009 19:26, Steve(dot)Toutant(at)inspq(dot)qc(dot)ca wrote:
> ok, done with thesse xxx, yyy I'm on a private network so here is the
real
> content of hba.conf

That's more like it! :-) The more detail you can give, the easier it is
for people to help you.

> host all all 127.0.0.1/32 md5
> host all dbaInspq 10.2.192.247 255.255.255.0 md5
> host all dbaInspq 10.2.195.37 255.255.254.0 md5
>
> 10.2.192.247 is the server running postgres/postgis
> 10.2.195.37 is my computer

I'm no whizz at working out IP addresses and masks, but if all these
machines are on the same network then they should be sharing the same
network mark. You've got two different ones there - is that correct?

Only a wild guess, but this *might* be the root of your trouble.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie