How To Set up PostgreSQL and PhpPgadmin on Ubuntu 18.04

Introduction

Install and Configure Postgresql

Installation

$ sudo apt install postgresql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libpq5 libsensors4 postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
lm-sensors postgresql-doc locales-all postgresql-doc-10 libjson-perl openssl-blacklist isag
The following NEW packages will be installed:
libpq5 libsensors4 postgresql postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common ssl-cert sysstat
0 upgraded, 9 newly installed, 0 to remove and 28 not upgraded.
Need to get 5331 kB of archives.
After this operation, 21.0 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
...
...
syncing data to disk ... ok
Success. You can now start the database server using: /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile startVer Cluster Port Status Owner Data directory Log file
10 main 5432 down postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (10+190) ...
Processing triggers for ureadahead (0.100.0-20) ...
Processing triggers for systemd (237-3ubuntu10.15) ...
Processing triggers for libc-bin (2.27-3ubuntu1) ...
$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Wed 2019-04-10 16:43:59 UTC; 3min 28s ago
Main PID: 15875 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 1152)
CGroup: /system.slice/postgresql.service

Configuring Role and Authentication

$sudo vim /etc/postgresql/10/main/pg_hba.conf# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD# "local" is for Unix domain socket connections only
#local all all peer
local all all md5
# IPv4 local connections:
#host all all 127.0.0.1/32 md5
host all all 10.10.1.0/24 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5

Configuring Connections

$ sudo vim /etc/postgresql/10/main/postgresql.conf # - Connection Settings -#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '10.10.1.84' # LISTEN ON THE SPECIFIED IP ADDRESS;
# 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
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
$ sudo systemctl restart postgresql
$ sudo netstat -antup | grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 24194/postgres
tcp6 0 0 :::5432 :::* LISTEN 24194/postgres

Configuring Postgres User and Password

$ sudo passwd postgres
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
$ sudo su -l postgres
postgres@ubuntu-bionic:~$ psql
psql (10.7 (Ubuntu 10.7-0ubuntu0.18.04.1))
Type "help" for help.
postgres=# \password
Enter new password:
Enter it again:
postgres=# \q
postgres@ubuntu-bionic:~$ exit

Install and Configure phppgadmin

$ sudo apt install phppgadmin 
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 javascript-common libapache2-mod-php libapache2-mod-php7.2 libapr1 libaprutil1
libaprutil1-dbd-sqlite3 libaprutil1-ldap libjs-jquery liblua5.2-0 libphp-adodb libsodium23 php-common php-mysql php-pgsql php7.2-cli
php7.2-common php7.2-json php7.2-mysql php7.2-opcache php7.2-pgsql php7.2-readline
Suggested packages:
www-browser apache2-doc apache2-suexec-pristine | apache2-suexec-custom php-pear postgresql-doc slony1-bin
The following NEW packages will be installed:
apache2 apache2-bin apache2-data apache2-utils javascript-common libapache2-mod-php libapache2-mod-php7.2 libapr1 libaprutil1
libaprutil1-dbd-sqlite3 libaprutil1-ldap libjs-jquery liblua5.2-0 libphp-adodb libsodium23 php-common php-mysql php-pgsql php7.2-cli
php7.2-common php7.2-json php7.2-mysql php7.2-opcache php7.2-pgsql php7.2-readline phppgadmin
0 upgraded, 26 newly installed, 0 to remove and 28 not upgraded.
Need to get 6820 kB of archives.
After this operation, 30.3 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
...
...
Enabling module mpm_prefork.
apache2_switch_mpm Switch to prefork
apache2_invoke: Enable module php7.2
Setting up php-mysql (1:7.2+60ubuntu1) ...
Setting up libapache2-mod-php (1:7.2+60ubuntu1) ...
Setting up phppgadmin (5.1+ds-3) ...
apache2_invoke: Enable configuration phppgadmin

Allow Connections from Remote Systems

sudo vim /etc/apache2/conf-available/phppgadmin.conf Alias /phppgadmin /usr/share/phppgadmin<Directory /usr/share/phppgadmin><IfModule mod_dir.c>
DirectoryIndex index.php
</IfModule>
AllowOverride None

# Only allow connections from localhost:
#Require local
Require all granted
<IfModule mod_php.c>
php_flag magic_quotes_gpc Off
php_flag track_vars On
#php_value include_path .
</IfModule>
<IfModule !mod_php.c>
<IfModule mod_actions.c>
<IfModule mod_cgi.c>
AddType application/x-httpd-php .php
Action application/x-httpd-php /cgi-bin/php
</IfModule>
<IfModule mod_cgid.c>
AddType application/x-httpd-php .php
Action application/x-httpd-php /cgi-bin/php
</IfModule>
</IfModule>
</IfModule>
</Directory>

Configure Server Address and Allow Some Users as postgres to Log in postresql

$ sudo vim /etc/phppgadmin/config.inc.php// Hostname or IP address for server.  Use '' for UNIX domain socket.
// use 'localhost' for TCP/IP connection on this computer
//$conf['servers'][0]['host'] = 'localhost';
$conf['servers'][0]['host'] = '10.10.1.84';
// If extra login security is true, then logins via phpPgAdmin with no
// password or certain usernames (pgsql, postgres, root, administrator)
// will be denied. Only set this false once you have read the FAQ and
// understand how to change PostgreSQL's pg_hba.conf to enable
// passworded local connections.
//$conf['extra_login_security'] = true;
$conf['extra_login_security'] = false;
// Only show owned databases?
// Note: This will simply hide other databases in the list - this does
// not in any way prevent your users from seeing other database by
// other means. (e.g. Run 'SELECT * FROM pg_database' in the SQL area.)
//$conf['owned_only'] = false;
$conf['owned_only'] = true;

Connect to phppgadmin

Original Source

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store