Database Security postgreSQL

We know that in today’s world protecting your system is a must. However, there are number of ways to protect and remove malware but no one ensures your system is secure, there always loophole for hackers in any terms. So we can use different layers of protection to minimize the risk of hacking.

(1). listen_addresses: Informs PostgreSQL which IP addresses to listen on. This usually defaults to localhost or local, but many people change it to *, meaning all available IP addresses.

 It is a good practice to allow connections only from the known ips or your network, and avoid general values like listen_address = “*” or “0.0.0.0:0” or “::”, which will tell PostgreSQL to accept connection from any IP.

(2). Port: Change the default port 5432 by modifying the value of the port parameter within postgresql.conf file.

(3). pg_hba.conf: This file controls: which hosts are allowed to connect, how clients are authenticated, which PostgreSQL user names they can use, which databases they can access.  Records take one of these forms:

local             DATABASE     USER                   METHOD  [OPTIONS]

host             DATABASE     USER   ADDRESS    METHOD  [OPTIONS]

hostssl          DATABASE     USER  ADDRESS     METHOD  [OPTIONS]

hostnossl       DATABASE     USER  ADDRESS     METHOD  [OPTIONS]

(The uppercase items must be replaced by actual values.)

The first field is the connection type: "local" is a Unix-domain socket, "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.

DATABASE can be "all", "sameuser", "samerole", "replication", a database name, or a comma-separated list thereof. The "all" keyword does not match "replication". Access to replication must be enabled in a separate record (see example below).

USER can be "all", a user name, a group name prefixed with "+", or a comma-separated list thereof.  In both the DATABASE and USER fields you can also write a file name prefixed with "@" to include names from a separate file.

ADDRESS specifies the set of hosts the record matches.  It can be a host name, or it is made up of an IP address and a CIDR mask that is an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies the number of significant bits in the mask.  A host name that starts with a dot (.) matches a suffix of the actual host name. Alternatively, you can write an IP address and netmask in separate columns to specify the set of hosts.  Instead of a CIDR-address, you can write "samehost" to match any of the server's own IP addresses, or "samenet" to match any address in any subnet that the server is directly connected to.

METHOD can be "trust", "reject", "md5", "password", "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that "password" sends passwords in clear text; "md5" is preferred since it sends encrypted passwords.

OPTIONS are a set of options for the authentication in the format NAME=VALUE.  The available options depend on the different authentication methods -- refer to the "Client Authentication" section in the documentation for a list of which options are available for which authentication methods.

Example:

# TYPE    DATABASE    USER    ADDRESS              METHOD

# IPv4 local connections:

host        all              all        127.0.0.1/32          ident

# IPv6 local connections:

host        all              all        ::1/128                 trust

host        all              all        192.168.1.0/24       md5

hostssl     all              all        0.0.0.0/0               md5

# Allow replication connections from localhost, by a user with the replication privilege.

#host replication        postgres     127.0.0.1/32    trust

#host replication        postgres     ::1/128           trust

IPv4 syntax for defining network range. The first part in this case,192.168.1.0 is the network address,followed by /24 as the bit mask. In our pg_hba.conf, we allow anyone in our subnet of 192.168.1.0 to connect as long as they provide a valid md5 hashed password.

IPv6 syntax for defining network range. This applies only to servers with IPv6 support and may prevent pg_hba.conf from loading if you add this section without actually having IPv6 networking.

SSL connection rule: In our example, we allow anyone to connect to our server as long as they connect using SSL and have a valid md5 password. You can take reference for SSL http://dbagurujee.blogspot.com/2018/02/ssl-configuration-in-postgresql-for.html.

This file is read on server startup and when the postmaster receives a SIGHUP signal.  If you edit the file on a running system, you have to SIGHUP the postmaster for the changes to take effect.  You can use "pg_ctl reload" to do that.

