Обсуждение: ODBC first time odbc

Okay,

   You may want to start at the index and read the manual.  

    But I think you need to "grant select ................" to your ODBC user for your permissions issue.

    http://www.postgresql.org/docs/9.0/static/sql-grant.html

    JMS..

-----Original Message-----
From: Marc Fromm
Sent: Mar 29, 2011 5:13 PM
To: "pgsql-odbc@postgresql.org"
Subject: [ODBC] ODBC first time odbc

By changing the user that I login with to a super user, everything works. I don’t get the permission denied error.

However, I only want the users to read. Being set as a super user allows the odbc user to change the data.

 

My problem is now down to two security points:

How do I have a user with only read access (not a super user) and not be denied viewing the table data?

 

How do I control access with listen_address=’localhost, specific.ip.addresses’ and not use ‘*’?

Setting listen_address=’localhost, specific.ip.addresses’ does not work, I get call failed errors asking if the server is accepting tcp/ip connections on port 5432

 

Thanks

 

 

From: Marc Fromm
Sent: Tuesday, March 29, 2011 1:23 PM
To: 'jsilberberg@mindspring.com'
Cc: pgsql-odbc@postgresql.org
Subject: RE: [ODBC] first time odbc

 

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 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

I figured out the permissions problem, by removing superuser and granting select. Now the users can view the data and
notalter it. 



I have one last step--locking access down by ip address.

Even though I set only one specific ip address in the pg_hba.conf file, I can odbc to postgresql database from a
varietyof ip addresses on different computers. The postgresql.conf file still has listen_address='*'. If I set
listen_address='one.ip.address'I get connection errors. Is setting listen_address='*' a security risk? 



thanks

________________________________
From: jsilberberg@mindspring.com [jsilberberg@mindspring.com]
Sent: Tuesday, March 29, 2011 3:34 PM
To: Marc Fromm; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] ODBC first time odbc




Okay,



   You may want to start at the index and read the manual.



    But I think you need to "grant select ................" to your ODBC user for your permissions issue.



    http://www.postgresql.org/docs/9.0/static/sql-grant.html



    JMS..






-----Original Message-----
From: Marc Fromm
Sent: Mar 29, 2011 5:13 PM
To: "pgsql-odbc@postgresql.org"
Subject: [ODBC] ODBC first time odbc

By changing the user that I login with to a super user, everything works. I don’t get the permission denied error.
However, I only want the users to read. Being set as a super user allows the odbc user to change the data.

My problem is now down to two security points:
How do I have a user with only read access (not a super user) and not be denied viewing the table data?

How do I control access with listen_address=’localhost, specific.ip.addresses’ and not use ‘*’?
Setting listen_address=’localhost, specific.ip.addresses’ does not work, I get call failed errors asking if the server
isaccepting tcp/ip connections on port 5432 

Thanks


From: Marc Fromm
Sent: Tuesday, March 29, 2011 1:23 PM
To: 'jsilberberg@mindspring.com'
Cc: pgsql-odbc@postgresql.org
Subject: RE: [ODBC] first time odbc

By editing postgresql.conf and changing listen_address = ‘*’ and editing pg_hba.conf and adding host all all
my.ip.addressallowed 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
towork. 
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
thewindows 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

The listen_address is the server's listening address, not a filter for
client addresses. This is only relevant, if the server has different
addresses, maybe on different interfaces, and you want for example only
one interface to listen to database access requests. IMHO it is
usually not a security risk to set it to '*'.

Regards,
Frank.

On Wed, 30 Mar 2011 03:01:10 +0000 Marc Fromm <Marc.Fromm@wwu.edu>
wrote:

> I figured out the permissions problem, by removing superuser and
> granting select. Now the users can view the data and not alter it.
>
>
>
> I have one last step--locking access down by ip address.
>
> Even though I set only one specific ip address in the pg_hba.conf
> file, I can odbc to postgresql database from a variety of ip
> addresses on different computers. The postgresql.conf file still has
> listen_address='*'. If I set listen_address='one.ip.address' I get
> connection errors. Is setting listen_address='*' a security risk?
>
>
>
> thanks
>
> ________________________________
> From: jsilberberg@mindspring.com [jsilberberg@mindspring.com]
> Sent: Tuesday, March 29, 2011 3:34 PM
> To: Marc Fromm; pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] ODBC first time odbc
>
>
>
>
> Okay,
>
>
>
>    You may want to start at the index and read the manual.
>
>
>
>     But I think you need to "grant select ................" to your
> ODBC user for your permissions issue.
>
>
>
>     http://www.postgresql.org/docs/9.0/static/sql-grant.html
>
>
>
>     JMS..
>
>
>
>
>
>
> -----Original Message-----
> From: Marc Fromm
> Sent: Mar 29, 2011 5:13 PM
> To: "pgsql-odbc@postgresql.org"
> Subject: [ODBC] ODBC first time odbc
>
> By changing the user that I login with to a super user, everything
> works. I don’t get the permission denied error. However, I only want
> the users to read. Being set as a super user allows the odbc user to
> change the data.
>
> My problem is now down to two security points:
> How do I have a user with only read access (not a super user) and not
> be denied viewing the table data?
>
> How do I control access with listen_address=’localhost,
> specific.ip.addresses’ and not use ‘*’? Setting
> listen_address=’localhost, specific.ip.addresses’ does not work, I
> get call failed errors asking if the server is accepting tcp/ip
> connections on port 5432
>
> Thanks
>
>
> From: Marc Fromm
> Sent: Tuesday, March 29, 2011 1:23 PM
> To: 'jsilberberg@mindspring.com'
> Cc: pgsql-odbc@postgresql.org
> Subject: RE: [ODBC] first time odbc
>
> 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
>


--
Frank Finner
Köpfchenstraße 36
57072 Siegen