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.


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.


Now open your terminal and run the installation process

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

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.

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

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.

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

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

Now change the password of the database administrator postgres.

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.

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

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

We will edit the file /etc/phppgadmin/ 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

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.

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.

Original Source

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.