Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Установка PgAdmin4 + Nginx + uwsgi

Установка PgAdmin4 + Nginx + uwsgi

Установка PgAdmin 4

Для установки PgAdmin 4 через pip необходимо выполнить команды:

sudo apt-get install python3-pip build-essential python3-dev libssl-dev libffi-dev
sudo pip3 install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v6.15/pip/pgadmin4-6.15-py3-none-any.whl

Для инициализации конфигурации в директории /usr/local/lib/python3.5/dist-packages/pgadmin4/ необходимо создать файл config_local.py:

LOG_FILE = '/var/log/pgadmin/pgadmin.log'
SQLITE_PATH = '/var/lib/pgadmin/pgadmin.db'
SESSION_DB_PATH = '/var/lib/pgadmin/sessions'
STORAGE_DIR = '/var/lib/pgadmin/storage'
SERVER_MODE = True
ALLOW_SAVE_PASSWORD = True

и выполнить команду:

python3 setup.py

Установка uwsgi

Для установки uwsgi необходимо выполнить команду:

sudo apt-get install uwsgi-core uwsgi-plugin-python3

Для проверки неоходимо запустить uwsgi командой

sudo uwsgi --http-socket :8000 --plugin python3 --chdir /usr/local/lib/python3.10/dist-packages/pgadmin4/ 
--wsgi-file /usr/local/lib/python3.10/dist-packages/pgadmin4/pgAdmin4.wsgi

и открыть в браузере ссылку http://localhost:8000 Далее необходимо создать сервис uwsgi с использованием systemd. Для этого необходимо в директории /etc/uwsgi создать файл настроект pgadmin.ini:

[uwsgi]
uid             = www-data
gid             = www-data
plugins         = python3
chdir           = /usr/local/lib/python3.10/dist-packages/pgadmin4/
wsgi-file       = /usr/local/lib/python3.10/dist-packages/pgadmin4/pgAdmin4.wsgi
master          = true
processes       = 1
socket          = /tmp/pgadmin.sock
chmod-socket    = 664
vacuum          = true

Для поддержки SSL необходимо добавить:

https = =0,/etc/uwsgi/pgadmin.crt,/etc/uwsgi/pgadmin.key,HIGH,!ca.crt

Самоподписанный сертификат (файлы /etc/uwsgi/pgadmin.crt и /etc/uwsgi/pgadmin.key) можно получить командой:

sudo openssl req -x509 -nodes -newkey rsa:2048 -days 365 -keyout /etc/uwsgi/pgadmin.key -out /etc/uwsgi/pgadmin.crt

В директорию /etc/systemd/system/ добавить файл uwsgi@.service:

[Unit]
Description=uWSGI service unit
After=syslog.target

[Service]
ExecStart=/usr/bin/uwsgi --ini /etc/uwsgi/%I.ini
ExecReload=/bin/kill -HUP $MAINPID
ExecStop=/bin/kill -INT $MAINPID
Restart=always
Type=notify
StandardError=syslog
NotifyAccess=all
KillSignal=SIGQUIT

[Install]
WantedBy=multi-user.target

и задействовать командой:

sudo systemctl enable uwsgi@pgadmin

Установка Nginx

Для установки nginx необходимо выполнить команду:

sudo apt-get install nginx-full

а в директорию /etc/nginx/sites-available/ добавить файл настройки сайта pgadmin.conf:

upstream pgadmin {
      server unix:///tmp/pgadmin.sock;
}
server {
    listen 8000;
    server_name  $server_addr;
    charset      utf-8;
    client_max_body_size 75M;
    location / {
        include uwsgi_params;
        uwsgi_pass  pgadmin;
    }
}
server {
    listen          8001 ssl;
    server_name     $server_addr;
    charset      utf-8;
    ssl_certificate      /etc/uwsgi/pgadmin.crt;
    ssl_certificate_key  /etc/uwsgi/pgadmin.key;
    location / {
        include uwsgi_params;
        uwsgi_pass  pgadmin;
    }
}

Для активации сайта необходимо в директории /etc/nginx/sites-enabled создать ссылку на файл /etc/nginx/sites-available/pgadmin.conf:

