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.
[email protected]:~# cat /etc/apt/sources.list.d/postgresql.list
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
Now we can install our desired PostgreSQL version by executing the following commands below:
[email protected]:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
[email protected]:~# apt-get update
Hit:1 http://mirrors.linode.com/ubuntu xenial InRelease
Hit:2 http://mirrors.linode.com/ubuntu xenial-updates InRelease
Hit:3 http://mirrors.linode.com/ubuntu xenial-backports InRelease
Hit:4 http://security.ubuntu.com/ubuntu xenial-security InRelease
Get:5 http://apt.postgresql.org/pub/repos/apt xenial-pgdg InRelease [36.6 kB] Get:6 http://apt.postgresql.org/pub/repos/apt xenial-pgdg/main amd64 Packages [55.6 kB] Get:7 http://apt.postgresql.org/pub/repos/apt xenial-pgdg/main i386 Packages [55.5 kB] Fetched 148 kB in 0s (256 kB/s)
Reading package lists... Done
[email protected]:~# apt-get install postgresql-9.6 postgresql-contrib
You can confirm the PostgreSQL installation by logging to the postgres user.
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.
[email protected]:~# sudo -u postgres psql
Type "help" for help.
postgres=# create role testadmin;
postgres=# SELECT rolname FROM pg_roles;
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:
By using --interactive option, we can make this process interactive. You can see several option for this command with help option.
[email protected]:~$ 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
-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
We can reset the password for a role using this command from psql prompt.
[email protected]:~# sudo -u postgres psql
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
- Database creation
- 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.
CREATE ROLE name LOGIN;
Superuser : You can make a role with super privileges using this command.
CREATE ROLE name SUPERUSER;
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.
CREATE ROLE name CREATEDB;
Password : You can specify a password upon role creation by using this command.
CREATE ROLE name PASSWORD 'password string';
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 :
[email protected]:~$ 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;
createdb -O rolename dbname
Similarly, you can destroy a database by simply executing this command below:
From SQL prompt:
DROP DATABASE name;
From login Shell:
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 :
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');
INSERT 0 1
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
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/config.inc.php. 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.
Now, you will be able to access PHPpgAdmin using the URL >> http://IP/phppgadmin/
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.
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.