Project

General

Profile

Feature #10151

PG ADMIN installation on server

Added by Giulio Di Anastasio over 1 year ago. Updated over 1 year ago.

Status:
Resolved
Priority:
Normal
Assignee:
Start date:
20/05/2020
Due date:
% Done:

0%

Close

Description

It would be good to have pgadmin installed on the server so that we can also connect from out-of-office

History

#1 Updated by Philippe May over 1 year ago

Make sure system (gisdb.csr.av) is updated to bullseye.

Add the apt repo:

root@gisdb:/etc/apt/sources.list.d# cat pgdg.list 
deb http://apt.postgresql.org/pub/repos/apt/ bullseye-pgdg main

Install packages:

root@gisdb:/var/log/nginx# apt install pgadmin4 gunicorn nginx

Configure nginx:

root@gisdb:/var/log/nginx# cat /etc/nginx/sites-enabled/pgadmin 
server {
    listen 9879;

    location / {
        include proxy_params;
        proxy_pass http://unix:/run/pgadmin.sock;
    }
}

Configure socket and service:

root@gisdb:/var/log/nginx# cat /etc/systemd/system/pgadmin.s*
[Unit]
Description=Gunicorn daemon (pgadmin)
Requires=pgadmin.socket
After=network.target

[Service]
Type=notify
# the specific user that our service will run as
User=www-data
Group=www-data
# another option for an even more restricted service is
# DynamicUser=yes
# see http://0pointer.net/blog/dynamic-users-with-systemd.html
RuntimeDirectory=gunicorn
WorkingDirectory=/usr/share/pgadmin4/web
ExecStart=/usr/bin/gunicorn --bind=unix:/run/pgadmin.sock --workers=1 --threads=5 pgAdmin4:app
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=mixed
TimeoutStopSec=5
PrivateTmp=true

[Install]
WantedBy=multi-user.target

[Unit]
Description=gunicorn socket (pgadmin)

[Socket]
ListenStream=/run/pgadmin.sock
# Our service won't need permissions for the socket, since it
# inherits the file descriptor by socket activation
# only the nginx daemon will need access to the socket
User=www-data
# Optionally restrict the socket permissions even more.
# Mode=600

[Install]
WantedBy=sockets.target

Setup pgadmin4:

python3 /usr/share/pgadmin4/web/setup.py

Setup nginx on the main frontend (infra.csr.av), in /etc/nginx/sites-enabled/gisaf:


    ## PgAdmin
    location /browser {
        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 https;
    proxy_redirect off;
    proxy_pass http://gisdb.csr.av:9879/browser;
        proxy_http_version 1.1;
    }

Restart nginx.

The network is bad today (Aurinoco) - so i'll to finish this up later.
Leaving as

#3 Updated by Philippe May over 1 year ago

  • Project changed from Gisaf to GIS

Fix setup nginx on the main frontend (infra.csr.av), in /etc/nginx/sites-enabled/gisaf:

    ## PgAdmin
    location /pgadmin/ {
        proxy_set_header Host $host;
        proxy_set_header X-Script-Name /pgadmin/;
        proxy_set_header X-Scheme $scheme;
        proxy_redirect off;
        proxy_pass http://gisdb.csr.av:9879/;
    }

#4 Updated by Philippe May over 1 year ago

The set up was working, except an annoying small bug: after providing user and password, the browser was redirected to "/" (Gisaf home page), instead of "/pgadmin/browser".

I applied a tweak in the code of PgAdmin, that needs to be re-applied when the Debian package "pgadmin4-common" is updated:

root@gisdb:/usr/share/pgadmin4/web# diff pgadmin/authenticate/__init__.py*
63,64c63
<         ## @Phil: redirect to /pgadmin/browser
<         return flask.redirect(get_post_logout_redirect())
---
>         return flask.redirect('/')

#5 Updated by Philippe May over 1 year ago

  • Status changed from New to Resolved

Closing this ticket: pgAdmin is up and running, available at: https://gis.auroville.org.in/pgadmin/ .

I created users giulio and selvarani (i'll give the passwords orally). We might need to fine tune the usage for this multi-user/server mode of PgAdmin.

Also available in: Atom PDF