/etc/nginx/sites-enabled# ln -s /etc/nginx/sites-available/pgadmin.conf

Nginx и uwsgi запускаются под www-data:www-data, поэтому у настроек необходимо изменить владельцев командами:

sudo chown www-data:www-data /var/lib/pgadmin/pgadmin.db
sudo chown -R www-data:www-data /var/log/pgadmin/
sudo chown -R www-data:www-data /var/lib/pgadmin/

Далее необходимо перезапустить nginx & uwsgi

sudo systemctl restart uwsgi@pgadmin
sudo systemctl restart nginx

PgAdmin4 должен быть доступен по ссылке http://localhost:8000

@BonBonSlick
Copy link

BonBonSlick commented Jul 27, 2019

It will not work:

  1. for remote DB, because of not local IP
  2. if SSL enabled
  3. if subdomain used
  4. if port blocked
  5. if another port used

I would suggest updating that guide for these salvations.

  1. with ssl
  2. with subdomain
  3. remote server installation + config

at least.

What if server restarted, should sudo systemctl enable uwsgi@pgadmin be called again?

Installation with apache much easier, but if nginx already installed this guide will not help.
https://www.digitalocean.com/community/tutorials/how-to-install-configure-pgadmin4-server-mode

Tried

upstream pgadmin {
      server unix:///tmp/pgadmin.sock;
}
server {
        listen 443 ssl http2 ipv6only=on;
        listen [::]:443 ssl http2;

        server_name pgsql4.test.tv;

        ssl_certificate /etc/letsencrypt/live/test.tv/fullchain.pem;
        ssl_certificate_key /etc/letsencrypt/live/test.tv/privkey.pem;
        include /etc/letsencrypt/options-ssl-nginx.conf;

        charset      utf-8;
        client_max_body_size 75M;

        location / {
                include uwsgi_params;
                uwsgi_pass  pgadmin;
        }
}

got


pgsql4.test.tv normally uses encryption to protect your information. When Google 
Chrome tried to connect to pgsql4.test.tv this time, the website sent back unusual 
and incorrect credentials. This may happen when an attacker is trying to pretend
 to be pgsql4.test.tv, or a Wi-Fi sign-in screen has interrupted the connection.
 Your information is still secure because Google Chrome stopped the connection before any data was exchanged.

@rdnvndr
Copy link
Author

rdnvndr commented Jul 28, 2019

Thanks for the comment. I wanted to show how to install and configure pgAdmin. And not how to configure nginx.

@BonBonSlick
Copy link

BonBonSlick commented Jul 31, 2019

@BonBonSlick
Copy link

BonBonSlick commented Aug 24, 2019

I was thinking about replacement of phppgadmin with pgadmin, seems it is much better.
Here is might be useful

import os
DATA_DIR = os.path.realpath(os.path.expanduser(u'~/.pgadmin/'))
LOG_FILE = os.path.join(DATA_DIR, 'pgadmin4.log')
SQLITE_PATH = os.path.join(DATA_DIR, 'pgadmin4.db')
SESSION_DB_PATH = os.path.join(DATA_DIR, 'sessions')
STORAGE_DIR = os.path.join(DATA_DIR, 'storage')
SERVER_MODE = False

And these https://uwsgi-docs.readthedocs.io/en/latest/tutorials/Django_and_nginx.html
http://djangonauts.ru/content/sozdanie-servera-s-pomoshyu-uwsgi-i-nginx-na-debia/

During installation, according to this guide, there is should be email and password prompt, but I did not have

test@srv49167738:/usr/local/lib/python3.5/dist-packages/pgadmin4$ sudo python3 setup.py             
                                                                                                      
pgAdmin 4 - Application Initialisation                                                                
======================================                                                                
                                                                                                      
test@srv49167738:/usr/local/lib/python3.5/dist-packages/pgadmin4$   

Also, changing server_name $server_addr; in nginx does not work, it still works only for http://185.****:8000/login
SSL also not applied.
But, nginx server block works, if put return 404; after listen 8000, got 404 error for http://185.****:8000/login

@BonBonSlick
Copy link

BonBonSlick commented Aug 26, 2019

My setting to make it work for specific domain and ssl

