Database errors on startup

[nathcoad]

Firstly, thanks for writing and sharing this - it looks like it would be a great way to keep an eye on my network at home.

I just tried deploying this on an Ubuntu 20.04 virtual machine. Docker version is "19.03.13, build 4484c46d9d", and docker-compose is "version 1.22.0, build f46880fe".

Only customisation was to configure the .env file as follows (hostname changed to protect the innocent):

EXTERNAL_HOSTNAME=flows.example.com
DB_DIR=./data/grafolean-db/
DB_NAME=grafolean
DB_USER=grafusr
DB_PASS=randomised-password-here
HTTP_PORT=8002

# Options: full, none or basic (default).
TELEMETRY=basic

The containers all start up successfully but I do note a number of database errors in the logs:

postgres            | 2023-01-19 23:25:59.970 UTC [101] ERROR:  relation "runtime_data" does not exist at character 28
postgres            | 2023-01-19 23:25:59.970 UTC [101] STATEMENT:  SELECT schema_version FROM runtime_data;
postgres            | 2023-01-19 23:25:59.973 UTC [101] ERROR:  relation "runtime_data" does not exist at character 28
postgres            | 2023-01-19 23:25:59.973 UTC [101] STATEMENT:  SELECT schema_version FROM runtime_data;

Loading the actual site I see a message saying "The database is empty and must be initialized before we can proceed." with a button "Create DB". Clicking it does nothing, but in the docker logs I see it corresponds with this entry: grafolean | 192.168.0.1 - - [19/Jan/2023:23:29:07 +0000] "POST /api/admin/migratedb HTTP/1.1" 403 33 "https://flows.example.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36" rt=0.007 urt=0.008

Any ideas?

[grafolean]

Yes! (and thank you for a detailed bug report)

This is probably the same issue that I was unable to reproduce in https://github.com/grafolean/grafolean/issues/30, and I would love to solve it. Same as there, 403 is returned when it shouldn't be and the size of the response is 33 bytes which indicates that the response could be the same.

Is there any chance you could open the developer tools, find this request in the Network tab and send the response body so that we can figure out which part of the code is sending this response?

[nathcoad]

Looking at #30 gave me a clue that maybe CORS was the issue, since I'm running this behind Nginx Proxy Manager. I tried setting the GRAFOLEAN_CORS_DOMAINS value in docker-compose.yml to the same as the EXTERNAL_HOSTNAME in .env and that got me past the database creation part. Now when I created the first user and try to login, I end up in a loop back to the login page.

I checked the developer tools in Chrome, this is the response body that is being received: {"user_id": 1078585794, "session_id": "4b5276a7529e8f8a91b36d1d57aead1ee7aaefc970a6f4eeef21b9fba66d3453", "permissions": [{"id": 1888847861, "resource_prefix": null, "methods": null}]}

I see the following in the docker logs:

grafolean           | 192.168.48.1 - - [20/Jan/2023:09:05:17 +0000] "POST /api/auth/login HTTP/1.1" 200 185 "https://flows.example.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36" rt=0.883 urt=0.880
grafolean           | 10.0.1.254 - - [20/Jan/2023:09:05:17 +0000] "\x16\x03\x01\x02\x00\x01\x00\x01\xFC\x03\x03Im\x11\xA8e4\xA2\xA1o\xA6_\xFC\xB5\x9DQ\xE3\xE2.)?K\xF6\x7FV\x82L\xBC\xDFB)-\x5C \xC3O\x88)tz\x9Cc+\xF0Nm\xFD\x14\xD2U\x1Cf(~\x1E#\xE1N\xCC\x0C" 400 173 "-" "-" rt=0.000 urt=-
grafolean           | 10.0.1.254 - - [20/Jan/2023:09:05:17 +0000] "\x16\x03\x01\x02\x00\x01\x00\x01\xFC\x03\x03\x9E\x11\x8FC\xA0\xA5\xD0Y\x9E\xFB\xA5\x8B\x95|#\x97\xE5d\x94lI9~\xA2\x88\xA3'\x17\xA1\xAA\x04\x88 \xE6)\xAB\xEB\x89\x83\x0E>Vf\x09\x86nv\xFCVa \xC5rJ\x97\xF5\xBA\xEC\xF5\xC3\xBA\xF1\xAB\xA9e\x00 \xAA\xAA\x13\x01\x13\x02\x13\x03\xC0+\xC0/\xC0,\xC00\xCC\xA9\xCC\xA8\xC0\x13\xC0\x14\x00\x9C\x00\x9D\x00/\x005\x01\x00\x01\x93\xCA\xCA\x00\x00\x00\x00\x00\x17\x00\x15\x00\x00\x12flows.coadcorp.com\x00\x17\x00\x00\xFF\x01\x00\x01\x00\x00" 400 173 "-" "-" rt=0.001 urt=-
grafolean           | 10.0.1.254 - - [20/Jan/2023:09:05:17 +0000] "\x16\x03\x01\x02\x00\x01\x00\x01\xFC\x03\x03\x84#r\x92\x22\x9E\xBA\xDD;\x16d\x97qq\x08\xF8\xD9\xB1\xDEHk\x0B\x82D\x12.\x1D\x06L\xE9\x14d \x1D)\xD4z\x95\x13\x17\xFA\x00pip\x02~\x1D\xA3\xE8s\xB2\x85W\x15\x0B\x18\x99+\xEE\x85sW\x9D1\x00 \xAA\xAA\x13\x01\x13\x02\x13\x03\xC0+\xC0/\xC0,\xC00\xCC\xA9\xCC\xA8\xC0\x13\xC0\x14\x00\x9C\x00\x9D\x00/\x005\x01\x00\x01\x93**\x00\x00\x00\x00\x00\x17\x00\x15\x00\x00\x12flows.coadcorp.com\x00\x17\x00\x00\xFF\x01\x00\x01\x00\x00" 400 173 "-" "-" rt=0.001 urt=-
grafolean           | 10.0.1.254 - - [20/Jan/2023:09:05:17 +0000] "\x16\x03\x01\x02\x00\x01\x00\x01\xFC\x03\x03\x88\x11\xB4\x99\x1F\x01\xA2\xE7\xA6n\xEC!\x04\xFD\x8C\x01-S\xA1eF\x11\xC3\x8B\xB5-S\xAFP\x0E\xB1G ;\x81\xF7\xBE\xE8{5\x1A\x9D\x9CZC\xEAlf\xF6\xBF\xC5" 400 173 "-" "-" rt=0.001 urt=-

A little more searching revealed https://serverfault.com/questions/195622/what-is-this-in-error-log-invalid-method-in-request-x16-x03-x01 which I think is basically what is happening here. Nginx Proxy Manager is forcing SSL, but grafolean isn't handling that. If I turn off SSL in Nginx Proxy Manager then the login succeeds.

I would prefer to use SSL though, do you have any suggestions on how to run grafolean behind Nginx where the SSL termination is done by Nginx?

Thanks!

[grafolean]

I tried setting the GRAFOLEAN_CORS_DOMAINS value in docker-compose.yml to the same as the EXTERNAL_HOSTNAME in .env and that got me past the database creation part.

Good to hear that! Though I would be curious how you have setup the system, because it should have warned you that the CORS settings are incorrect and suggest the correct ones. Can you maybe share the details? If I can replicate this setup, I can maybe prepare a better installation guide (or hints along the way) to help others.

That said, it is not completely clear to me what the configuration is that you would like to achieve? Is it one of these two?

1. [browser] -- HTTPS -- [Nginx] -- HTTPS -- [Grafolean]
2. [browser] -- HTTPS -- [Nginx] -- HTTP --- [Grafolean]

Grafolean should be able to handle both, though the first one doesn't make much sense, unless you would like to have communication in your local network encrypted?

Anyway, for the first one, Grafolean needs to have access to valid certificates that some external process is updating, see this HOWTO for more details.

For the second one, you need to make sure that Nginx communicates with Grafolean on port 80 using HTTP (looking at the logs it looks like it is using HTTPS).

[nathcoad]

I'm trying to achieve the second configuration - as you say, the first one doesn't make a lot of sense.

