Обсуждение: first time odbc
Before learning there was an odbc list, I sent this post to the admin list.
I am running postgres on a red hat linux server.
postgresql-python-8.1.23-1.el5_6.1
postgresql-test-8.1.23-1.el5_6.1
postgresql-libs-8.1.23-1.el5_6.1
postgresql-docs-8.1.23-1.el5_6.1
postgresql-contrib-8.1.23-1.el5_6.1
postgresql-8.1.23-1.el5_6.1
postgresql-pl-8.1.23-1.el5_6.1
postgresql-odbc-08.01.0200-3.1
postgresql-jdbc-8.1.407-1jpp.4
postgresql-server-8.1.23-1.el5_6.1
postgresql-tcl-8.1.23-1.el5_6.1
User on windows computers would like to odbc to the postgres databases.
I downloaded the windows postgres odbc drivers from the following site and installed version “psqlodbc-08_01_0200” on the windows computer.
http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/
When I configure a “File Data Source” connection in access, I cannot connect to the postgresql database. I get the message, “A connection could not be made using the file data source parameters entered. Save non-verified file DSN?”
The parameters entered are:
Database: database_name
Server: I entered the IP address of the server
User Name: I entered postgres
Password:
SSL Mode: prefer
Port: 5432 (which is the port used by postgres)
Do I need to configure the /etc/odbc.ini file? It is currently just a blank file.
Thanks for any help.
Marc
<p><font color="#000000"><font size="2"><font face="arial,sans-serif"></font></font></font> <p><font color="#000000"><fontsize="2"><font face="arial,sans-serif">Why, </font></font></font><p><font color="#000000"><font size="2"><fontface="arial,sans-serif"></font></font></font> <p><font color="#000000"><font size="2"><font face="arial,sans-serif"> That's a vintage 02-Feb-2006 driver.</font></font></font><p><font color="#000000"><font size="2"><fontface="arial,sans-serif"></font></font></font> <p><font color="#000000"><font size="2"><font face="arial,sans-serif"> I use 8.04 from Dec-2009 and even that's getting a little stale. Why not try the 9.n drivers.</font></font></font><p><fontcolor="#000000"><font size="2"><font face="arial,sans-serif"></font></font></font> <p><fontcolor="#000000"><font size="2"><font face="arial,sans-serif"> Couldthe port(s) be blocked / restricted between you and the server and is PostgreSQL SSL supportenabled -- </font></font></font><p><font color="#000000"><font size="2"><font face="arial,sans-serif"> <a href="http://www.postgresql.org/docs/9.0/static/ssl-tcp.html">http://www.postgresql.org/docs/9.0/static/ssl-tcp.html</a></font></font></font><p> <p> JMS<p><font color="#000000"><fontsize="2"><font face="arial,sans-serif"></font></font></font> <p><font color="#000000"><font size="2"><fontface="arial,sans-serif"> -----------------------------------</font></font></font><p><br /><blockquote style="BORDER-LEFT:#0000ff 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 0px">-----Original Message----- <br />From: Marc Fromm<br />Sent: Mar 29, 2011 1:58 PM <br />To: "pgsql-odbc@postgresql.org" <br />Subject: [ODBC] first time odbc <br /><br/><style><ZZZ!-- /* Font Definitions */ @font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";} a:ZZZlink, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;} span.EmailStyle17{mso-style-type:personal-compose;font-family:"Calibri","sans-serif";color:windowtext;} .MsoChpDefault{mso-style-type:export-only;font-family:"Calibri","sans-serif";} @page WordSection1{size:8.5in 11.0in;margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1{page:WordSection1;} --></style><div class="WordSection1"><p class="MsoNormal">Before learning there was an odbc list, I sent this post to theadmin list.<p class="MsoNormal"> <p class="MsoNormal">I am running postgres on a red hat linux server.<p class="MsoNormal">postgresql-python-8.1.23-1.el5_6.1<pclass="MsoNormal">postgresql-test-8.1.23-1.el5_6.1<p class="MsoNormal">postgresql-libs-8.1.23-1.el5_6.1<pclass="MsoNormal">postgresql-docs-8.1.23-1.el5_6.1<p class="MsoNormal">postgresql-contrib-8.1.23-1.el5_6.1<pclass="MsoNormal">postgresql-8.1.23-1.el5_6.1<p class="MsoNormal">postgresql-pl-8.1.23-1.el5_6.1<pclass="MsoNormal"><b>postgresql-odbc-08.01.0200-3.1</b><p class="MsoNormal">postgresql-jdbc-8.1.407-1jpp.4<pclass="MsoNormal">postgresql-server-8.1.23-1.el5_6.1<p class="MsoNormal">postgresql-tcl-8.1.23-1.el5_6.1<pclass="MsoNormal"> <p class="MsoNormal">User on windows computers wouldlike to odbc to the postgres databases.<p class="MsoNormal">I downloaded the windows postgres odbc drivers from thefollowing site and installed version “psqlodbc-08_01_0200” on the windows computer.<p class="MsoNormal"><a href="http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/" target="_blank">http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/</a><pclass="MsoNormal"> <p class="MsoNormal">WhenI configure a “File Data Source” connection in access, I cannot connect to the postgresql database.I get the message, “A connection could not be made using the file data source parameters entered. Save non-verifiedfile DSN?”<p class="MsoNormal">The parameters entered are:<p class="MsoNormal">Database: database_name<p class="MsoNormal">Server:I entered the IP address of the server<p class="MsoNormal">User Name: I entered postgres<p class="MsoNormal">Password:<pclass="MsoNormal">SSL Mode: prefer<p class="MsoNormal">Port: 5432 (which is the port used bypostgres)<p class="MsoNormal"> <p class="MsoNormal">Do I need to configure the /etc/odbc.ini file? It is currently justa blank file.<p class="MsoNormal"> <p class="MsoNormal">Thanks for any help.<p class="MsoNormal"> <p class="MsoNormal"><spanstyle="FONT-FAMILY: 'Courier New'; FONT-SIZE: 10pt">Marc</span></div></blockquote>
First step: Upgrade your Postgres ODBC driver. Get at least 8.04. We’ve got a customer using 8.01, and we’ve run into significant problems.
Second step: Try a User DSN or System DSN.
Good luck!
RobR
I updated the windows postgresql odbc drivers and now I get the message:
No connection could be made because the target machine actively refused it.
This sounds like something needs to be configured on the server to allow the odbc connection.
From: jsilberberg@mindspring.com [mailto:jsilberberg@mindspring.com]
Sent: Tuesday, March 29, 2011 11:13 AM
To: Marc Fromm; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] first time odbc
Why,
That's a vintage 02-Feb-2006 driver.
I use 8.04 from Dec-2009 and even that's getting a little stale. Why not try the 9.n drivers.
Could the port(s) be blocked / restricted between you and the server and is PostgreSQL SSL support enabled --
http://www.postgresql.org/docs/9.0/static/ssl-tcp.html
JMS
-----------------------------------
-----Original Message-----
From: Marc Fromm
Sent: Mar 29, 2011 1:58 PM
To: "pgsql-odbc@postgresql.org"
Subject: [ODBC] first time odbcBefore learning there was an odbc list, I sent this post to the admin list.
I am running postgres on a red hat linux server.
postgresql-python-8.1.23-1.el5_6.1
postgresql-test-8.1.23-1.el5_6.1
postgresql-libs-8.1.23-1.el5_6.1
postgresql-docs-8.1.23-1.el5_6.1
postgresql-contrib-8.1.23-1.el5_6.1
postgresql-8.1.23-1.el5_6.1
postgresql-pl-8.1.23-1.el5_6.1
postgresql-odbc-08.01.0200-3.1
postgresql-jdbc-8.1.407-1jpp.4
postgresql-server-8.1.23-1.el5_6.1
postgresql-tcl-8.1.23-1.el5_6.1
User on windows computers would like to odbc to the postgres databases.
I downloaded the windows postgres odbc drivers from the following site and installed version “psqlodbc-08_01_0200” on the windows computer.
http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/
When I configure a “File Data Source” connection in access, I cannot connect to the postgresql database. I get the message, “A connection could not be made using the file data source parameters entered. Save non-verified file DSN?”
The parameters entered are:
Database: database_name
Server: I entered the IP address of the server
User Name: I entered postgres
Password:
SSL Mode: prefer
Port: 5432 (which is the port used by postgres)
Do I need to configure the /etc/odbc.ini file? It is currently just a blank file.
Thanks for any help.
Marc
<p><font color="#000000"><font size="2"><font face="arial,sans-serif"></font></font></font> <p><font color="#000000"><fontsize="2"><font face="arial,sans-serif"><a href="http://www.postgresql.org/docs/9.0/static/auth-pg-hba-conf.html">http://www.postgresql.org/docs/9.0/static/auth-pg-hba-conf.html</a></font></font></font><p><font color="#000000"><fontsize="2"><font face="arial,sans-serif"> </font></font></font><p><br /><blockquote style="BORDER-LEFT:#0000ff 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 0px">-----Original Message----- <br />From: Marc Fromm<br />Sent: Mar 29, 2011 3:01 PM <br />To: "jsilberberg@mindspring.com" <br />Cc: "pgsql-odbc@postgresql.org" <br />Subject:RE: [ODBC] first time odbc <br /><br /><style><ZZZ!-- /* Font Definitions */ @font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";} a:ZZZlink, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;} p{mso-style-priority:99;margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New Roman","serif";} span.EmailStyle18{mso-style-type:personal;font-family:"Calibri","sans-serif";color:windowtext;} span.EmailStyle19{mso-style-type:personal-reply;font-family:"Calibri","sans-serif";color:#1F497D;} .MsoChpDefault{mso-style-type:export-only;font-size:10.0pt;} @page WordSection1{size:8.5in 11.0in;margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1{page:WordSection1;} --></style><div class="WordSection1"><p class="MsoNormal"><span style="COLOR: #1f497d">I updated the windows postgresql odbcdrivers and now I get the message:</span><p class="MsoNormal"><span style="COLOR: #1f497d">No connection could be madebecause the target machine actively refused it.</span><p class="MsoNormal"><span style="COLOR: #1f497d"> </span><p class="MsoNormal"><spanstyle="COLOR: #1f497d">This sounds like something needs to be configured on the server to allow theodbc connection.</span><p class="MsoNormal"><span style="COLOR: #1f497d"> </span><div><div style="BORDER-BOTTOM: mediumnone; BORDER-LEFT: medium none; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BORDER-TOP: #b5c4df 1ptsolid; BORDER-RIGHT: medium none; PADDING-TOP: 3pt"><p class="MsoNormal"><b><span style="FONT-FAMILY: 'Tahoma','sans-serif';FONT-SIZE: 10pt">From:</span></b><span style="FONT-FAMILY: 'Tahoma','sans-serif'; FONT-SIZE: 10pt">jsilberberg@mindspring.com [mailto:jsilberberg@mindspring.com] <br /><b>Sent:</b> Tuesday, March 29, 2011 11:13 AM<br/><b>To:</b> Marc Fromm; pgsql-odbc@postgresql.org<br /><b>Subject:</b> Re: [ODBC] first time odbc</span></div></div><pclass="MsoNormal"> <p><span style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 10pt"> </span><p><spanstyle="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 10pt">Why, </span><p><span style="FONT-FAMILY:'Arial','sans-serif'; COLOR: black; FONT-SIZE: 10pt"> </span><p><span style="FONT-FAMILY: 'Arial','sans-serif';COLOR: black; FONT-SIZE: 10pt"> That's a vintage 02-Feb-2006 driver.</span><p><span style="FONT-FAMILY:'Arial','sans-serif'; COLOR: black; FONT-SIZE: 10pt"> </span><p><span style="FONT-FAMILY: 'Arial','sans-serif';COLOR: black; FONT-SIZE: 10pt"> I use 8.04 from Dec-2009 and even that's getting a little stale. Why not try the 9.n drivers.</span><p><span style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 10pt"> </span><p><spanstyle="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 10pt"> Could the port(s) beblocked / restricted between you and the server and is PostgreSQL SSL support enabled -- </span><p><span style="FONT-FAMILY:'Arial','sans-serif'; COLOR: black; FONT-SIZE: 10pt"> <a href="http://www.postgresql.org/docs/9.0/static/ssl-tcp.html" target="_blank">http://www.postgresql.org/docs/9.0/static/ssl-tcp.html</a></span><p><spanstyle="FONT-FAMILY: 'Arial','sans-serif';COLOR: black; FONT-SIZE: 10pt"> </span><p><span style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black;FONT-SIZE: 10pt"> JMS</span><p><span style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 10pt"> </span><p><spanstyle="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE: 10pt"> -----------------------------------</span><p><spanstyle="FONT-FAMILY: 'Arial','sans-serif'; COLOR: black; FONT-SIZE:10pt"> </span><blockquote style="BORDER-BOTTOM: medium none; BORDER-LEFT: blue 1.5pt solid; PADDING-BOTTOM: 0in;MARGIN-TOP: 5pt; PADDING-LEFT: 4pt; PADDING-RIGHT: 0in; MARGIN-BOTTOM: 5pt; MARGIN-LEFT: 0in; BORDER-TOP: medium none;BORDER-RIGHT: medium none; PADDING-TOP: 0in"><p class="MsoNormal"><span style="FONT-FAMILY: 'Arial','sans-serif'; COLOR:black; FONT-SIZE: 10pt">-----Original Message----- <br />From: Marc Fromm <br />Sent: Mar 29, 2011 1:58 PM <br />To:"<a href="mailto:pgsql-odbc@postgresql.org" target="_blank">pgsql-odbc@postgresql.org</a>" <br />Subject: [ODBC] firsttime odbc <br /><br /><br /></span><p class="MsoNormal"><span style="COLOR: black">Before learning there was an odbclist, I sent this post to the admin list.</span><p class="MsoNormal"><span style="COLOR: black"> </span><p class="MsoNormal"><spanstyle="COLOR: black">I am running postgres on a red hat linux server.</span><p class="MsoNormal"><spanstyle="COLOR: black">postgresql-python-8.1.23-1.el5_6.1</span><p class="MsoNormal"><span style="COLOR:black">postgresql-test-8.1.23-1.el5_6.1</span><p class="MsoNormal"><span style="COLOR: black">postgresql-libs-8.1.23-1.el5_6.1</span><pclass="MsoNormal"><span style="COLOR: black">postgresql-docs-8.1.23-1.el5_6.1</span><pclass="MsoNormal"><span style="COLOR: black">postgresql-contrib-8.1.23-1.el5_6.1</span><pclass="MsoNormal"><span style="COLOR: black">postgresql-8.1.23-1.el5_6.1</span><pclass="MsoNormal"><span style="COLOR: black">postgresql-pl-8.1.23-1.el5_6.1</span><pclass="MsoNormal"><b><span style="COLOR: black">postgresql-odbc-08.01.0200-3.1</span></b><spanstyle="COLOR: black"></span><p class="MsoNormal"><span style="COLOR:black">postgresql-jdbc-8.1.407-1jpp.4</span><p class="MsoNormal"><span style="COLOR: black">postgresql-server-8.1.23-1.el5_6.1</span><pclass="MsoNormal"><span style="COLOR: black">postgresql-tcl-8.1.23-1.el5_6.1</span><pclass="MsoNormal"><span style="COLOR: black"> </span><p class="MsoNormal"><spanstyle="COLOR: black">User on windows computers would like to odbc to the postgres databases.</span><pclass="MsoNormal"><span style="COLOR: black">I downloaded the windows postgres odbc drivers from the followingsite and installed version “psqlodbc-08_01_0200” on the windows computer.</span><p class="MsoNormal"><span style="COLOR:black"><a href="http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/" target="_blank">http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/</a></span><p class="MsoNormal"><spanstyle="COLOR: black"> </span><p class="MsoNormal"><span style="COLOR: black">When I configure a “FileData Source” connection in access, I cannot connect to the postgresql database. I get the message, “A connection couldnot be made using the file data source parameters entered. Save non-verified file DSN?”</span><p class="MsoNormal"><spanstyle="COLOR: black">The parameters entered are:</span><p class="MsoNormal"><span style="COLOR: black">Database:database_name</span><p class="MsoNormal"><span style="COLOR: black">Server: I entered the IP address of theserver</span><p class="MsoNormal"><span style="COLOR: black">User Name: I entered postgres</span><p class="MsoNormal"><spanstyle="COLOR: black">Password:</span><p class="MsoNormal"><span style="COLOR: black">SSL Mode: prefer</span><pclass="MsoNormal"><span style="COLOR: black">Port: 5432 (which is the port used by postgres)</span><p class="MsoNormal"><spanstyle="COLOR: black"> </span><p class="MsoNormal"><span style="COLOR: black">Do I need to configurethe /etc/odbc.ini file? It is currently just a blank file.</span><p class="MsoNormal"><span style="COLOR: black"> </span><pclass="MsoNormal"><span style="COLOR: black">Thanks for any help.</span><p class="MsoNormal"><span style="COLOR:black"> </span><p class="MsoNormal"><span style="FONT-FAMILY: 'Courier New'; COLOR: black; FONT-SIZE: 10pt">Marc</span><spanstyle="COLOR: black"></span></blockquote></div></blockquote>
On 29/03/2011 20:27, jsilberberg@mindspring.com wrote: > http://www.postgresql.org/docs/9.0/static/auth-pg-hba-conf.html > I think you get a different message if the problem is in pg_hba.conf - IIRC there's a specific reference to that file in the message. This sounds to me more like a firewall problem. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Hello, most likely you'll have to set listen_addresses in postgresql.conf correctly as well ( see link ). Don't forget to remove the # ;) Good luck, Peter Am 29.03.2011 21:27, schrieb jsilberberg@mindspring.com: > > > http://www.postgresql.org/docs/9.0/static/auth-pg-hba-conf.html > > > > > -----Original Message----- > From: Marc Fromm > Sent: Mar 29, 2011 3:01 PM > To: "jsilberberg@mindspring.com" > Cc: "pgsql-odbc@postgresql.org" > Subject: RE: [ODBC] first time odbc > > I updated the windows postgresql odbc drivers and now I get the message: > > No connection could be made because the target machine actively > refused it. > > > > This sounds like something needs to be configured on the server to > allow the odbc connection. > > > > *From:* jsilberberg@mindspring.com [mailto:jsilberberg@mindspring.com] > *Sent:* Tuesday, March 29, 2011 11:13 AM > *To:* Marc Fromm; pgsql-odbc@postgresql.org > *Subject:* Re: [ODBC] first time odbc > > > > > > Why, > > > > That's a vintage 02-Feb-2006 driver. > > > > I use 8.04 from Dec-2009 and even that's getting a little > stale. Why not try the 9.n drivers. > > > > Could the port(s) be blocked / restricted between you and the > server and is PostgreSQL SSL support enabled -- > > http://www.postgresql.org/docs/9.0/static/ssl-tcp.html > > > > JMS > > > > ----------------------------------- > > > > -----Original Message----- > From: Marc Fromm > Sent: Mar 29, 2011 1:58 PM > To: "pgsql-odbc@postgresql.org <mailto:pgsql-odbc@postgresql.org>" > Subject: [ODBC] first time odbc > > > Before learning there was an odbc list, I sent this post to the > admin list. > > > > I am running postgres on a red hat linux server. > > postgresql-python-8.1.23-1.el5_6.1 > > postgresql-test-8.1.23-1.el5_6.1 > > postgresql-libs-8.1.23-1.el5_6.1 > > postgresql-docs-8.1.23-1.el5_6.1 > > postgresql-contrib-8.1.23-1.el5_6.1 > > postgresql-8.1.23-1.el5_6.1 > > postgresql-pl-8.1.23-1.el5_6.1 > > *postgresql-odbc-08.01.0200-3.1* > > postgresql-jdbc-8.1.407-1jpp.4 > > postgresql-server-8.1.23-1.el5_6.1 > > postgresql-tcl-8.1.23-1.el5_6.1 > > > > User on windows computers would like to odbc to the postgres > databases. > > I downloaded the windows postgres odbc drivers from the > following site and installed version “psqlodbc-08_01_0200” on > the windows computer. > > http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/ > > > > When I configure a “File Data Source” connection in access, I > cannot connect to the postgresql database. I get the message, “A > connection could not be made using the file data source > parameters entered. Save non-verified file DSN?” > > The parameters entered are: > > Database: database_name > > Server: I entered the IP address of the server > > User Name: I entered postgres > > Password: > > SSL Mode: prefer > > Port: 5432 (which is the port used by postgres) > > > > Do I need to configure the /etc/odbc.ini file? It is currently > just a blank file. > > > > Thanks for any help. > > > > Marc >
By editing postgresql.conf and changing listen_address = ‘*’ and editing pg_hba.conf and adding host all all my.ip.address allowed me to connect to the postgresql data base with Microsoft access.
In access I can see the table and it fields, but if I try to use the table in a querry or view the table I get an error: permission denied for relation summer_finaid; error while executing the query (#7)
Any ideas?
Also in the postgresql instead of listen_address=’*’ I tried listen_address=’localhost, my.ip.address’ but that failed to work.
How can I keep local host and only allow specific address? I don’t want to open it up to all with ‘*’
Thanks
From: jsilberberg@mindspring.com [mailto:jsilberberg@mindspring.com]
Sent: Tuesday, March 29, 2011 11:13 AM
To: Marc Fromm; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] first time odbc
Why,
That's a vintage 02-Feb-2006 driver.
I use 8.04 from Dec-2009 and even that's getting a little stale. Why not try the 9.n drivers.
Could the port(s) be blocked / restricted between you and the server and is PostgreSQL SSL support enabled --
http://www.postgresql.org/docs/9.0/static/ssl-tcp.html
JMS
-----------------------------------
-----Original Message-----
From: Marc Fromm
Sent: Mar 29, 2011 1:58 PM
To: "pgsql-odbc@postgresql.org"
Subject: [ODBC] first time odbcBefore learning there was an odbc list, I sent this post to the admin list.
I am running postgres on a red hat linux server.
postgresql-python-8.1.23-1.el5_6.1
postgresql-test-8.1.23-1.el5_6.1
postgresql-libs-8.1.23-1.el5_6.1
postgresql-docs-8.1.23-1.el5_6.1
postgresql-contrib-8.1.23-1.el5_6.1
postgresql-8.1.23-1.el5_6.1
postgresql-pl-8.1.23-1.el5_6.1
postgresql-odbc-08.01.0200-3.1
postgresql-jdbc-8.1.407-1jpp.4
postgresql-server-8.1.23-1.el5_6.1
postgresql-tcl-8.1.23-1.el5_6.1
User on windows computers would like to odbc to the postgres databases.
I downloaded the windows postgres odbc drivers from the following site and installed version “psqlodbc-08_01_0200” on the windows computer.
http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/
When I configure a “File Data Source” connection in access, I cannot connect to the postgresql database. I get the message, “A connection could not be made using the file data source parameters entered. Save non-verified file DSN?”
The parameters entered are:
Database: database_name
Server: I entered the IP address of the server
User Name: I entered postgres
Password:
SSL Mode: prefer
Port: 5432 (which is the port used by postgres)
Do I need to configure the /etc/odbc.ini file? It is currently just a blank file.
Thanks for any help.
Marc
Is the user you connect with allowed to see / modify data? Maybe it would be a good idea to increase the log level on the server and look in the logs, what happens. This can be done at runtime, no need to stop the database. Regards, Frank. On Tue, 29 Mar 2011 20:21:44 +0000 Marc Fromm <Marc.Fromm@wwu.edu> wrote: > By editing postgresql.conf and changing listen_address = ‘*’ and > editing pg_hba.conf and adding host all all my.ip.address allowed me > to connect to the postgresql data base with Microsoft access. > > In access I can see the table and it fields, but if I try to use the > table in a querry or view the table I get an error: permission denied > for relation summer_finaid; error while executing the query (#7) > > Any ideas? > > Also in the postgresql instead of listen_address=’*’ I tried > listen_address=’localhost, my.ip.address’ but that failed to work. > How can I keep local host and only allow specific address? I don’t > want to open it up to all with ‘*’ > > Thanks > > > From: jsilberberg@mindspring.com [mailto:jsilberberg@mindspring.com] > Sent: Tuesday, March 29, 2011 11:13 AM > To: Marc Fromm; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] first time odbc > > > > > Why, > > > > That's a vintage 02-Feb-2006 driver. > > > > I use 8.04 from Dec-2009 and even that's getting a little > stale. Why not try the 9.n drivers. > > > > Could the port(s) be blocked / restricted between you and the > server and is PostgreSQL SSL support enabled -- > > http://www.postgresql.org/docs/9.0/static/ssl-tcp.html > > > > JMS > > > > ----------------------------------- > > > -----Original Message----- > From: Marc Fromm > Sent: Mar 29, 2011 1:58 PM > To: "pgsql-odbc@postgresql.org<mailto:pgsql-odbc@postgresql.org>" > Subject: [ODBC] first time odbc > > > Before learning there was an odbc list, I sent this post to the admin > list. > > I am running postgres on a red hat linux server. > postgresql-python-8.1.23-1.el5_6.1 > postgresql-test-8.1.23-1.el5_6.1 > postgresql-libs-8.1.23-1.el5_6.1 > postgresql-docs-8.1.23-1.el5_6.1 > postgresql-contrib-8.1.23-1.el5_6.1 > postgresql-8.1.23-1.el5_6.1 > postgresql-pl-8.1.23-1.el5_6.1 > postgresql-odbc-08.01.0200-3.1 > postgresql-jdbc-8.1.407-1jpp.4 > postgresql-server-8.1.23-1.el5_6.1 > postgresql-tcl-8.1.23-1.el5_6.1 > > User on windows computers would like to odbc to the postgres > databases. I downloaded the windows postgres odbc drivers from the > following site and installed version “psqlodbc-08_01_0200” on the > windows computer. > http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/ > > When I configure a “File Data Source” connection in access, I cannot > connect to the postgresql database. I get the message, “A connection > could not be made using the file data source parameters entered. Save > non-verified file DSN?” The parameters entered are: Database: > database_name Server: I entered the IP address of the server User > Name: I entered postgres Password: > SSL Mode: prefer > Port: 5432 (which is the port used by postgres) > > Do I need to configure the /etc/odbc.ini file? It is currently just a > blank file. > > Thanks for any help. > > Marc
On 29/03/2011 21:21, Marc Fromm wrote: > By editing postgresql.conf and changing listen_address = ‘*’ and editing > pg_hba.conf and adding host all all my.ip.address allowed me to connect > to the postgresql data base with Microsoft access. > > In access I can see the table and it fields, but if I try to use the > table in a querry or view the table I get an error: permission denied > for relation summer_finaid; error while executing the query (#7) You need to grant permission on the table to the user: GRANT SELECT ON summer_finaid TO [insert your user here]; See the docs here: http://www.postgresql.org/docs/9.0/static/sql-grant.html > Also in the postgresql instead of listen_address=’*’ I tried > listen_address=’localhost, my.ip.address’ but that failed to work. > > How can I keep local host and only allow specific address? I don’t want > to open it up to all with ‘*’ listen_addresses specifies the interfaces on which Postgres *listens*, not the remote hosts which can connect - that's done in pg_hba.conf. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
I only want the users to read data, thus I removed superuser and applied grant select. If the listen_address is not a client filter, would it still be better to only have it listen to localhost and the few ipaddresses that will need to odbc to the postgres database? Is it just not possible to assign listen_address = 'localhost,1.ip.address, 2.ip.address'? One other thing still, if in the pg_hba.conf file I do not include any ip addresses I cannot connect to the database. IfI enter just one ip address, any computer can connect to the database, even though some machines have static ip addressesand others have dhcp address from a completely different subnet. Do you know why this is? I figured the pg_hba.conffile controlled access by ip address, based on other googled articles. Thanks -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Frank Finner Sent: Tuesday, March 29, 2011 9:18 PM To: pgsql-odbc@postgresql.org Subject: Re: [ODBC] first time odbc Is the user you connect with allowed to see / modify data? Maybe it would be a good idea to increase the log level on the server and look in the logs, what happens. This can be doneat runtime, no need to stop the database. Regards, Frank. On Tue, 29 Mar 2011 20:21:44 +0000 Marc Fromm <Marc.Fromm@wwu.edu> wrote: > By editing postgresql.conf and changing listen_address = ‘*’ and > editing pg_hba.conf and adding host all all my.ip.address allowed me > to connect to the postgresql data base with Microsoft access. > > In access I can see the table and it fields, but if I try to use the > table in a querry or view the table I get an error: permission denied > for relation summer_finaid; error while executing the query (#7) > > Any ideas? > > Also in the postgresql instead of listen_address=’*’ I tried > listen_address=’localhost, my.ip.address’ but that failed to work. > How can I keep local host and only allow specific address? I don’t > want to open it up to all with ‘*’ > > Thanks > > > From: jsilberberg@mindspring.com [mailto:jsilberberg@mindspring.com] > Sent: Tuesday, March 29, 2011 11:13 AM > To: Marc Fromm; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] first time odbc > > > > > Why, > > > > That's a vintage 02-Feb-2006 driver. > > > > I use 8.04 from Dec-2009 and even that's getting a little stale. > Why not try the 9.n drivers. > > > > Could the port(s) be blocked / restricted between you and the > server and is PostgreSQL SSL support enabled -- > > http://www.postgresql.org/docs/9.0/static/ssl-tcp.html > > > > JMS > > > > ----------------------------------- > > > -----Original Message----- > From: Marc Fromm > Sent: Mar 29, 2011 1:58 PM > To: "pgsql-odbc@postgresql.org<mailto:pgsql-odbc@postgresql.org>" > Subject: [ODBC] first time odbc > > > Before learning there was an odbc list, I sent this post to the admin > list. > > I am running postgres on a red hat linux server. > postgresql-python-8.1.23-1.el5_6.1 > postgresql-test-8.1.23-1.el5_6.1 > postgresql-libs-8.1.23-1.el5_6.1 > postgresql-docs-8.1.23-1.el5_6.1 > postgresql-contrib-8.1.23-1.el5_6.1 > postgresql-8.1.23-1.el5_6.1 > postgresql-pl-8.1.23-1.el5_6.1 > postgresql-odbc-08.01.0200-3.1 > postgresql-jdbc-8.1.407-1jpp.4 > postgresql-server-8.1.23-1.el5_6.1 > postgresql-tcl-8.1.23-1.el5_6.1 > > User on windows computers would like to odbc to the postgres > databases. I downloaded the windows postgres odbc drivers from the > following site and installed version “psqlodbc-08_01_0200” on the > windows computer. > http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi > / > > When I configure a “File Data Source” connection in access, I cannot > connect to the postgresql database. I get the message, “A connection > could not be made using the file data source parameters entered. Save > non-verified file DSN?” The parameters entered are: Database: > database_name Server: I entered the IP address of the server User > Name: I entered postgres Password: > SSL Mode: prefer > Port: 5432 (which is the port used by postgres) > > Do I need to configure the /etc/odbc.ini file? It is currently just a > blank file. > > Thanks for any help. > > Marc -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc
On 03/30/2011 07:54 AM, Marc Fromm wrote: > I only want the users to read data, thus I removed superuser and applied grant select. > > If the listen_address is not a client filter, would it still be better to only have it listen to localhost and the fewip addresses that will need to odbc to the postgres database? Is it just not possible to assign listen_address = 'localhost,1.ip.address, 2.ip.address'? listen_address refers to the addresses available on the host. More than one assumes you have more than one IP address bound to the database host. You will not get the result you want using the method you propose above. > > One other thing still, if in the pg_hba.conf file I do not include any ip addresses I cannot connect to the database. IfI enter just one ip address, any computer can connect to the database, even though some machines have static ip addressesand others have dhcp address from a completely different subnet. Do you know why this is? I figured the pg_hba.conffile controlled access by ip address, based on other googled articles. pg_hba.conf works by first match wins. My guess is you have line above your IP line that is more permissive and is allowing connections. Per a previous post, you probably should spend some time with the manual to fully understand the interaction between the various security,access components of Postgres:) > > Thanks > > -- Adrian Klaver adrian.klaver@gmail.com
On 30/03/2011 15:54, Marc Fromm wrote: > I only want the users to read data, thus I removed superuser and > applied grant select. Good stuff. > If the listen_address is not a client filter, would it still be > better to only have it listen to localhost and the few ip addresses > that will need to odbc to the postgres database? Is it just not > possible to assign listen_address = 'localhost, 1.ip.address, > 2.ip.address'? You're still misunderstanding listen_addresses - it has *nothing* to do with client addresses. Example: I have a server with two network interfaces, a.b.c.x and a.b.c.y. If I want PG to listen on both interfaces, I can set listen_addresses = '*' (or list the two addresses explicitly). If I want it to listen *only* on a.b.c.x, then I set listen_addresses='a.b.c.x'. > One other thing still, if in the pg_hba.conf file I do not include > any ip addresses I cannot connect to the database. If I enter just > one ip address, any computer can connect to the database, even though > some machines have static ip addresses and others have dhcp address > from a completely different subnet. Do you know why this is? I > figured the pg_hba.conf file controlled access by ip address, based > on other googled articles. Can you show us your pg_hba.conf? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
This is my pg_hba.conf file. I replaced x's with my ip address. If I comment line 74, then no one can odbc to postgresql. Lines 1 - 67 are all just commented text. 68 # TYPE DATABASE USER CIDR-ADDRESS METHOD 69 70 # "local" is for Unix domain socket connections only 71 local all all trust 72 # IPv4 local connections: 73 host all all 127.0.0.1/32 trust 74 host all all xxx.xxx.xxx.xxx 255.255.0.0 md5 75 # IPv6 local connections: 76 host all all ::1/128 trust -----Original Message----- From: Raymond O'Donnell [mailto:rod@iol.ie] Sent: Wednesday, March 30, 2011 8:48 AM To: Marc Fromm Cc: postgresql@finner.de; pgsql-odbc@postgresql.org Subject: Re: [ODBC] first time odbc On 30/03/2011 15:54, Marc Fromm wrote: > I only want the users to read data, thus I removed superuser and > applied grant select. Good stuff. > If the listen_address is not a client filter, would it still be better > to only have it listen to localhost and the few ip addresses that will > need to odbc to the postgres database? Is it just not possible to > assign listen_address = 'localhost, 1.ip.address, 2.ip.address'? You're still misunderstanding listen_addresses - it has *nothing* to do with client addresses. Example: I have a server with two network interfaces, a.b.c.x and a.b.c.y. If I want PG to listen on both interfaces, I canset listen_addresses = '*' (or list the two addresses explicitly). If I want it to listen *only* on a.b.c.x, then I setlisten_addresses='a.b.c.x'. > One other thing still, if in the pg_hba.conf file I do not include any > ip addresses I cannot connect to the database. If I enter just one ip > address, any computer can connect to the database, even though some > machines have static ip addresses and others have dhcp address from a > completely different subnet. Do you know why this is? I figured the > pg_hba.conf file controlled access by ip address, based on other > googled articles. Can you show us your pg_hba.conf? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie