3

I've never connected to a PostgreSQL database before, so this is all new territory. I'm creating an intranet version of a desktop application that we had created for us in our office. I currently have the desktop application installed and it connects to the PostgreSQL database (on a network server) without any hiccups. I've installed the PostgreSQL odbc drivers and when I use ODBC Data Source Administrator in the Control Panel to test the connection, I get a successful test connection. My issue is when I try to connect directly via a PHP script using all of the same information. I get this error: 

Warning: odbc_connect(): SQL error: FATAL: no pg_hba.conf entry for host "10.0.1.218", user "", database "", SSL off, SQL state 28000 in SQLConnect

I understand that I could go to the server and change the connection settings (that's if IT allows me to make changes to the server's files... Not counting on that one).

My setup is: Windows XP, XAMPP v. 1.8.2, PHP 5.4, Postgre SQL drivers 9.03.03. My connection information via ODBC Data Source Administrator is: 

Data Source - PostgreSQL
Database - db
Server - 10.0.0.149
User Name - user
SSL Mode - require
Port - 5432
Password - password
Driver - PostgreSQL Unicode

The connection string from the desktop application is: 

sDeeperConnection = "Server=10.0.0.149;Database=db;User Id=user;Password=password;SSL=true;Pooling=false";

The connection string that I've created in PHP is:

$dsn='10.0.0.149';
$username='user';
$password='password';
$dbname='db';

$odb = odbc_connect("Driver={PostgreSQL Unicode};Server=$dsn;SSL=true;Pooling=false;Port=5432;Database=$dbname;", $username, $password);

I did notice that the error when I run the PHP script says that SSL 'off' even though I've set it to true, but I don't know how to change this. I've tried 'require' like in the ODBC Data Source Administrator but to no avail. To me it seems that the error (other than the SSL issue) doesn't make sense. I can connect to the PostgreSQL database two different ways on my computer: the desktop application and the ODBC Data Source Administrator. So the issue with there not being an entry for host '10.0.1.218', should be a non-issue in my mind. I've also tried changing the driver I use from PostgreSQL Unicode to PostgreSQL ANSI, but that was also unsuccessful.

I've tried using the pg_connect() function with the same connection parameters after removing the ; from these lines php.ini: 

;extension=php_pdo_pgsql.dll
;extension=php_pgsql.dll

But I received the same error. I think it has something to do with the SSL, but I have no idea where to start in testing/debugging the connection using SSL. This problem is a little more difficult due to the fact that I don't have access to the server and getting access to change the files would be like pulling nails.

Any ideas or other options would be great! Thanks ahead of time!

EDIT Looking in my phpinfo this is what I see:

pgsql
PostgreSQL Support  enabled
PostgreSQL(libpq) Version   8.3.3
PostgreSQL(libpq)   PostgreSQL 8.3.3, compiled by Visual C++ build 1500
Multibyte character support     enabled
SSL support     enabled
Active Persistent Links     0
Active Links    0 

openssl
OpenSSL support     enabled
OpenSSL Library Version     OpenSSL 0.9.8y 5 Feb 2013
OpenSSL Header Version  OpenSSL 0.9.8y 5 Feb 2013

EDIT AGAIN

I downloaded pgAdmin III according to the comments below and setup the connection and I'm connecting just fine. So I know that it's not the host. It's something with the SSL setup, I guess. I just have no idea why/how.... Argh...

Michael
  • 2,016
  • 5
  • 35
  • 51
  • When you tried using `pg_connect`, did you pass `sslmode=require` and use libpq's connection string syntax, e.g. `host=localhost port=5432 dbname=mydb connect_timeout=10`? – tsnorri Jul 18 '14 at 15:11
  • @tsnorri I thought I had, but I don't remember this error: Unable to connect to PostgreSQL server: sslmode value "require" invalid when SSL support is not compiled. That's what I'm getting now. I'm looking into how to solve that error. If you have a suggestion, I'd welcome it. – Michael Jul 18 '14 at 15:17
  • I found this: http://stackoverflow.com/questions/23726779/how-to-installpostgresql-client-library-for-php-on-windows-with-ssl-enabled – tsnorri Jul 18 '14 at 15:21
  • Also http://stackoverflow.com/a/13441842/856976 – tsnorri Jul 18 '14 at 15:23
  • @tsnorri I've tried the suggestion from the first stackoverflow link and I'm still getting the same error. Any other ideas? – Michael Jul 18 '14 at 15:49
  • Take a look at this http://www.postgresql.org/docs/8.3/static/auth-pg-hba-conf.html – Dayron Gallardo Jul 18 '14 at 15:55
  • `user "", database ""`: is this verbatim from the error message or have you edited this out? – Daniel Vérité Jul 18 '14 at 16:38
  • @DanielVérité I edited out the actual username and password. Sorry, forgot to mention that. – Michael Jul 18 '14 at 16:41
  • If you check `phpinfo()`, does it say that SSL support is enabled in the pgsql section? – tsnorri Jul 18 '14 at 18:36
  • I'd say this is more or less the same problem as in [this question](http://stackoverflow.com/q/17837263/372643). – Bruno Jul 21 '14 at 08:08
  • @tsnorri `phpinfo()` states that it is enabled. I included the relevant sections of `phpinfo()` in my question. – Michael Jul 21 '14 at 17:24
  • @Bruno I looked at that question and that shows that SSL is disabled, however, in my `phpinfo()` it states that it is enabled. I've included the relevant sections of `phpinfo()` in my question. – Michael Jul 21 '14 at 17:26
  • It's quite odd to get `sslmode value "require" invalid when SSL support is not compiled` with `pg_connect` then. Did you change anything between these tests? Not sure if it matters, but you also have a version mismatch (using libpq 8.3). It's OK to use client version higher than the server, but the opposite doesn't work so well. – Bruno Jul 21 '14 at 17:31

1 Answers1

0

After doing much more digging and reading a ton of solutions for similar but different problems, I ran across a suggestion that someone made to copy the libpq.dll from my c:\xampp\php folder to my C:\xampp\apache\bin folder. I had to overwrite the existing libpq.dll file in that directory. Then I got another error:

pg_connect(): Unable to connect to PostgreSQL server: FATAL: password authentication failed for user

After doing more reading of random issues someone else had an issue that regardless of the password used or not used, the connection would be created. So, I decided to take out the password in the connection string and it worked! Thanks to all those who commented and kept me looking for a solution!

Michael
  • 2,016
  • 5
  • 35
  • 51