0

I am struggling to get a postgresql db to play nice.

I'm running a vps server with ubuntu 22.04 and openlitespeed.

I have checked: text and text and text but without success. I have allowed the port in ufw:

stiofan@stiofaniridian:~$ sudo ufw status numbered
Status: active

     To                         Action      From
     --                         ------      ----
[ 1] 22                         ALLOW IN    Anywhere
[ 2] 80                         ALLOW IN    Anywhere
[ 3] 443                        ALLOW IN    Anywhere
[ 4] 7080/tcp                   ALLOW IN    Anywhere
[ 5] 5432/tcp                   ALLOW IN    Anywhere
[ 6] 22 (v6)                    ALLOW IN    Anywhere (v6)
[ 7] 80 (v6)                    ALLOW IN    Anywhere (v6)
[ 8] 443 (v6)                   ALLOW IN    Anywhere (v6)
[ 9] 7080/tcp (v6)              ALLOW IN    Anywhere (v6)
[10] 5432/tcp (v6)              ALLOW IN    Anywhere (v6)

and altered postgresql.conf to include:

listen_addresses = '*'      # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
port = 5432             # (change requires restart)
max_connections = 100           # (change requires restart)

as well as pg_hba.conf to include:

host    all     all     0.0.0.0/0       md5
host    all     all     149.XXX.XXX.XXX/32  scram-sha-256

(the "XXX"'s are in the file as the full IP address)

I have also altered the environment file to include:

PGOPTIONS="-i"

in both /etc/postgresql/14/main/ directory as well as the environment file located at /etc/ directory.

None of it has worked. when I run nmap -a|grep 5432 nothing is returned and lsop doesn't contain any reference to postgresql. I have run sudo systemctl is-active postgresql and get active as the response.

Further searching gets me back the same articles, etc.

Does anyone know where I've gone wrong?

edit In response to pg_lsclusters I am getting:

:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
14  main    5432 down   postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log

and as for nmap, I am running it from an ssh shell with a sudo user against the IP address of the server (149.XXX.XXX.XXX). As far as I know ufw is the only firewall involved (I haven't installed or enabled any others explicitly).

** edit #2 ** I hadn't known about pg_clusters before and took the opportunity to examin the postgresql log file, which may be of use:

2023-06-19 15:34:52.297 UTC [5615] LOG:  starting PostgreSQL 14.8 (Ubuntu 14.8-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04.1) 11.3.0, 64-bit
2023-06-19 15:34:52.297 UTC [5615] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-06-19 15:34:52.298 UTC [5615] LOG:  listening on IPv6 address "::", port 5432
2023-06-19 15:34:52.298 UTC [5615] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-06-19 15:34:52.299 UTC [5615] LOG:  invalid CIDR mask in address "149.XXX.XXX.XXX/5432"
2023-06-19 15:34:52.299 UTC [5615] CONTEXT:  line 106 of configuration file "/etc/postgresql/14/main/pg_hba.conf"
2023-06-19 15:34:52.299 UTC [5615] FATAL:  could not load pg_hba.conf
2023-06-19 15:34:52.300 UTC [5615] LOG:  database system is shut down
pg_ctl: could not start server

the CIDR Mask has been changed to '/32' and postgresql restarted, but the same log entry appears after the changes/restart.

Stiofán
  • 1
  • 3
  • 1) On the server what does `pg_lscluster` show? 2) Where are you running `nmap` and against what? 3) Is there another firewall between you and the server? **Add answers as update to question**. – Adrian Klaver Jun 19 '23 at 16:45
  • @AdrianKlaver Thanks for your interest, I have updated the question with the answers you requested as well as including a potentially informative log entry. I'm new to vps' and had never even heard of a CIDR Mask before... – Stiofán Jun 19 '23 at 17:32
  • 1) The server is not running which explains why you cannot connect to `5432` 2) This `149.XXX.XXX.XXX/5432` is at odds with this `host all all 149.XXX.XXX.XXX/32 scram-sha-256`. Best bet line 106 is an additional entry. 3) Read [pg_hba.conf](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html) for more information on CIDR masks. – Adrian Klaver Jun 19 '23 at 17:44
  • @AdrianKlaver Line 106 is the one which I changed to have the CIDR mask from 5432 (initially I thought this was a port number) to 32 (which matches most of the other entries). I've restarted the service since the change, but it doesn't seem to have picked it up. Is there a way to 'flush' it or force it to pick up the newer entry? 3) Thanks for the link, I'm reading up on CIDR masks now – Stiofán Jun 19 '23 at 17:52
  • 1) Restart what service? 2) Are you sure you are changing the correct `pg_hba.conf' file? – Adrian Klaver Jun 19 '23 at 18:08
  • @AdrianKlaver 1) The postgresql service (using sudo /etc/init.d/postgresql restart) 2) I changed the pg_hba.conf located in the /etc/postgresql/14/main directory (where the postgresql.conf and environment files are. – Stiofán Jun 19 '23 at 18:14
  • To restart the service the preferred methods are 1) ` sudo systemctl restart postgresql@14-main.service` or 2) `sudo pg_ctlcluster restart 14 main` – Adrian Klaver Jun 19 '23 at 18:25
  • @AdrianKlaver Thanks for all your input, restarting as per your suggestion (#1) revealed another issue, the PGOPTIONS="-i" I had entered into the environment file was another sticking point. removed that and now the service is up and running and responding (not working yet, but that's another issue for me to play with). If you can convert your comments to an answer, I will gratefully accept it as the solution. Thanks again – Stiofán Jun 19 '23 at 19:00

0 Answers0