upstream pgadmin {
      server unix:///tmp/pgadmin.sock;
}

server {
        listen 80 http2;
        listen [::]:80 http2;
        server_name sub.test.tv;
        charset utf-8;
        client_max_body_size 75M;
        location / {
                include uwsgi_params;
                uwsgi_pass pgadmin;
        }
}
server {
        listen 443 ssl http2;
        listen [::]:443 ssl http2;
        server_name sub.test.tv;
        include /etc/letsencrypt/options-ssl-nginx.conf;
        charset utf-8;
        location / {
                include uwsgi_params;
                uwsgi_pass pgadmin;
        }
}

But still can not access it because of no default user... Trying to reinstall it but got error aka

test@srv49167738:/usr/local/lib/python3.5/dist-packages/pgadmin4$ sudo pip3 install Babel
Requirement already satisfied: Babel in /usr/local/lib/python3.5/dist-packages
Requirement already satisfied: pytz>=2015.7 in /usr/local/lib/python3.5/dist-packages (from Babel)

test@srv49167738:/usr/local/lib/python3.5/dist-packages/pgadmin4$ sudo pip3 install flask_babel
Requirement already satisfied: flask_babel in /usr/local/lib/python3.5/dist-packages
Requirement already satisfied: Flask in /usr/local/lib/python3.5/dist-packages (from flask_babel)
Requirement already satisfied: Jinja2>=2.5 in /usr/local/lib/python3.5/dist-packages (from flask_babel)
Requirement already satisfied: Babel>=2.3 in /usr/local/lib/python3.5/dist-packages (from flask_babel)
Requirement already satisfied: click>=5.1 in /usr/local/lib/python3.5/dist-packages (from Flask->flask_babel)
Requirement already satisfied: itsdangerous>=0.24 in /usr/local/lib/python3.5/dist-packages (from Flask->flask_babel)
Requirement already satisfied: Werkzeug>=0.14 in /usr/local/lib/python3.5/dist-packages (from Flask->flask_babel)
Requirement already satisfied: MarkupSafe>=0.23 in /usr/local/lib/python3.5/dist-packages (from Jinja2>=2.5->flask_babel)
Requirement already satisfied: pytz>=2015.7 in /usr/local/lib/python3.5/dist-packages (from Babel>=2.3->flask_babel)

test@srv49167738:/usr/local/lib/python3.5/dist-packages/pgadmin4$ sudo pip3 install Flask-BabelEx
Requirement already satisfied: Flask-BabelEx in /usr/local/lib/python3.5/dist-packages
Requirement already satisfied: Jinja2>=2.5 in /usr/local/lib/python3.5/dist-packages (from Flask-BabelEx)
Requirement already satisfied: Flask in /usr/local/lib/python3.5/dist-packages (from Flask-BabelEx)
Requirement already satisfied: Babel>=1.0 in /usr/local/lib/python3.5/dist-packages (from Flask-BabelEx)
Requirement already satisfied: speaklater>=1.2 in /usr/local/lib/python3.5/dist-packages (from Flask-BabelEx)
Requirement already satisfied: MarkupSafe>=0.23 in /usr/local/lib/python3.5/dist-packages (from Jinja2>=2.5->Flask-BabelEx)
Requirement already satisfied: itsdangerous>=0.24 in /usr/local/lib/python3.5/dist-packages (from Flask->Flask-BabelEx)
Requirement already satisfied: click>=5.1 in /usr/local/lib/python3.5/dist-packages (from Flask->Flask-BabelEx)
Requirement already satisfied: Werkzeug>=0.14 in /usr/local/lib/python3.5/dist-packages (from Flask->Flask-BabelEx)
Requirement already satisfied: pytz>=2015.7 in /usr/local/lib/python3.5/dist-packages (from Babel>=1.0->Flask-BabelEx)


test@srv49167738:/usr/local/lib/python3.5/dist-packages/pgadmin4$ sudo python setup.py
Traceback (most recent call last):
  File "setup.py", line 17, in <module>
    from pgadmin.model import db, User, Version, ServerGroup, Server, \
  File "/usr/local/lib/python3.5/dist-packages/pgadmin4/pgadmin/__init__.py", line 21, in <module>
    from flask_babelex import Babel, gettext