(4). User and Role Management: Be careful when you create role, group or user and providing them privileges on DATABSE level, SCHEMA(objects) level. As per your Database architecture requirement create some different user or role or group for doing different types of work. For example, with read only privilege, with DML privilege, with DDL privilege and with Administrative privilege.

For DML privilege: You can give USAGE privilege on schema and SELECT, INSERT, UPDATE, DELETE,EXECUTE etc. privileges on objects.

READ only privilege: You can give USAGE privilege on schema and SELECT privilege on objects.

DDL privilege: You can give USAGE, CREATE privilege on schema and SELECT,INSERT,
UPDATE,DELETE,EXECUTE etc. privileges on objects.

(5). Data Encryption within Database (pg_crypto): It is always better to encrypt or decrypt data in middleware. We can encrypt or decrypt data within database through pg_crypto. It is an extension of PostgreSQL. we can do column based encryptions using PostgreSQL native functions PGP_SYM_ENCRYPT and PGP_SYM_DECRYPT.

Example:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE student (roll serial, name text, address text); 

INSERT INTO student (name, address)VALUES (PGP_SYM_ENCRYPT('Sidh','AES_KEY'),PGP_SYM_ENCRYPT('Jsr', 'AES_KEY'))
UPDATE student SET (name, address) = (
    PGP_SYM_ENCRYPT('Ramana', 'AES_KEY'),
    PGP_SYM_ENCRYPT('Pune', 'AES_KEY')
) WHERE roll=1;

SELECT
    PGP_SYM_DECRYPT(name::bytea, 'AES_KEY') as name,
    PGP_SYM_DECRYPT(address::bytea, 'AES_KEY') as age
FROM student WHERE (
    PGP_SYM_DECRYPT(name::bytea, 'AES_KEY')
    ILIKE ('%Ram%'));

Or

CREATE TABLE test_encrypt(username varchar(100), testpwd text);

INSERT INTO test_encrypt VALUES ('Ram', encrypt ('test', 'key', 'aes')),('Sidh1', encrypt ('test1','key', 'aes'));

INSERT INTO test_encrypt VALUES ('Ram', 'test'),('Sidh1', 'test1');

select * from  test_encrypt;

Decrypt :

select convert_from(decrypt('\222\332\367VM\362\244!C2\251W\365\204\200\355'::bytea, 'key', 'aes'), 'sqlascii') FROM  test_encrypt  WHERE username = 'Sidh1';

(6).Data Encryption on connection: PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. SSL (Secure Sockets Layer) is the standard security technology for establishing an encrypted link between a web server and a browser. http://dbagurujee.blogspot.com/2018/02/ssl-configuration-in-postgresql-for.html.

(7). SSH: It is a software package that enables secure system administration and file transfers over insecure networks. It is used in nearly every data center, in every larger enterprise. The SSH protocol uses encryption to secure the connection between a client and a server. All user authentication, commands, output, and file transfers are encrypted to protect against attacks in the network.

Use the following steps to generate an RSA key pair to configure SSH protocol. This is the default starting with OpenSSH 2.9.

  To generate an RSA key pair to work with version 2 of the protocol, type the following command at a shell prompt:

  (a). ssh-keygen -t rsa

#ssh-keygen

Accept the default file location of ~/.ssh/id_rsa. Enter a passphrase different from your account password and confirm it by entering it again.

The public key is written to ~/.ssh/id_rsa.pub. The private key is written to ~/.ssh/id_rsa. Never distribute your private key to anyone.

  (b). Change the permissions of the .ssh directory using the following command:

  (c). chmod 755 ~/.ssh

  (d). Copy the contents of ~/.ssh/id_rsa.pub into the file ~/.ssh/authorized_keys on the machine to which you want to connect. If the file ~/.ssh/authorized_keys exist, append the contents of the file ~/.ssh/id_rsa.pub to the file ~/.ssh/authorized_keys on the other machine.

For this, move id_rsa.pub file to destination machine at any specific directory and copy the contents of it to authorized_keys file without opening id_rsa.pub file as like:

