How to Secure Postgresql on an Ubuntu VPS (with Pictures)
Edit Article

  • 1 Editor

Today we are going to update about the most commonly heard PostgreSQL. Most of us must require a slight trigger to our brains to bring out the concepts. Let us first refresh through the basics:

About PostgreSQL:-

PosgreSQL is an ORDBMS (object-oriented database management system) and standard compliances. PostgreSQL is famous for the implements the majority of the SQL. It is an open source database system. It is developed on POSTGRES 4.2 at Berkeley Computer Science department, University of California. PosgreSQL is specially designed to run on UNIX-kind platforms. It is portable and able to run in various platforms i.e. Mac OS X, Solaris, and Windows. It is free and open source platform. Its source code is available under PostgreSQL license. Everyone is lenient to use, modify and distribute PostgreSQL in any form. PostgreSQL is needed lowest maintenance efforts due to its stability.

pgsql

Features of PostgreSQL-

  • User-defined types
  • Table inheritance
  • Sophisticated locking mechanism
  • Foreign key referential integrity
  • Views, rules, subquery
  • Nested transactions (savepoints)
  • Multi-version concurrency control (MVCC)
  • Asynchronous replication

Recent Versions of PostgreSQL Features:

  • Native Microsoft Windows Server version
  • Table spaces
  • Point-in-time recovery

About Ubuntu VPS:-

Ubuntu VPS is popular and vast user community. VPS is the virtual Private Server. It is required hosting for the web application framework. The latest version of the Ubuntu VPS is Ubuntu 12.04 for LTS (Long Term Features), it can be accessed from anywhere and Full LAMP stacks support.

Basic Requirements:

  • VPS Hosting Account
  • A Registered Domain Name from online sites but it is optional.
  • Created an SSH Key Pairs
  • Terminal App, if on Linux or Mac
  • Putty, if on Windows
  • Any text editor of your choice (Gedit, Notepad++, SublimeText 3)
  • A working Internet Connection

There are some procedures for Secure PostgreSQL on an Ubuntu VPS:-

First of all install the PostgreSQL and follow the basic command for installing it:

pgsql22
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Now the database software should install on your system.

  • Peer Authentication

The required term and PostgreSQL is authenticated associating Linux user accounts with PostgreSQL accounts. This process is called the peer authentication. Linux user which is created by Postgres is called “Postgre” and it can be used for accessing the system. We are able to change the user with this command:

sudo su - postgres

With following command we can connect with the system:

Psql

We can connect without Password because Postgre has the authenticated by username and due to it assumes is secured. The special authority consideration does not need to use Linux "postgres" user here for accessing the database software. Next step is Exit from PostgreSQL and places the following command:

q
Exit

Always remember do Not Allow Remote Connections:


Just remove the potential attack vector. It is for not allowing the remote connections to the database. This is by default in case of the installing PostgreSQL from the Ubuntu repositories.


During the cross checks no remote connections are allowed. Then use the following way which is inside the host based authentication file:


sudo nano /etc/postgresql/9.1/main/pg_hba.conf


local all postgres peer local all all peer host all all 127.0.0.1/32 md5 host all all  ::1/128 md5

Security within PostgreSQL:-

The security prompt is important and essential. You need to secure your data within the PostgreSQL environment.

pgsql3

Log into PostgreSQL to follow along with this section:



sudo su - postgres
psql

Perform Separate Roles for Each Application:-

The following command is to be created with separate roles for each application:


For create a new role and type the following command: CREATE ROLE role_name WITH optional_permissions; For see the permissions you can assign with following command:

h CREATE ROLE

You are able to alter the permissions of any role with following commands:

ALTER ROLE role_name WITH optional_permissions;

Through the following command you can list the current roles and their attributes:

du

There you are illegible to create the new user. Assign appropriate permissions for every new application which is utilized in PostgreSQL.

Specified Separate Users from Functions:

This is a flexible way for handling the permission because they share few aspects of users and groups. There are separations for each user who allow managing it. Then assign the roles as a member in access to perform the particular and actual platform.

Let’s create two roles with the following:

CREATE ROLE login_role WITH login;
CREATE ROLE access_role;
du

Here we can create the owned database with “access_role”:


CREATE DATABASE demo_application WITH OWNER access_role;


Now will connect to the database and need to lock up the permission to only access_role" create tables:


c demo_application REVOKE ALL ON SCHEMA public FROM public; GRANT ALL ON SCHEMA public TO access_role;


Authorized to check it with swap the users to “login_role” and try to create the table:


SET ROLE login_role; CREATE TABLE test_table( name varchar(25));


There error is present such as:


ERROR: permission denied for schema public


Next we can add the login_role as a member to access_role; this is because to allow the access to the same functionality that "access_role" has.


Reset the role of "postgres", grant "login_role" membership within "access_role", and then re-try the process:

RESET ROLE;
GRANT access_role TO login_role;
SET ROLE login_role;
CREATE TABLE test_table(
        name varchar(25));

Conclusion:

This tutorial will help you for enhancing your skill. Theirs follow all command which is required for Secure PostgreSQL on an Ubuntu VPS. This article will help you for installing the Secure PostgreSQL on an Ubuntu VPS successfully. I will try to provide all the required information. The entire concepts are supporting you for installing other setup also. Few concepts are similar in all the hosting. This article will also help you in your career perspective. Ubuntu is having many new versions so if you need more tutorial regarding it you can connect with us.

Article Tools

Did this article help you?

YesNo

Become
an Author!

Write an Article