Definitely Nginx can communicate with Grafolean on the port that I specify in .env (ie, port 8002). If I configure Nginx to redirect http to https, then I get those log entries that start with "\x16\x03\x01\x02". But if I don't configure Nginx to redirect http to https, then everything works fine.

I'm suspecting it is something to do with the websockets configuration. The login process seems to start off ok but whatever should load after login isn't working.

Here is the actual Nginx configuration that is generated by Nginx Proxy Manager:

server {
  set $forward_scheme http;
  set $server         "10.0.1.25";
  set $port           8002;
  listen 80;
  listen [::]:80;
  listen 443 ssl http2;
  listen [::]:443 ssl http2;

  server_name flows.example.com;

  # Let's Encrypt SSL
  include conf.d/include/letsencrypt-acme-challenge.conf;
  include conf.d/include/ssl-ciphers.conf;
  ssl_certificate /etc/letsencrypt/live/npm-32/fullchain.pem;
  ssl_certificate_key /etc/letsencrypt/live/npm-32/privkey.pem;

  # Force SSL
  if ($scheme = "http") {
          return 301 https://$host$request_uri;
  }

  proxy_set_header Upgrade $http_upgrade;
  proxy_set_header Connection $http_connection;
  proxy_http_version 1.1;

  access_log /data/logs/proxy-host-16_access.log proxy;
  error_log /data/logs/proxy-host-16_error.log warn;

  location / {
    # Access Rules
    allow 10.0.1.254;
    deny all;

    # Access checks must...

    satisfy all;

    proxy_set_header Upgrade $http_upgrade;cd
    proxy_set_header Connection $http_connection;
    proxy_http_version 1.1;

    # Proxy!
    add_header       X-Served-By $host;
    proxy_set_header Host $host;
    proxy_set_header X-Forwarded-Scheme $scheme;
    proxy_set_header X-Forwarded-Proto  $scheme;
    proxy_set_header X-Forwarded-For    $proxy_add_x_forwarded_for;
    proxy_set_header X-Real-IP          $remote_addr;
    proxy_pass       $forward_scheme://$server:$port$request_uri;
  }

  # Custom
  include /data/nginx/custom/server_proxy[.]conf;
}

[nathcoad]

I think I have figured it all out now, thanks to https://stackoverflow.com/questions/36521858/nginx-reverse-proxying-wss-client-sent-invalid-method-while-reading-client-req I noticed the environment variable MQTT_WS_PORT in docker-compose.yml - after I changed that to 443, the login process worked with SSL redirect configured.

Perhaps this is something worth noting in your documentation, for those who are deploying grafolean behind a reverse proxy.

[grafolean]

Thank you for the details, I will try to replicate these issues and improve the documentation. Of course, if you have an idea where / what should be changed in the documentation, I would be happy to accept a pull request.

Anyway, glad to see you managed to solve the problems, I hope you enjoy using Grafolean.

[nathcoad]

I'm no expert when it comes to writing documentation, but maybe you could add a section to https://github.com/grafolean/grafolean#installation that deals with deployment behind a reverse proxy. Maybe something similar to the following.

Reverse Proxy considerations If you plan on deploying Grafolean behind a reverse proxy, there are several settings within docker-compose.yml that you should pay particular attention to.

• Ensure that the FQDN utilised by the reverse proxy is specifed as a permitted domain in the GRAFOLEAN_CORS_DOMAINS environment variable (eg GRAFOLEAN_CORS_DOMAINS=https://www.example.com)
• Ensure that the port the reverse proxy is listening on (probably 443 if your reverse proxy is also configured to handle certificates) is specified in the MQTT_WS_PORT environment variable (eg MQTT_WS_PORT=443)

Please be aware that Grafolean makes use of websockets, so your reverse proxy should be configured to handle websockets.

[grafolean]

That sounds great - I will incorporate it into the documentation, thank you very much!

I now also see where the problem was. MQTT_WS_PORT gets initialized to HTTP_PORT by default, which is not correct - it should be the external port (the same way that MQTT_WS_HOSTNAME is an external hostname).

Thank you for the follow up, I'll fix this. Leaving the issue open until then.