CODEWITHSUNDEEP
postgresqlreverse-proxypgadmin
user23917815
user23917815

Reputation: 1

PGAdmin4 configured behind a reverse proxy but unable to connect to Postgresql server

I installed Posgresql 16 server on a Debian 11 host that I access through SSH, and all works as expected when I use PSQL cli. Then I installed and configured PGAdmin4 Web, and I configured NGINX with reverse proxy so I can access to PGAdmin4 web interface from a browser.

The firewall is correctly configured.

The NGINX directives are as follow:

    location /pgadmin4/ {
         proxy_pass http://127.0.0.1:80/pgadmin4/;
         proxy_redirect off;
         proxy_set_header Host $host;
         proxy_set_header X-Real-IP $remote_addr;
         proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
         proxy_set_header X-Forwarded-Proto $scheme;

         proxy_set_header X-Script-Name /pgadmin4;
         proxy_buffering off;
    }

I can access to PGAdmin4 web interface through an URL: https://<mydomain_name>/pgadmin4/

The problem is that I am unable to connect to the Postgresql server through the Server Dialog, I systematically get an error message: Unable to connect to server: connection timeout expired.

In file /usr/pgadmin4/web/config.py, I replaced DEFAULT_SERVER = '127.0.0.1' by DEFAULT_SERVER = '0.0.0.0'.

In file /etc/postgresql/16/main/pg_hba.conf, I added the below line: host all all 0.0.0.0/0 md5

In the PGAdmin4 Dialog box I configured the following options (let's assume that the host name is example.tld):

Connection tab:

SSH Tunnel tab:

I also created a new database (bookstore) with that I can access directly under user 'pgadmin' from a shell with the below command, and modified the connection tab accordingly:

psql -U pgadmin -d bookstore -h 127.0.0.1 -p 5432 -W

I read countless documentation online but still no luck.

I am stuck and any help would be appreciated.

I tried the configuration with different databases and users in the Connection tab where I have no issue with PSQL. When I try to use the port 5432, I immediately get an error message, see below: Cannot use port 5432 because behind a reverse proxy on port 80

In the SSH Tunnel tab, when I use a non-existent user (e.g. test) or a wrong password, I have a different error message:Error when a wrong user or wrong password is used

Upvotes: 0

Views: 778

Answers (1)

Eric
Eric

Reputation: 1259

The default for postgres CLI tools is to use the local class of connections, which is configured in /etc/postgres/16/pg_hba.conf, the default being:

...
# Database administrative login by Unix domain socket
local   all             postgres                                peer
...
# "local" is for Unix domain socket connections only
local   all             all                                     peer
...

local sockets are unix sockets.

Because you said that can connect using psql CLI, and assuming you haven't provided a host via psql -h HOST, it means your postgres server is configured (in pg_hba.conf) to allow connections via unix sockets. Consequently, you should be able to tell pgadmin to use the local connection too, by using the unix socket path as hostname, and disabing SSH tunnelling (SSH tunnelling is only useful when pgadmin and postgres server are on separate hosts)

So look at pg_hba.conf and figure out what is enabled.

If you want to allow pgadmin to connect via localhost then you need to add a line like this to pg_hba.conf:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

I've listed md5 but you'd better use scram-sha-256.

There are also other postgresql configuration files which will enable unix sockets or specify/restrict the IP addresses ranges to listen on.

Upvotes: 0

Related Questions