PgAdmin4 Setup
In this tutorial we will setup pgadmin4, which is a frontend for PostgreSQL databases which is quite similar to phpmyadmin which is used for MySQL databases
We're going to use a debian LXC container on proxmox:
In this tutorial we will setup pgadmin4, which is a frontend for PostgreSQL databases which is quite similar to phpmyadmin which is used for MySQL databases
We're going to use a debian LXC container on proxmox:
First make sure your debian CT / VM is updated:
apt update -y ; apt upgrade -y
Then if you are not on debian, add the postgresql repositories, and follow the postgresql downloads page instructions. If you are on debian, everything should be in the repositories already:
root@debian-pgadmin:~# apt search pgadmin
Sorting... Done
Full Text Search... Done
pgadmin3/stable 1.22.2-5 amd64
graphical administration tool for PostgreSQL
pgadmin3-data/stable 1.22.2-5 all
graphical administration tool for PostgreSQL - documentation
phppgadmin/stable 5.1+ds-4 all
web-based administration tool for PostgreSQL
postgresql-11-pldebugger/stable 1:1.0-10-g2a298eb-1 amd64
PostgreSQL pl/pgsql Debugger API
root@debian-pgadmin:~# apt search postgresql-11
Sorting... Done
Full Text Search... Done
postgresql-11/stable 11.11-0+deb10u1 amd64
object-relational SQL database, version 11 server
Then we will first install postgresql:
root@debian-pgadmin:~# apt install postgresql-11
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
distro-info-data libpq5 libxslt1.1 lsb-release postgresql-client-11
postgresql-client-common postgresql-common sysstat
Suggested packages:
lsb postgresql-doc-11 libjson-perl isag
The following NEW packages will be installed:
distro-info-data libpq5 libxslt1.1 lsb-release postgresql-11
postgresql-client-11 postgresql-client-common postgresql-common sysstat
0 upgraded, 9 newly installed, 0 to remove and 0 not upgraded.
Need to get 16.8 MB of archives.
After this operation, 56.1 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
root@debian-pgadmin:~# pg_isready
/var/run/postgresql:5432 - accepting connections
root@debian-pgadmin:~# systemctl status postgresql
* postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2021-04-03 12:28:08 UTC; 1min 29s ago
Main PID: 10959 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 7372)
Memory: 0B
CGroup: /system.slice/postgresql.service
Apr 03 12:28:08 debian-pgadmin systemd[1]: Starting PostgreSQL RDBMS...
Apr 03 12:28:08 debian-pgadmin systemd[1]: Started PostgreSQL RDBMS.
Now that postgresql is installed, we can configure it, first change the password of the postgres user (here i chose P@SSW0RD):
root@debian-pgadmin:~# passwd postgres
New password:
Retype new password:
passwd: password updated successfully
next is the postgres ROLE that needs to be secured (i used the same password as above):
root@debian-pgadmin:~# su - postgres
postgres@debian-pgadmin:~$ psql
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD 'P@SSW0RD';
ALTER ROLE
postgres=# exit
postgres@debian-pgadmin:~$ exit
logout
root@debian-pgadmin:~#
Now that's done, we want to secure the password authentication itself, to do so we need to edit the /etc/postgresql/11/main/pg_hba.conf config file.
root@debian-pgadmin:~# su - postgres
postgres@debian-pgadmin:~$ vim /etc/postgresql/11/main/pg_hba.conf
Make sure to use the postgres user to edit postgresql's config files, we don't want to be left with permission problems as if we would use the root user.
hit :wq to save and quit out of vim, and then restart the postgresql service back into the root user shell since we changed the config file:
postgres@debian-pgadmin:~$ exit
logout
root@debian-pgadmin:~# systemctl restart postgresql
root@debian-pgadmin:~# systemctl status postgresql
* postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2021-04-03 12:42:14 UTC; 4s ago
Process: 12477 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 12477 (code=exited, status=0/SUCCESS)
Apr 03 12:42:14 debian-pgadmin systemd[1]: Starting PostgreSQL RDBMS...
Apr 03 12:42:14 debian-pgadmin systemd[1]: Started PostgreSQL RDBMS.
Now that's done let's create a simple test database:
root@debian-pgadmin:~# su - postgres
postgres@debian-pgadmin:~$ psql
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.
postgres=# CREATE DATABASE test_db;
CREATE DATABASE
postgres=# CREATE USER test_user PASSWORD 'P@SSW0RD';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user;
GRANT
Now we created a test database, and a test user with it. If you want to connect to that database, using that user, you can run the following command:
postgres=# exit
postgres@debian-pgadmin:~$ psql -d test_db -U test_user
Password for user test_user:
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.
test_db=>
And that's it! We managed to install postgresql, and secure it for a basic usage.
Most sysadmins are confortable in the commandline, so they can handle the databases directly from a SSH connection. However sometimes it's nice to have a web-based graphical interface to handle the same database. For MySQL there is a tool called PHPMyAdmin, it's equivalent for PostgreSQL is called PGadmin. Right now we are just on debian and for the time being we're lazy to add any repositories and we want our postgresql Web interface. Debian allows us to install a bad looking version of pgadmin4 called phppgadmin, as if they were imitating phpmyadmin. We can run the following apt command to install it:
root@debian-pgadmin:~# apt search pgadmin
Sorting... Done
Full Text Search... Done
pgadmin3/stable 1.22.2-5 amd64
graphical administration tool for PostgreSQL
pgadmin3-data/stable,now 1.22.2-5 all [installed,auto-removable]
graphical administration tool for PostgreSQL - documentation
phppgadmin/stable 5.1+ds-4 all
web-based administration tool for PostgreSQL
postgresql-11-pldebugger/stable 1:1.0-10-g2a298eb-1 amd64
PostgreSQL pl/pgsql Debugger API
root@debian-pgadmin:~# apt install phppgadmin
Reading package lists... Done
Building dependency tree
Reading state information... Done
[...]
After this operation, 32.7 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Once it finished installing, edit phppgadmin's apache configuration:
root@debian-pgadmin:~# vim /etc/apache2/conf-available/phppgadmin.conf
hit :wq to save and quit out of vim, and then test the config, if it is ok then restart apache:
root@debian-pgadmin:~# vim /etc/apache2/conf-available/phppgadmin.conf
root@debian-pgadmin:~# apachectl configtest
Syntax OK
root@debian-pgadmin:~# systemctl restart apache2
root@debian-pgadmin:~#
Let it install, then go to your debian's IP in your browser:
root@debian-pgadmin:~# ip a | grep inet
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
inet 10.0.0.112/16 brd 10.0.255.255 scope global eth0
inet6 fe80::68b5:5ff:fe35:dd26/64 scope link
The URL is http://10.0.0.112/phppgadmin for me:
And that's it! We have been able to install phpgadmin and since it has such a garbage color scheme we will install pgadmin4 correctly:
We login as the test user we created earlier, and then we are able to access the database from our web interface:
Let's start fresh with a new CT (This one will be at the 10.0.0.114 ip address so we can setup a remote connection to our other CT at 10.0.0.112):
Once in the console setup ssh properly:
Debian GNU/Linux 10 debian-pgadmin4 tty1
debian-pgadmin4 login: root
Password:
Linux debian-pgadmin4 5.4.106-1-pve #1 SMP PVE 5.4.106-1 (Fri, 19 Mar 2021 11:08:47 +0100) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
root@debian-pgadmin4:~# apt update -y ; apt upgrade -y ; apt install vim -y
root@debian-pgadmin4:~# vim /etc/ssh/sshd_config
PermitRootLogin yes
:wq to save and quit out of vim, then restart the ssh service and log into it:
root@debian-pgadmin4:~# systemctl restart sshd
root@debian-pgadmin4:~# systemctl status sshd
* ssh.service - OpenBSD Secure Shell server
Loaded: loaded (/lib/systemd/system/ssh.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2021-04-03 14:33:40 UTC; 4s ago
Docs: man:sshd(8)
man:sshd_config(5)
Process: 8823 ExecStartPre=/usr/sbin/sshd -t (code=exited, status=0/SUCCESS)
Main PID: 8824 (sshd)
Tasks: 1 (limit: 7372)
Memory: 1.4M
CGroup: /system.slice/ssh.service
`-8824 /usr/sbin/sshd -D
Apr 03 14:33:40 debian-pgadmin4 systemd[1]: Starting OpenBSD Secure Shell server...
Apr 03 14:33:40 debian-pgadmin4 sshd[8824]: Server listening on 0.0.0.0 port 22.
Apr 03 14:33:40 debian-pgadmin4 sshd[8824]: Server listening on :: port 22.
Apr 03 14:33:40 debian-pgadmin4 systemd[1]: Started OpenBSD Secure Shell server.
Now exit the console tab from pve and get back in your terminal:
[ 10.0.0.10/16 ] [ /dev/pts/3 ] [Github/blog/servers]
→ ssh root@10.0.0.114
The authenticity of host '10.0.0.114 (10.0.0.114)' can't be established.
ED25519 key fingerprint is SHA256:XkTLKhgdLL4CRiW2bq3FA6jsWNY9Vl0DwZkseHh+Qzs.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.114' (ED25519) to the list of known hosts.
root@10.0.0.114's password:
Linux debian-pgadmin4 5.4.106-1-pve #1 SMP PVE 5.4.106-1 (Fri, 19 Mar 2021 11:08:47 +0100) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Sat Apr 3 14:30:47 2021
root@debian-pgadmin4:~# id
uid=0(root) gid=0(root) groups=0(root)
root@debian-pgadmin4:~# apt install gnupg2 curl ca-certificates lsb-release -y
root@debian-pgadmin4:~# sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
Hit:1 http://security.debian.org buster/updates InRelease
Hit:2 http://ftp.debian.org/debian buster InRelease
Hit:3 http://ftp.debian.org/debian buster-updates InRelease
Get:4 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/buster pgadmin4 InRelease [4217 B]
Hit:5 http://apt.postgresql.org/pub/repos/apt buster-pgdg InRelease
Get:6 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/buster pgadmin4/main amd64 Packages [5949 B]
Get:7 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/buster pgadmin4/main all Packages [4530 B]
Fetched 14.7 kB in 1s (9846 B/s)
Reading package lists... Done
Building dependency tree
Reading state information... Done
All packages are up to date.
root@debian-pgadmin4:~# apt install pgadmin4
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
apache2 apache2-bin apache2-data apache2-utils libapache2-mod-wsgi-py3 libapr1 libaprutil1 libaprutil1-dbd-sqlite3
libaprutil1-ldap libatomic1 libbrotli1 libjansson4 liblua5.2-0 libpq5 libpython3.7 pgadmin4-desktop pgadmin4-server
pgadmin4-web pgdg-keyring postgresql-client postgresql-client-13 postgresql-client-common
Suggested packages:
apache2-doc apache2-suexec-pristine | apache2-suexec-custom www-browser postgresql-13 postgresql-doc-13
The following NEW packages will be installed:
apache2 apache2-bin apache2-data apache2-utils libapache2-mod-wsgi-py3 libapr1 libaprutil1 libaprutil1-dbd-sqlite3
libaprutil1-ldap libatomic1 libbrotli1 libjansson4 liblua5.2-0 libpq5 libpython3.7 pgadmin4 pgadmin4-desktop
pgadmin4-server pgadmin4-web pgdg-keyring postgresql-client postgresql-client-13 postgresql-client-common
0 upgraded, 23 newly installed, 0 to remove and 0 not upgraded.
Need to get 166 MB of archives.
After this operation, 22.0 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Let it install, and check if apache2 started:
root@debian-pgadmin4:~# systemctl status apache2
* apache2.service - The Apache HTTP Server
Loaded: loaded (/lib/systemd/system/apache2.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Sat 2021-04-03 15:07:56 UTC; 43s ago
Docs: https://httpd.apache.org/docs/2.4/
Apr 03 15:07:56 debian-pgadmin4 systemd[1]: Starting The Apache HTTP Server...
Apr 03 15:07:56 debian-pgadmin4 systemd[17065]: apache2.service: Failed to set up mount namespacing: Permission denied
Apr 03 15:07:56 debian-pgadmin4 systemd[17065]: apache2.service: Failed at step NAMESPACE spawning /usr/sbin/apachectl: Permis
Apr 03 15:07:56 debian-pgadmin4 systemd[1]: apache2.service: Control process exited, code=exited, status=226/NAMESPACE
Apr 03 15:07:56 debian-pgadmin4 systemd[1]: apache2.service: Failed with result 'exit-code'.
Apr 03 15:07:56 debian-pgadmin4 systemd[1]: Failed to start The Apache HTTP Server.
If apache2 couldnt start, it means that the 'nested' flag hasn't been set for your debian CT, so you can fix it this way:
Once you set the nesting feature, reboot your CT and see if apache2 started:
[ 10.0.0.10/16 ] [ /dev/pts/3 ] [Github/blog/servers]
→ ssh root@10.0.0.114
root@10.0.0.114's password:
Linux debian-pgadmin4 5.4.106-1-pve #1 SMP PVE 5.4.106-1 (Fri, 19 Mar 2021 11:08:47 +0100) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Sat Apr 3 14:34:12 2021 from 10.0.0.10
root@debian-pgadmin4:~# systemctl status apache2
* apache2.service - The Apache HTTP Server
Loaded: loaded (/lib/systemd/system/apache2.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2021-04-03 15:13:52 UTC; 33s ago
Docs: https://httpd.apache.org/docs/2.4/
Process: 105 ExecStart=/usr/sbin/apachectl start (code=exited, status=0/SUCCESS)
Main PID: 158 (apache2)
Tasks: 55 (limit: 7372)
Memory: 20.2M
CGroup: /system.slice/apache2.service
|-158 /usr/sbin/apache2 -k start
|-160 /usr/sbin/apache2 -k start
`-161 /usr/sbin/apache2 -k start
Apr 03 15:13:51 debian-pgadmin4 systemd[1]: Starting The Apache HTTP Server...
Apr 03 15:13:52 debian-pgadmin4 systemd[1]: Started The Apache HTTP Server.
Now that's done run the pgadmin4 setup script and go to your pgadmin4 web interface:
root@debian-pgadmin4:~# /usr/pgadmin4/bin/setup-web.sh
Setting up pgAdmin 4 in web mode on a Debian based platform...
Creating configuration database...
NOTE: Configuring authentication for SERVER mode.
Enter the email address and password to use for the initial pgAdmin user account:
Email address: ech0666@protonmail.com
Password:
Retype password:
pgAdmin 4 - Application Initialisation
======================================
Creating storage and log directories...
We can now configure the Apache Web server for you. This involves enabling the wsgi module and configuring the pgAdmin 4 application to mount at /pgadmin4. Do you wish to continue (y/n)? y
The Apache web server is running and must be restarted for the pgAdmin 4 installation to complete. Continue (y/n)? y
Apache successfully restarted. You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4
root@debian-pgadmin4:~# ip a | grep inet
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
inet 10.0.0.114/16 brd 10.0.255.255 scope global eth0
inet6 fe80::2432:2bff:fe8c:f5c9/64 scope link
Here the url of the pgadmin4 web interface is http://10.0.0.114/pgadmin4
And finally log in to see your pgadmin4 web interface:
As you can see we can use remote servers:
Now obviously, the remote server has to accept remote connections! Let's enable them on our remote server at 10.0.0.112:
[ 10.0.0.10/16 ] [ /dev/pts/3 ] [Github/blog/servers]
→ ssh root@10.0.0.112
root@10.0.0.112's password:
Linux debian-pgadmin 5.4.106-1-pve #1 SMP PVE 5.4.106-1 (Fri, 19 Mar 2021 11:08:47 +0100) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Sat Apr 3 12:22:58 2021 from 10.0.0.10
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
root@debian-pgadmin:~# su - postgres
postgres@debian-pgadmin:~$ vim /etc/postgresql/11/main/postgresql.conf
[...]
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' #and not localhost # 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)
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
[...]
hit :wq to save and quit out of vim:
We also need to modify pg_hba.conf to allow the remote connection to our pgadmin4 server (10.0.0.114):
postgres@debian-pgadmin:~$ vim /etc/postgresql/11/main/pg_hba.conf
In the config file, we need to add the following line:
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 10.0.0.114/32 md5
this will make sure we allow the remote connections from 10.0.0.114 to 10.0.0.112.
hit :wq to save and quit out of vim, and then restart postgresql:
postgres@debian-pgadmin:~$ exit
logout
root@debian-pgadmin:~# systemctl restart postgresql
root@debian-pgadmin:~# systemctl status postgresql
* postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2021-04-03 15:36:24 UTC; 4s ago
Process: 30558 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 30558 (code=exited, status=0/SUCCESS)
Apr 03 15:36:24 debian-pgadmin systemd[1]: Starting PostgreSQL RDBMS...
Apr 03 15:36:24 debian-pgadmin systemd[1]: Started PostgreSQL RDBMS.
Now from here let's test the postgresql connection from our pgadmin4 server:
[ 10.0.0.10/16 ] [ /dev/pts/39 ] [Github/blog/servers]
→ ssh root@10.0.0.114
root@10.0.0.114's password:
Linux debian-pgadmin4 5.4.106-1-pve #1 SMP PVE 5.4.106-1 (Fri, 19 Mar 2021 11:08:47 +0100) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Sat Apr 3 15:13:56 2021 from 10.0.0.10
root@debian-pgadmin4:~# psql -h 10.0.0.112 -d testdb -U test_user
psql: error: FATAL: no pg_hba.conf entry for host "10.0.0.114", user "test_user", database "testdb", SSL on
FATAL: no pg_hba.conf entry for host "10.0.0.114", user "test_user", database "testdb", SSL off
root@debian-pgadmin4:~# apt install postgresql -y
root@debian-pgadmin4:~# su - postgres
postgres@debian-pgadmin4:~$ psql -h 10.0.0.112 -d testdb -U test_user
Password for user test_user:
psql: error: FATAL: database "testdb" does not exist
postgres@debian-pgadmin4:~$ psql -h 10.0.0.112 -d test_db -U test_user
Password for user test_user:
psql (13.2 (Debian 13.2-1.pgdg100+1), server 11.11 (Debian 11.11-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
test_db=>
Much better! Now since we can connect to our remote postgresql database, let's connect to it through our pgadmin4 web interface:
Hit 'save' and see the result:
And that's it! We have managed to setup pgadmin4's web interface and connect to our remote postgresql database.
Some Address 67120,
Duttlenheim, France.
This cute theme was created to showcase your work in a simple way. Use it wisely.