How to Setup Postgresql 9.6 and access PHPpgAdmin on Ubuntu 16.04

October 14, 2016 | By in OPEN SOURCE TOOLS
| 1 Reply More

PostgreSQL is a powerful and open source object-relational database management system developed by the PostgreSQL Global Development Group.  It gives primary focus on extensibility and standards-compliance. This stores data securely, and  allow users to  retrieve data at the request of other software applications.

It is supported  on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64) and Windows.

In this article, I'll  explain on how to install Postgresql 9.6 on an Ubuntu 16.04 server.

How to Install PostgreSQL

Default Ubuntu repositories may not contain our desired PostgreSQL version, hence, we need to create custom repo file to download/install the required version. You can create an apt source file "/etc/apt/sources.list.d/postgresql.list" with the following contents.

root@ubuntu:~# cat /etc/apt/sources.list.d/postgresql.list
deb xenial-pgdg main

Now we can install our desired PostgreSQL version by executing the following commands below:

root@ubuntu:~# wget --quiet -O - | sudo apt-key add -
root@ubuntu:~# apt-get update
Hit:1 xenial InRelease
Hit:2 xenial-updates InRelease
Hit:3 xenial-backports InRelease
Hit:4 xenial-security InRelease
Get:5 xenial-pgdg InRelease [36.6 kB]
Get:6 xenial-pgdg/main amd64 Packages [55.6 kB]
Get:7 xenial-pgdg/main i386 Packages [55.5 kB]
Fetched 148 kB in 0s (256 kB/s)
Reading package lists... Done
root@ubuntu:~# apt-get install postgresql-9.6 postgresql-contrib

You can confirm the PostgreSQL installation by logging to the postgres user.

root@ubuntu:~# su - postgres
postgres@ubuntu:~$ psql
psql (9.6.0)
Type "help" for help.

Managing / Modifying the User Roles in PostgreSQL

PostgreSQL manages database access permissions using the concept of "roles". It can be either a database user, or a group of database users, depending on how the role is set up. They're some way similar to Linux user accounts. Any role can act as a user, group or both.

By default, a postgres role will be setup on installation which act similar to a Linux username with shell access. This role has the admin privilege and can manage all transactions. Let's see how we can create new roles from the psql command line.  I've created a new role called  testadmin using this command. You can determine the set of existing roles from pg_roles system catalog.

root@ubuntu:~# sudo -u postgres psql
psql (9.6.0)
Type "help" for help.

postgres=# create role testadmin;

postgres=# SELECT rolname FROM pg_roles;
(4 rows)

Similarly, you can delete a user role using the command "DROP ROLE name;". We can also use the commands "createuser username or dropuser username" for this purpose. Check out the commands below:

root@ubuntu:~# su - postgres
postgres@ubuntu:~$ createuser --interactive
Enter name of role to add: saheadmin
Shall the new role be a superuser? (y/n) y

By using --interactive option,  we can make this process  interactive. You can see several option for this command with help option.

postgres@ubuntu:~$ createuser --help
createuser creates a new PostgreSQL role.

createuser [OPTION]... [ROLENAME]

-c, --connection-limit=N connection limit for role (default: no limit)
-d, --createdb role can create new databases
-D, --no-createdb role cannot create databases (default)
-e, --echo show the commands being sent to the server
-E, --encrypted encrypt stored password
-g, --role=ROLE new role will be a member of this role
-i, --inherit role inherits privileges of roles it is a
member of (default)
-I, --no-inherit role does not inherit privileges
-l, --login role can login (default)
-L, --no-login role cannot login
-N, --unencrypted do not encrypt stored password
-P, --pwprompt assign a password to new role
-r, --createrole role can create new roles
-R, --no-createrole role cannot create roles (default)
-s, --superuser role will be superuser
-S, --no-superuser role will not be superuser (default)
-V, --version output version information, then exit
--interactive prompt for missing role name and attributes rather
than using defaults
--replication role can initiate replication
--no-replication role cannot initiate replication
-?, --help show this help, then exit

Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as (not the one to create)
-w, --no-password never prompt for password
-W, --password force password prompt

Report bugs to <>.

We can reset the password for a role using this command from psql prompt.

root@ubuntu:~# sudo -u postgres psql
psql (9.6.0)
Type "help" for help.

