How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?
Hey guys, I'm trying to get a VB program to make a client connection to my
PostgreSQL server running on an
Ubuntu 10.10 server.
Here's what I've done:
Client side - installed and registered the OLE DB .dll's from the PostgreSQL
OLE DB Provider project.
server side -
the configuration described here:
https://help.ubuntu.com/10.10/serverguide/C/postgresql.html
---------------
Have added this line to postgresql.conf:
listen_addresses = '*, 144.96.80.35, localhost'
I've also tried this as simply:
listen_addresses = '144.96.80.35, localhost'
also, port = 5432
--------------
I have added the following to pg_hba.conf:
# IPv4 local connections:
host all all 144.96.80.35 255.255.255.0 md5
local all postgres md5
--------------
I've got the following code going in VB:
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Const connString As String = "Provider=PostgreSQL;
Server=144.96.80.35; User Id=postgres;Password=postgres;"
Dim theOleDBconnection As New OleDb.OleDbConnection
theOleDBconnection.ConnectionString = connString
theOleDBconnection.Open()
End Sub
End Class
So when the project opens up, it should make the connection and display the
form.
What happens is that I get an error...
OleDbException was unhandled
could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "" and accepting
TCP/IP connections on port 5432?
---------------------------
So, obviously I've got something configured incorrectly.
I'm troubled that the VB error seems to show a null value for the host.
Shouldn't that be the Server value in
the connection string?
Thanks to all for any help.
On 03/09/2011 07:31 AM, John Edens wrote:
Hey guys, I'm trying to get a VB program to make a client connection
to my PostgreSQL server running on anUbuntu 10.10 server....
listen_addresses = '*, 144.96.80.35, localhost'
Using * should be fine unless you have multiple IP addresses and want
the *server* to *listen* on only some of those addresses - say localhost
if you were running web and db on the same machine and didn't want to
listen to connections from the outside. This setting only determines
where the server listens.
I have added the following to pg_hba.conf:
# IPv4 local connections:
host all all 144.96.80.35 255.255.255.0 md5
local all postgres md5
This setting determines what *clients* are allowed to connect to the
server. Your setting here is strange. It should be a proper network
range. You have an IP address with a class-C netmask.
If you want to only accept connections from a specific single IP
address, use 144.96.80.35/32 or 144.96.80.35 255.255.255.255. But if you
want to listen to the class-C then 144.96.80.0/24 or 144.96.80.0
255.255.255.0.
And since the IP address you gave appears real since it is in the
assigned public space for Stephen F. Austin State University, I hope
this machine is hiding behind a firewall.
Cheers,
Steve
------------
Using * should be fine unless you have multiple IP addresses and want the
*server* to *listen* on only some of those addresses - say localhost if you
were running web and db on the same machine and didn't want to listen to
connections from the outside. This setting only determines where the server
listens.
-------------
Okay, done that - what is the difference between listening on only some of
those addresses and allowing only clients in a certain range to connect?
------------
And since the IP address you gave appears real since it is in the assigned
public space for Stephen F. Austin State University, I hope this machine is
hiding behind a firewall.
------------
Yes, behind a firewall and also that machine is a brand new linux box that
literally has nothing else on it besides a new install of PostgreSQL
But, yeah, should have anoned up the address...
On 03/09/2011 09:54 AM, John Edens wrote:
------------
Using * should be fine unless you have multiple IP addresses and want
the *server* to *listen* on only some of those addresses - say
localhost if you were running web and db on the same machine and
didn't want to listen to connections from the outside. This setting
only determines where the server listens.-------------
Okay, done that - what is the difference between listening on only
some of those addresses and allowing only clients in a certain range
to connect?
They are completely different.
Your server can have multiple IP addresses. In fact, it almost certainly
has an IP assigned to your NIC and a localhost address. But you could
have multiple NICs in the machine and/or multiple IP addresses assigned
to each NIC. This is very common - especially for web servers hosting
several sites on different addresses. The addresses can be on different
networks, as well.
Suppose your server has addresses 10.1.1.2, 10.1.1.3 and 192.168.1.4. If
listen_addresses is set to *, a client could connect to PostgreSQL by
connecting to any of those addresses - i.e. the host in their connection
string could be any of the addresses you listen on. If that is not what
you want, you need to explicitly list the addresses you want PostgreSQL
to use.
Now that you are listening on one or more addresses, the pg_hba.conf
determines who is allowed to connect. It is completely independent of
where you listen. It could include all IP addresses, or one or more
individual IPs or IP ranges. And the IP addresses allowed for clients do
not need to overlap your listen_address or be on the same IP network.
By way of example, you could have three independent instances of
PostgreSQL running on your machine - perhaps one for dev, one for QA and
one for deployment-staging. You could have dev listen only on .2, QA on
.3 and deploy on .4. You could even have a 4th, say "sandbox", listening
on all (though it would have to have a different port). And each one of
those instances could have different pg_hba.conf settings to restrict
client access as appropriate.
Cheers,
Steve
Thanks for that explanation, Steve. This is my first time doing much of
anything with a server, so that's all good to know!
John
------------
Your server can have multiple IP addresses. In fact, it almost certainly has
an IP assigned to your NIC and a localhost address. But you could have
multiple NICs in the machine and/or multiple IP addresses assigned to each
NIC. This is very common - especially for web servers hosting several sites
on different addresses. The addresses can be on different networks, as well.
-------------