PostgreSQL

Introduction

PostgreSQL is a powerful object-relational database management system, provided under a flexible BSD-style license.^[1] PostgreSQL contains many advanced features, is very fast and standards compliant.

PostgreSQL has bindings for many programming languages such as C, C++, Python, Java, PHP, Ruby... It can be used to power anything from simple web applications to massive databases with millions of records.

Installation in Ubuntu Server

To install Postgresql  you may use the command line and type:

$sudo apt-get install postgresql

Administration

pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:

sudo apt-get install pgadmin3

Basic Server Setup

To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command.

Create New User

In a terminal, type: 

@-desktop:~$ sudo -s

root@-desktop:~# su - postgres

postgres@-desktop:~$ createuser jagdeep

Shall the new role be a superuser? (y/n) y

Shall the new role be allowed to create databases? (y/n) y

Shall the new role be allowed to create more new roles? (y/n) y

postgres@-desktop:~$ exit

logout

root@-desktop:~# exit

exit

@-desktop:~$ sudo -u postgres psql postgres
psql (8.4.4)
Type "help" for help.

Set a password for the "postgres" database role using the command:

\password postgres

and give your password when prompted. The password text will be hidden from the console for security purposes.

Type Control+D to exit the posgreSQL prompt. 

Create database

To create the first database, which we will call "mydb", simply type :

sudo -u postgres createdb mydb

Install phppgadmin for GUI Inter-phase 

sudo apt-get install phppgadmin

How to login using phppgadmin

First of all enable login of postgres user via phppgadmin by editing
the /etc/phppgadmin/config.inc.php or {your phppgadmin install
path}/conf/config.inc.php file change the following line-:

$conf['extra_login_security'] = true;
to:
$conf['extra_login_security'] = false;

Now you will be able to login in phppdadmin using postgres username.

After login using postgres or any other superuser account. Create a
new role (by clicking on Roles and then Create role).Give desired
username, password and tick on following checkboxes-:

Inherits privileges?
Can login?

Then press create button to create new user.

Now give the select privilege on the tables to the newly created
user.This can be done by clicking on the table name and grant
permissions under privileges tab.Give select privileges to newly
created user.Now that user can browse through the table and see
individual values.
Logout from superuser account and login again using newly created user
to verify the results.