How debug it?  #1

Activity

1. impala454 commented on Oct 31, 2018

   

   impala454

   on Oct 31, 2018

   More actions

   I realize this is an old issue, but just wanted to put some more detail to it. I am also seeing the Cannot connect to server response. In further researching it seems to have something to do with the pgsql startup packet generation. My server has the error in its log of invalid length of startup packet, which is from here. I can't seem to find documentation on what that startup packet is supposed to look like, so it's hard to continue to debug, but any guidance on why the @ethanak's code sets up the startup packet the way it does would be helpful (e.g. why is it doing the memcpy(packet,"\0\003\0\0", 4); at the beginning of PGconnection::build_startup_packet()?

   I suspect this could just be a postgresql version thing, but not sure. I'm running 11.0 on Windows Server 2016. It'd be great to get this going instead of having to set up a separate webserver just to handle incoming arduino database writes.
2. ethanak commented on Nov 1, 2018

   

   ethanak

   on Nov 1, 2018

   Last edited by ethanak

   Owner

   More actions

   "Cannot connect to server" is set if TCP connection failed, so no valid or invalid packet is sent to server. Please check your server configuration (line 'listen_addresses_' in postgresql.conf)
   This magic "\0\003\0\0" simple means "protocol version 3".
   Please look at libpq sources: https://github.com/postgres/postgres/tree/master/src/interfaces/libpq - it was base for my code.
3. impala454 commented on Nov 1, 2018

   

   impala454

   on Nov 1, 2018

   More actions

   @ethanak thanks for pointing me in the right direction! I do know that packets are getting to my server, hence the server log showing the invalid length of startup packet. I'll check out that libpq interface and see if I can update this.
4. impala454 commented on Nov 3, 2018

   

   impala454

   on Nov 3, 2018

   More actions

   I ended up having multiple issues, only one of which was code. I ended up resolving them all so I'll describe them just in case it helps someone else:

   1. My arduino was making a faulty connection to my router due to a duplicate IP. It was connecting but ghosting another machine's IP, so Tx worked but Rx didn't, which is why I was curiously able to send packets to the server but still not log in. My fix for this was to set the arduino to DHCP by only passing the MAC address to the Ethernet.begin() function. I can change this in the example code if @ethanak thinks it's a good idea.

   2. My second issue was actually a code problem which I'll submit a PR for soon. The pqPacketSend() function is doing a dangerous shift of a 32 bit integer into an 8 bit integer without masking beforehand. I finally wiresharked my server to find that some random 0xFFs were shifted into this value, resulting in a invalid length of startup packet error on the server side. The fix is to change the shifts to mask the buf_len variable before shifting. E.g. change:

      *start++ = ((buf_len + 4) >> 24) & 0xff;

      to

      *start++ = ((buf_len + 4) & 0xff000000) >> 24;

      And similar for the other shifts. I'll submit a PR soon to fix this.

   3. Finally, I was unable to fit the code with the MD5 auth on my arduino, so I ran without it, only to realize my server wasn't configured to accept password authentication. The fix was to go into the server's pg_hba.conf file and change the METHOD part of my access control line from md5 to password. Note that this will end up sending your password in plain text over the wire. I'm ok with it because I only allow connections on my internal network.

   Thanks for your help @ethanak , this is a great library!
5. SaudAlfurhud commented on Mar 13, 2021

   

   SaudAlfurhud

   on Mar 13, 2021

   More actions

   I ended up having multiple issues, only one of which was code. I ended up resolving them all so I'll describe them just in case it helps someone else:

   1. My arduino was making a faulty connection to my router due to a duplicate IP.  It was connecting but ghosting another machine's IP, so Tx worked but Rx didn't, which is why I was curiously able to send packets to the server but still not log in.  My fix for this was to set the arduino to DHCP by only passing the MAC address to the `Ethernet.begin()` function.  I can change this in the example code if @ethanak thinks it's a good idea.
   
   2. My second issue was actually a code problem which I'll submit a PR for soon.  The `pqPacketSend()` function is doing a dangerous [shift of a 32 bit integer into an 8 bit integer without masking beforehand](https://github.com/ethanak/SimplePgSQL/blob/master/SimplePgSQL.cpp#L576).  I finally wiresharked my server to find that some random `0xFF`s were shifted into this value, resulting in a `invalid length of startup packet` error on the server side.  The fix is to change the shifts to mask the `buf_len` variable before shifting.  E.g. change:
      `*start++ = ((buf_len + 4) >> 24) & 0xff;`
      to
      `*start++ = ((buf_len + 4) & 0xff000000) >> 24;`
      And similar for the other shifts.  I'll submit a PR soon to fix this.
   
   3. Finally, I was unable to fit the code with the MD5 auth on my arduino, so I ran without it, only to realize my server wasn't configured to accept password authentication.  The fix was to go into the server's `pg_hba.conf` file and change the `METHOD` part of my access control line from `md5` to `password`.  Note that this will end up sending your password in plain text over the wire.  I'm ok with it because I only allow connections on my internal network.
   

   Thanks for your help @ethanak , this is a great library!

   Thank you for your comment,
   I faced the same error Cannot connect to server, is your solution in the server side or in the code and header? because I can't find the shifting line you refare to.

   Thank you
6. impala454 commented on Apr 2, 2021

   

   impala454

   on Apr 2, 2021

   More actions

   Hey @SaudAlfurhud this was the PR but it never got merged.