Forum OpenACS Q&A: Problem connecting to postgresql via stunnel (user can, Aolserver can't)

I'm trying to set up an OACS server to connect to a database on a separate server over an stunnel connection. The stunnel was setup like so:

On the database server:

stunnel -d 5555 -r localhost:5432

Which sets up a tunnel receiveing connections on port 5555 and forwarding them to 5432 (pgsql)

On the web server:

stunnel -c -d 55432 -r dataserver:5555

Which sets up a client tunnel listening on 55432 and forwarding to the listening end of the stunnel on the database server at port 5555.

I'm doing this because the webserver will be going into our DMZ and we want to encrypt the traffic (passwords, y'know), but for the moment it's not in the DMZ, so as a user I can connect to the ACS database one of two ways (skipping any discussion of pg_hba.conf):

1. via the stunnel: psql -U nsadmin -h localhost -p 55432 acsdb
2. via a normal remote connection: psql -U nsadmin -h dataserver acsdb

The server can connect via method #2 with the following setup in ns_section ns/db/pool/pool1 (and pool2 and pool3):

ns_param driver postgres
ns_param datasource dataserver::acsdb
ns_param user nsadmin
ns_param password ""

But it cannot connect via method #1 (stunnel), even though it works for psql:

ns_param driver postgres
ns_param datasource localhost:55432:acsdb
ns_param user nsadmin
ns_param password ""

In the logs I find the message:

Error: Ns_PgOpenDb(postgres): Could not connect to localhost:55432:acsdb: could not connect to server: No such file or directory
Is the server running locally and accepting connections on Unix domain socket "/tmp.s.PGSQL.55432"?

Of course the answer is "no," but it looks perhaps like the postgres driver is trying to use a file socket instead of a network socket (because it's localhost?).

Any ideas what to do?

Try
ns_param datasource 127.0.0.1:55432:acsdb

If I remember right aolserver or the driver makes the presumption that localhost = unix domain socket so using 127.0.0.1 tells it to look for a tcp socket.

My understanding is that as you suggest Postgres will use a unix socket for connections out of the box. Because the client process is not ACTUALLY running on localhost, and because the smoke and mirrors with ssh is done on top of tcpip I don't think that you can use unix socket based connections down an ssh tunnel (someone may tell me something I don't know here!).

Unless the parameter 'tcpsocket=true' is uncommented in the postgres.conf file you won't have any joy so that may be worth double checking.

Also, I don't think that you can avoid disussing pg_hba.conf because the default entry is only for unix socket connections.

You will need to add the following, to open up host based connections on the appropriate ip address, or something similar (I like to use the option for storing the authorised users in a file).

host     all     192.168.50.32     255.255.255.0     md5     pg_auth_users

From memory md5 is the option recommended in the docs for the most recent versions of postgres

If you have two network ports you can set one to 192.168.xxx.xxx and connect to that one on the local subnet which helps to avoid opening postgres up on the public port.

Regards
Richard

It's not a typo, it's just unecessarily confusing. The connection goes localhost:55432->dataserver:5555->dataserver:5432. 5555 and 55432 are arbitrary numbers (5555 came from a web page I was getting an example from, 55432 is the first slight variation on 5432 that came to mind). It would probably be better to pick different numbers to avoid just this kind of confusion.

Well, here is the code fragment from the postgres driver. If it's a bug it's an intentional one.

 if (!strcmp(host, "localhost")) {
            Ns_Log(Notice, "Opening %s on %s", db, host);
            pgConn = PQsetdbLogin(NULL, port, NULL, NULL, db, handle->user,
                                  handle->password);
        } else {
            Ns_Log(Notice, "Opening %s on %s, port %s", db, host, port);
            pgConn = PQsetdbLogin(host, port, NULL, NULL, db, handle->user,
                             handle->password);
        }

Maybe if we are connection because we have "localhost" we should try 127.0.0.1 if the unix domain socket fails.

I will ping Lamar Owen and see what he thinks...