postgres=# ALTER USER testadmin PASSWORD 'password';

How to set Role Attributes

A database role can have a number of attributes that define its privileges and interact with the client authentication system. Let me explain some of the important and useful attributes which can be used during role creation.

  •  Login privilege
  •  Superuser
  •  Database creation
  •  Password
  •  Role creation

We can see how we can assign this attributes to a role during creation.

Login privilege: A role with the LOGIN attribute can be considered the same thing as a "database user". You can use the command below for passing this attribute.


Superuser : You can make a role with super privileges using this command.


Database creation : A role can be created with  permission to create databases using the below command (except for superusers, since those bypass all permission checks.


Password : You can  specify a password upon role creation by using this command.

CREATE ROLE name PASSWORD 'password string';

Database Management

We can create a database in PostgreSQL using the command createdb  dbname from the login prompt or using the command CREATE DATABASE name from psql prompt as below:

From login shell :

postgres@ubuntu:~$ createdb sahedb

From psql prompt:

postgres=# CREATE DATABASE testdb;

At times, you can also use this command to create a database for a particular user in one command.  The role will  become the owner of the new database, so he can configure and manage it himself.

From SQL prompt:

CREATE DATABASE dbname OWNER rolename;

From Shell:

createdb -O rolename dbname

Similarly, you can destroy a database by simply executing this command below:

From SQL prompt:


From login Shell:

dropdb dbname

Managing Tables

Now let's see how we can create tables for a particular database. We can create a table named employees in the database "testdb" which we created before and insert some values to the tables.

Connecting to our desired DB :

root@ubuntu:~# su - postgres
postgres@ubuntu:~$ psql testdb
psql (9.6.0)
Type "help" for help.

Creating a table name employees with the three fileds for employee id, first name and last name.

testdb=# CREATE TABLE employees (employee_id int, first_name varchar, last_name varchar);

Inserting following details to the table and listing out the table content.

testdb=# INSERT INTO employees VALUES (1, 'Joe', 'Sam');
testdb=# SELECT * FROM employees;
employee_id | first_name | last_name
1 | Joe | Sam

Similarly, we can delete a table using this command below:

DROP TABLE tablename;

Most of the SQL commands are similar to MySQL. You can get more options about table creation here.

How to Install PHPpgadmin

PHPpgAdmin provides a  web interface for accessing and managing PostgreSQL databases in a very easy way. We can easily create databases, tables in database, users, stored procedures etc. It also provides options for backup and restore.

Phppgadmin, is similar to PHPMyAdmin. You can install this interface by just running the command :apt-get install phppgadmin

Accessing PHPpgAdmin

After installing this tool, we need to edit the PHPpgAdmin configuration file to allow remote access. By default it locally allow access. Let's see how we grant access over remote public IPs.

You can edit the phppgAdmin configuration file : /etc/phppgadmin/ Edit the following line

$conf['extra_login_security'] = true;

Modify this value to "false".

After these changes, make sure to restart both PostgreSQL and Apache 2 services.

root@ubuntu:/etc/phppgadmin# service postgresql restart
root@ubuntu:/etc/phppgadmin# systemctl restart apache2

Now, you will be able to access PHPpgAdmin using the URL >> http://IP/phppgadmin/

phppgadmin 5.1 web console

You can view all the databases which we created from the command line from here and even manage them efficiently from the web interface using more user-friendly options.

phppgadmin 5.1 databases


Finally, we've completed with our PostgresSQL 9.6 installation on Ubuntu 16.04.x Server and discussed some of the key elements. In addition, we've installed an excellent PostgreSQL graphical management and administration tool. Yet, there are a lot more about this to learn. You can go through the official documentation on PostgreSQL for more details. I hope you this article is informative and useful for you. Please post your valuable comments and suggestions on this.

Tags: ,


Share This :

Free Linux Ebook to Download

Comments (1)

Trackback URL | Comments RSS Feed

  1. Sarah says:

    Many people have no interest in using PHP + Apache to manage a Postgres database. It is a bit of overkill when simply using PgAdminIII works great. I'm not suggesting that you can't include the Apache bits, but you may want to consider updating the tutorial to warn people that it isn't necessary. Especially considering the high ranking in Google for this topic.

Leave a Reply

Commenting Policy:
Promotion of your products ? Comment gets deleted.
All comments are subject to moderation.