ImportError: No module named flask_babelex

ok, here is how it works after packages installations
run sudo python3 setup.py to see

NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

But now it is impossible to login in pgAdmin, page loading forever. It does not matter if credentials correct or not, all pages except specified server_name do not work =/
I read pgAdmin is much worse than pgAdmin3.

It seems some SSL setting issues, do not know what site uwsgi or nginx. If SSL enabled, pgAdmin doe snot work.
Have disabled SSL.
Login works, but whole pgAdmin4 has lags and security vulnerabilities. 504 Gateway Time-out, servers can not be added, because NOT_AUTHORIZED response error.

I have killed about a couple of days trying to resurrect crappy phpPgAdmin, now killing 3-d day to config this pgadmin crap.

I would rather develop for that time own simple admin panel for admins / moders. Web GUIs for RDBMS phpmyadmin, this one etc are crap. I recommend doing the same to everyone.

@olen2006
Copy link

olen2006 commented Oct 28, 2019

But still can not access it because of no default user...
nginx should be a default user...anyways I'm having the same issue...day 1...

@MashinaMashina
Copy link

MashinaMashina commented Feb 11, 2020

На версии pgadmin из инструкции вываливаются ошибки, тк ставятся новые версии psycopg2 и werkzeug, которые не полностью совместимы
Надо так:
sudo pip3 install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.18/pip/pgadmin4-4.18-py2.py3-none-any.whl

Тут точка в конце лишняя
server unix:///tmp/pgadmin.sock;.

@rdnvndr

@rdnvndr
Copy link
Author

rdnvndr commented Feb 12, 2020

Спасибо, @MashinaMashina! Обновил описание по комментарию.

@ASUSRV
Copy link

ASUSRV commented Jan 20, 2021

Добрый день! Первые дни знакомлюсь с Linux, стандартно поставить PGAdmin на Apache нельзя, нужен именно nginx.
Делаю все по инструкции, дохожу до команды sudo chown www-data:www-data /var/lib/pgadmin/pgadmin.db и возвращается ошибка:
'/var/lib/pgadmin/pgadmin.db': Нет такого файла или каталога
И его действительно нет. Все сверху до этой команды выполнял.

@rdnvndr
Copy link
Author

rdnvndr commented Jan 20, 2021

Проверьте содержимое файла config_local.py и попробуйте переустановить pgadmin4.

@rxsend19
Copy link

rxsend19 commented Nov 5, 2022

прошу обновить и исправить некоторые команды и конфиги

sudo pip3 install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v6.15/pip/pgadmin4-6.15-py3-none-any.whl

 

sudo uwsgi --http-socket :8000 --plugin python3 --chdir /usr/local/lib/python3.10/dist-packages/pgadmin4/  --wsgi-file /usr/local/lib/python3.10/dist-packages/pgadmin4/pgAdmin4.wsgi   ( указываете установленную версию python) 

в файле /etc/uwsgi/pgadmin.ini

( указываете установленную версию python в строке chdir и  wsgi-file)
[uwsgi]
uid             = www-data
gid             = www-data
plugins         = python3
chdir           = /usr/local/lib/python3.10/dist-packages/pgadmin4/ 
wsgi-file       = /usr/local/lib/python3.10/dist-packages/pgadmin4/pgAdmin4.wsgi 
master          = true
processes       = 1
socket          = /tmp/pgadmin.sock
chmod-socket    = 664
vacuum          = true

в файле /etc/systemd/system/uwsgi@.service

 [Unit]
Description=uWSGI service unit
After=syslog.target
[Service]
ExecStart=/usr/bin/uwsgi --ini /etc/uwsgi/pgadmin.ini
ExecReload=/bin/kill -HUP $MAINPID
ExecStop=/bin/kill -INT $MAINPID
Restart=always
Type=notify
StandardError=syslog
NotifyAccess=all
KillSignal=SIGQUIT
[Install]
WantedBy=multi-user.target

@rdnvndr
Copy link
Author

rdnvndr commented Nov 5, 2022

Спасибо, @rxsend19!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment