Postgresql on Vultr - unable to connect to database

J

justinnoor

Looking for any guidance on how to troubleshoot a failed Postgresql connection to a remote FreeBSD machine. Don’t have much experience with PostgresQL and a bit rusty on FreeBSD. This is a NodeJS application that connects to a database via TypeORM.

It appears to be a permissions or firewall problem on the remote FreeBSD machine. Previously the application connected flawlessly to a remote Postgres-as-a-service database provided by ElephantSQL. When the attempt was made to manage the database independently it suddenly stopped working.

Code: 
FreeBSD version:

  13.0-RELEASE-p12

Postgresql error:

  ERROR [TypeOrmModule] Unable to connect to the database.

  Error: connect ETIMEDOUT XX.XX.XX.XXX:5432
      at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1187:16)

Steps to create database:

Configure remote machine
  # freebsd-update fetch install
  # reboot
  # freebsd-update install
  # freebsd-version
  # pkg bootstrap
  # pkg install sudo vim postgresql14-server
  # pkg update
  # pkg upgrade

Configure PF
  ext_if="vtnet0"

  set block-policy return
  scrub in on $ext_if all fragment reassemble
  set skip on lo

  block in all
  pass out quick keep state
  antispoof for $ext_if inet
  pass in inet proto tcp from any to any port { 22 5432 } flags S/SA keep state

Enable services
  # sysrc pf_enable="yes"
  # sysrc pflog_enable="yes"
  # sysrc postgres_enable="yes"
  # reboot

Initiate database
  # /usr/local/etc/rc.d/postgresql initdb
  # /usr/local/etc/rc.d/postgresql start

Create postgres database password
  # su - postgres
  $ psql
      \password // Enter password
  $ exit

Postgresql database url
  postgresql://postgres:secret_password@XX.XX.XX.XXX/postgres

Ensure database processes are owned by user postgres
  # top
      output:
    PID USERNAME    THR PRI NICE   SIZE    RES STATE    TIME    WCPU COMMAND
  642 root          1  20    0    11M   948K select   0:10   0.00% devd
  915 root          1  20    0    17M  3560K select   0:10   0.00% ntpd
  401 _pflogd       1  20    0    13M  1980K bpf      0:07   0.00% pflogd
  847 root          1  20    0    13M  1980K select   0:06   0.00% syslogd
14350 postgres      1  20    0   174M    30M select   0:04   0.00% postgres
14355 postgres      1  20    0   174M    30M kqread   0:02   0.00% postgres
14356 postgres      1  20    0    32M    19M kqread   0:02   0.00% postgres
  946 root          1  20    0    20M  5660K select   0:01   0.00% sshd
  928 root          1  20    0    13M  1948K nanslp   0:01   0.00% cron
14353 postgres      1  20    0   174M    30M kqread   0:01   0.00% postgres
14354 postgres      1  20    0   174M    30M kqread   0:01   0.00% postgres
14357 postgres      1  20    0   174M    30M kqread   0:00   0.00% postgres
14352 postgres      1  20    0   174M    30M kqread   0:00   0.00% postgres
18520 root          1  20    0    21M  7796K select   0:00   0.00% sshd
18524 root          1  20    0    13M  2772K wait     0:00   0.00% sh
  395 root          1  20    0    13M  1808K kqread   0:00   0.00% rtsold
  636 _dhcp         1  20    0    13M  2176K select   0:00   0.00% dhclient
  556 root          1  45    0    13M  1984K select   0:00   0.00% dhclient
 
Can’t see any rules in your pf.conf to allow remote connection to Postgres (Oops just noticed the 5432)

Also have you allowed remote connections in the Postgres config

Been a while since I did this myself so rusty on the details but you’ll need firewall rules and appropriate Postgres set-up.
 
Sorry forgot to attach pg_hba.conf and postgresql.conf.

Yes remote connections allowed but still no connection.

/var/db/postgres/data14/pg_hba.conf
Code: 
host     all     all    0.0.0.0/0    scram-sha-256

/var/db/postgres/data14/postgresql.conf
Code: 
listen_addresses='*'
 
Confirm with pg_isready -U postgres -h [I]host[/I] that you can connect to the server.

I think by default the user postgres can't connect via TCP. In my opinion it would be better to create a specific user to connect to the database
 
msplsh said:
Turn off the firewall and check or use pflog since you turned it on.
Click to expand...
Updating the firewall for logging appears to show the packets are passing.

/etc/pf.conf
pass in log (all, to pflog0) inet proto tcp from any to any port { 22 5432 } flags S/SA keep state.
Code: 
# tcpdump -n -e -ttt -i pflog0

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on pflog0, link-type PFLOG (OpenBSD pflog file), capture size 262144 bytes
 00:00:00.000000 rule 5/0(match): pass in on vtnet0: X.X.XXX.XX.59392 > X.X.XXX.XX.5432: Flags [S], seq 519680271, win 64240, options [mss 1440,nop,wscale 8,nop,nop,sackOK], length 0
 00:00:00.000086 rule 5/0(match): pass out on vtnet0: X.X.XXX.XX.5432 > X.X.XXX.XX.59392: Flags [R.], seq 0, ack 519680272, win 0, length 0
 00:00:00.534915 rule 5/0(match): pass in on vtnet0: X.X.XXX.XX.54450 > X.X.XXX.XX.5432: Flags [S], seq 519680271, win 64240, options [mss 1440,nop,wscale 8,nop,nop,sackOK], length 0
 00:00:00.000060 rule 5/0(match): pass out on vtnet0: X.X.XXX.XX.5432 > X.X.XXX.XX.54450: Flags [R.], seq 0, ack 519680272, win 0, length 0
 
Criosphinx said:
Confirm with pg_isready -U postgres -h [I]host[/I] that you can connect to the server.

I think by default the user postgres can't connect via TCP. In my opinion it would be better to create a specific user to connect to the database
Click to expand...
# pg_isready -U postgres -h XX.XX.XX.XXX returns 'XX.XX.XX.XXX:5432 - no response'

# pg_isready -U postgres returns '/tmp:5432 - accepting connections'

Creating a new user and database did not work.
 
Also discovered the Vultr cloud firewall was mistakenly running simultaneously with PF. After disabling that still no connection but the error message changed to:
Code: 
Unable to connect to the database. Retrying (5)...
Error: connect ECONNREFUSED XX.XX.XX.XXX:5432
Currently looking into this error.
 
To eliminate any doubts as to whether the NodeJS app was the issue (don't think it is), I created two FreeBSD 13.1 VMs - test0 and test1 - on Vultr and with the exact configuration above. Using test1 as the server and test0 as the client, still no connection. Am I missing a step?

Code: 
root@test0:~ # pg_isready -U postgres -h XXX.XX.XXX.XXX
XXX.XX.XXX.XXX:5432 - no response
 
You might have to reduce it even further and build up layer by layer (unless someone in the know comes along).

So a server VM and a client VM.

No firewall.

Test Postgres connection works client to server.

Turn firewalls on with open rules, still works?

Add smallest chunk of firewall rules and test.

And so on adding more rules until you hit whatever breaks the setup.

Hopefully during one step the lightbulb moment will happen.
 
Also check with sockstat(8) and see if the postgresql server actually listening on your 'external' interface.

Code: 
dice@case:~ % sockstat -l | grep postgres
postgres postgres   2197  6  tcp6   ::1:5432              *:*
postgres postgres   2197  7  tcp4   127.0.0.1:5432        *:*
postgres postgres   2197  8  stream /tmp/.s.PGSQL.5432
You can tell from this output that my postgresql server is only bound to localhost, thus not reachable from other hosts.

On this server it is accessible from other hosts in my network:
Code: 
root@pgsql:~ # sockstat -l | grep postgres
postgres postgres   1260  6  tcp4   192.168.21.20:5432    *:*
postgres postgres   1260  7  stream /tmp/.s.PGSQL.5432
 
Interesting it's working now on a new server. Literally did nothing different in the config files. The only process change was taking extra caution when disabling the Vultr cloud firewall to let PF takeover. This time it was disabled and rebooted twice before attempting to create tables in the database. This is not sustainable for production but we can work on that. I will consolidate my notes and update this post for anyone trying to manage a database on Vultr. Perhaps the title should be renamed sorry about that.

Thanks for all the help - honor and a pleasure to be working on FreeBSD again.

Updated the title to: 'Postgresql on Vultr - unable to connect to database'.
 
Below are the consolidated steps used to deploy a postgresql database on FreeBSD-13.0 using the Vultr cloud platform. Unfortunately the root cause of the connection issues were never confirmed, but they appear to be related to the built-in Vultr cloud firewall, which to my knowledge needs to be disabled to let PF takeover.

You may prefer creating a new user and database instead of using the default postgres user.

Feel free to offer any suggestions on how this process can be improved.

Code: 
Login to your Vultr portal and create a cloud firewall allowing inbound port 22
Deploy a new FreeBSD server with the attached cloud firewall

Login to server
  ssh root@XXX.XXX.XX.XX

Update droplet
  # freebsd-update fetch install
  # reboot
  # freebsd-version
  # pkg bootstrap
  # pkg install -y postgresql14-contrib postgresql14-server sudo vim
  # pkg update
  # pkg upgrade

Configure PF
  # vim /etc/pf.conf

  ext_if="vtnet0"

  set block-policy return
  scrub in on $ext_if all fragment reassemble
  set skip on lo

  block in all
  pass out quick keep state
  antispoof for $ext_if inet
  pass in inet proto tcp from any to any port { 22 5432 } flags S/SA keep state
  // For logging:
  //   pass in log inet proto tcp from any to any port { 22 5432 } flags S/SA keep state

Enable services
  # sysrc pf_enable="yes"
  # sysrc pflog_enable="yes"
  # sysrc postgresql_enable="yes"

Test PF ruleset
  # pfctl -n -f /etc/pf.conf
  # reboot
  // Deactivate cloud firewall so PF can takeover
  // Ensure cloud firewall is deactivated
  // Reboot again
  // This step needs more testing
  # reboot

Initiate database
  # /usr/local/etc/rc.d/postgresql initdb
  # /usr/local/etc/rc.d/postgresql start

Create database password
  # su - postgres
  $ psql
      \password
      \q
  $ exit

Allow remote connections
  # vim /var/db/postgres/data14/pg_hba.conf
    host    all   all   0.0.0.0/0   scram-sha-256

  # vim /var/db/postgres/data14/postgresql.conf
    listen_addresses='*'

Restart database server
  # service postgresql restart
  # pg_isready -U postgres -h XXX.XXX.XX.XX

Monitor traffic if pflog is active (optional)
  # tcpdump -n -e -ttt -i pflog0
 
The cloud firewall may or may not be necessay. In this case it was assumed if a new server is deployed without an attached cloud firewall, there would be a brief moment when the server is unprotected until PF is enabled - just a precautionary measure. It was also assumed that running the cloud firewall and PF simultaneously would result in strange behavior. Never had the chance to test this with any rigor, but when the cloud firewall was disabled, the database connection succeeded. The cloud firewall can be accessed under 'Settings' in each individual server.
vultr_cloud_firewall.png
 
You must log in or register to reply here.
Back
Top