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

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

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