How To Set up PostgreSQL and PhpPgadmin on Ubuntu 18.04

By Alain Francois, Alibaba Cloud Community Blog author

You can need to manage a database in other to store some information regarding your operations. There are many object-relational database system and one which is famous and powerful among them is PostgreSql. It's a free and open source relational database management system which is mostly in production environment. It can use and extend the SQL language to handle workloads from small to large applications. Most of the Linux professional open source tools work with command line such as postgresql which can give you quite difficulties to manage all your databases if you are a novice in the area.

To solve this issue, there is another open source tool called PhpPgAdmin which can help you to graphically manage your databases. It's a web interface helping you to manage your PostgreSql database in an easy way. If offers many features like the possibility to backup and restore your databases. This can save you some times in your work.

In this article, we will see how you can install PostgreSql and PhpPgAdmin on Ubuntu 18.04 to handle your database through the web interface.

Introduction

Postgresql is present in the default repositories of Ubuntu 18.04 and for PhpPgAdmin, it works with apache and php which means that if it's not already installed, it will download and install automatically. the default version of php that it will install is php 7.2

When writing this article, the default version of postgresql present by default in the repository is postgresql 10 which means that if you want another version like 9 or 11, you will need to follow the steps of this postresql wiki page recommended by the official website.

By default, postgresql creates a basic user postgres and database administrator with the same name and uses many authentications methods of which the most used are peer and md5. The peer authentication is used for database administrators and is only supported for local connections because it tries to obtain the client's operating system user name from the kernel to use it as the allowed database user name while the md5 authentication works the same but is used across the connection. That one can be particularly helpful on local connections when you have a multi-users machine.

Postrgresql also use sthe system of role to handle efficiently the permissions for the database access which can be a group of DB users, or simply a DB user, etc. The roles allow to assign privileges to database objects they own, enabling access and actions to those objects and have the ability to grant membership to another role.

Install and Configure Postgresql

In our case, we will use the default version of postgresql which means the 10 version so we will not follow the wiki page but you can do it if you want another version depending on what you need.

Installation

Now open your terminal and run the installation process

$ 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 start

Ver 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) ...

As you have seen, it's the version 10 which is install by default. Now check if the postgresql is running

$ 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

Now you need to configure postgresql because by default it works only for localhost authentication. You will need to edit the default configuration file /etc/postgresql/10/main/pg_hba.conf, so open the file with your command line editor and edit the lines of the local IPv4 local connections. So we will comment the line with the localhost address (or you can simply delete it) and add the network address of the clients machines that you will use to connect to the server.

$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

Notice that if you want to access your database from any computer, instead of specifying the remote network address 10.10.1.0/24 of the clients machines, you can use the default route 0.0.0.0/0

Configuring Connections

Now we need to configure on which interfaces PostgreSql should listen and accept the connections on, by editing the value of the listen_addresses configuration parameter because the default behavior is to listen for TCP/IP connections only on the local loopback address. There are some others parameters that you can edit to increase the security as the default port or the maximum connections, so you can try it.

$ 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

In our case, we ask to accept the connections on a specific interface of the server. If you want you server to accept the connections on any interface, instead of the ip address you should use *

Now we need to restart postgresql service and check if the service is listening on the default port 5432 with the netstat command

$ 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

You can see that our postgres is listening on the default port 5432 on all the interfaces.

Configuring Postgres User and Password

We need to configure the postgres user by changing the default password. Remember that postgresql creates a Linux and database admin user called posgres. It means that we have to change the passwords of the two, so directly on the system and on the psql shell command.

First let's change the password of the Linux postgres user

$ sudo passwd postgres
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

Now change the password of the database administrator postgres.

$ 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

Now you have changed the password. You can continue with the installation of PhpPgAdmin

Install and Configure phppgadmin

PhpPgAdmin is present by default in the repositories of Ubuntu 18.04. So we can install it normally.

$ 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

Now that the installation is finished, we need to complete some configurations.

Allow Connections from Remote Systems

We need to edit the phppgadmin from apache to allow connections from remote systems. So we will change the parameters for localhost connections

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

We will edit the file /etc/phppgadmin/config.inc.php and change some parameters. We will indicate the ip address of the postgresql server, also allow the defaults usernames to log in and just hide the other users databases from the database list but if a user uses a sql query, he will see the other databases.

By default, the default address of Postgresql is localhost but you need to change it and put the ip address of the server otherwise you can face some troubles to connect to the database and you will be not probably able to remotely connect from the remote network addresses of the clients machines that you have specified in the /etc/postgresql/10/main/pg_hba.conf file

$ 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';

Postgresql doesn't allow some defaults users such as posgres, root, or administrators to login in the database even the users with no password. So we need at least , in our case to allow the default users to log in by editing the same file as below.

// 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;

Now that we have finaliezd the configuration of phppgadmin, you can try to access it throught a browser of one remote machine which is in the network specified in the pg_hba.conf file

Connect to phppgadmin

Open your browser and put the ip add of the server. Then choose your language and go to the server

There you can see all your database servers and the ip addresses. Click on your postgresql server

Now use your postgres database username and password, not the password of the postgres system user.

Now you can see that you are connected to the database

Now you have all the details that you can need to configure a postgresql server and it's possible for you to increase the security by configuring your firewall with good communication rules. You can also decide to create a new database user instead of using the default one which is postgres.

Normally when you have a postgreSql server and you want to make it accessible from everywhere, it means that it must be opened on Internet. So, it may listen on a public IP address to accept connections from any origin but it can also cause a security issue so you must be careful on the configuration that you will do.

Community

How To Set up PostgreSQL and PhpPgadmin on Ubuntu 18.04

Introduction

Install and Configure Postgresql

Installation

Configuring Role and Authentication

Configuring Connections

Configuring Postgres User and Password

Install and Configure phppgadmin

Allow Connections from Remote Systems

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

Connect to phppgadmin

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Alibaba Clouder

Related Products

AnalyticDB for PostgreSQL

Data Transmission Service