Обсуждение: Connection to database failed: FATAL: no pg_hba.conf entry for host
Introduction: I am working on an application that connects to a postgres database using the libpq API. As a requirement the remote host uses SSL. The scenario involves two Mac computers. We also have two different users, each with their own account to access the database. On one computer, the application works fine and both users can connect to the database using SSL. On the other computer, both users fail to connect to a database and the “no pg_hba.cong entry for host …” error is returned from PQerrorMessage. All users can connect to the database on both computers using either psql, or pgAdmin. Code Syntax: Development is done on Mac with code written in Cocoa / Objectice-C, and C language. I am using the following include file: #include <libpq-fe.h> I create the connection string using the following format. Then NSString *connstr = [NSString stringWithFormat:@"sslmode='prefer' host='%@' port='%@' user='%@' password='%@' dbname='%@'", host, port, user, pass, db]; const char *conninfo = [connstr cStringUsingEncoding:NSASCIIStringEncoding]; PGconn *conn = PQconnectdb (conninfo); /* conninfo = "sslmode='prefer' host='host.domain.com' port='' user='username' password='password' dbname='database'" */ if (PQstatus(conn) != CONNECTION_OK) {// no pg_hba.conf entry for host error occurs } Configuration Settings: pg_hba.conf contains hostssl all all 0.0.0.0/0 md5 postgresql.conf contains ssl on on ssl_cert_file server.crt server.crt ssl_key_file server.key server.key Since I able to connect to both computers using psql and pgAdmin, I suspect the problem is with the connection within the Application. The connection string in the application is correct, and I verified the user name and password being passed properly. -- View this message in context: http://postgresql.nabble.com/Connection-to-database-failed-FATAL-no-pg-hba-conf-entry-for-host-tp5898099.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On 04/11/2016 03:31 PM, Chris wrote: > Introduction: > > I am working on an application that connects to a postgres database using > the libpq API. As a requirement the remote host uses SSL. > > The scenario involves two Mac computers. We also have two different users, > each with their own account to access the database. > > On one computer, the application works fine and both users can connect to > the database using SSL. > > On the other computer, both users fail to connect to a database and the “no > pg_hba.cong entry for host …” error is returned from PQerrorMessage. > > All users can connect to the database on both computers using either psql, > or pgAdmin. > > Code Syntax: > > Development is done on Mac with code written in Cocoa / Objectice-C, and C > language. > > I am using the following include file: > > #include <libpq-fe.h> > > I create the connection string using the following format. Then > > NSString *connstr = [NSString stringWithFormat:@"sslmode='prefer' host='%@' > port='%@' user='%@' password='%@' dbname='%@'", host, port, user, pass, db]; > > const char *conninfo = [connstr cStringUsingEncoding:NSASCIIStringEncoding]; > > PGconn *conn = PQconnectdb (conninfo); > > /* > conninfo = "sslmode='prefer' host='host.domain.com' port='' user='username' > password='password' dbname='database'" > */ > > if (PQstatus(conn) != CONNECTION_OK) > { > // no pg_hba.conf entry for host error occurs > } > > > Configuration Settings: > > pg_hba.conf contains > > hostssl all all 0.0.0.0/0 md5 > > > postgresql.conf contains > > ssl on on > ssl_cert_file server.crt server.crt > ssl_key_file server.key server.key > > > Since I able to connect to both computers using psql and pgAdmin, I suspect > the problem is with the connection within the Application. The connection > string in the application is correct, and I verified the user name and > password being passed properly. Have you verified the other parameters, in particular the port. I am suspicious of the port='' in the above. The error is pretty straight forward, the correct host name is not being found in the the pg_hba.conf file. That means either the host name is not specified correctly in the file or you are connecting to a different cluster(port) then you think are and are picking up a different pg_hba.conf. > > > > -- > View this message in context: http://postgresql.nabble.com/Connection-to-database-failed-FATAL-no-pg-hba-conf-entry-for-host-tp5898099.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks for your reply Adrian. I thought about the port. The server name is correct, and I provided the default port 5432 in place of the empty string. And it still did not work. We are using the default port settings for both SSL and non-SSL connections. I also tried using the PQconnectdbParams (see below) in place of PQconnectdb. Using exactly the same code as in the Open Source with few minor modifications. Connecting using this method still fails with the following error message... Connection to database failed: FATAL: no pg_hba.conf entry for host "12.224.126.19", user "james", database "database", SSL off Now I am trying to figure out why psql works with the code below, but the Application that I am creating does not. I checked that the encoding NSASCIIStringEncoding is converted properly, I even hard coded the parameters to no avail. Would there be any compiler setting I need to set in XCode? I am building on Yosemite. The application is connecting properly on several Mac computers running both Yosemite and El Capitan. We only have three computers where the application fails to connect, each being either Yosemite or El Capitan. Each have some type of SSL network connection, otherwise spql would not succeed. Strange part about this problem is that not every computer is having this connection issue, and the computers that are failing, work perfectly fine with psql and pgAdmin. bool notty = (!isatty(fileno(stdin)) || !isatty(fileno(stdout))); #define PARAMS_ARRAY_SIZE 7 const char **keywords= malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords)); const char **values = malloc(PARAMS_ARRAY_SIZE * sizeof(*values)); keywords[0] = "host"; values[0] = [host cStringUsingEncoding:NSASCIIStringEncoding]; keywords[1]= "port"; if ([port isEqualToString:@""]) { values[1] = "5432"; } else{ values[1] = [portcStringUsingEncoding:NSASCIIStringEncoding]; } keywords[2] = "user"; values[2] = [user cStringUsingEncoding:NSASCIIStringEncoding]; keywords[3] = "password"; values[3] = [pass cStringUsingEncoding:NSASCIIStringEncoding]; keywords[4] = "dbname"; values[4] = [db cStringUsingEncoding:NSASCIIStringEncoding]; keywords[5] = "client_encoding"; values[5] = (notty || getenv("PGCLIENTENCODING"))? NULL : "auto"; keywords[6] = NULL; values[6] = NULL; //new_pass = false; conn = PQconnectdbParams(keywords,values, true); free(keywords); free(values); -- View this message in context: http://postgresql.nabble.com/Connection-to-database-failed-FATAL-no-pg-hba-conf-entry-for-host-tp5898099p5898337.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
<p dir="ltr">Hi, <p dir="ltr">Please look into postgresql.conf file for listen_addresses and set to "*".<div class="gmail_quote">OnApr 13, 2016 4:27 AM, "Adrian Klaver" <<a href="mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</a>>wrote:<br type="attribution" /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 04/11/2016 03:31 PM, Chris wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> Introduction:<br/><br /> I am working on an application that connects to a postgres database using<br /> the libpq API. Asa requirement the remote host uses SSL.<br /><br /> The scenario involves two Mac computers. We also have two differentusers,<br /> each with their own account to access the database.<br /><br /> On one computer, the application worksfine and both users can connect to<br /> the database using SSL.<br /><br /> On the other computer, both users failto connect to a database and the “no<br /> pg_hba.cong entry for host …” error is returned from PQerrorMessage.<br /><br/> All users can connect to the database on both computers using either psql,<br /> or pgAdmin.<br /><br /> Code Syntax:<br/><br /> Development is done on Mac with code written in Cocoa / Objectice-C, and C<br /> language.<br /><br />I am using the following include file:<br /><br /> #include <libpq-fe.h><br /><br /> I create the connection stringusing the following format. Then<br /><br /> NSString *connstr = [NSString stringWithFormat:@"sslmode='prefer' host='%@'<br/> port='%@' user='%@' password='%@' dbname='%@'", host, port, user, pass, db];<br /><br /> const char *conninfo= [connstr cStringUsingEncoding:NSASCIIStringEncoding];<br /><br /> PGconn *conn = PQconnectdb (conninfo);<br /><br/> /*<br /> conninfo = "sslmode='prefer' host='<a href="http://host.domain.com" rel="noreferrer" target="_blank">host.domain.com</a>'port='' user='username'<br /> password='password' dbname='database'"<br /> */<br /><br/> if (PQstatus(conn) != CONNECTION_OK)<br /> {<br /> // no pg_hba.conf entry for host error occurs<br /> }<br/><br /><br /> Configuration Settings:<br /><br /> pg_hba.conf contains<br /><br /> hostssl all all <ahref="http://0.0.0.0/0" rel="noreferrer" target="_blank">0.0.0.0/0</a> md5<br /><br /><br /> postgresql.conf contains<br/><br /> ssl on on<br /> ssl_cert_file server.crt server.crt<br /> ssl_key_file server.key server.key<br /><br /><br /> Since I able to connect to both computers using psql and pgAdmin,I suspect<br /> the problem is with the connection within the Application. The connection<br /> string in the applicationis correct, and I verified the user name and<br /> password being passed properly.<br /></blockquote><br /> Haveyou verified the other parameters, in particular the port. I am suspicious of the port='' in the above. The error ispretty straight forward, the correct host name is not being found in the the pg_hba.conf file. That means either the hostname is not specified correctly in the file or you are connecting to a different cluster(port) then you think are andare picking up a different pg_hba.conf.<br /><br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px#ccc solid;padding-left:1ex"><br /><br /><br /> --<br /> View this message in context: <a href="http://postgresql.nabble.com/Connection-to-database-failed-FATAL-no-pg-hba-conf-entry-for-host-tp5898099.html" rel="noreferrer" target="_blank">http://postgresql.nabble.com/Connection-to-database-failed-FATAL-no-pg-hba-conf-entry-for-host-tp5898099.html</a><br />Sent from the PostgreSQL - sql mailing list archive at Nabble.com.<br /><br /><br /></blockquote><br /><br /> -- <br />Adrian Klaver<br /><a href="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a><br /><br /><br/> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"rel="noreferrer" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></blockquote></div>
On 04/12/2016 04:19 PM, Chris wrote: > Thanks for your reply Adrian. > > I thought about the port. The server name is correct, and I provided the > default port 5432 in place of the empty string. And it still did not work. > We are using the default port settings for both SSL and non-SSL connections. > > I also tried using the PQconnectdbParams (see below) in place of > PQconnectdb. Using exactly the same code as in the Open Source with few > minor modifications. Connecting using this method still fails with the > following error message... > > Connection to database failed: FATAL: no pg_hba.conf entry for host > "12.224.126.19", user "james", database "database", SSL off So is it possible to show the complete pg_hba.conf? > > Now I am trying to figure out why psql works with the code below, but the > Application that I am creating does not. I checked that the encoding > NSASCIIStringEncoding is converted properly, I even hard coded the > parameters to no avail. Would there be any compiler setting I need to set in > XCode? > > I am building on Yosemite. The application is connecting properly on several > Mac computers running both Yosemite and El Capitan. We only have three > computers where the application fails to connect, each being either Yosemite > or El Capitan. Each have some type of SSL network connection, otherwise spql > would not succeed. > > Strange part about this problem is that not every computer is having this > connection issue, and the computers that are failing, work perfectly fine > with psql and pgAdmin. > > bool notty = (!isatty(fileno(stdin)) || !isatty(fileno(stdout))); > > #define PARAMS_ARRAY_SIZE 7 > const char **keywords = malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords)); > const char **values = malloc(PARAMS_ARRAY_SIZE * sizeof(*values)); > > keywords[0] = "host"; > values[0] = [host cStringUsingEncoding:NSASCIIStringEncoding]; > keywords[1] = "port"; > > if ([port isEqualToString:@""]) { > values[1] = "5432"; > } > else{ > values[1] = [port cStringUsingEncoding:NSASCIIStringEncoding]; > } > keywords[2] = "user"; > values[2] = [user cStringUsingEncoding:NSASCIIStringEncoding]; > keywords[3] = "password"; > values[3] = [pass cStringUsingEncoding:NSASCIIStringEncoding]; > keywords[4] = "dbname"; > values[4] = [db cStringUsingEncoding:NSASCIIStringEncoding]; > keywords[5] = "client_encoding"; > values[5] = (notty || getenv("PGCLIENTENCODING")) ? NULL : "auto"; > keywords[6] = NULL; > values[6] = NULL; > > //new_pass = false; > conn = PQconnectdbParams(keywords, values, true); > free(keywords); > free(values); > > > > -- > View this message in context: http://postgresql.nabble.com/Connection-to-database-failed-FATAL-no-pg-hba-conf-entry-for-host-tp5898099p5898337.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian and Dhaval: Here is the pg_hba.config and postgresql configuration settings. listen_addresses is set to * <http://postgresql.nabble.com/file/n5898467/pg_hba.jpg> <http://postgresql.nabble.com/file/n5898467/postgresql.jpg> -- View this message in context: http://postgresql.nabble.com/Connection-to-database-failed-FATAL-no-pg-hba-conf-entry-for-host-tp5898099p5898467.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.