HowtoForge

How to Install PostgreSQL and phpPgAdmin on OpenSUSE Leap 42.1

Author: Muhammad Arul

PostgreSQL (most people call it Postgres) is an advanced object-relational database management system (ORDBMS). Postgres is an open source database server with more than 18 years of active development which makes Postgres the best choice for enterprise-class applications.

Postgresql is cross-platform and runs on many operating systems like Linux, OSX, Windows and the BSD family. It's the right choice when you have an application with large database workloads. Postgres has been used by many large organizations including Yahoo, Instagram, and Disqus. Postgres is also offered as software as a service (SaaS) by e.g. Heroku and AWS (Amazon Web Services).

In this tutorial, I will show you how to install Postgres on your own server with OpenSUSE Leap as operating system. Then I will install phpPgAdmin so we can manage our Postgres database from the web browser.

Prerequisite

Step 1 - Install PostgreSQL

The openSUSE repository contains two version of Postgres. In this step, we will install PostgreSQL 9.4 on openSUSE with zypper command.

Install PostgreSQL with zypper:

zypper in postgresql94 postgresql94-contrib postgresql94-server

Start postgresql with systemctl:

systemctl start postgresql

Step 2 - Configure Default User

In this step, we will set a password for the postgres user. Postgresql uses a concept that is similar to the Linux user system called "role" for basic authentication and authorization. By default, postgresql creates a new user named "postgres" to manage the postgresql server.

Let's take the first step to configuring the postgresql database by logging into the user postgres from the terminal:

sudo su
su - postgres

Now we will use the psql command. psql is the command line front-end for PostgreSQL, we can directly execute PostgreSQL queries from it.

psql

From the psql shell, we can change the password of the "postgres" user with the "password" command below:

\password postgres
New Password:

Enter your new password for postgres user.

Step 3 - Create a new PostgreSQL User

In this step, we will create a new user/role with superuser privileges and create a new database and grant the user access.

To create a new a user, we must login as postgres user, and then create a new role and database with the command below:

su - postgres
createuser -s nano
createdb nano_db

Note:

-s option is used to give the new user superuser privileges.

nano is new user name.

nano_db is new database name.

Now login to the postgresql shell psql and give our new role a password and grant database access.

psql
alter user nano with encrypted password 'aqwe123@';
grant all privileges on database nano_db to nano;

I will use the password 'aqwe123@' for the user nano here, and grant the privileges to the database nano_db.

This user will be used to login to the postgresql from the web based phpPgAdmin interface because we should not allow any default user or user without a password to login to postgresql from the phpPgAdmin.

Step 4 - Configure PostgreSQL

In this step, we will configure PostgreSQL to listen on the public IP so we can access it with DB modeling tools remotely from our desktop computer.You can skip this step if you don't want to access Postgres remotely.

Go to the postgresql directory "/var/lib/pgsql/" and edit the main configuration file "postgresql.conf" with your favorite editor:

cd /var/lib/pgsql/
cd data/
vim postgresql.conf

Uncomment line 59 and change the value from localhost to "*".

Uncomment line 63 to configure the default port.

listen_addresses = '*'
port = 5432

Save the file and exit the editor.

To allow access from the web application phpPgAdmin to the PostgreSQL database, we need to edit the pg_hba.conf file and change some configuration settings.

Edit the "pg_hba.conf" file with vim:

vim pg_hba.conf

At the end of the line, change the configuration for authentication to md5:

#TYPE   Database    User    Address     Method
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

If you want to allow new connections from outside of the server like using PgAdmin3, you can add more "host" records e.g. for the IP of your desktop.

Save and exit.

Now restart the PostgreSQL service with systemctl command:

systemctl restart postgresql

Step 5 - Install and Configure phpPgAdmin

phpPgAdmin is a web-based application to manage PostgreSQL databases. It is based on PHP and provides a GUI to manage databases and to create roles. In this step, we will install phpPgAdmin and because it's accessible from the web browser we will use apache2 as the web server.

Install phpPgAdmin with this zypper command:

zypper in phpPgAdmin

The command will automatically install the dependencies PHP and apache web server.

Next, go to the phpPgAdmin directory and edit the configuration file config.inc.php.

cd /etc/phpPgAdmin/
vim config.inc.php

In the host configuration line 18, set the value to "localhost".

$conf['servers'][0]['host'] = 'localhost';

If you want to allow a user with name "postgres" or "root" to login to the postgresql with phpPgAdmin, you can set the extra security to false, but it's not recommended to set it  false. check the line 93.

$conf['extra_login_security'] = true;

Save and exit.

Then activate the PHP and version extension in apache as they are needed by phpPgAdmin:

a2enmod php5
a2enmod version

Now restart all services with systemctl command:

systemctl restart postgresql
systemctl restart apache2

Step 6 - Testing

Access phpPgAdmin from the web browser with URL:

http://192.168.1.101/phpPgAdmin/

Login with user nano and password 'aqwe123@'.

if you log in with user 'postgres' and the correct password, it's disallowed.

Reference

How to Install PostgreSQL and phpPgAdmin on OpenSUSE Leap 42.1