cat /home/user/id_rsa.pub >> authorized_keys

  (e). Change the permissions of the authorized_keys file using the following command:

chmod 644 ~/.ssh/authorized_keys

(8). Firewall: it’s always recommended to keep firewall turn on. There are two types of firewall i.e. software and hardware firewall for security. Use suitable firewall as per your infrastructure setup and needs.

(9). Antivirus Software: Antivirus plays major role in real time protection and another step to protect the system. It’s always recommended to use good antivirus, which helps to protect the computer from any unauthorized program, code or software that creates a threat to the computer. Even your computer is virus free; you must install antivirus software and regularly update it.

(10). Anti-Spyware Software: Anti-Spyware is similar to antivirus offers real time protection. Anti-Spyware scans all incoming information to you and helps to block which contains virus or any threats once detected.

(11). Complex and Secure Passwords: First line of defense and always recommended to have strong and complex passwords. Use a long password and include a combination of numbers, letters that are both upper and lower case and a special character. Complex passwords are difficult for the hackers to find.

(12). HDD encryption:

Encryption is essential to protect critical data, no matter if it is stored locally, on the PC, or sent over the Internet. There are some open source encryption tools. Which are as below:

BitLocker: BitLocker is a full-disk encryption tool built-in the latest Windows operating systems (Windows 10), which uses AES (128 and 256-bit) encryption to encrypt data on the drives.

VeraCrypt

Another free encryption software tool you can use which is available for Windows, OS X and Linux operating systems.

Just like BitLocker, it supports Advanced Encryption Standard (AES) and can hide encrypted volumes within other volumes. It is an open source program which means developers and researchers can download and use the source code. This encryption software is a great alternative to TrueCrypt tool and is constantly getting improvements and security enhancements. VeraCrypt 1.24, the upcoming next version of VeraCrypt is currently available for beta tests and can be downloaded from here.The new version is expected to come with lots of security and functionality improvements to better secure users’ data.

FileVault 2

Looking to encrypt your data stored on devices featuring macOS and Mac hardware? FileVault 2 is a free encryption software tool we recommend checking out. FileVault 2 (FileVault full-disk encryption) uses XTS-AES-128 encryption with a 256-bit key “to help prevent unauthorized access to the information on your startup disk”.

DiskCryptor

Yet another open-source and free encryption software you can use to secure your internal and external drive, including system partition, and even ISO images, or USB flash drives or any other storage devices. Just like BitLocker, it is a full-disk encryption tool for Windows OS and includes multiple encryption algorithms such as AES, Twofish, and Serpent. The interface is simple and intuitive,  all you need to do is select the drive and click on “Encrypt” to protect your data.

Tor Browser:If you want to access the Internet anonymously, Tor browser can be a great option to encrypt your online traffic and keep your browsing activities from prying eyes. Tor Browser will block popular browser plugins such as Flash, RealPlayer, Quicktime, and others which can be manipulated into finding out your IP address. It is recommended not to install additional add-on or plugins into Tor Browser, because it may bypass Tor and compromise your privacy and security. The special Tor browser has been designed to be used by anyone who wants to conceal any browsing activity from prying eyes.

VPN: Another way to hide your online identity is to use a VPN (Virtual Private Network) which can encrypt your communication and keep it safe from constant interception attempts. This private network is able to spread across the normal Internet space, using its resources to create an encrypted channel and protect your data from hackers or the prying eyes of others. CyberGhost is a popular VPN solution to ensure that your web traffic and valuable information remain encrypted. It is the winner of the “Best Value” category at the BestVPN.com Awards 2019.

It includes a 30-day money-back guarantee for everything else and friendly support. A VPN service provider like Cyberghost helps you keep your data protected and secure your digital assets. Cyberghost works with Windows, macOS, iOS, Android, Linux or routers.

(13). Backup : Take important data backup on external drives on regular basis.

(14). Disable USB, CD drives,Bluetooth etc. from System.

(15). Have a well enough physical security to your server. Only authorized person can physically access server.