P3 connection error
Locked
 
|
Hi,
I have a PostgreSQL database on a remote host which I want to access using P3. I do have a username and a password and can connect via SQuirreL and DBeaver. Both use a JDBC driver. However, when I try to access it via Pharo and P3 I get the infamous "no pg_hba.conf entry for host <my IP address>“ error. The thing is that I cannot change the pg_hba.conf file as the server does not belong to me. I wonder why the JDBC driver does not run into this problem when connecting from my IP address? It must do something differently. As I have just started playing with P3 (and PostgreSQL to be honest) I may be missing something fundamental. Using #setSSL did not help, by the way. Any other ideas I could try? Cheers, Bernhard |
Re: P3 connection error
|
|
Hi Bernard,
> On 18 May 2021, at 18:40, Bernhard Pieber <[hidden email]> wrote: > > Hi, > > I have a PostgreSQL database on a remote host which I want to access using P3. I do have a username and a password and can connect via SQuirreL and DBeaver. Both use a JDBC driver. However, when I try to access it via Pharo and P3 I get the infamous "no pg_hba.conf entry for host <my IP address>“ error. The thing is that I cannot change the pg_hba.conf file as the server does not belong to me. I wonder why the JDBC driver does not run into this problem when connecting from my IP address? It must do something differently. > > As I have just started playing with P3 (and PostgreSQL to be honest) I may be missing something fundamental. Using #setSSL did not help, by the way. Any other ideas I could try? > > Cheers, > Bernhard This is an interesting problem: to do a remote, over the network, connection this has to be enabled in PostegreSQL in the pg_hba.conf. But since other clients can connect, it would help if you could give me more details regarding their connection settings. I know this could include confidential information, so be careful what you post. You could also try to connect using the command line psql client, from your machine. Sven |
Re: P3 connection error
|
|
(CC-ing the list)
Hmm, that should just work. Are there any special characters in the username, password or host (non-ascii, URL unsafe characters) ? You could try the explicit init form P3Client new host: 'host'; user: 'user'; password: 'password'; database: 'database'; yourself. > On 18 May 2021, at 19:47, Bernhard Pieber <[hidden email]> wrote: > > Hi Sven, > > Thank you for the fast response. > > Yes, I can connect using the psql client using this command line: > C:\PostgreSQL\12\bin\psql.exe -h host -U user -d database -p 5432 > > I have to enter the password in the command prompt. > > The driver URL in SQuirreL is: > jdbc:postgresql://host:5432/database > > User name and password are separate text fields. > > pgAdmin also works, by the way. > > In P3 I use the long form: > P3Client new url: 'psql://user:password@host:5432/database'. > > Cheers, > Bernhard > >> Am 18.05.2021 um 19:16 schrieb Sven Van Caekenberghe <[hidden email]>: >> >> >> Hi Bernard, >> >>> On 18 May 2021, at 18:40, Bernhard Pieber <[hidden email]> wrote: >>> >>> Hi, >>> >>> I have a PostgreSQL database on a remote host which I want to access using P3. I do have a username and a password and can connect via SQuirreL and DBeaver. Both use a JDBC driver. However, when I try to access it via Pharo and P3 I get the infamous "no pg_hba.conf entry for host <my IP address>“ error. The thing is that I cannot change the pg_hba.conf file as the server does not belong to me. I wonder why the JDBC driver does not run into this problem when connecting from my IP address? It must do something differently. >>> >>> As I have just started playing with P3 (and PostgreSQL to be honest) I may be missing something fundamental. Using #setSSL did not help, by the way. Any other ideas I could try? >>> >>> Cheers, >>> Bernhard >> >> This is an interesting problem: to do a remote, over the network, connection this has to be enabled in PostegreSQL in the pg_hba.conf. But since other clients can connect, it would help if you could give me more details regarding their connection settings. I know this could include confidential information, so be careful what you post. >> >> You could also try to connect using the command line psql client, from your machine. >> >> Sven > |
|
|
Hi Sven,
The explicit form does not work either. All the fields contain safe characters. However, I just found out that I can connect to three other hosts. All four hosts should have the same settings (databases and users), and just one of them does not work. So there must be a difference in the settings after all. I noticed that the error message ends with "SSL off“. So maybe the problem is related to SSL after all. Just calling #setSSL does not help, though. I get SSL Exception: connect failed [code:-5]. Maybe I am missing some certificates? When I connect with psql, three of the four hosts show this message: psql (12.5, Server 12.6) SSL-Verbindung (Protokoll: TLSv1.2, Verschlüsselungsmethode: ECDHE-ECDSA-AES128-GCM-SHA256, Bits: 128, Komprimierung: aus) The fourth does not mention SSL. However, only one of the three hosts that show SSL does not work. Really strange. (All of the four hosts work with psql, SQuirreL and DBeaver.) Thanks for your support! Bernhard > Am 18.05.2021 um 20:16 schrieb Sven Van Caekenberghe <[hidden email]>: > > > (CC-ing the list) > > Hmm, that should just work. > > Are there any special characters in the username, password or host (non-ascii, URL unsafe characters) ? > > You could try the explicit init form > > P3Client new host: 'host'; user: 'user'; password: 'password'; database: 'database'; yourself. > >> On 18 May 2021, at 19:47, Bernhard Pieber <[hidden email]> wrote: >> >> Hi Sven, >> >> Thank you for the fast response. >> >> Yes, I can connect using the psql client using this command line: >> C:\PostgreSQL\12\bin\psql.exe -h host -U user -d database -p 5432 >> >> I have to enter the password in the command prompt. >> >> The driver URL in SQuirreL is: >> jdbc:postgresql://host:5432/database >> >> User name and password are separate text fields. >> >> pgAdmin also works, by the way. >> >> In P3 I use the long form: >> P3Client new url: 'psql://user:password@host:5432/database'. >> >> Cheers, >> Bernhard >> >>> Am 18.05.2021 um 19:16 schrieb Sven Van Caekenberghe <[hidden email]>: >>> >>> >>> Hi Bernard, >>> >>>> On 18 May 2021, at 18:40, Bernhard Pieber <[hidden email]> wrote: >>>> >>>> Hi, >>>> >>>> I have a PostgreSQL database on a remote host which I want to access using P3. I do have a username and a password and can connect via SQuirreL and DBeaver. Both use a JDBC driver. However, when I try to access it via Pharo and P3 I get the infamous "no pg_hba.conf entry for host <my IP address>“ error. The thing is that I cannot change the pg_hba.conf file as the server does not belong to me. I wonder why the JDBC driver does not run into this problem when connecting from my IP address? It must do something differently. >>>> >>>> As I have just started playing with P3 (and PostgreSQL to be honest) I may be missing something fundamental. Using #setSSL did not help, by the way. Any other ideas I could try? >>>> >>>> Cheers, >>>> Bernhard >>> >>> This is an interesting problem: to do a remote, over the network, connection this has to be enabled in PostegreSQL in the pg_hba.conf. But since other clients can connect, it would help if you could give me more details regarding their connection settings. I know this could include confidential information, so be careful what you post. >>> >>> You could also try to connect using the command line psql client, from your machine. >>> >>> Sven >> > |
Re: P3 connection error
|
|
Hi,
Since you can connect to 3 of the 4 machines, both over plain and tls, it basically works. You will have to find out what is different in the host configurations of the servers. It could be a certificate issue like you suggest, I don't know. I am guessing you are on Windows ? Tomorrow I will try to test plain and tls connections on my machine. Sven > On 18 May 2021, at 21:22, Bernhard Pieber <[hidden email]> wrote: > > Hi Sven, > > The explicit form does not work either. All the fields contain safe characters. > > However, I just found out that I can connect to three other hosts. All four hosts should have the same settings (databases and users), and just one of them does not work. So there must be a difference in the settings after all. > > I noticed that the error message ends with "SSL off“. So maybe the problem is related to SSL after all. Just calling #setSSL does not help, though. I get SSL Exception: connect failed [code:-5]. Maybe I am missing some certificates? > > When I connect with psql, three of the four hosts show this message: > psql (12.5, Server 12.6) > SSL-Verbindung (Protokoll: TLSv1.2, Verschlüsselungsmethode: ECDHE-ECDSA-AES128-GCM-SHA256, Bits: 128, Komprimierung: aus) > > The fourth does not mention SSL. > > However, only one of the three hosts that show SSL does not work. Really strange. > > (All of the four hosts work with psql, SQuirreL and DBeaver.) > > Thanks for your support! > > Bernhard > >> Am 18.05.2021 um 20:16 schrieb Sven Van Caekenberghe <[hidden email]>: >> >> >> (CC-ing the list) >> >> Hmm, that should just work. >> >> Are there any special characters in the username, password or host (non-ascii, URL unsafe characters) ? >> >> You could try the explicit init form >> >> P3Client new host: 'host'; user: 'user'; password: 'password'; database: 'database'; yourself. >> >>> On 18 May 2021, at 19:47, Bernhard Pieber <[hidden email]> wrote: >>> >>> Hi Sven, >>> >>> Thank you for the fast response. >>> >>> Yes, I can connect using the psql client using this command line: >>> C:\PostgreSQL\12\bin\psql.exe -h host -U user -d database -p 5432 >>> >>> I have to enter the password in the command prompt. >>> >>> The driver URL in SQuirreL is: >>> jdbc:postgresql://host:5432/database >>> >>> User name and password are separate text fields. >>> >>> pgAdmin also works, by the way. >>> >>> In P3 I use the long form: >>> P3Client new url: 'psql://user:password@host:5432/database'. >>> >>> Cheers, >>> Bernhard >>> >>>> Am 18.05.2021 um 19:16 schrieb Sven Van Caekenberghe <[hidden email]>: >>>> >>>> >>>> Hi Bernard, >>>> >>>>> On 18 May 2021, at 18:40, Bernhard Pieber <[hidden email]> wrote: >>>>> >>>>> Hi, >>>>> >>>>> I have a PostgreSQL database on a remote host which I want to access using P3. I do have a username and a password and can connect via SQuirreL and DBeaver. Both use a JDBC driver. However, when I try to access it via Pharo and P3 I get the infamous "no pg_hba.conf entry for host <my IP address>“ error. The thing is that I cannot change the pg_hba.conf file as the server does not belong to me. I wonder why the JDBC driver does not run into this problem when connecting from my IP address? It must do something differently. >>>>> >>>>> As I have just started playing with P3 (and PostgreSQL to be honest) I may be missing something fundamental. Using #setSSL did not help, by the way. Any other ideas I could try? >>>>> >>>>> Cheers, >>>>> Bernhard >>>> >>>> This is an interesting problem: to do a remote, over the network, connection this has to be enabled in PostegreSQL in the pg_hba.conf. But since other clients can connect, it would help if you could give me more details regarding their connection settings. I know this could include confidential information, so be careful what you post. >>>> >>>> You could also try to connect using the command line psql client, from your machine. >>>> >>>> Sven >>> >> > > |
|
|
Yes, I am on Windows 10.
Thanks! Bernhard > Am 18.05.2021 um 22:23 schrieb Sven Van Caekenberghe <[hidden email]>: > > > Hi, > > Since you can connect to 3 of the 4 machines, both over plain and tls, it basically works. > > You will have to find out what is different in the host configurations of the servers. > > It could be a certificate issue like you suggest, I don't know. > > I am guessing you are on Windows ? > > Tomorrow I will try to test plain and tls connections on my machine. > > Sven > >> On 18 May 2021, at 21:22, Bernhard Pieber <[hidden email]> wrote: >> >> Hi Sven, >> >> The explicit form does not work either. All the fields contain safe characters. >> >> However, I just found out that I can connect to three other hosts. All four hosts should have the same settings (databases and users), and just one of them does not work. So there must be a difference in the settings after all. >> >> I noticed that the error message ends with "SSL off“. So maybe the problem is related to SSL after all. Just calling #setSSL does not help, though. I get SSL Exception: connect failed [code:-5]. Maybe I am missing some certificates? >> >> When I connect with psql, three of the four hosts show this message: >> psql (12.5, Server 12.6) >> SSL-Verbindung (Protokoll: TLSv1.2, Verschlüsselungsmethode: ECDHE-ECDSA-AES128-GCM-SHA256, Bits: 128, Komprimierung: aus) >> >> The fourth does not mention SSL. >> >> However, only one of the three hosts that show SSL does not work. Really strange. >> >> (All of the four hosts work with psql, SQuirreL and DBeaver.) >> >> Thanks for your support! >> >> Bernhard >> >>> Am 18.05.2021 um 20:16 schrieb Sven Van Caekenberghe <[hidden email]>: >>> >>> >>> (CC-ing the list) >>> >>> Hmm, that should just work. >>> >>> Are there any special characters in the username, password or host (non-ascii, URL unsafe characters) ? >>> >>> You could try the explicit init form >>> >>> P3Client new host: 'host'; user: 'user'; password: 'password'; database: 'database'; yourself. >>> >>>> On 18 May 2021, at 19:47, Bernhard Pieber <[hidden email]> wrote: >>>> >>>> Hi Sven, >>>> >>>> Thank you for the fast response. >>>> >>>> Yes, I can connect using the psql client using this command line: >>>> C:\PostgreSQL\12\bin\psql.exe -h host -U user -d database -p 5432 >>>> >>>> I have to enter the password in the command prompt. >>>> >>>> The driver URL in SQuirreL is: >>>> jdbc:postgresql://host:5432/database >>>> >>>> User name and password are separate text fields. >>>> >>>> pgAdmin also works, by the way. >>>> >>>> In P3 I use the long form: >>>> P3Client new url: 'psql://user:password@host:5432/database'. >>>> >>>> Cheers, >>>> Bernhard >>>> >>>>> Am 18.05.2021 um 19:16 schrieb Sven Van Caekenberghe <[hidden email]>: >>>>> >>>>> >>>>> Hi Bernard, >>>>> >>>>>> On 18 May 2021, at 18:40, Bernhard Pieber <[hidden email]> wrote: >>>>>> >>>>>> Hi, >>>>>> >>>>>> I have a PostgreSQL database on a remote host which I want to access using P3. I do have a username and a password and can connect via SQuirreL and DBeaver. Both use a JDBC driver. However, when I try to access it via Pharo and P3 I get the infamous "no pg_hba.conf entry for host <my IP address>“ error. The thing is that I cannot change the pg_hba.conf file as the server does not belong to me. I wonder why the JDBC driver does not run into this problem when connecting from my IP address? It must do something differently. >>>>>> >>>>>> As I have just started playing with P3 (and PostgreSQL to be honest) I may be missing something fundamental. Using #setSSL did not help, by the way. Any other ideas I could try? >>>>>> >>>>>> Cheers, >>>>>> Bernhard >>>>> >>>>> This is an interesting problem: to do a remote, over the network, connection this has to be enabled in PostegreSQL in the pg_hba.conf. But since other clients can connect, it would help if you could give me more details regarding their connection settings. I know this could include confidential information, so be careful what you post. >>>>> >>>>> You could also try to connect using the command line psql client, from your machine. >>>>> >>>>> Sven >>>> >>> >> >> > |
Re: P3 connection error
|
|
In reply to this post by Sven Van Caekenberghe-2
Bernard,
> On 18 May 2021, at 22:23, Sven Van Caekenberghe <[hidden email]> wrote: > > Tomorrow I will try to test plain and tls connections on my machine. It took some trial & error with my local PostgreSQL server (on my mac I use https://postgresapp.com although I normally use the regular Ubuntu versions on servers). From a high level (P3, Pharo 7, macOS), I can do the following for a plain connection: P3LogEvent logToTranscript. (P3Client new url: 'psql://sven@localhost') in: [ :client | [ client isWorking ] ensure: [ client close ] ]. This returns true and shows the following in the Transcript: 2021-05-19 13:06:44 001 [P3] 68689 #Connect psql://sven@localhost:5432 Trust 2021-05-19 13:06:44 002 [P3] 68689 #Query SELECT 721 AS N 2021-05-19 13:06:44 003 [P3] 68689 #Result SELECT 1, 1 record, 1 colum, 0 ms 2021-05-19 13:06:44 004 [P3] 68689 #Close For a secure connection, either of the following work: (P3Client new url: 'psql://sven@localhost?sslmode=require') in: [ :client | [ client isWorking ] ensure: [ client close ] ]. (P3Client new url: 'psql://sven@localhost') in: [ :client | [ client connectSSL; isWorking ] ensure: [ client close ] ]. These both return true and give the same output: 2021-05-19 13:07:39 005 [P3] 68701 #Connect psql://sven@localhost:5432?sslmode=require Trust 2021-05-19 13:07:39 006 [P3] 68701 #Query SELECT 316 AS N 2021-05-19 13:07:39 007 [P3] 68701 #Result SELECT 1, 1 record, 1 colum, 0 ms 2021-05-19 13:07:39 008 [P3] 68701 #Close This is a local login without a password. Now, since you both got plain and secure connections working, there is no fundamental problem for either type, or the rest of your setup/situation. You seem to have hit some edge, resulting from a small configuration difference in one of the server you are trying to log in to. You say you get a "SSL Exception: connect failed [code:-5]" - we have to find out what the -5 means. As you can see in ZdcPluginSSLSession>>#primitiveSSL:connect:startingAt:count:into: these errors are not specified or explained. We will have to look into the SSL plugin C code for that, and maybe/probably consult the Windows documentation for the system calls used. Apart from certificate issues, it could also be a cipher issue (as in something specific is required but not available). Sven PS: to configure the server (version 13.2), I added the following to postgres.conf ssl = on ssl_cert_file = '/Users/sven/.ssh/ssl-cert-snakeoil.pem' ssl_key_file = '/Users/sven/.ssh/ssl-cert-snakeoil.key' the ssl-cert-snakeoil I took from a standard Ubuntu postgreSQL installation. In pg_hba.conf the first field allows you to control access over different connection types: - host = both plain & secure - hostssl = only secure - hostnossl = only plain |
«
Return to Pharo Smalltalk Users
|
1 view|%1 views
| Free forum by Nabble | Edit this page |